Prisma•3mo ago•

5 replies

sql-template-tag in raw queries inconsistently breaking

ORM

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
`;

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)}

${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) ...

($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)}

${Prisma.join(inserts)}

is replaced with just one parameter

$1

$1

, and params contains an object, where the actual parameters are stored in

values

values

, accompanied with the filler strings in

strings

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)"]
}]

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"

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.

Prisma•3mo ago•

5 replies

Hornster

sql-template-tag in raw queries inconsistently breaking

ORM

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
`;

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)}

${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) ...

($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)}

${Prisma.join(inserts)}

is replaced with just one parameter

$1

$1

, and params contains an object, where the actual parameters are stored in

values

values

, accompanied with the filler strings in

strings

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)"]
}]

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"

Raw query failed. Code:  '42601' . Message:  ERROR: syntax error at or near "$1"

sql-template-tag in raw queries inconsistently breaking

sql-template-tag in raw queries inconsistently breaking

Similar Threads

Similar Threads

Similar Threads