Prisma $queryRaw with variable length parameter list

Question

I am using Prisma with Postgres. I need to use $queryRaw for a particular query due to use of unsupported tsvector type in the underlying table. In this query, I also need to use an 'in' statement where the items in the 'in' list need to be parameterised.. I have tried this

const ids = [41, 55]
const result = await prisma.$queryRaw`select * from users where id in (${ids})`;

but I get a kernel panic

PANIC in /root/.cargo/git/checkouts/rust-postgres-dc0fca9be721a90f/8a61d46/postgres-types/src/lib.rs:762:18
expected array type

I also tried this...

const result = await prisma.$queryRaw`select * from users where id in (${ids.join(',')})`;

but then I get this error...

Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`

The sql-template-tag library which I think is used by prisma, has a way of supporting this so after installing and importing it, I tried this..

const result = await prisma.$queryRaw`select * from users where id in (${join(ids)})`;

but this throws the same error.

any Idea how I can achieve this?

score 35 · Accepted Answer · answered Jul 17 '21 at 10:50

35

RTFM: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#tagged-template-helpers

import { Prisma } from "@prisma/client";

const ids = [1, 3, 5, 10, 20];
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${Prisma.join(
  ids
)})`;

answered Jul 17 '21 at 10:50

Michael Dausmann

4,202
3
35
48

6

I flagged this until I realized you were answering your own question. But thank you for finding it, because Prisma's docs can be dense and not conducive to finding what you are looking for. – Tyler Clendenin Feb 10 '22 at 01:50

score 5 · Answer 2 · edited Feb 25 '23 at 23:11

As @MichaelDausmann has mentioned above, there is a Prisma function that joins array items and formats the SQL by the types of parameters. But when you use uuid type, Prisma's join function formats the parameters adding double apostrophe. So you can take error like 'type mismatch' in PostreSQL. Alternatively, you can use native Array.join function to build a sql string and send it to Prisma raw query function using Prisma.raw function.

const sql = `select * from table where id in (${idArray.map(v => `'${v}'::uuid`).join(",")})`
result = await prisma.$queryRaw(Prisma.raw(sql))

Prisma $queryRaw with variable length parameter list

2 Answers2