Search code examples
postgresqlprisma

Prisma $queryRaw with variable length parameter list

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?

Solution

  • 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
)})`;