PrismaP
Prisma•2mo ago•
5 replies
Hornster

sql-template-tag in raw queries inconsistently breaking

I am using prisma in a nextjs application, where I use raw queries in several places due to limited support from prisma (e.g. unsupported postgis types, or bulk upserts).

A bulk upsert might look like this:
const inserts: Prisma.Sql[] = [];

// bulk insert based on e.g. a parsed csv file
for (const row of rows) {
  inserts.push(
    Prisma.sql`(${row.ts}::timestamp, ${row.scenario_id}, ${row.entity_id}, ${row.measurements}::jsonb)`
  );
}

await prismaClient.$executeRaw`INSERT INTO entity_measurements (
    ts,
    scenario_id,
    entity_id,
    measurements
  )
  VALUES ${Prisma.join(inserts)}
  ON CONFLICT (ts, scenario_id, entity_id) DO UPDATE SET measurements = EXCLUDED.measurements
`;


Using prisma's query event I can log both the resulting query and the list of parameters. If the query works, it produces a query where the ${Prisma.join(inserts)} is replaced with the full list of parameters in the form of ($1::timestamp, $2, $3, $4::jsonb), ($5::timestamp, $6, $7, $8::jsonb) ..., and the param list simply contains all the actual data - just as expected

However, very often this query produces a syntax error, in which case the following can be observed: ${Prisma.join(inserts)} is replaced with just one parameter $1, and params contains an object, where the actual parameters are stored in 
values
, accompanied with the filler strings in strings. example: 
const query = "INSERT INTO entity_measurements (ts, scenario_id, entity_id, measurements) VALUES $1  ON CONFLICT (ts, scenario_id, entity_id) DO UPDATE SET measurements = EXCLUDED.measurements"
const params = [{
  "values":["2025-12-09 12:00:00.000 Z", 1, 1, "{\"field\":\"value\"}"],
  "strings":["(","::timestamp, ",", ",", ","::jsonb)"]
}]

This will then throw a syntax error:
Raw query failed. Code: '42601' . Message: ERROR: syntax error at or near "$1"

It seems like for some reason the sql-template-tag breaks, and I have not found an explanation for why. Using the example above, the exact same input csv will sometimes work, and other times it produces the mentioned error.

For context:
Im running prisma 6.17.1 in nextjs 15.5.7, the target database is a postgres 17 instance. Previously this usually only happened in the next dev build, and restarting the dev server fixed it, but now it also happens in a production build. For production and development turbopack is being used.
Due to a multi-tenant scheme, I have to create a dedicated prisma client for every tenant, so that I can inject the tenant's schema name in the connection string.

Has anyone encountered a similiar issue, or might have a clue why these raw queries break so inconsistently? Help would be greatly appreciated.
Was this page helpful?