Basic upsert
Create-or-update records keyed by a unique identifier without issuing separate select and update statements.
Drizzle ORM · Module
.onConflictDoUpdate
.onConflictDoUpdate() performs an upsert: it updates an existing row when an insert conflicts with a selected target. Use set, plus optional targetWhere and setWhere conditions, to control the conflict update.
How it works
Attach this method to an insert query to perform an upsert: when the chosen conflict target matches an existing row, Drizzle applies the assignments in set instead of inserting a duplicate. For more selective behavior, targetWhere filters the conflict target and setWhere filters whether the update itself may run.
Syntax
.onConflictDoUpdate({ target, set, targetWhere?, setWhere? })Parameters
| Name | Type | Description |
|---|---|---|
target | column | column[] | Column or column array that identifies the conflict; it may represent a composite index or composite primary key. |
set | record of column updates | Assignments applied to the conflicting existing row. |
targetWhere | SQL expression | Condition attached to the conflict target, useful for partial indexes. |
setWhere | SQL expression | Condition that controls whether the update clause runs. |
Returns
An insert query builder configured to update an existing row when the specified conflict occurs.
Key points
- The method turns an insert into an upsert by applying the configured update when insertion encounters a conflict.
- Use the two dedicated condition properties when the conflict target and update clause need different filtering logic.
- Pass an array to target when the uniqueness rule spans multiple columns.
- Use excluded in a SQL expression to read values from the attempted insert during the conflict update.
Common mistakes
- Do not treat the two conditions as interchangeable: use targetWhere for a partial-index conflict condition and setWhere for the update condition.
- This ON CONFLICT-based method is not available for SQL Server; use the database-specific upsert approach instead.
Example: Update on insert conflict
import { sql } from 'drizzle-orm';
import { db } from './db';
import { users } from './schema';
await db.insert(users)
.values({ id: 1, name: 'Dan' })
.onConflictDoUpdate({
target: users.id,
set: { name: 'John' },
});Inserts a user and changes the existing row's name when the insert conflicts with the users.id constraint.
When to use it
Composite-key upsert
Write updates against rows identified by more than one column.
Conditional conflict handling
Apply different SQL conditions to a partial-index target and to the update operation.
Proposed-value updates
Carry values from the attempted insert into the update expression with excluded.
Interview questions
QWhat does onConflictDoUpdate do?
It performs an upsert. The insert is attempted first, and if the target conflicts, the existing row is updated with the assignments supplied in set.
Follow-up: How would you update from the proposed conflicting row? Use an SQL expression referencing excluded, which represents the row that was proposed for insertion but was not inserted.
QHow do you handle composite conflicts?
target identifies the conflicting column or columns. It can also be an array, allowing composite indexes or composite primary keys to determine the conflict.
Follow-up: How would you increment an existing quantity? Put a SQL expression such as sql${inventory.quantity} + 100 in set for the quantity field.
QWhat is the difference between targetWhere and setWhere?
Use targetWhere for a condition belonging to the conflict target, such as a partial index condition. Use setWhere for a condition on whether the update clause should execute.
Follow-up: Why are both fields needed? SQL permits these conditions in separate positions within the ON CONFLICT clause, so one generic where field cannot express both meanings.
Related
insertonConflictDoNothingsqlexcludedsetWheretargetWhere