better-drizzle
0
Querying

Explain

Inspect query plans without executing queries using .explain() on any read operation.

Every read operation in Better Drizzle returns an explainable result -- a lazy promise that exposes an .explain() method. Calling .explain() inspects the query plan the database would execute, without actually running the query.

Basic usage

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

const plan = await client.users
  .findMany({ where: { active: true } })
  .explain();

console.log(plan.driver);      // "sqlite" | "pg" | "mysql"
console.log(plan.operation);   // "findMany"
console.log(plan.statements);  // array of ExplainStatement

The real query never runs. You can safely call .explain() in tests, diagnostics, or monitoring without side effects.

Lazy execution

The result of a read call is a deferred promise. The actual database query starts only when you .then(), .catch(), or .finally() it:

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

// At this point, no query has been sent to the database

const plan = await result.explain({ analyze: true });
// Still no real query -- only the EXPLAIN ran

const users = await result;
// NOW the real query runs

This means you can inspect the plan and decide whether to run the query at all.

Result shape

ExplainResult contains three fields:

FieldTypeDescription
driver'sqlite' | 'pg' | 'mysql'The dialect that produced the plan
operationstringThe operation name (e.g. "findMany", "paginate")
statementsExplainStatement[]One or more statement details

Each ExplainStatement contains:

FieldTypeDescription
keystringRole of the statement: "data", "total", "count", "exists", "probe:hasNext", "probe:hasPrevious"
sqlstringThe compiled SQL without the EXPLAIN prefix
paramsunknown[]Bound parameter values
appliedOptionsPartial<ExplainOptions>Options the driver actually recognized
ignoredOptionsArray<keyof ExplainOptions>Options the driver silently dropped
rawunknownThe 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 produces the data query plus runtime probe queries for hasNext / hasPrevious:

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"]

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

OptionTypePostgreSQLMySQLSQLiteDescription
analyzebooleanYesYesignoredRun the query and report actual execution statistics
verbosebooleanYesignoredignoredInclude output schema of each plan node
costsbooleanYesignoredignoredWhen false, omit estimated startup and total cost
timingbooleanYesignoredignoredWhen false, omit actual time even with analyze
summarybooleanYesignoredignoredWhen false, omit the summary line
namestringYesignoredignoredPrepared statement name
commentstringYesignoredignoredSQL comment prepended to the query (sanitized)
timeoutMsnumberYesYesYesTimeout in ms for the EXPLAIN execution itself

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 plan = await client.users
  .findMany({ where: { active: true } })
  .explain({ analyze: true, verbose: true });

const stmt = plan.statements[0];

// 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 plan = await client.users
  .findMany({ where: { id: 1 } })
  .explain();

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

Query hooks (beforeQuery, afterQuery) are skipped during .explain(). Only plugin transforms apply.

Cross-dialect behavior

FeaturePostgreSQLMySQLSQLite
EXPLAIN prefixEXPLAIN (OPTIONS) ...EXPLAIN [ANALYZE] ...EXPLAIN QUERY PLAN ...
analyzeRuns query with statsRuns query with statsignored
verboseExtra columnsignoredignored
costsToggle cost displayignoredignored
timingToggle timingignoredignored
summaryToggle summaryignoredignored
namePrepared statement nameignoredignored
commentPrepended as block commentignoredignored
timeoutMsYesYesYes

Which operations support .explain()?

OperationSupports .explain()
findManyYes
findFirstYes
findOneYes
findUniqueYes
countYes
existsYes
paginateYes
cursorYes
create, createManyNo
update, updateMany, updateEachNo
delete, deleteManyNo
upsert, upsertManyNo

Reads

Pagination

Hooks

Plugins

Pagination

Use paginate() for offset pages and cursor() for cursor navigation.

Create, update & delete

The write surface — create, createMany, update, updateMany, updateEach, delete, deleteMany, upsert, and upsertMany.

On this page

Basic usageLazy executionResult shapeMulti-statement operationsOptionsOptions referenceChecking applied vs ignored optionsPlugin transforms are reflectedCross-dialect behaviorWhich operations support .explain()?