Explain

Every read operation in Better Drizzle returns an explainable result: a lazy thenable, awaited like a promise, with an extra .explain() method. Calling .explain() sends an EXPLAIN for the compiled SQL and returns the plan.

Basic usage

Chain .explain() off any read call to get the query plan:

const {
	driver, // "sqlite" | "pg" | "mysql"
	operation, // "findMany"
	statements, // array of ExplainStatement
} = await client.users.findMany({ where: { active: true } }).explain();

What runs, and when

• Read methods are lazy, like Drizzle's own query builders: calling one builds the read but runs nothing. The read, and its query hooks, run only when the result is awaited (or consumed through then, catch, finally, or Promise.all), and at most once. .explain() alone never runs the read.
• .explain() runs only EXPLAIN statements for the SQL the read would send. Query hooks do not run for it. Cursor probes that depend on page contents appear as conditional statements; .explain() does not fetch the page to decide whether they will run.
• A plain EXPLAIN (the default) only plans the statement. With analyze: true, PostgreSQL (EXPLAIN (ANALYZE true)) and MySQL (EXPLAIN ANALYZE) execute the statement to collect real timings. SQLite ignores analyze.

const result = client.users.findMany({ where: { active: true } });

// nothing has run yet; this only plans the statements
const { statements } = await result.explain();

// the read runs here
const users = await result;

Result shape

ExplainResult contains four standard fields and an optional deferredProbes field:

Each deferredRelations entry has path, table, cardinality ('one' \| 'many'), filtered, sorted, paginated, and through (the junction table, for many-to-many).

Each ExplainStatement contains:

Multi-statement operations

Most operations produce a single statement. Two exceptions:

paginate produces two: the data query and the count query.

const result = await client.users
	.paginate({ limit: 10, orderBy: { id: 'asc' } })
	.explain();

console.log(result.statements.map((s) => s.key));
// ["data", "total"]

cursor includes an indexed existence check in the data query for simple primary-key pages. An empty page may also need a hasNext / hasPrevious probe. The plan includes that probe with condition: 'when the data page is empty', even if the page would be populated:

const result = await client.users
	.cursor({
		after: previousCursor,
		limit: 25,
		orderBy: { id: 'asc' },
	})
	.explain();

console.log(result.statements.map((s) => s.key));
// ["data", "probe:hasPrevious"]

console.log(result.statements[1].condition);
// "when the data page is empty"

For other cursor query shapes, the probe binds a cursor from the first or last returned row. .explain() reports it under deferredProbes instead of showing SQL with guessed parameters.

Options

Pass an ExplainOptions object to control the EXPLAIN output:

const plan = await client.users
	.findMany({ where: { active: true } })
	.explain({
		analyze: true,
		verbose: true,
		costs: false,
		timing: true,
		summary: false,
		name: 'users.findMany',
		comment: 'active user lookup',
		timeoutMs: 5000,
	});

Options reference

Checking applied vs ignored options

const {
	statements: [stmt],
} = await client.users
	.findMany({ where: { active: true } })
	.explain({ analyze: true, verbose: true });

// On SQLite, both are ignored
console.log(stmt.ignoredOptions); // ["analyze", "verbose"]

// On PostgreSQL, both are applied
console.log(stmt.appliedOptions); // { analyze: true, verbose: true }

Plugin transforms are reflected

.explain() runs the plugin transform pipeline. If a plugin modifies where, select, or other args, the explained SQL reflects those changes:

import { better } from 'better-drizzle';
import { definePlugin } from 'better-drizzle/plugins';

// Injects { active: true } into every findMany
const forceActive = definePlugin({
	id: 'force-active',
	transform(operation) {
		if (operation.kind === 'findMany')
			operation.where = (
				operation.where
					? { AND: [operation.where, { active: true }] }
					: { active: true }
			) as typeof operation.where;
		return operation;
	},
});

const client = better(db, { plugins: [forceActive] });

const {
	statements: [{ sql }],
} = await client.users.findMany({ where: { id: 1 } }).explain();

// The SQL includes the plugin-injected active filter
console.log(sql);
// contains "active" clause

Cross-dialect behavior

Which operations support .explain()?