Create, update & delete

The write methods mirror the read API and return predictable shapes. Single-row mutations return the affected row; batch mutations return a summary.

create

Inserts one row and returns the created record:

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

createMany

Inserts many rows in one statement and returns a batch summary:

const {
	count, // number inserted
	data, // inserted rows, when the driver supports RETURNING
} = await client.users.createMany({
	data: [
		{ email: 'a@example.com', name: 'A', active: true },
		{ email: 'b@example.com', name: 'B', active: false },
	],
});

create and createMany both support skipDuplicates:

const maybeCreated = await client.users.create({
	data: {
		email: 'alice@example.com',
		name: 'Alice',
		active: true,
	},
	skipDuplicates: ['email'],
});

With single-row create, a skipped insert returns null. With createMany, count only includes rows that were actually inserted.

For large inputs, batchSize splits data into one INSERT per chunk so a single statement stays under driver parameter limits. count sums the inserted rows and data keeps input order. Hooks and plugins still run once per call. Chunks run one after another without an implicit transaction, the same as upsertMany; wrap the call in db.transaction(...) when all chunks must commit together. batchSize must be a positive integer.

const { count } = await client.users.createMany({
	data: rows,
	batchSize: 500,
	skipDuplicates: true,
});

update

Updates at most one matching row. The result is nullable (the target may not exist) and also exposes .throw().

When where matches several rows, only one of them is updated, on every dialect. Use updateMany to update every match. A where that pins the whole primary key or every column of a unique key (a unique column, a unique() constraint, or a non-partial uniqueIndex() on plain columns) runs as a plain UPDATE; any other where is limited to one row in the same statement (a LIMIT 1 subquery on PostgreSQL and SQLite). MySQL has no RETURNING, so there the read and the write run in one transaction: the active one, or an implicit one when none is open. The first matching row by primary key is locked with SELECT ... FOR UPDATE and updated by that key, so the returned row is the one that changed. On a MySQL table without a primary key, the row is read without a lock and written with LIMIT 1, so a concurrent write can make the returned row differ from the changed one.

const updated = await client.users.update({
	where: { id: 1 },
	data: { name: 'Alice Updated' },
});

if (!updated) {
	throw new Error('Update target not found');
}

Relation writes

Single-row create, update, and upsert accept relation commands inside their data payloads, so you can attach related rows without resolving foreign keys yourself:

const post = await client.posts.create({
	data: {
		title: 'Connected post',
		author: { connect: { email: 'alice@example.com' } },
	},
});

await client.users.update({
	where: { id: 1 },
	data: {
		posts: {
			connect: [{ id: 2 }, { id: 3 }],
			disconnect: { id: 1 },
		},
	},
});

connect, disconnect, and set each have their own rules per cardinality, selectors must match exactly one row, and the root mutation plus every relation change run in one implicit transaction. createMany, updateMany, updateEach, and upsertMany intentionally remain scalar batch APIs.

See relation writes for the full command matrix, the failure modes, and the batch workaround.

updateMany

Updates every matching row and returns { count, data? }:

const { count, data } = await client.users.updateMany({
	where: { active: true },
	data: { active: false },
}); // count: rows updated

PostgreSQL and SQLite return complete updated rows in data by default using native RETURNING. Pass a scalar select to narrow those rows, for example select: { id: true, active: true }. Relation projections are unsupported. When no row matches, the result is { count: 0 } with no data; MySQL always returns only { count }.

updateEach

Updates many rows with different values in a single statement. Instead of applying the same value to all matching rows (like updateMany), updateEach generates a CASE expression per column so each row can have its own value:

const { data } = await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, name: 'Alice Updated', city: 'New York' },
		{ id: 2, name: 'Bob Updated', city: 'Chicago' },
	],
	update: {
		name: (row) => row.name,
		city: (row) => row.city,
	},
});

This generates a single SQL statement:

UPDATE users
SET
  name = CASE WHEN id = 1 THEN 'Alice Updated' WHEN id = 2 THEN 'Bob Updated' ELSE name END,
  city = CASE WHEN id = 1 THEN 'New York' WHEN id = 2 THEN 'Chicago' ELSE city END
WHERE id IN (1, 2)

Options

A callback can return a Drizzle SQL value instead of a plain value. It is placed in that row's CASE branch as is, so it can call SQL functions or reference columns. Returning undefined throws.

import { sql } from 'drizzle-orm';

const { count } = await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, name: 'alice' },
		{ id: 2, name: 'bob' },
	],
	update: {
		name: (row) => sql`upper(${row.name})`,
		city: () => sql`coalesce(${users.city}, 'Unknown')`,
	},
});

