Detecting SQLite Full Table Scans in Node.js

Back in July, Aaron Patterson wrote about detecting full table scans with SQLite. The trick is that you don't need EXPLAIN QUERY PLAN for this. SQLite already keeps a per-statement counter of how many rows it walked during a scan, and you can read it after the query runs. If the number is greater than zero, that statement scanned.

One day later, Kevin Gibbons opened an issue on nodejs/node asking for the same thing in node:sqlite, citing that post. There was no way to get at sqlite3_stmt_status() from JavaScript. I picked it up, and it landed today. Two methods on StatementSync:

statement.stat(counter)   // read one counter
statement.resetStats()    // zero all of them

The scan check

Same shape as Aaron's Ruby example. A thousand users, a query on a column with no index:

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

const insert = db.prepare('INSERT INTO users (name, age) VALUES (?, ?)');
for (let i = 0; i < 1000; i++) {
  insert.run(`user-${i}`, i % 80);
}

const stmt = db.prepare('SELECT * FROM users WHERE age = ?');

function query(age) {
  const rows = stmt.all(age);
  console.log('fullscanStep:', stmt.stat('fullscanStep'));
  stmt.resetStats();
  return rows;
}

query(30);
db.exec('CREATE INDEX users_age_idx ON users (age)');
query(30);

fullscanStep: 999
fullscanStep: 0

999 before the index, 0 after. vmStep moves too, from 3059 down to 101 for the exact same result set.

Note the resetStats() call. The counters are cumulative for the lifetime of the prepared statement, so if you reuse a statement across a request loop - which is the whole point of preparing it - you need to zero them between measurements or you're reading a running total.

The counters

stat() takes a name and returns a number:

filterMiss and filterHit need SQLite 3.38.0 or newer. Node bundles a recent one, so this only bites if you built with --shared-sqlite against something old. In that case the names throw ERR_INVALID_ARG_VALUE.

memused is the odd one. It reports current usage rather than an accumulated count, so SQLite ignores the reset flag for it and resetStats() leaves it alone.

A good usage for it

Aaron floated wiring this into Rails to warn or raise in test and development. Same idea here, and it's cheap - stat() reads an integer SQLite already maintains. The whole guardrail is six lines:

import assert from 'node:assert';

function assertNoScan(stmt, ...params) {
  stmt.resetStats();
  const rows = stmt.all(...params);
  assert.strictEqual(
    stmt.stat('fullscanStep'), 0,
    `full table scan in: ${stmt.sourceSQL}`,
  );
  return rows;
}

Against a table with no index on email, that fails with 99 !== 0 and prints the offending SQL.

This matters more now that a lot of SQL gets written by an agent. A model emitting WHERE email = ? doesn't know whether email is indexed, and nothing in its output marks the guess.

Review misses it too, because the query is correct. The index only becomes load-bearing in production, months later. fullscanStep turns that into an assertion CI can fail on.

A five-row indexed table still reports zero, so small fixtures don't false-alarm. And the counters are per-statement, so you opt in query by query - which you want, since plenty of queries are supposed to scan.

It's on main, so it ships in Node 27. If you wire the assertion into a test helper, I'd like to hear how it goes.

Thanks for reading!