prisma: Type error in `IN` clause in postgreSQL using `$queryRaw` when upgrading to v4

Using the separator and suffix arguments of Prisma.join also appears to work. Though now that I’ve seen the map casting individual values with Prisma.sql I find that more readable.

const rows = await client.$queryRaw`
  SELECT id
  FROM repro
  WHERE id IN (${Prisma.join(ids, '::uuid,', '', '::uuid')});
`;

Hey @floriantz,

Could you try casting every single uuids as follow?

const ids = [
 '422b72f9-2ec9-4482-8a2f-527251af5606',
 'ee5f92a7-1741-48d4-b8c3-517e9621453a',
].map(uuid => Prisma.sql`${uuid}::uuid`);

const rows = await client.$queryRaw`
  SELECT id
  FROM repro
  WHERE id IN (${Prisma.join(ids)});
`;

Let us know whether that fixes your issue.

@Weakky Option 2 would be enough imho, but it requires to be well documented and easy to discover since it is a very common use-case of Prisma when using Postresql.

Hey folks,

This reason behind this issue is a bit complex so I’ll explain you how we arrived to this.

The need for more specific type casts was introduced in https://github.com/prisma/prisma/releases/tag/3.14.0. For context, it was introduced because we were suffering from lots of subtle and implicit type casts on PG’s side which were confusing users or even worse, preventing basic queries from working.

By default, Postgres infers the types of your query parameters based on where they are used. Here’s an example:

PREPARE SELECT LENGTH($1);

For this prepared statement, Postgres will answer back with a type for $1 of TEXT (because LENGTH can only accept a TEXT input). Based on which type PG infers, the PG driver encodes query parameters values as the specified type.

Unfortunately, there were many cases where Postgres could not infer the types correctly. Here’s a simple example:

await prisma.$queryRaw`SELECT ${1.5}::int`;

This query used to fail because PG always infers the type of the query param as the type it is being cast to. So in this case, $1 was inferred as an int. Consequently, the value 1.5 was encoded as an integer and sent to the database like that. No need to explain you why a float encoded as an int was not working well 🙈.

To circumvent that issue, we have decided to no longer let PG infer the types of query parameters and instead specify the type of every individual query parameters based on the incoming javascript values. In the example above, this means that 1.5 is inferred, by Prisma, as a NUMERIC type, encoded as a NUMERIC and sent to PG as a NUMERIC. Problem solved.

The downside of this approach is that, in some cases, we can’t properly infer the types of your values. In fact, Prisma can only infer the following types (there might be small mistakes in this list):

• Number -> INT(4, 8)
• Number -> NUMERIC
• String -> TEXT
• Bytes -> BYTEA
• Boolean -> BOOL
• Char -> CHAR
• Date -> TIMESTAMPTZ
• (maybe more, but I can’t think of more from the top of my head)

In OP’s case, we’ve got a problem because Prisma cannot know that the string value is actually a uuid. So the string is encoded as a string, PG fails to implicitly cast it to a uuid and so it errors. This is the reason why it fails.

Now, we wouldn’t want to go to the old behaviour, even knowing this bug. There are only two other alternatives that I can think of to improve the situation:

1. Switch to the TEXT protocol (instead of the binary protocol we currently use), which is less sensitive to these types shenanigans (and which is used by all JS drivers out there)
2. Introduce type-specific wrappers client side so that users can specify the types of their values when it is ambiguous

   import { Prisma, PrismaClient } from "@prisma/client";
   
   async function main() {
     const prisma = new PrismaClient();
   
     const ids = [
       Prisma.Uuid("178d1412-35a2-4a9d-9e6c-e85607f52733"),
       Prisma.Uuid("2515c1d5-c17a-4988-ba2b-cf5dd9232b36"),
       Prisma.Uuid("27a8fdde-1514-4338-ab08-32750bd8bb30"),
     ];
   
     const rows = await prisma.$queryRaw<{ id: string }[]>(Prisma.sql`
       SELECT id
       FROM repro
       WHERE id IN (${Prisma.join(ids)});
     `);
   
     await prisma.$disconnect();
   }
   

The TEXT protocol sounds like our best bet, but also the riskiest one.

All that to say what? There’s technically something we can do about this, and you’re right that the DX is not great. @SevInf has already created an issue to improve documentation and that’s what we should focus on short-term.

Long-term, solutions presented above feel too costly for the attraction this bug currently has.

Based on that, here’s what I’ll do:

• Keep this issue open until we do something about it. I’ll mark it as confirmed.
• Remove the kind/regression label. It’s not a regression. It’s a known limitation that we knew when introducing this fix. It was clearly noted as a breaking change in the release notes.

Finally, I know this comment doesn’t bring much to the table in terms of solution. But I hope it’ll give context to others coming to this issue.

Thanks

I had tried something similar, yet a bit different. It works indeed with my repro case ✅ . Though it would be super helpful to update Prisma.join() to accept a type casting argument as proposed by @emmanuelpineda.

I understand your choice of explicit types (actually I’m quite in favour of it), but the UX suffers a bit at the moment imho.

Seems in 3.x Prisma just sent the resulting string of userIdsToIgnore (for @emmanuelpineda) and Prisma.join(ids) (for @floriantz) in an let the database figure out how to match that with the uuid type column. In 4.x we changed some things around how raw queries type the data, and it is now probably explicitly sent as tpye text - which the database can not match to uuid itself. (Casting should somehow fix this, but I am not sure what exactly that should look like for a IN param)

I’ve tried quite a few different things to try and cast to a uuid array, but nothing worked except this version which does not use an IN query:

WHERE id = ANY (ARRAY[${Prisma.join(ids)}]::uuid[])

Though I’d rather use IN queries. But I haven’t found a way to cast that works, I always end up with an explicit text type.

Since these kind of queries are quite common, it might be good to have an example on how to cast those.