Return type

updateEach returns Promise<BatchResult<T>>:

{
  count: number;   // number of rows affected
  data?: T[];      // returned rows when `select` is provided and driver supports RETURNING
}

Using where with updateEach

Combine a where filter with the by column to scope which rows are eligible for update:

const { data } = await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, name: 'Alice' },
		{ id: 2, name: 'Bob' },
	],
	update: { name: (row) => row.name },
	where: { active: true }, // only update active users
});

Projecting results

Use select to return specific scalar columns after the update:

const { data } = await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, name: 'Alice' },
		{ id: 2, name: 'Bob' },
	],
	update: { name: (row) => row.name },
	select: { id: true, name: true, email: true },
});

console.log(data); // [{ id: 1, name: 'Alice', ... }, ...]

Empty input handling

By default, passing an empty data array returns { count: 0 }. Set onEmpty: 'throw' to raise an error instead:

// Returns { count: 0 }
await client.users.updateEach({
	by: users.id,
	data: [],
	update: { name: (row) => row.name },
});

// Throws BetterDrizzleError
await client.users.updateEach({
	by: users.id,
	data: [],
	update: { name: (row) => row.name },
	onEmpty: 'throw',
});

Duplicate detection

updateEach rejects duplicate by values in the data array. Each row must have a unique match column value:

// Throws - duplicate id values
await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, name: 'Alice' },
		{ id: 1, name: 'Bob' }, // duplicate!
	],
	update: { name: (row) => row.name },
});

When to use updateEach vs updateMany

delete

Deletes at most one matching row and returns it (nullable, with .throw()). When where matches several rows, only one is deleted (on MySQL, the same locked read in a transaction as update); use deleteMany to delete every match:

const deleted = await client.users.delete({
	where: { id: 1 },
});

deleteMany

Deletes every matching row and returns { count, data? }:

const { count, data } = await client.users.deleteMany({
	where: { active: false },
}); // count: rows deleted

On PostgreSQL and SQLite, data contains the deleted rows, or the scalar fields requested by select, from native RETURNING. Empty results omit data. MySQL returns only { count }. Relation projections are unsupported.

upsert

"Create if missing, update if present" - without branching in your own code:

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

upsert is usually cleaner than "find, then branch, then write" when the behavior is genuinely upsert-shaped.

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 one atomic statement: INSERT ... ON CONFLICT (those columns) DO UPDATE on PostgreSQL and SQLite. MySQL's ON DUPLICATE KEY UPDATE fires on any unique key, so there it is used only when the target is the table's only unique key, and a unique key target also needs an insert that leaves 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.

upsertMany

where accepts a structured filter or Drizzle SQL and controls whether the existing conflicting row is updated. It does not filter incoming inserts. PostgreSQL and SQLite support it; MySQL rejects it.

For bulk create-or-update flows, use upsertMany instead of looping over upsert():

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

upsertMany is designed around native conflict handling, not userland loops.

• target is required: one column key ('email') or an array of keys (['tenantId', 'email'])
• update can be 'all', a column list, an object, or a function
• batchSize chunks large inserts without changing the API. It must be a positive integer, otherwise the call throws batchSize must be a positive integer.
• where accepts structured filters or SQL conflict-update predicates (not on MySQL)
• on MySQL it compiles to ON DUPLICATE KEY UPDATE, which fires on any unique key, so target must be the primary key or a unique key and rows cannot set another unique key; data is never returned there
• relation include is intentionally not supported

Projecting write results

create, update, delete, upsert, createMany, updateEach, and upsertMany can project results. updateEach and upsertMany support select; the others support select and include:

const user = await client.users.create({
	data: { email: 'writer@example.com', name: 'Writer', active: true },
	select: { id: true, email: true },
});
//    ^? { id: number; email: string }

Notes

• createMany, updateMany, updateEach, and deleteMany return a { count, data? } summary, not individual rows.
• upsertMany also returns { count, data? }.
• update and delete are nullable by default because the target row may not exist - handle null, or call .throw().
• Returning affected rows (data) depends on the driver supporting RETURNING.
• updateEach is native-first - it generates a single UPDATE ... CASE statement instead of per-row loops.
• updateEach rejects duplicate by values in the input data.
• upsertMany is intentionally native-first and fails fast on shapes the dialect cannot express instead of degrading to slow per-row loops.
• Need lifecycle behavior on writes - timestamps, soft delete, audit trails? That belongs in plugins or hooks, not duplicated per call.