2

I am using Typescript + Prisma + Postgres and I am running into problems with passing bytea/Buffer parameters to raw queries.

Let's say I have a following DB schema

model Item {
  id       BigInt @id @default(autoincrement()) @db.BigInt
  hash     Bytes  @unique
...
}

I can query Items by their hashes using a simple query like this without any problems

const itemHexHashes = ['ca6854726b0139254b7e5c4831c87ac043a938fa3543b5eb0ce0906e124483e9', ...]
const itemBufferHashes = itemHexHashes.map((h) => (Buffer.from(h, 'hex')))
const itemIds = await prisma.item.findMany({
  where: {
    hash: {
      in: itemBufferHashes
    }
  },
  select: {
    id: true
  }
})

but I can't find a way of passing the Buffer parameters using $queryRaw (the actual query is more complicated), because following simply doesn't work

const itemIds = await prisma.$queryRaw`
  SELECT id
  FROM "Item"
  WHERE hash IN (${Prisma.join(itemBufferHashes)})
`

When I check Postgres logs, the first query interprets the hash correctly as

parameters: $1 = '\xca6854726b0139254b7e5c4831c87ac043a938fa3543b5eb0ce0906e124483e9'

but the raw query does something weird and parses the parameter as

parameters: $1 = '\x7b2274797065223a22427566666572222c2264617461223a5b3230322c3130342c38342c3131342c3130372c312c35372c33372c37352c3132362c39322c37322c34392c3230302c3132322c3139322c36372c3136392c35362c3235302c35332c36372c3138312c3233352c31322c3232342c3134342c3131302c31382c36382c3133312c3233335d7d'

I tried several things, e.g. instead of hash IN (...) doing hash = ANY(...), or trying to send list of strings in the query - the closest I got to the real value of hash was, when I converted the Buffers back to string using ascii encoding, i.e.

const itemHashes = itemBufferHashes.map((buffer) => (buffer.toString('ascii')))

then the used parameter was

parameters: $1 = '\x4a6854726b0139254b7e5c4831487a404329387a3543356b0c60106e12440369'

which is almost the same as the correct one

parameters: $1 = '\xca6854726b0139254b7e5c4831c87ac043a938fa3543b5eb0ce0906e124483e9'

but what I noticed is that every second byte is incorrect, if its value is greater than 8, then 8 is subtracted...

What the hell is going on?

radoh
  • 4,554
  • 5
  • 30
  • 45
  • I have found a probably related Prisma bug https://github.com/prisma/prisma/issues/11834 – radoh Mar 21 '22 at 19:27

0 Answers0