Filters

where is shared by every read, count, exists, update, delete, and upsert. It is typed against your table's columns and relations.

Equality shorthand

The simplest filter is a direct value - it means "equals":

await client.users.findMany({
	where: { active: true, name: 'Alice' },
});

That is identical to the explicit form:

await client.users.findMany({
	where: { active: { equals: true } },
});

Multiple keys are combined with AND.

String filters

String columns accept equality, membership, and pattern operators:

await client.users.findMany({
	where: {
		name: { contains: 'ali', mode: 'insensitive' },
		email: { endsWith: '@example.com' },
	},
});

Numeric and date filters

Numbers, bigints, and dates accept range comparisons:

await client.users.findMany({
	where: {
		id: { gte: 100, lte: 200 },
	},
});

contains and startsWith treat %, _, and the SQL escape character as literal search text. This applies to case-insensitive filters, nested not, PostgreSQL JSONB paths and array elements, and prepared params. Use a Drizzle SQL fragment when you need a wildcard pattern.

Boolean filters

await client.users.findMany({
	where: { active: { equals: true } },
});

Boolean columns support equals and not.

Null and negation

Nullable columns accept null directly, and any operator can be negated with not:

await client.posts.findMany({
	where: {
		deletedAt: null, // IS NULL
		title: { not: { contains: 'draft' } },
	},
});

Logical operators

Combine conditions with AND, OR, and NOT. They nest arbitrarily:

await client.users.findMany({
	where: {
		AND: [
			{ active: true },
			{
				OR: [
					{ email: { endsWith: '@company.com' } },
					{ name: { startsWith: 'Admin' } },
				],
			},
		],
	},
});

const posts = await client.posts.findMany({
	where: {
		tags: { has: 'drizzle', length: { lte: 3 } },
		roles: { hasEvery: ['admin', 'member'] },
		scores: { hasSome: [10, 20], hasNone: [0] },
	},
});

Filtering by relations

where also reaches across relations with some / every / none (to-many) and is / isNot (to-one). That has its own page:

Dropping to raw Drizzle SQL

When you need an expression the structured filter does not model, pass a Drizzle SQL fragment straight into where:

import { eq } from 'drizzle-orm';
import { users } from './schema';

const user = await client.users.findFirst({
	where: eq(users.id, 1),
});

Anything that produces a Drizzle SQL / SQLWrapper is accepted, so you keep the full expressive power of Drizzle for the cases that need it.

Reusing a filter in raw Drizzle

$where() goes the other way: it compiles a typed where into a Drizzle SQL condition, so the same filter works in raw queries, joins, and subqueries.

The condition includes every plugin scope of the model, so it selects the same rows as findMany: soft-delete hides deleted rows, and a tenant-scoped client adds its tenant filter. deleted and other per-call arguments do not apply. It returns undefined only when the where is empty and the model has no scope.

import { and, eq } from 'drizzle-orm';
import { users, posts } from './schema';

const rows = await db
	.select()
	.from(posts)
	.innerJoin(users, eq(posts.authorId, users.id))
	.where(
		and(
			client.users.$where({ OR: [{ age: { gte: 18 } }, { active: true }] }),
			client.posts.$where({ published: true, score: { gt: 10 } }),
		),
	);

• It takes the same where as findMany, including logical operators and relation filters.
• An empty where returns undefined, which Drizzle treats as no filter.
• Static plugin filters such as the soft-delete visibility rule are not applied. Add them yourself when you need them. On a tenant-scoped client the tenant filter is applied, so the condition never reaches another tenant's rows.