Recipes

Each recipe is a small, complete answer to a question that comes up in almost every app. They use the docs schema: users (id, email, name, active) and posts (id, authorId, title, published, score, tags), with users.posts and posts.author relations.

Conditional filters

Build one where from optional inputs. A key set to undefined is skipped, so you do not need if chains:

type Filters = { search?: string; authorId?: number; published?: boolean };

export const listPosts = ({ search, authorId, published }: Filters) =>
	client.posts.findMany({
		where: {
			title: search ? { contains: search, mode: 'insensitive' } : undefined,
			authorId,
			published,
		},
		orderBy: { id: 'desc' },
		take: 20,
	});

Increment or decrement a counter

Atomic updates need a SQL expression, not a value read from the row. updateEach accepts one per column, and each row can carry the amount:

import { sql } from 'drizzle-orm';

// score = score + delta, for many rows in one UPDATE ... CASE statement
await client.posts.updateEach({
	by: posts.id,
	data: [
		{ id: 1, delta: 5 },
		{ id: 2, delta: -1 },
	],
	update: { score: (row) => sql`${posts.score} + ${row.delta}` },
});

The expression runs in the database, so concurrent requests never overwrite each other's increments.

Toggle a boolean

The same pattern flips a flag without reading it first:

import { sql } from 'drizzle-orm';

await client.posts.updateEach({
	by: posts.id,
	data: [{ id: 10 }],
	update: { published: () => sql`not ${posts.published}` },
});

Top N per group

"The 3 best posts of each author" is a window function in SQL. With better-drizzle it is a nested take, and the per-parent limit still runs in the database:

const authors = await client.users.findMany({
	where: { active: true },
	include: {
		posts: {
			where: { published: true },
			orderBy: { score: 'desc' },
			take: 3,
		},
	},
});

Count related rows

Use _count instead of loading the rows. It becomes a subquery in the same statement:

const authors = await client.users.findMany({
	include: {
		_count: {
			select: {
				posts: { where: { published: true } },
			},
		},
	},
});

authors[0]?._count.posts; // number

Get or create

For "insert unless it exists", let the database resolve the conflict:

// returns null when a row with the same email already exists
const created = await client.users.create({
	data: { email, name },
	skipDuplicates: ['email'],
});

const user = created ?? (await client.users.findUnique({ where: { email } }));

When the existing row should be updated instead, use upsert. It compiles to a native ON CONFLICT ... DO UPDATE when it can:

const user = await client.users.upsert({
	where: { email },
	create: { email, name },
	update: { name },
});

For many rows at once, upsertMany sends batched native upserts:

const { count } = await client.users.upsertMany({
	data: importedRows,
	target: 'email',
	update: ['name', 'active'],
	batchSize: 500,
});

See Create, update & delete for every option.

Add tags to an array column

On PostgreSQL, array columns can be updated in place, without reading the row first:

await client.posts.update({
	where: { id: 10 },
	data: { tags: { addUnique: ['drizzle', 'orm'] } },
});

append, prepend, remove, and replace work the same way. See Array filters.

Case-insensitive search

contains with mode: 'insensitive' covers simple search boxes:

await client.users.findMany({
	where: {
		OR: [
			{ name: { contains: query, mode: 'insensitive' } },
			{ email: { contains: query, mode: 'insensitive' } },
		],
	},
	take: 10,
});

Full-text search on PostgreSQL

where also accepts a Drizzle sql expression, so PostgreSQL full-text search needs no special API:

import { sql } from 'drizzle-orm';

const results = await client.posts.findMany({
	where: sql`to_tsvector('english', ${posts.title}) @@ plainto_tsquery('english', ${query})`,
	take: 20,
});

Add a GIN index on the same expression so the search does not scan the table:

create index posts_title_fts on posts using gin (to_tsvector('english', title));

Aggregates and GROUP BY

The delegate API has count and exists, but no groupBy or sum. Aggregates read better as SQL, and $raw keeps them typed and parameterized:

const totals = await client.$raw<{ authorId: number; posts: number }>`
	select author_id as "authorId", count(*)::int as posts
	from posts
	where published = ${true}
	group by author_id
	order by posts desc
`;

Inside client.transaction(...), use tx.$raw so the query runs in the same transaction. See Raw SQL.

Check before you act

exists is cheaper than count when you only need a yes or no:

const hasDrafts = await client.posts.exists({
	where: { authorId: userId, published: false },
});

Require a row, or fail

Reads return null when nothing matches. Chain .throw() to get a RESULT_NOT_FOUND error instead, which maps to HTTP 404:

const post = await client.posts.findUnique({ where: { id } }).throw();

See Throwing results.