---
title: upsert
description: Create a record if it doesn't exist, or update it if it does
icon: refresh-cw
---

`upsert` updates the record matching `where` if it exists, otherwise creates it — in a single atomic operation.

```ts
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.

:::warning
The `where` may narrow the unique lookup with non-unique scalar filters (see
[Narrowing a Unique Lookup](/docs/client/find-unique#narrowing-a-unique-lookup)),
but think twice before doing it here. When the unique key matches a row the
filter excludes, there is nothing to update, so the **create** branch runs — and
walks straight into the unique key that row already holds. The
`UniqueConstraintError` surfaces immediately: it is a genuine conflict, not a
race, so it is not retried. That is the same outcome a hand-written
find-then-create would produce, and the P2002-equivalent Prisma reports for the
same shape.

When the create branch runs and *succeeds*, the record you get back is the row
that was just created — always. It is addressed by its own identity, never by the
`where`. Your `create` payload is under no obligation to satisfy any part of the
`where`, not the filter half and not the unique key itself; whatever it writes is
what comes back.
:::

## 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 in `create`;
- 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.

:::info
`$transaction([...])` stays indivisible. A scalar generated-output upsert folds
into one statement on a RETURNING adapter. PostgreSQL-family relation-bearing
create graphs can also fold when their result projection does not read a table
written by a sibling mutation. If an operation still needs generated output
across internal statements and the adapter has no exact one-batch lowering,
VibORM refuses the array before provider effects. Awaiting the operation on its
own can use the default transaction or guarded-segment route instead.
:::

## Options

```ts
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

```ts
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

```ts
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

```ts
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.
