upsert
Create a record if it doesn't exist, or update it if it does
upsert updates the record matching where if it exists, otherwise creates it — in a single atomic operation.
const user = await client.user.upsert({
where: { email: "alice@example.com" },
create: {
email: "alice@example.com",
name: "Alice",
},
update: {
name: "Alice Updated",
},
});
upsert is atomic and race-safe: concurrent upserts on the same key never fail with a duplicate error — one creates, the other updates.
What the create branch needs in create
To hand back the row it just inserted, upsert has to identify that exact row.
It never assumes that the where names the created row. Any one of these is
enough:
- every primary key field, as a literal;
- every column of some unique constraint of the model (a
.unique()column, or a whole compound unique), explicitly written increate; - a database-generated primary-key member that the active adapter can publish exactly from the INSERT. This includes a compound key whose generated members are returned by the provider.
On a non-returning adapter, one generated increment key can use the driver’s
exact statement-local insert ID. A generated compound key has no singular insert
ID, but it can still work when create explicitly writes another complete public
unique key: VibORM uses that stable key for one focused read and publishes every
generated primary-key member from the inserted row. Omitted/defaulted, null,
SQL-expression, incomplete compound, and raw-index-only values are not stable
locators.
If neither provider output nor a stable explicit locator can identify the row,
the create branch refuses with UnsupportedOperationError before it writes. The
refusal is branch-local: the same call still updates normally when the row exists.
Options
await client.user.upsert({
where: { ... }, // Required: unique identifier
create: { ... }, // Required: data for new record
update: { ... }, // Required: data for existing record
select: { ... }, // Optional: fields to return
include: { ... }, // Optional: relations to include
});
Conditional updates
targetWhere and setWhere are optional additional filters on an existing row
located by where. When supplied together, both must match before the update
arm runs. If either does not match, VibORM returns the existing row unchanged.
If where finds no row, the create arm still runs. These are VibORM conditional
update guards, not PostgreSQL ON CONFLICT target or conflict-update clauses.
An empty update: {} keeps a found row unchanged, including its updatedAt.
A missing row still uses create.
With Relations
const user = await client.user.upsert({
where: { email: "alice@example.com" },
create: {
email: "alice@example.com",
name: "Alice",
profile: {
create: { bio: "New user" },
},
},
update: {
name: "Alice Updated",
profile: {
upsert: {
create: { bio: "New profile" },
update: { bio: "Updated profile" },
},
},
},
include: { profile: true },
});
Examples
User Settings
async function updateSetting(userId: string, key: string, value: string) {
return client.userSetting.upsert({
where: {
userId_key: { userId, key }, // Compound unique
},
create: {
userId,
key,
value,
},
update: {
value,
},
});
}
Increment Counter
async function recordPageView(pageId: string) {
return client.pageStats.upsert({
where: { pageId },
create: {
pageId,
views: 1,
lastViewedAt: new Date(),
},
update: {
views: { increment: 1 },
lastViewedAt: new Date(),
},
});
}
On PostgreSQL and SQLite, when where names one unique key that create spells
with the same values, a counter upsert (increment, decrement, multiply,
divide) runs as one INSERT … ON CONFLICT DO UPDATE statement; an exact
decimal’s multiply/divide and list operators keep the locate-then-write
route. MySQL always uses that route.
vs Create + Update
Prefer upsert over a find-then-create/update sequence: it’s an atomic operation with no exposed race window. Use separate queries only when the two branches have genuinely different logic or you need to know which one ran.