Upserts and RETURNING

You can already insert a row and update a row by id. But many of the writes a web app does aren’t cleanly one or the other. A payment provider sends you a webhook, then sends the same webhook again because it never saw your 200. A user clicks “Join” on an organization they might already belong to. A settings panel saves the same row every time someone toggles a switch. Each has the same shape: insert this if it’s new, update it if it already exists.

The obvious approach is to read the row, check whether it’s there, then branch to an insert or an update. That code reads correctly and ships, then produces duplicate rows in production that you can never reproduce on your laptop. So this lesson answers two questions: how do you make “create or update” a single statement that can’t break under concurrent load, and how do you get the row you just wrote back without asking the database for it a second time?

The two tools are the upsert and RETURNING. The upsert is Postgres’ ON CONFLICT clause, which Drizzle exposes as onConflictDoNothing and onConflictDoUpdate; RETURNING is what Drizzle attaches with .returning(). They are independent features that tend to travel together, and by the end of this chapter every mutation you write ends in .returning().

The read-then-write race

Start with the version that first comes to mind, since seeing why it breaks is what makes the fix land. Here is “add this user to this organization, but don’t duplicate them if they’re already a member,” written the obvious way:

const existing = await db.query.memberships.findFirst({
  where: (m, { and, eq }) =>
    and(eq(m.userId, userId), eq(m.organizationId, orgId)),
});

if (existing) return existing;

const [created] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .returning();
return created;

Read top to bottom, it looks airtight: look the row up, hand it back if it’s there, otherwise create it. The flaw isn’t the logic, it’s the gap between the two steps. These are two separate trips to the database with your application code running in between, and nothing stops a second request from slipping into that gap.

Picture two requests arriving at almost the same instant, because the user double-clicked “Join” or two tabs fired the same action. Both run the findFirst before either has inserted anything, so both get back “no such membership,” both conclude they need to create it, and both run the insert.

STEP 1 / 4 Request A reads first No membership exists yet, so the database answers: absent.

time T1 T2 T3 T4

Request A

SELECT memberships → absent

INSERT memberships ✓ row created

Request B

SELECT memberships → absent

INSERT memberships ✕ collision

Request A reads first and finds nothing. The membership genuinely doesn't exist yet.

STEP 2 / 4 Request B reads a heartbeat later Before A has written anything — so it sees absent too. Both reads are done; both saw nothing.

time T1 T2 T3 T4

Request A

SELECT memberships → absent

INSERT memberships ✓ row created

Request B

SELECT memberships → absent

INSERT memberships ✕ collision

B reads in the window before A writes, sees the same emptiness, and independently decides to create the row.

STEP 3 / 4 Request A inserts A real membership row now exists in the table. B is still holding its stale absent.

time T1 T2 T3 T4

Request A

SELECT memberships → absent

INSERT memberships ✓ row created

Request B

SELECT memberships → absent

INSERT memberships ✕ collision

A's insert lands and a real row now exists, but nothing tells B, which is still acting on the absence it read at T2.

STEP 4 / 4 Request B inserts the row it decided was missing It collides with A's. Either a duplicate slips in, or the unique constraint rejects it and the request crashes.

time T1 T2 T3 T4

Request A

SELECT memberships → absent

INSERT memberships ✓ row created

Request B

SELECT memberships → absent

INSERT memberships ✕ collision

B's decision was made on data that's now stale — A's row already exists.

B inserts the row it decided was missing. Without a unique constraint that's a duplicate; with one, B crashes on the violation.

Prev

Next

This is a race condition , and it stays invisible when you test. On your machine, one request at a time, the read always finishes before the next request starts, so nothing slips into the gap and the code looks correct forever. It only fails when two requests genuinely overlap, which is what production traffic does and your test suite doesn’t.

The fix is to remove the gap. If the database can do “insert it, or update it if it’s already there” as a single statement, there is no in-between moment for a second request to slip into: Postgres holds a lock on the row for the whole statement, so the two requests take turns instead of overlapping. That statement is INSERT ... ON CONFLICT, and it is atomic .

This is the same instinct as reaching for a UNIQUE constraint instead of checking for duplicates in application code: push the guarantee down into the database, where it holds without your code coordinating it.

Watch video

CockroachDB defines the upsert from scratch and shows the Postgres INSERT ... ON CONFLICT form this lesson is built on, including why it skips the read-first step (4 min).

onConflictDoNothing: skip on conflict

The simplest thing to do on a conflict is nothing, and it fits a common case: a webhook you might receive twice.

When a provider like Stripe sends you an event, it expects a 200 back. If it never sees one, because your server was slow, a deploy restarted mid-request, or the network hiccuped, it assumes the event was lost and sends the same event again. Your handler has to survive that: processing an event twice must do no more harm than processing it once. The handler must be idempotent , and the cleanest way there is to record each event under its provider-assigned delivery id and refuse to record the same id twice.

await db
  .insert(webhookDeliveries)
  .values({ deliveryId: event.id, payload: event })
  .onConflictDoNothing({ target: webhookDeliveries.deliveryId });

