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.

update

Updates a single matching row. The result is nullable (the target may not exist) and also exposes .throw():

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 the affected count:

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

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 a single matching row and returns it (nullable, with .throw()):

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

deleteMany

Deletes every matching row and returns the affected count:

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

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.

upsertMany

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 is available for SQL-only conflict-update predicates
• 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 currently fails fast on unsupported dialects 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.