Prisma nested connect: syntax error at or near "ON"

TL;DR#

A Prisma create (or update) that uses connect on a many-to-many relation fails with syntax error at or near "ON" (SQLSTATE 42601) when the database server is older than PostgreSQL 9.5. Prisma writes the link rows with INSERT ... ON CONFLICT DO NOTHING, and ON CONFLICT did not exist before 9.5. That is why the same code works on a modern local PostgreSQL and fails on an old production server. The fix is the server: check SHOW server_version, then move the data to a supported PostgreSQL major (Prisma ORM 7 documents 9.6 and later) with pg_dump and pg_restore.

The error#

The Stack Overflow question (2021) shows the error as Prisma printed it in 2021, from a prisma.movie.create() call that connects genres by their unique name:

Invalid `prisma.movie.create()` invocation:
Error occurred during query execution:
ConnectorError(ConnectorError {
user_facing_error: None, kind: QueryError(Error {
 kind: Db, cause: Some(DbError {
    severity: "ERROR", parsed_severity: None, code: SqlState("42601"),
    message: "syntax error at or near \"ON\"", detail: None, hint: None,

The asker noted that everything worked on the local machine but not on the hosting server, and the accepted answer found the difference: the server ran PostgreSQL 9.2, the laptop PostgreSQL 13, and upgrading the server fixed it.

Current Prisma prints the same database error in its newer format. Reproduced on 10 October 2026 with Prisma ORM 7.10.0 (prisma-client generator, @prisma/adapter-pg, Node 22) against the official postgres:9.2 Docker image (excerpt of the log; the code-frame context lines are trimmed):

server_version: 9.2.23
prisma:query INSERT INTO "public"."Movie" ("title") VALUES ($1) RETURNING "public"."Movie"."id"
prisma:query SELECT "public"."Genre"."id" FROM "public"."Genre" WHERE (("public"."Genre"."name" = $1 AND 1=1) OR ("public"."Genre"."name" = $2 AND 1=1)) OFFSET $3
prisma:query ROLLBACK
FAILED: PrismaClientKnownRequestError P2039
Invalid `prisma.movie.create()` invocation in
/app/create.ts:12:36
→ 12   const movie = await prisma.movie.create(
Database error. Code: `42601`. Message: `syntax error at or near "ON"`

The failing insert never appears in the query log: the genre lookup is followed directly by ROLLBACK. That line also shows that the Movie row inserted a moment earlier was discarded: a failed nested connect leaves no orphan parent row behind.

Why it happens#

The schema in the question is an implicit many-to-many relation: Movie.genres Genre[] on one side, Genre.movies Movie[] on the other. Prisma manages the relation table for you. Its many-to-many documentation names it after both models in alphabetical order (here _GenreToMovie) with two columns, A and B, one per model.

A nested connect therefore does three things inside one transaction: insert the movie, look up the genres by their unique field, then insert one row per genre into _GenreToMovie. That last insert is built in Prisma's query compiler, in the create_relation_table_records function of write.rs (at the engine commit that Prisma 7.10.0 ships), which ends with:

insert.build().on_conflict(OnConflict::DoNothing).into()

On a server that accepts it, the query log shows the statement verbatim:

prisma:query INSERT INTO "public"."_GenreToMovie" ("B","A") VALUES ($1,$2),($3,$4) ON CONFLICT DO NOTHING

ON CONFLICT arrived in PostgreSQL 9.5. The 9.5 release notes list it under Queries: "The syntax is INSERT ... ON CONFLICT DO NOTHING/UPDATE. This is the Postgres implementation of the popular UPSERT command." Before 9.5 the parser does not know the clause, stops at the first word it cannot place, and reports SQLSTATE 42601 at ON. Sending the same statement by hand with psql to the 9.2 container gives the raw PostgreSQL message, with the cursor under ON:

ERROR:  syntax error at or near "ON"
LINE 1: ...lic"."_GenreToMovie" ("B","A") VALUES (1,1),(1,2) ON CONFLIC...

Nothing in your Prisma code is wrong, and no change to connect syntax makes the error go away. The ON CONFLICT DO NOTHING clause is exactly what makes a repeated connect harmless on a supported server: a link that already exists is skipped instead of raising a duplicate-key error.

Where the version boundary sits#

The reproduction ran the identical script, schema and data against five official images, changing only the server:

9.5 passing does not make it a target. The Prisma ORM 7 supported databases page lists self-hosted PostgreSQL as 9.6+, and the PostgreSQL versioning policy shows 9.5 out of support since February 11, 2021 and 9.6 since November 11, 2021 (9.2 since November 9, 2017). Prisma was only tested here at 7.10.0; this page makes no claim about the Prisma 8 release candidates.

Fix: move the database to a supported PostgreSQL#

1. Confirm the server version on both machines. Run this against the database that fails and the one that works, using the same DATABASE_URL the app uses (if psql is missing on the server, see installing the PostgreSQL client):

   bash

   psql "$DATABASE_URL" -Atc 'SHOW server_version'

   Or from the app itself, which proves you are asking the same server Prisma talks to:

   ts

   const rows = await prisma.$queryRaw<{ server_version: string }[]>`SHOW server_version`;
   console.log(rows[0].server_version);

   Anything below 9.5 explains this error. Anything below 9.6 is outside what Prisma ORM 7 supports.

2. Start a supported PostgreSQL server. Use your host's managed upgrade if it has one. Self-hosted, a current official image works. Start a container named pg17 from postgres:17, then create an empty database for the restore once it accepts connections:

   bash

   docker run -d --name pg17 -e POSTGRES_PASSWORD="$NEW_PASSWORD" -v pg17data:/var/lib/postgresql/data postgres:17
   docker exec pg17 psql -U postgres -c 'CREATE DATABASE moved'

3. Dump the old server with the new server's pg_dump. The pg_dump documentation for PostgreSQL 17 states that it "can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 9.2 are supported.)" Run the newer binary against the old host:

   bash

   docker exec -e PGPASSWORD="$OLD_PASSWORD" pg17 \
     pg_dump -h old-db.internal -U postgres -Fc movies -f /tmp/movies.dump

4. Restore into the new database:

   bash

   docker exec pg17 pg_restore -U postgres -d moved --no-owner /tmp/movies.dump

5. Point DATABASE_URL at the new server and redeploy. No Prisma schema change is needed: the relation table and its A and B columns are restored as they were.

Verify the fix#

In the reproduction, the PostgreSQL 9.2 database was dumped with pg_dump 17.11 and restored into PostgreSQL 17. The unchanged script then ran against the restored copy (excerpt; the genre listing, the Movie insert, the genre lookup and the two read-back SELECT lines are trimmed):

pg_dump (PostgreSQL) 17.11 (Debian 17.11-1.pgdg13+2)
server_version: 17.11 (Debian 17.11-1.pgdg13+2)
prisma:query INSERT INTO "public"."_GenreToMovie" ("B","A") VALUES ($1,$2),($3,$4) ON CONFLICT DO NOTHING
prisma:query COMMIT
OK: {"id":5,"title":"Heat","genres":[{"name":"Drama"},{"name":"Crime"}]}

The id of 5 also shows the sequences came across: the restored Movie sequence continued from where it stood on 9.2 (the failed attempts there had consumed values before rolling back) instead of restarting at 1. On your own server, run the failing create once and check that the query log now ends in COMMIT after the _GenreToMovie insert.

Other Prisma calls that break on the same old server#

Rewriting the query will not get you round the problem. On the same 9.2 container, each of the other four writes tested failed with the identical message:

[update + connect] FAILED: P2039 Database error. Code: `42601`. Message: `syntax error at or near "ON"`
[create + connectOrCreate] FAILED: P2039 Database error. Code: `42601`. Message: `syntax error at or near "ON"`
[createMany skipDuplicates] FAILED: P2039 Database error. Code: `42601`. Message: `syntax error at or near "ON"`
[create + nested create (new genre)] FAILED: P2039 Database error. Code: `42601`. Message: `syntax error at or near "ON"`

createMany with skipDuplicates belongs on the list even though it touches no relation: in the same write.rs, the skip-duplicates branch adds the same OnConflict::DoNothing clause to the insert. So if your app also bulk-imports rows with that flag, it will start failing the moment it runs against the old server.

The second answer on the question reports fixing a similar-looking error by removing :6543/postgres from a Supabase pooler URL. That mechanism is neither explained there nor reproduced here, and it is a different case from the one above. SHOW server_version tells you which case you are in: at 9.5 or later, the version cause is ruled out, and a pooled URL has its own known failure, the prepared statement "s0" already exists error, which has a different message.

The next error: a genre that is not in the table yet#

Once the server accepts the insert, the most likely next failure in this exact code comes from the data. The question maps genres from an external API into connect. connect only links rows that already exist, so one genre missing from the Genre table fails the whole create. Reproduced on the restored PostgreSQL 17 database, with Heist absent:

connect FAILED: P2025 An operation failed because it depends on one or more records that were required but not found. Expected 2 records to be connected, found only 1.

P2025 is the code the Prisma error reference gives for "An operation failed because it depends on one or more records that were required but not found." When a genre may or may not exist yet, the relation queries documentation points to connectOrCreate:

await prisma.movie.create({
  data: {
    title: details.title,
    genres: {
      connectOrCreate: genres.map((g) => ({
        where: { name: g.name },
        create: { name: g.name },
      })),
    },
  },
});

On PostgreSQL 17 that call created Heist and linked both genres:

connectOrCreate OK: {"title":"Inside Man","genres":[{"name":"Drama"},{"name":"Heist"}]}

As the list above shows, it is not a workaround on 9.2. It writes to the same relation table and fails there with the same 42601.

If the server is already 9.5 or later and Prisma still fails on a different constraint, for example a delete blocked by child rows, the P2003 foreign key error is the next page to read. For this error, though, the code is fine and the server is too old: once production runs a PostgreSQL that Prisma supports, the same connect that worked on your laptop works in production too.

Further reading: Drizzle TableLikeHasEmptySelection error in generic helpers. Also see Prisma Nested Create Returns Undefined: Include the Relation.