Unable to EXPLAIN a query with parameters

[merceyz]

Running EXPLAIN on a query that contains parameters doesn't seem to work correctly as an error is thrown because the parameters aren't set but that shouldn't be required.

Tested with better-sqlite3 v11.2.1 and Node.js v18.20.4.

Reproduction:

import Database from 'better-sqlite3';

const db = new Database(':memory:');

// Works
console.log(db.prepare('EXPLAIN select 1').all());

// Doesn't work
console.log(db.prepare('EXPLAIN select ?').all());
console.log(db.prepare('EXPLAIN select :foo').all());

Error:

RangeError: Too few parameter values were provided
    at file:///tmp/test.mjs:9:44
    at ModuleJob.run (node:internal/modules/esm/module_job:195:25)
    at async ModuleLoader.import (node:internal/modules/esm/loader:337:24)
    at async loadESM (node:internal/process/esm_loader:34:7)
    at async handleMainPromise (node:internal/modules/run_main:106:12)

[neoxpert]

I just played around with it a bit, also in a C++ application using the same functions. It looks like that a prepared statement really requires the parameters to be bound, even if you just want to use explain. That would not be a pure better-sqlite3 problem. It might be a shortcoming that there is no way of just executing a statement and fetch the result, but maybe I am missing something :-/

[merceyz]

Note that it works with the built-in node:sqlite module https://nodejs.org/docs/latest/api/sqlite.html.

[mceachen]

@merceyz thanks for taking the time to describe this issue.

This feels arguably like a feature request, not a bug--I would have expected the clause to require bound parameters. @JoshuaWise feel free to re-open if you think this should be entertained.

[JoshuaWise]

I think this is a legitimate use-case, and it currently doesn't work as a result of better-sqlite3's strict parameter checking, not due to SQLite's underlying behavior.

I wonder what the best way to solve this is. Two options that come to mind:

• parse the prepared statement to determine that it's an EXPLAIN statement, and treat it differently
• provide a db.explain() function, similar to db.pragma()