Upsert a conflicting row
A new record should be inserted, but an existing record with the same primary or unique key should receive updated values.
Drizzle ORM · Module
.onDuplicateKeyUpdate
.onDuplicateKeyUpdate() adds MySQL duplicate-key upsert behavior to a Drizzle insert. MySQL automatically chooses conflicts from primary keys and unique indexes, while set defines the update values.
How it works
Call this method after an insert and provide a set object describing the columns to update when MySQL detects a duplicate primary-key or unique-index value. Because MySQL chooses the conflict target automatically, no target column is passed; assigning a column to itself can emulate doing nothing.
Syntax
.onDuplicateKeyUpdate({ set: { column: value } })Parameters
| Name | Type | Description |
|---|---|---|
config | object | Configuration object containing set, whose column-value mappings define the update applied after a duplicate-key conflict. |
Returns
An insert query configured to update the conflicting row when MySQL detects a duplicate key.
Key points
- Use this method to add MySQL’s duplicate-key update behavior to an insert.
- This API corresponds to MySQL’s duplicate-key syntax rather than PostgreSQL-style
ON CONFLICT. - The database, not the method call, determines which key conflict activates the update.
- A conflict on any unique index can cause the existing row to be updated.
- The documented pattern inserts values and supplies replacement columns through
set.
Common mistakes
- You cannot select a conflict target with this method; MySQL derives it from primary keys and unique indexes.
- There is no direct do-nothing branch; use a self-assignment no-op when the conflicting row should remain unchanged.
Example: Insert with duplicate-key handling
import { sql } from 'drizzle-orm';
await db.insert(users)
.values({ id: 1, name: 'John' })
.onDuplicateKeyUpdate({ set: { name: 'John' } });
await db.insert(users)
.values({ id: 1, name: 'John' })
.onDuplicateKeyUpdate({ set: { id: sql`id` } });The first insert updates the conflicting row's name. The second performs a no-op update by assigning the id column to itself.
When to use it
Emulate do-nothing behavior
A duplicate should be tolerated without changing stored data.
Interview questions
QWhat does onDuplicateKeyUpdate do?
onDuplicateKeyUpdate adds MySQL duplicate-key update behavior to an insert. Its set object specifies the values written when a primary key or unique index conflict occurs.
Follow-up: How does it choose the conflict target? MySQL automatically checks the table’s primary key and unique indexes, and any conflicting unique index can trigger the update.
QWhat is the method’s call shape?
The method accepts an object with a set property, such as { set: { name: 'John' } }. It is chained after insert(...).values(...).
Follow-up: Can the method target a named constraint? Not through this API as documented; MySQL determines targets automatically.
QHow can an insert ignore a duplicate?
MySQL does not provide a direct do-nothing-on-conflict operation in this syntax. Drizzle’s documented workaround is assigning a column to itself, for example id: sql\id\.
Follow-up: Why is sql used? It expresses the SQL column reference so the generated update leaves that value unchanged.
Related
insertvaluessqlON DUPLICATE KEY UPDATEON CONFLICT