Upsert with Drizzle in NestJS

To upsert with Drizzle in NestJS, call upsert(dto, { conflictTarget }) on your service. It inserts the row, or updates the existing row that already holds the same unique key, in one statement, and returns the row as stored either way. On PostgreSQL that statement is INSERT ... ON CONFLICT DO UPDATE; on MySQL it is INSERT ... ON DUPLICATE KEY UPDATE. massUpsert does the same for many rows in one transaction.

How do I upsert a row?Copy Anchor Link

Call upsert with the row and the unique column that decides "already exists":

const user = await users.upsert(
  { email: 'ada@example.com', name: 'Ada', role: 'admin' },
  { conflictTarget: 'email' },
);

On PostgreSQL this runs:

INSERT INTO users (...) VALUES (...)
ON CONFLICT (email) DO UPDATE SET name = excluded.name, role = excluded.role, ...
RETURNING *

The signature is upsert(data, upsertOptions?, options?). The second argument is UpsertOptions, the third is the usual SqlOperationOptions (transaction, relations, counts).

What options does upsert take?Copy Anchor Link

UpsertOptions has two fields, and both are optional:

The default update list includes updatedAt, because the insert sets it, so the timestamp moves when an existing row is updated. createdAt keeps its original value.

How do I get or create a row without changing it?Copy Anchor Link

Pass update: []. The existing row stays as it is and still comes back, which turns upsert into a get-or-create:

const tag = await tags.upsert(
  { name: 'urgent' },
  { conflictTarget: 'name', update: [] },
);

With nothing to overwrite, the package sets the conflict target to its own value so that the database still returns the row. On an entity with a version column, the version still goes up by one when the row existed.

Which hooks run on upsert?Copy Anchor Link

validateCreate, beforeCreate and mapCreateDtoToEntity run, because the statement starts as an insert, and writableColumns applies to the body. The after hooks (afterCreate, afterUpdate) do not run: the package cannot tell an insert from an update without an extra query.

With global hooks on, upsert raises one before and one after event with the operation name upsert.

Does upsert work with tenant scope, versions and relations?Copy Anchor Link

Yes, on PostgreSQL:

What errors can upsert return?Copy Anchor Link

Upsert maps driver errors the same way create does:

// sku is unique too: moving row 1 onto row 2's sku is a 409
await items.upsert({ id: 1, sku: 'A2', name: 'Lamp' }, { conflictTarget: 'id' });

The full mapping is on the errors page.

How do I upsert many rows at once?Copy Anchor Link

Call massUpsert(rows, upsertOptions?, options?). It sends one INSERT ... ON CONFLICT for the whole batch, inside a transaction:

const written = await users.massUpsert(
  [
    { email: 'ada@example.com', name: 'Ada' },
    { email: 'bob@example.com', name: 'Bob' },
  ],
  { conflictTarget: 'email' },
);

When the statement fails, the failing items are named in a BulkOperationException, each by its index in the input array, and nothing is written.

Two rows of the same batch with the same conflict key are a 400 on PostgreSQL ("the same key appears more than once in one upsert"), because one statement cannot update a row twice. Remove duplicates before you call it.

How does upsert differ on MySQL?Copy Anchor Link

MySQL uses ON DUPLICATE KEY UPDATE, and three things change:

• Any unique key triggers the update. MySQL does not let you choose the key, so conflictTarget does not pick it there.
• The rows are read back. MySQL has no RETURNING, so the package selects the rows by the conflictTarget columns after the write. Send a value for every conflict target column, otherwise the call is a 400.
• Scoped entities are refused. ON DUPLICATE KEY UPDATE has no WHERE, so it cannot keep the update inside one tenant. Any upsert on a scoped entity is a 400 ("upsert on a scoped entity needs PostgreSQL") rather than a risk of overwriting another tenant's row.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link