Explain
Inspect the query plan of any read operation with .explain().
Every read operation in Better Drizzle returns an explainable result: a normal 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
- Calling a read method starts the read right away, on the next microtask, whether or not you await it.
.explain()does not delay or cancel it. .explain()runs a separateEXPLAINstatement for each SQL statement the read would send. Query hooks do not run for it.- A plain
EXPLAIN(the default) only plans the statement. Withanalyze: true, PostgreSQL (EXPLAIN (ANALYZE true)) and MySQL (EXPLAIN ANALYZE) execute the statement to collect real timings. SQLite ignoresanalyze.
const result = client.users.findMany({ where: { active: true } });
// the read above has already been scheduled
const { statements } = await result.explain();
const users = await result;analyze executes the query
Reads have no side effects, but analyze: true still costs a full query
execution. Avoid it on hot paths and against large tables in production.
Result shape
ExplainResult contains four fields:
| Field | Type | Description |
|---|---|---|
driver | 'sqlite' | 'pg' | 'mysql' | The dialect that produced the plan |
operation | string | The operation name (e.g. "findMany", "paginate") |
statements | ExplainStatement[] | One or more statement details |
deferredRelations | object[] | Relation queries from include / relation select that depend on root rows, so they are described but not explained |
Each deferredRelations entry has path, table, cardinality ('one' \| 'many'), filtered, sorted, paginated, and through (the junction table, for many-to-many).
Each ExplainStatement contains:
| Field | Type | Description |
|---|---|---|
key | string | Role of the statement: "data", "total", "count", "exists", "probe:hasNext", "probe:hasPrevious" |
sql | string | The compiled SQL without the EXPLAIN prefix |
params | unknown[] | Bound parameter values |
appliedOptions | Partial<ExplainOptions> | Options the driver actually recognized |
ignoredOptions | Array<keyof ExplainOptions> | Options the driver silently dropped |
raw | unknown | The raw database output (shape varies by driver) |
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. Empty pages and other query shapes may also need a hasNext / hasPrevious probe:
const result = await client.users
.cursor({
after: previousCursor,
limit: 25,
orderBy: { id: 'asc' },
})
.explain();
console.log(result.statements.map((s) => s.key));
// ["data"] for a populated primary-key pageOptions
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
| Option | Type | PostgreSQL | MySQL | SQLite | Description |
|---|---|---|---|---|---|
analyze | boolean | Yes | Yes | ignored | Execute the statement and report actual execution statistics |
verbose | boolean | Yes | ignored | ignored | Include output schema of each plan node |
costs | boolean | Yes | ignored | ignored | When false, omit estimated startup and total cost |
timing | boolean | Yes | ignored | ignored | When false, omit actual time even with analyze |
summary | boolean | Yes | ignored | ignored | When false, omit the summary line |
name | string | label only | ignored | ignored | A label for the call. Reported in appliedOptions on PostgreSQL but not added to the SQL |
comment | string | Yes | ignored | ignored | Block comment prepended to the explained query (*/ is escaped) |
timeoutMs | number | Yes | Yes | Yes | Timeout for the EXPLAIN itself. Throws RAW_TIMEOUT; 0 or less throws immediately |
Unsupported options are silently ignored. Check statement.ignoredOptions
to detect when a flag had no effect on the current dialect.
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:
const client = better(db, {
schema,
plugins: [forceActivePlugin], // injects { active: true } into every findMany
});
const {
statements: [{ sql }],
} = await client.users.findMany({ where: { id: 1 } }).explain();
// The SQL includes the plugin-injected active filter
console.log(sql);
// contains "active" clauseQuery hooks (beforeQuery, afterQuery) are skipped during .explain().
Only plugin transforms apply.
Cross-dialect behavior
| Feature | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| EXPLAIN prefix | EXPLAIN (OPTIONS) ... | EXPLAIN [ANALYZE] ... | EXPLAIN QUERY PLAN ... |
analyze | Executes the statement | Executes the statement | ignored |
verbose | Extra columns | ignored | ignored |
costs | Toggle cost display | ignored | ignored |
timing | Toggle timing | ignored | ignored |
summary | Toggle summary | ignored | ignored |
name | Reported as applied, not sent | ignored | ignored |
comment | Prepended as block comment | ignored | ignored |
timeoutMs | Yes | Yes | Yes |
Which operations support .explain()?
| Operation | Supports .explain() |
|---|---|
findMany | Yes |
findFirst | Yes |
findOne | Yes |
findUnique | Yes |
count | Yes |
exists | Yes |
paginate | Yes |
cursor | Yes |
create, createMany | No |
update, updateMany, updateEach | No |
delete, deleteMany | No |
upsert, upsertMany | No |