Upsert

Use upsert when a write is "create if missing, update if present" and you do not want to branch in your own code. Use upsertMany for the same thing on many rows; it compiles to native conflict handling instead of a loop.

const user = await client.users.upsert({
	where: { email: 'alice@example.com' },
	create: {
		email: 'alice@example.com',
		name: 'Alice',
		active: true,
	},
	update: {
		name: 'Alice Renamed',
	},
});

When upsert is one atomic statement

When where pins the primary key, or holds only the columns of one unique key (a unique column, constraint, or non-partial unique index), and create sets the same values, upsert runs INSERT ... ON CONFLICT (those columns) DO UPDATE on PostgreSQL and SQLite.

MySQL's ON DUPLICATE KEY UPDATE fires on any unique key, so there the native path is used only when the target is the table's only unique key; a unique key target also needs create to leave the primary key to auto-increment.

Every other shape reads first, then creates or updates. That path is not atomic, so concurrent calls can hit a unique violation. An update that resolves to no values (empty, or every field undefined) also takes this path and returns the existing row unchanged.

upsert accepts relation commands in create and update.

Upsert many rows

import { sql } from 'drizzle-orm';

const { count, data } = await client.users.upsertMany({
	data: [
		{ email: 'alice@example.com', name: 'Alice', active: true },
		{ email: 'bob@example.com', name: 'Bob', active: false },
	],
	target: ['email'],
	update: (ctx) => ({
		name: ctx.excluded.name,
		active: ctx.excluded.active,
		updatedAt: sql`now()`,
	}),
	batchSize: 500,
	select: {
		id: true,
		email: true,
		name: true,
	},
});

• target is required: one column key ('email') or an array of keys (['tenantId', 'email']).
• update is 'all', a column list, an object, or a function receiving ctx.excluded.
• batchSize splits large inputs into one statement per chunk. Chunks run one after another without an implicit transaction; wrap the call in db.transaction(...) when all chunks must commit together.

Update only some existing rows

where takes a structured filter or a Drizzle SQL fragment. It applies to the existing conflicting row and decides whether it is updated; it never filters incoming inserts. PostgreSQL and SQLite support it; MySQL rejects it.

await client.users.upsertMany({
	data: rows,
	target: ['email'],
	update: 'all',
	where: { active: true }, // inactive existing users keep their values
});

Edge cases

Reference