Prisma Upsert Guide: Atomic Create or Update for PostgreSQL, MySQL,

Need to guarantee a record is either created or updated in one go? Prisma’s upsert handles this atomically across PostgreSQL, MySQL, SQLite, and SQL Server. Learn the syntax, prerequisites, and how to verify results.

Problem & Takeaway

When an application must insert a new row or update an existing one without risking a race condition, the classic pattern is “check‑then‑insert or update.” In relational databases this is error‑prone and can lead to duplicate rows if two processes run simultaneously. Prisma Client’s upsert solves this by translating the logic into a single, provider‑specific SQL statement that is guaranteed to be atomic. The takeaway: use upsert whenever you need an idempotent create‑or‑update operation.

Desired Outcome

Insert a new record when a unique key is not present.
Update the existing record when the unique key matches.
Perform the operation atomically, without manual transaction handling.
Verify the result in code or via Prisma Studio.

Prerequisites

Node.js 18+ and npm installed.
Prisma CLI (install with npm i prisma --save-dev).
A database provider that supports upsert natively: PostgreSQL, MySQL, SQLite, or SQL Server.
Database user with INSERT and UPDATE privileges on the target table.
A Prisma schema file (schema.prisma) that defines a unique or primary key for the model.

Focused Procedure

Define the Model

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

The @unique on email allows Prisma to use it for the where clause.

Generate the Client
```
npx prisma migrate dev --name init
npx prisma generate
```
Run these commands in the terminal at the project root. They create the database schema and generate the type‑safe client.

Write the Upsert Script

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function upsertUser(email: string, name: string) {
  try {
    const result = await prisma.user.upsert({
      where: { email },
      create: { email, name },
      update: { name }
    })
    console.log('Upserted:', result)
  } catch (e) {
    console.error('Upsert failed:', e)
  } finally {
    await prisma.$disconnect()
  }
}

upsertUser('[contact removed]', 'Alice')

Key points:

The where clause must reference a unique key.
Only fields present in create or update are written; missing fields remain unchanged.
For composite unique keys, supply all key fields in where.

Run the Script
```
node upsert.js
```
Execute from the terminal. The script will insert a new user if the email is absent, or update the name if it already exists.
Verify the Result
1. Query the table directly: SELECT * FROM User WHERE email = '[contact removed]';
2. Open Prisma Studio: npx prisma studio and inspect the User table.
3. Check that the row count for that email remains 1 after repeated runs.

Expected Checks

After the first run, the table should contain exactly one row for the email.
Running the script again should not increase the row count but should modify the name field.
If the where clause references a non‑unique field, Prisma throws a PrismaClientKnownRequestError with code P2002.
In SQLite, the operation may be slower due to transaction emulation; monitor PRAGMA journal_mode if performance is critical.

Recovery Options

Because upsert is atomic, there is no partial state to roll back. If the operation fails, the database remains unchanged.
For debugging, examine the stack trace and error code. A P2002 indicates a unique constraint violation.
When using nested writes (e.g., creating a related Profile), Prisma wraps the entire operation in a transaction automatically. If the nested part fails, the whole upsert rolls back.
To batch multiple upserts, consider using prisma.$transaction([prisma.user.upsert(...), prisma.user.upsert(...)]) to avoid N+1 patterns.

Provider‑Specific Notes

Provider	SQL Emitted	Notes
PostgreSQL	INSERT … ON CONFLICT … DO UPDATE	Native support, high performance.
MySQL	INSERT … ON DUPLICATE KEY UPDATE	Same performance, but syntax differs.
SQL Server	MERGE	Requires a unique index on the key.
SQLite	BEGIN TRANSACTION; INSERT; IF NOT FOUND UPDATE; COMMIT	Emulated, may be slower for large tables.

Limitations & Considerations

Upsert requires a unique or primary key; composite keys must include all key fields.
Nullable fields in the where clause are treated as distinct values; two rows with null will not conflict.
Large nested upserts can hit transaction size limits (e.g., PostgreSQL’s 1 MB limit).
Upsert does not support returning multiple rows; only the affected row is returned.
For databases that lack native upsert support, Prisma emulates it with a transaction, which may impact performance.

Practical Verification Checklist

Confirm the database user has INSERT/UPDATE rights.
Run npx prisma migrate dev to ensure the schema is up to date.
Execute the upsert script and check console output for the returned object.
Query the table to verify row count and field values.
Open Prisma Studio to visually confirm the state.
Repeat the script to ensure idempotence.
If an error occurs, inspect the error code and message to determine if it’s a unique constraint violation or another issue.

Conclusion

Prisma’s upsert provides a clean, type‑safe, and atomic way to implement create‑or‑update logic across supported relational databases. By following the steps above, you can confidently integrate upsert into your application, knowing that race conditions are avoided and the database remains consistent.

Using Prisma Client Upsert: A Practical Guide to Atomic Create‑or‑Update Operations