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 be able to name that row. It works this out from your create data alone — never from the where — and 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);
• nothing at all, when the model’s single primary key is a database-generated increment — the insert captures the value the database assigns.

Almost every model satisfies at least one of these without you doing anything. The exception is a model whose primary key is a generated compound key and which has no other unique constraint: nothing in create can name the inserted row, so upsert refuses with an UnsupportedOperationError instead of guessing. The refusal only fires when the create branch is actually taken — the same call updates normally when the row exists — and such a model cannot be written through create either (use createMany, or add a unique constraint).

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
});

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(),
    },
  });
}

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.