Query options

This page is the reference for the arguments accepted by reads and (where noted) writes.

Read arguments

Accepted by findMany, findFirst, findOne, and findUnique:

For example, orderBy: { lastSeenAt: { direction: 'desc', nulls: 'last' } } puts rows with a NULL value after non-NULL values. When nulls is omitted, the database default applies. PostgreSQL and SQLite use native NULLS FIRST / NULLS LAST ordering; MySQL gets equivalent behavior through an IS NULL ordering term. Better Drizzle rejects an unrecognized provider dialect during client initialization.

Relation keys nest like relation filters. A one relation takes the related table's own orderBy field map, at any depth, and a many or many-to-many relation takes exactly { count: 'asc' | 'desc' }:

orderBy: [
	{ author: { profile: { bio: { direction: 'desc', nulls: 'last' } } } },
	{ id: 'asc' },
]

orderBy: { posts: { count: 'desc' } }

Each relation key becomes a correlated subquery in the same statement's ORDER BY. A missing one relation sorts as NULL; a to-many count with no related rows is 0. An aggregate count on a one relation, a field map on a to-many relation, or a non-object relation value throws INVALID_ARGS, as does a relation key combined with cursor or cursor(). A real scalar column named count on a one relation remains sortable. SQL expressions in orderBy still require Drizzle directly. See ordering by relations and limitations.

count and exists accept only where, cursor, and meta.

findUnique accepts the same query-argument shape as the other reads, but its where must pin one row: an equality on every primary key column, on a unique column, or on every column of a composite unique key. Otherwise it throws UNIQUE_WHERE_REQUIRED before any SQL runs. See reads.

await client.transaction(async (tx) => {
	return tx.users.findMany({
		where: { id: { gt: 0 } },
		lock: {
			mode: 'update',
			skipLocked: true,
		},
	});
});

Filter operators

Operators available inside where, by column type. A bare value means equals; null matches nullable columns.

mode is 'default' (LIKE / =, case sensitivity follows the dialect) or 'insensitive' (patterns use ILIKE on PostgreSQL and lower(col) LIKE lower(?) elsewhere; equals, in, notIn, and a scalar not compare lower() on both sides). See filters and relations for examples.

Projections

// select - narrows, scalars + relations
await client.posts.findMany({
	select: { id: true, author: { select: { name: true } } },
});

// include - keeps all scalars, adds relations
await client.posts.findMany({ include: { author: true } });

// relation counts - available only in include
await client.posts.findMany({
	include: { count: { select: { comments: true } } },
});

A relation value can be true or a nested read-args object (with its own where, select, orderBy, take). See selecting fields.

Pagination arguments

paginate accepts everything above plus page, perPage, and limit:

It returns { data, pagination: { type, page, perPage, total, pageCount, hasNext, hasPrevious } }, where perPage is the resolved page size and page is Math.floor(skip / perPage) + 1.

cursor accepts everything above plus:

Pass after or before, never both. Both take a cursor object, such as a previous nextCursor / previousCursor. Without orderBy, pages are ordered by primary key ascending. orderBy must use scalar columns only: a relation key throws INVALID_ARGS, including on the first page.

It returns { data, pagination: { type, hasNext, hasPrevious, nextCursor, previousCursor } }. See pagination.

Write-specific arguments

Read args are only part of the API surface. The write methods add a few important options that deserve their own reference.

Single-row writes

Batch writes

updateMany and deleteMany return BatchResult<Payload> ({ count, data? }). PostgreSQL and SQLite return complete affected rows by default, or the scalar fields requested by select, using native RETURNING. Empty results omit data. MySQL returns only { count }. Relation projections are unsupported.

updateEach details

const { count } = await client.users.updateEach({
	by: users.id,
	data: [
		{ id: 1, active: false },
		{ id: 2, active: true },
	],
	update: {
		active: (row) => row.active,
	},
});

upsertMany details

import { sql } from 'drizzle-orm';

await client.users.upsertMany({
	data: [
		{ email: 'a@example.com', name: 'A', active: true },
		{ email: 'b@example.com', name: 'B', active: false },
	],
	target: ['email'],
	update: (ctx) => ({
		name: ctx.excluded.name,
		active: ctx.excluded.active,
		updatedAt: sql`now()`,
	}),
	batchSize: 1000,
	where: sql`users.disabled = false`,
});

update accepts four shapes:

• 'all'
• an explicit column list
• an explicit update object
• a function receiving { excluded, sql, table }

target is a single column name ('email') or an array of column names (['email']). batchSize splits data into chunks and must be a positive integer, otherwise the call throws INVALID_ARGS.

where accepts the same WhereArg as other operations: a structured filter or a Drizzle SQL fragment. It controls whether the existing conflicting row is updated; it does not filter incoming inserts. PostgreSQL and SQLite support it, while MySQL rejects it.