Read it as one sentence: insert this delivery, and if a row with the same deliveryId already exists, do nothing. The first event inserts a row. On a redelivery the deliveryId collides, and instead of throwing a unique-violation error, Postgres skips the insert. The handler now does real work exactly once and nothing every time after.

Note

target names the column that defines a conflict. It works only because deliveryId carries a UNIQUE constraint in the schema; without one there’s no collision for Postgres to catch. The next section returns to this dependency, the part of upserts that most often goes wrong.

But the statement resolves the same way whether the row was inserted or skipped, so the two outcomes look identical from the outside. A real handler usually cares about the difference: it wants to do the downstream work, provision the subscription, send the receipt, only on the first delivery. Attach .returning() and an insert that happened hands back the row, while one that was skipped hands back an empty array, so you can branch on whether you got a row.

No signal

await db
  .insert(webhookDeliveries)
  .values({ deliveryId: event.id, payload: event })
  .onConflictDoNothing({ target: webhookDeliveries.deliveryId });

The write happens, but you learn nothing. A fresh insert and a skipped one resolve the same way, so you can’t tell a first delivery from a repeat, or decide whether to do the downstream work.

With returning

const [delivery] = await db
  .insert(webhookDeliveries)
  .values({ deliveryId: event.id, payload: event })
  .onConflictDoNothing({ target: webhookDeliveries.deliveryId })
  .returning();

if (!delivery) {
  return; // already processed — this is a redelivery
}
// first time we've seen this event — safe to do the real work

Now the skip is observable. .returning() gives you [row] on a real insert and [] on a skip, so an empty result means you’ve seen this event before.

Drizzle lets you omit target, in which case any unique conflict triggers the no-op. Naming the column is clearer, and it becomes required once a table has more than one unique constraint you might hit, since Postgres can’t otherwise tell which one you mean. Name the target by default.

Note

You’ll build the full webhook handler, signature verification and replay protection included, much later in the course; here you’re learning the write shape it’s built around.

Watch video

Software Nuggets contrasts the read-then-branch approach with INSERT ... ON CONFLICT DO NOTHING and DO UPDATE, and walks through the “no unique or exclusion constraint matching the ON CONFLICT specification” error (7 min).

onConflictDoUpdate: update on conflict

The other branch on a conflict is to update the row that already exists. This is the full upsert, the one “find or create” actually needs.

Take the membership example from the start of the chapter: adding a user to an organization. The memberships table carries a composite unique constraint on (userId, organizationId), so a user can be a member of a given org at most once. That constraint is what lets you write “find or create” as a single statement: insert this membership, and if the user is already in this org, update their role to the one you just tried to insert.

const [membership] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .onConflictDoUpdate({
    target: [memberships.userId, memberships.organizationId],
    set: { role: sql`excluded.role` },
  })
  .returning();

const [membership] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .onConflictDoUpdate({
    target: [memberships.userId, memberships.organizationId],
    set: { role: sql`excluded.role` },
  })
  .returning();

The values to insert if no row exists yet: the create half, an ordinary insert with the default 'member' role.

const [membership] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .onConflictDoUpdate({
    target: [memberships.userId, memberships.organizationId],
    set: { role: sql`excluded.role` },
  })
  .returning();

The columns that define a conflict. The target references the existing unique constraint, it does not create one, so it must list both columns the constraint covers. If no unique covers them, this errors at runtime.

const [membership] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .onConflictDoUpdate({
    target: [memberships.userId, memberships.organizationId],
    set: { role: sql`excluded.role` },
  })
  .returning();

What to change when the row already exists. excluded is the row Postgres tried to insert but couldn’t, exposed under that fixed alias, so excluded.role is the 'member' you passed to .values(). The line reads: set the existing row’s role to the value the insert proposed.

const [membership] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .onConflictDoUpdate({
    target: [memberships.userId, memberships.organizationId],
    set: { role: sql`excluded.role` },
  })
  .returning();

Hand back the resulting row, inserted or updated, typed exactly like a select from memberships: a Membership. No second query to find out what happened.

← Previous 1 / 1 Next →

You could restate the literal with set: { role: 'member' }, and for one hardcoded value that’s harmless. excluded earns its place when the value isn’t a literal. Picture a bulk upsert of fifty memberships, each with its own role: a literal would stamp every conflicting row with 'member', while excluded.role gives each row the role its own proposed insert carried.

set: { role: sql`excluded.role` }

Caution

The conflict target must point at a column or set of columns that already carries a UNIQUE or PRIMARY KEY constraint. Otherwise Postgres rejects the statement at runtime:

there is no unique or exclusion constraint matching the ON CONFLICT specification

This is a runtime error, not a compile error. Drizzle can’t see your table’s constraints from the query, so the type checker stays silent and you find out only when the query runs.

Some columns are insert-only, such as id and especially createdAt. Leave them out of the set: copying createdAt from excluded overwrites the original creation timestamp every time the row is touched.

.returning(): read the written row back

.returning() has ridden along on every example so far, and it isn’t upsert-specific: it belongs on the tail of every mutation.

