sqlite: add reader to Statement, aligned with better-sqlite3 #67352

What is the problem this feature will solve?

Hey 👋

Currently, the only way to tell whether a prepared Statement returns rows is statement.columns().length > 0, which is great when you also need the column metadata, except when all you need is a yes/no. columns() builds a metadata object per column, so it gets slower the wider the result set is.

Query builders and ORMs need this answer on every execution, to pick all() vs run(). Kysely does it for raw SQL, where insert ... returning and friends can't be classified from the query alone (kysely-org/kysely#1292). Knex, TypeORM and Prisma 7 do it for every query:

https://github.com/knex/knex/blob/e25d54bcb707714a17f5a5744eba5c4246bb4d1d/lib/dialects/better-sqlite3/index.js#L51
https://github.com/typeorm/typeorm/blob/5a572a40bae2f85a1d5f7b87a0aa88bbbaec5ab3/packages/typeorm/src/driver/better-sqlite3/BetterSqlite3QueryRunner.ts#L122
https://github.com/prisma/prisma/blob/0b596ffecfcc55e37d6a7095c03193e2d28b8090/packages/adapter-better-sqlite3/src/better-sqlite3.ts#L110

These integrations were written against better-sqlite3, which exposes it as a reader boolean, computed once at prepare time from sqlite3_column_count:

https://github.com/WiseLibs/better-sqlite3/blob/dbc2ea1165fef1f599b9be12faea33fa5e9d7ffb/src/objects/statement.cpp#L142-L155

On node:sqlite, they're left with paying for columns() or guessing. Prisma 8 calls columns() on every write, and MikroORM 7 fakes reader with regexes over the SQL text:

https://github.com/prisma/prisma/blob/557c0d4c1b1061b8fff954dc96598ff4db95d262/packages/3-targets/sqlite/3-driver/src/sqlite-driver.ts#L85
https://github.com/mikro-orm/mikro-orm/blob/14737a2349c834dbb39e1dc82bd5adf32fb2531c/packages/sql/src/dialects/sqlite/NodeSqliteDialect.ts#L31

node:sqlite already calls sqlite3_column_count internally, but only exposes it through columns():

columns().length > 0 on v26.13.0 (darwin-arm64):

insert (0 cols)    20 ns/op
select 1 col      315 ns/op
select 8 cols   2,202 ns/op
select 32 cols  8,653 ns/op

For comparison, prepare() of the 8 column select is ~5,200 ns/op, and better-sqlite3's stmt.reader is ~8 ns/op.

import { Database } from 'node:sqlite'

const db = new Database(':memory:')
db.exec('create table t (id integer primary key, name text, a, b, c, d, e, f)')

const N = 1_000_000
const bench = (label, fn) => {
  for (let i = 0; i < 1e5; i++) fn()
  const start = process.hrtime.bigint()
  for (let i = 0; i < N; i++) fn()
  console.log(label, (Number(process.hrtime.bigint() - start) / N).toFixed(1), 'ns/op')
}

for (const [label, sql] of [
  ['insert (0 cols)', 'insert into t (name) values (?)'],
  ['select 1 col', 'select id from t'],
  ['select 8 cols', 'select * from t'],
  ['select 32 cols', 'select *, *, *, * from t'],
]) {
  const stmt = db.prepare(sql)
  bench(label, () => stmt.columns().length > 0)
}

What is the feature you are proposing to solve the problem?

A side-effect-free reader getter on Statement, next to sourceSQL and expandedSQL, with the same semantics as better-sqlite3 (true when the statement returns data):

columns() itself was added to mirror better-sqlite3 (#57490). This is purely additive, like stat() and resetStats() (#64541), and removes one of the differences that keep better-sqlite3-based integrations from accepting a node:sqlite Database as is.

What alternatives have you considered?

A columnCount getter that returns sqlite3_column_count as is. It's more general, and reader would just be columnCount > 0, but integrations written against better-sqlite3 would have to special-case it.

sqlite3_stmt_readonly is not a substitute, insert ... returning is not read-only but does return rows.

Classifying the SQL text or the query builder's AST in userland works for builder queries, but not for raw SQL, which is why MikroORM's regexes above can misclassify statements.