Prepared Statements

A prepared statement fixes the shape of a read once and runs it many times with new values. Mark each value with param(name), call .prepare() on the read, and pass the values to execute():

import { param, type PreparedParams, type PreparedResult } from 'better-drizzle';

const findUserByEmail = client.users
	.findUnique({ where: { email: param('email') } })
	.prepare('users.by-email');

const user = await findUserByEmail.execute({ email: 'user@example.com' });

type Params = PreparedParams<typeof findUserByEmail>; // { email: string }
type Result = PreparedResult<typeof findUserByEmail>; // User | null

At runtime param() is Drizzle's sql.placeholder(), so either works. Use param() to get typed values: sql.placeholder() types its value as any. .prepare() builds the SQL, runs the plugin pipeline and the client beforeQuery hook once, and creates the Drizzle prepared statement. execute() runs that statement and shapes the result exactly like the regular read. Each param's type comes from the position it is used in: a param compared with a text column is a string, a param in take is a number, and a wrong or missing value is a type error.

Which reads can be prepared

Every read returns a result with .prepare(name?):

Writes cannot be prepared.

const findPost = client.posts
	.findFirst({ where: { id: param('id') } })
	.prepare();

const post = await findPost.execute({ id: 42 }).throw();

A statement without params executes without values:

const listAdmins = client.users
	.findMany({ where: { role: 'admin' } })
	.prepare();

const admins = await listAdmins.execute();

Where params can go

param() takes the place of a value, never of a key or an operator:

Pattern params take the bare search text: { contains: param('q') } binds %value%, built when the statement executes, so the database compares against a ready pattern. As in regular reads, % and _ inside the value are not escaped. in / notIn params bind one array (column = any($1)); the other dialects cannot bind an array, so .prepare() fails with PREPARED_UNSUPPORTED there.

Keep a take / skip param at zero or above. It is not validated: a regular read reverses the order for a negative take, but a prepared statement fixes its order when it is prepared. SQLite treats a negative LIMIT as no limit, and PostgreSQL rejects it.

Pagination

paginate() accepts params for page, perPage (or limit / take), and skip. execute() validates page and computes the offset, then runs the page and the total as two prepared statements:

const listPosts = client.posts
	.paginate({
		orderBy: { createdAt: 'desc' },
		page: param('page'),
		perPage: 20,
		where: { published: true },
	})
	.prepare();

const {
	data,
	pagination: { total, hasNext },
} = await listPosts.execute({ page: 3 });

cursor() accepts a param for after or before and for limit. The param takes the whole cursor object, such as a previous nextCursor:

const firstPage = client.posts
	.cursor({ limit: 20, orderBy: { id: 'asc' } })
	.prepare();

const nextPage = client.posts
	.cursor({ after: param('after'), limit: 20, orderBy: { id: 'asc' } })
	.prepare();

const {
	pagination: { nextCursor },
} = await firstPage.execute();

if (nextCursor) await nextPage.execute({ after: nextCursor });

A statement either has a cursor or does not, so the first page and the following pages are two statements. A prepared cursor needs a non-null value for every orderBy field, so page over nullable keys with the regular cursor(). When the cursor is a param and there is no orderBy, the statement orders by the primary key. The probes that resolve hasPrevious / hasNext are prepared too.

Relations

include and relation select work in prepared reads. The root query is prepared, and each relation level still loads with its own query on every execution, because those queries depend on the root rows. Params therefore cannot appear inside a relation's include / select args, and .prepare() fails with PREPARED_UNSUPPORTED when they do. Relation filters in where and include._count filters compile into the root query, so they accept params.

const findAuthor = client.users
	.findUnique({
		include: { posts: { orderBy: { createdAt: 'desc' }, take: 5 } },
		where: { id: param('id') },
	})
	.prepare();

Hooks, plugins, and metadata

.prepare() runs everything that shapes the query, once. execute() runs everything that observes or serves a result, every time:

Plugins that rewrite a read, such as softDelete() filters and tenant filters from transforms, apply when the statement is prepared. A statement keeps the shape it was prepared with, so prepare a separate statement per plugin state ($withState(...), $withoutPlugins()) or per tenant when a transform depends on request data. Rules and query validation (better-drizzle/zod, better-drizzle/ata) also run at prepare time and see param() markers instead of values:

• Rules that check values cannot see a param. maxLimit does not limit a take: param('take'), and requireExplicitLimit / noUnboundedFindMany treat it as unbounded. Bound such values yourself before execute().
• Query-args validation rejects the markers. Pass validate: false to a prepared read when the Zod or ATA plugin validates reads.

If a plugin before hook returns a result instead of letting the read run, that result is captured at prepare time, and every execute() returns it.

Without a beforeQuery hook or plugin work for the read, .prepare() builds the statement synchronously and throws setup errors such as PREPARED_UNSUPPORTED right away. Otherwise .prepare() defers those steps to the first execute(), even when they are synchronous, and a setup error rejects every execute() call.

meta given to the read is captured at prepare time. Pass per-execution metadata as the second argument; it is merged over the captured meta and reaches afterQuery, intercepts, and plugin after hooks:

await findUserByEmail.execute(
	{ email: 'user@example.com' },
	{ meta: { requestId } },
);

The cache plugin keys prepared reads by the statement plus its values. A param in a primary-key where still produces an entity dependency, so an update to one row does not invalidate the cached results of other ids.

Transactions

A statement belongs to the client it was prepared on, like a Drizzle prepared statement. One prepared on the root client runs outside any transaction; to run inside transaction(), prepare it on tx:

await client.transaction(async (tx) => {
	const findBalance = tx.accounts
		.findUnique({ lock: 'update', where: { id: param('id') } })
		.prepare();

	const from = await findBalance.execute({ id: fromId }).throw();
	const to = await findBalance.execute({ id: toId }).throw();
	// ...
});

Do not keep a statement prepared on tx after the callback returns: it is bound to the finished transaction's session.

Statement names

With node-postgres the name becomes the server-side prepared statement name, and without one Drizzle derives a name from the SQL. postgres-js manages its own statement names. Reads that run more than one statement name the others after it: paginate adds name:count, and cursor adds name:probe and name:probe-all. A name must identify one SQL text, so do not reuse a name for a different query. MySQL and SQLite ignore the name.

Prepare statements once, for example at module scope or when a service is created, and reuse them. Preparing inside a request handler repeats the work the statement is meant to save.

Explain

explain(values, options?) checks values like execute(), compiles the read with the values inlined, and returns its query plan. The plan can differ from the prepared statement's generic plan: an in param becomes an IN (...) list, for example.

const { statements } = await findUserByEmail.explain({
	email: 'user@example.com',
});

Errors

Invalid pagination values reject with OPERATION_ERROR, as in the regular read. A null value binds SQL NULL, which never equals anything; filter on null with a literal ({ deletedAt: null }) instead.

Performance

A prepared read skips argument compilation, plugin transforms, and SQL generation on every call. On PostgreSQL with node-postgres it also reuses the server-side plan. execute() checks the values against the param names, runs the Drizzle statement, and shapes the result.

From one bun run bench:report run on SQLite in-memory (absolute timings depend on the machine; compare the ratios):

Prepared reads stay within ~4% of the equivalent Drizzle prepared statement. The gain over a regular read is largest when compilation dominates, as in short point lookups, and smallest when the database does most of the work. Reproduce with bun run bench:report; bun run bench:verify checks that both sides return the same result first. See benchmarks.