`node:sqlite` read-only statements may report nonzero change counts

[turbocrime]

Version

v24.7.0

Platform

Darwin cut.local 24.5.0 Darwin Kernel Version 24.5.0: Tue Apr 22 19:54:43 PDT 2025; root:xnu-11417.121.6~2/RELEASE_ARM64_T8132 arm64

Subsystem

sqlite

What steps will reproduce the bug?

Several repro cases here https://github.com/turbocrime/node-sqlite-changes-repro

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE test_table (id INTEGER, name TEXT)");

const insertResult = db.prepare("INSERT INTO test_table VALUES (1, 'test1')").run();

const selectResult = db.prepare("SELECT * FROM test_table").run();

console.log(selectResult.changes);
// Expected: 0, Actual: 1

db.close();

How often does it reproduce? Is there a required condition?

Reproduces when:

• One or more read-only statements directly follow an effectful statement reporting nonzero changes

Does not reproduce when:

• A read-only statement is the first operation in the session
• A read-only statement follows an effectful statement reporting zero changes
• An effectful statement reports any number of changes

What is the expected behavior? Why is that the expected behavior?

Some SQL statements never modify any rows. Those statements should always report 0 changes.

What do you see instead?

Read-only operations report the change count from the most recent effectful statement.

Additional information

Tests of the sqlite module seem to expect this behavior:

node/test/parallel/test-sqlite-statement-sync.js

Lines 177 to 187 in 538186b

	test('executes a query and returns change metadata', (t) => {
	const db = new DatabaseSync(nextDb());
	t.after(() => { db.close(); });
	const setup = db.exec(`
	CREATE TABLE storage(key TEXT, val TEXT);
	INSERT INTO storage (key, val) VALUES ('foo', 'bar');
	`);
	t.assert.strictEqual(setup, undefined);
	const stmt = db.prepare('SELECT * FROM storage');
	t.assert.deepStrictEqual(stmt.run(), { changes: 1, lastInsertRowid: 1 });
	});

[turbocrime]

this behavior seems to accurately represent the underlying C api
https://sqlite.org/c3ref/changes.html

but i found the behavior surprising. i expected a returned results object to contain data corresponding to the call.

this behavior also seems to disagree with other implementations like better-sqlite3

[Renegade334]

Not only the API, but also the builtin changes() SQL function.

(better-sqlite3 uses sqlite3_total_changes() to return zero if the last run statement didn't alter the database, but overriding the sqlite definition of "changes" at the application level doesn't feel like a very satisfactory approach. node-sqlite3's behaviour, for example, is the same as node:sqlite's.)

$0.02: The user should have a definitive notion of what sort of query is being executed, which will govern whether the value of changes or the last inserted rowid have any meaning in context.