I am attempting to run an `executRaw` query passing in a lis Prisma #prisma-client

I am attempting to run an `executRaw` query, passi...

William Harding

06/25/2022, 4:32 PM

I am attempting to run an

executRaw

query, passing in a list of numbers (

customerIds

) (joined on

","

to create a string). I am getting this error:

Copy code

Raw query failed. Code: `08P01`. Message: `db error: ERROR: insufficient data left in message`

This is the the query that does not work,

Copy code

await prisma.$executeRaw`UPDATE "Customer" SET fields = fields || ${fieldUpdates} WHERE ("organizationId" = ${organizationId} AND id IN (${customerIds.join(",")}));`

It works if I hardcode the values, like this ,

Copy code

await prisma.$executeRaw`UPDATE "Customer" SET fields = fields || ${fieldUpdates} WHERE ("organizationId" = ${organizationId} AND id IN (1,2,3)}));`

Console logging my list out, produces the correct results,

Copy code

1,2,3

I wasn't able to find this as a bug specifically against Prisma, but wanted to check here if it is a known bug, or if there is something I'm doing obviously wrong.

✅ 1

Richard Ward

06/25/2022, 9:38 PM

Hi @William Harding - I think you may be getting caught by the "restrictions" for

queryRaw

and

executeRaw

Richard Ward

06/25/2022, 9:38 PM

Check the

Be aware that:

section of https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#executeraw

Richard Ward

06/25/2022, 9:39 PM

What you may need to do is :

Copy code

const query = Prisma.sql`UPDATE "Customer" SET fields = fields || ${fieldUpdates} WHERE ("organizationId" = ${organizationId} AND id IN (${customerIds.join(",")}));`;

await prisma.$executeRaw`${query}`;

Richard Ward

06/25/2022, 9:40 PM

I've just done something similar for a query I'm working on and have:

Copy code

let query = Prisma.sql`select min(salary) as minSalary, max(salary) as maxSalary, avg(salary) as avgSalary 
from Employee`;
  if (onContract) {
    query = Prisma.sql`${query} where onContract = ${onContract} `;
  }
  const result = await prisma.$queryRaw`${query}`;

💯 1

William Harding

06/25/2022, 11:32 PM

Thanks @Richard Ward! I'll give that a shot!

William Harding

06/25/2022, 11:37 PM

Sadly, it appears to be doing the same thing - but thanks for responding nonetheless!

William Harding

06/26/2022, 2:13 AM

For posterity, I figured out that using

= ANY

syntax instead of

IN

allows it to work. The following query worked:

Copy code

prisma.$executeRaw`UPDATE "Customer" SET fields = fields || ${fieldUpdates} WHERE ("organizationId" = ${organizationId} AND id = ANY (${customerIds}));`;

🙌 2

prisma rainbow 1

Ilia Kostichenko

06/27/2022, 6:04 PM

Did u try prisma function to build IN queries?

IN (${Prisma.join(arrayVar)})

71 Views

Open in Slack

Previous Next