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?