Back in July, Aaron Patterson wrote about detecting full table scans with SQLite.
The trick is that you don't need 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 asking for the same thing in , citing that post.
There was no way to get at from JavaScript.
I picked it up, and it landed today.
Two methods on : The scan check Same shape as Aaron's Ruby example.
A thousand users, a query on a column with no index: 999 before the index, 0 after. moves too, from 3059 down to 101 for the exact same result set.
Note the 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 takes a name and returns a number: Name What it counts Rows stepped through during a full table scan Sort operations performed Rows inserted into transient indices SQLite built to speed up a join Virtual machine operations executed Automatic re-prepares after a schema change Execution cycles started Bloom filter results that still required the join step Join steps skipped because a Bloom filter returned not-found Approximate heap bytes held by the statement and need SQLite 3.38.0 or newer.
Node bundles a recent one, so this only bites if you built with against something old.
In that case the names throw . is the odd one.
It reports current usage rather than an accumulated count, so SQLite ignores the reset flag for it and 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 - reads an integer SQLite already maintains.
The whole guardrail is six lines: Against a table with no index on , that fails with and prints the offending SQL.
This matters more now that a lot of SQL gets written by an agent.
A model emitting doesn't know whether 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. 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 , 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!