prisma $queryRaw doesn't work with single quotes in the string

Question

i have a query that goes

select count(id), status,  "createdAt"::date from "ProjectLog" where "projectId" = (select id from "Project" where slug = ${id}) and "createdAt" > current_date - interval '${interval} day' group by "createdAt"::date, status;

i've also tried user Prisma.sql to pass the value inside the quotes but it keeps throwing error that it expected 1 argument but fount 2.

i did not have this issue with prisma 2.20.1 this issue is only happening on version 3.3.0

Query: select count(id) as count, status,  "createdAt"::date from "ProjectLog" where "projectId" = (select id from "Project" where slug = $1) and "createdAt" >  current_date - interval '$2 day' and key notnull group by "createdAt"::date, status
Param: ["main","30"]
PrismaClientKnownRequestError:
Invalid `prisma.queryRaw()` invocation:


  Your raw query had an incorrect number of parameters. Expected: `1`, actual: `2`.
  code: 'P1016',
  clientVersion: '3.3.0',
  meta: { expected: 1, actual: 2 }
}

any suggestions ? i'm not sure if this is a bug.

Tendril

problem solved? – Redeem

nope, still having this issue – Tendril

the error is because query is not expecting a variable there. may be you try with string interpolation, and replace a block of query with a variable prisma.io/docs/concepts/components/prisma-client/… have a look at this link. may be it helps – Redeem

Answer 1

Try to use:

const interval = num + ' days';

${interval}::TEXT::INTERVAL

instead of interval '${interval} day'

This is work for me!

Foist

Answer 2

didn't find an elegant solution in the prism documentation, I couldn't even find one, but there is a way that works...you can try this ${'variable'}

Leonardaleonardi

Answer 3

Another alternative, if the source for interval is safe (e.g. defined in your code elsewhere, not from user input) is to leverage Prisma.raw for that whole string, surrounding it with single quotes per usual:

const interval = 3;

const request = prisma.$queryRaw`
select count(id), 
       status,
       "createdAt"::date 
from "ProjectLog" 
where "projectId" = (select id 
                     from "Project" 
                     where slug = ${id}) 
  and "createdAt" > current_date - interval '${Prisma.raw(`${interval} day`}' 
group by "createdAt"::date, 
         status`;

Be careful with $queryRaw in combination with Prisma.raw, however. The values placed in there aren't parameterized, but rather are included in the query text itself, so this code would be vulnerable to SQL injection attacks if you were getting interval from an unsafe source.

Annul