$executeRawUnsafe: incorrect binary data format in bind parameter 6 #23872
Description
Bug description
As Prisma does not support batched upserts, I wanted to implement them with Kysely ... this worked fine when using also the Kysely DB provider (pq driver), but when using prisma-extension-kysely which is officially recommended from your side, the insert into query failed, with the error in the header. Internally it uses the $executeRawUnsafe method, which has a bug to send the correct bytes to my Postgres database ... the transaction model has this structure:
type in Prisma
{
projectId: string
storeId: string
fromTimestamp: Date
toTimestamp: Date
productName: string
productPrice: number
productAmount: number
invoiceAmount: number
}type in Kysely
{
projectId: string;
storeId: string;
fromTimestamp: Timestamp;
toTimestamp: Timestamp;
productName: string;
productPrice: number;
productAmount: number;
invoiceAmount: Generated<number>;
}this is the method that produces the error:
async storeTransactionGroups(
projectId: string,
transactionGroups: TransactionGroupDto[],
chunkSize: number = DEFAULT_DATABASE_BATCH_SIZE,
): Promise<void> {
const transactionModels: Transaction[] = transactionGroups.map((x) =>
this.convertTransactionGroupToTransactionModel(projectId, x),
);
await this.prismaService.client.$transaction(async (tx) => {
await Promise.all(
[...chunks(transactionModels, chunkSize)].map(
(transactionModelChunk) => {
const batchUpsertQuery = tx.$kysely
.insertInto('Transaction')
.values(transactionModelChunk)
.onConflict((ocb) =>
ocb
.columns([
'projectId',
'storeId',
'fromTimestamp',
'toTimestamp',
])
// eslint-disable-next-line max-nested-callbacks
.doUpdateSet((eb) => ({
productName: eb.ref('excluded.productName'),
productPrice: eb.ref('excluded.productPrice'),
productAmount: eb.ref('excluded.productAmount'),
invoiceAmount: eb.ref('excluded.invoiceAmount'),
})),
);
return batchUpsertQuery.execute();
},
),
);
});
}somehow the productPrice is transmitted incorrectly ... when inserting multiple values isolated as single values the above exception is thrown on the second and third entry, never on the first ... furthermore when hardcoding productPrice to 0 or 12.7 also no exception is thrown, I am completely puzzled what your Prisma engine is doing under the hood to produce such kind of errors ... anyway in the meantime I just use kysely with the native pg driver which works just fine and has no such bugs ...
If you need more input just ask
How to reproduce
check the description
Expected behavior
query finished with your recommended extension, there must be an error in your engine, as the data seems fine, I double and triple checked my input data and its also validated with class-validator ...
Prisma information
generator prisma-client-js {
provider = "prisma-client-js"
binaryTargets = ["native"]
}
generator prisma-client-kysely {
provider = "prisma-kysely"
fileName = "kysely.types.ts"
output = "../backend/bff/lib/service-utils/src/lib/database"
}
generator prisma-client-py {
provider = "prisma-client-py"
interface = "sync"
recursive_type_depth = -1
}
datasource db {
provider = "postgresql"
url = env("DB_TIMESCALE_DB_URL")
}
model Transaction {
projectId String
storeId String
fromTimestamp DateTime
toTimestamp DateTime
productName String
productPrice Float
productAmount Float
invoiceAmount Int @default(1)
@@id([projectId, storeId, fromTimestamp, toTimestamp])
@@index([projectId, storeId, fromTimestamp, toTimestamp])
}
model Event {
projectId String
storeId String
fromTimestamp DateTime
toTimestamp DateTime
eventType String
eventName String
@@id([projectId, storeId, fromTimestamp, toTimestamp])
@@index([projectId, storeId, fromTimestamp, toTimestamp])
}
model WeatherMeasurement {
projectId String
storeId String
fromTimestamp DateTime
toTimestamp DateTime
temperature Float
humidity Float?
cloudCoverage Float?
windSpeed Float?
precipitation Float?
@@id([projectId, storeId, fromTimestamp, toTimestamp])
@@index([projectId, storeId, fromTimestamp, toTimestamp])
}
model Forecast {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
projectId String
storeId String
fromTimestamp DateTime
toTimestamp DateTime
timezone String
status ForecastStatusEnum
resolution ForecastResolutionEnum
results ForecastResult[]
message String?
createdAt DateTime @default(now())
lastUpdatedAt DateTime @default(now()) @updatedAt
}
model ForecastResult {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
forecastId String @db.Uuid
forecast Forecast @relation(fields: [forecastId], references: [id], onUpdate: Cascade, onDelete: Cascade)
fromTimestamp DateTime
toTimestamp DateTime
predictedValue Float
upperBoundPredictedValue Float
lowerBoundPredictedValue Float
}
enum ForecastStatusEnum {
processing
finished
error
}
enum ForecastResolutionEnum {
daily
hourly
half_hourly
}Environment & setup
- OS: macOS (my dev computer)
- Database:PostgreSQL
- Node.js version: 20
Prisma Version
5.11.0