Here is the problem it solves. An ordinary insert resolves successfully but tells you nothing about what it wrote. So if you need the generated id, or any column the database filled in (a defaultNow() timestamp, a default status), the obvious move is to select the row back. That works, but the comparison below shows why it’s the wrong instinct.

Two trips

await db.insert(invoices).values({ organizationId: orgId, amountDue: '0.00' });

const [invoice] = await db
  .select()
  .from(invoices)
  .where(eq(invoices.id, /* …but we don't know the id yet */));

A select right after a write is the smell. Two round-trips instead of one, a fresh race window between them where another request can change the row, and a result you type by hand because it came from a separate query. Worst of all, the insert never told you the generated id, so you have nothing to filter on.

One statement

const [invoice] = await db
  .insert(invoices)
  .values({ organizationId: orgId, amountDue: '0.00' })
  .returning();

One trip, one type. The write hands back the row it wrote, generated id and all, typed exactly like a select from invoices. No second query, no race window, nothing to type by hand.

So treat a select right after a write as a sign to reach for .returning() instead: the write already touched those rows, so ask it to hand them back.

It rides on every write. An update returns the rows it changed, a delete returns the rows it removed:

const [archived] = await db
  .update(invoices)
  .set({ deletedAt: new Date() })
  .where(eq(invoices.id, invoiceId))
  .returning();

const [removed] = await db
  .delete(sessions)
  .where(eq(sessions.id, sessionId))
  .returning();

The first is a soft delete, covered in the next chapter, and .returning() hands you the freshly archived row to send back to the client. The second returns what it removed, exactly what you’d write to an audit log: you can’t read a deleted row afterward, so the delete is your only chance to capture it.

When you don’t need the whole row, narrow it. .returning() accepts a projection, just like a select, and the inferred type follows it exactly:

const [{ id }] = await db
  .insert(memberships)
  .values({ userId, organizationId: orgId, role: 'member' })
  .returning({ id: memberships.id });

Here the result type is { id: string }[], not the full Membership. Reach for a projection when the caller only needs the new id back.

For a bulk insert, insert([...]).returning() gives the rows back in insertion order, so the returned array lines up position-for-position with the values array you passed in. That alignment is what lets you match each input to the id it generated.

The upsert-plus-returning write

Upsert plus .returning() is the create-or-update that runs atomically and hands back the row, with no race and no follow-up query. It’s the default for a family of web-app writes, in three places:

• Webhook idempotency: onConflictDoNothing on the delivery-id unique, with .returning() to tell a first delivery from a repeat.
• Find or create: onConflictDoUpdate on the natural or composite unique (the memberships example), with .returning() for the resulting row.
• Settings save: onConflictDoUpdate keyed on a unique(organizationId), one settings row per organization rewritten on every change, each save setting the changed columns from excluded.

For a wide settings table, writing set: { theme: sql\excluded.theme`, … }by hand for every column is busywork that drifts the moment you add a column. The usual fix is a small helper that builds thesetobject, mapping each non-primary-key column to itsexcluded` counterpart.

Two conflict-clause options are worth recognizing by name. targetWhere constrains which rows count as a conflict, pairing with a partial unique index (uniqueness that holds only where deleted_at is null, so a soft-deleted row doesn’t block a new insert). setWhere constrains which conflicts actually update, for example only overwriting when the incoming row is newer. Reach for them only when a partial-unique or conditional-overwrite problem comes up.

Know where the tool stops. An upsert resolves one row’s conflict using that same row’s values. When the resolution needs another row’s data, ON CONFLICT can’t express it; that’s a job for a CTE, the next lesson.

Practice: find or create a membership

Now write the shape yourself. The memberships table below is seeded with one row: user 1 is a member of organization 1. Do the find-or-create: add user 1 to organization 1 as admin, and .returning() the row.

Each wrong approach fails differently. A plain insert collides with the seeded row and throws a unique violation. onConflictDoNothing skips the insert and returns an empty array, so you never get admin. Only onConflictDoUpdate, with the composite target and the set pulling the role from excluded (or a literal 'admin'), updates the row in place and returns it as admin.

Add user 1 to organization 1 as an admin. The pair (user_id, organization_id) is already taken by a member row, so a plain insert would collide on the composite unique. Upsert on that unique and .returning() the row — the existing membership should end up admin, not duplicated. One row out.

View schema & seed rows

Schema (Drizzle)

export const memberships = pgTable('memberships', {
  id: integer('id').primaryKey(),
  userId: integer('user_id').notNull(),
  organizationId: integer('organization_id').notNull(),
  role: text('role').notNull(),
}, (t) => [
  unique('memberships_user_org_unique').on(t.userId, t.organizationId),
]);

Seed rows (SQL)

INSERT INTO memberships (id, user_id, organization_id, role) VALUES
  (1, 1, 1, 'member');

Run query Feedback Reset

External resources

Drizzle ORM — Upsert Query

orm.drizzle.team

The exact API this lesson uses — onConflictDoUpdate, excluded, composite targets, and the column-mapping helper named at the end.

PostgreSQL — INSERT (ON CONFLICT & RETURNING)

postgresql.org

The canonical reference under Drizzle's surface: the ON CONFLICT clause, the excluded table, and what RETURNING does and does not give back.