sqlite integer parameters should not be bound as doubles

[andrewitsover]

Version

v26.2.0

Platform

Darwin rocksteady 25.5.0 Darwin Kernel Version 25.5.0: Mon Apr 27 20:40:51 PDT 2026; root:xnu-12377.121.6~2/RELEASE_ARM64_T8112 arm64

Subsystem

sqlite

What steps will reproduce the bug?

I am assuming parameters that are number are always bound as type double, but integers should be bound as type integer so that SQLite performs integer arithmetic and returns a type of integer instead of a type of double.

const db = new DatabaseSync(':memory:', { readBigInts: true });
const big = db.prepare('select 2 * 2');
const bigResult = big.all();
const notBig = db.prepare('select 2 * ?');
const notResult = notBig.all(2);

This is the offending code in nodejs:

node/src/node_sqlite.cc

Line 2703 in 19c46ab

const double val = value.As<Number>()->Value();

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

Every time

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

Integers should be bound as integers not double as they are integers.

What do you see instead?

Integers being bound as double.

Additional information

No response

[Renegade334]

It would be very confusing indeed if the inferred storage class of the JS number type was not constant, which is what you're suggesting.

A number in JS is a double; the only true integer primitive is bigint. In the (uncommon) instance where the storage class is inferred from JS input rather than being explicitly known by SQLite, the current type inference rules are entirely appropriate.

[andrewitsover]

From what I understand, SQLite chooses its storage class based on the actual value being stored, not based on whatever bind command you use. It is much more confusing that 3 / 2 returns different results depending on whether the 2 is a parameter or not!

const db = new DatabaseSync(':memory:');
const first = db.prepare('select 3 / 2');
console.log(first.all()); // 1
const second = db.prepare('select 3 / ?');
console.log(second.all(2)); // 1.5

Further, I don't know if this is the same code, but if so, bun does it how I would expect:

https://github.com/oven-sh/bun/blob/09703da1aeb34e8415e700c0115cbf203e7b7351/src/jsc/bindings/sqlite/JSSQLStatement.cpp#L871

[Renegade334]

Further, I don't know if this is the same code, but if so, bun does it how I would expect

And better-sqlite3 uses the same convention as node:sqlite.

@nodejs/sqlite opinions welcome!

[andrewitsover]

It is probably better to leave it how it is. Anyway, something to think about.

[TrevorBurnham]

Confirmed that:

• better-sqlite3 binds integers as type double (the current node:sqlite behavior).
• sqlite3-wasm binds integers as type integer (the proposed behavior), but with some caveats:
  • If the integer fits into and int32, it's always bound as integer.
  • If the integer is larger, it's bound as integer if BigInt is enabled, or as double otherwise.
• bun's bun:sqlite binds integers as integer, while their node:sqlite module binds integers as double to match Node's behavior.

I found some discussion of the better-sqlite3 behavior here: WiseLibs/better-sqlite3#627 (comment) I think the strongest argument for keeping the existing behavior is expressed in this comment:

Yes. It would be much worse for better-sqlite3 to assume that any number without a fractional part is an INTEGER instead of a REAL. That would make it impossible to insert 666.0 into SQLite.

That is, unlike other languages, JavaScript doesn't make a distinction between 666 and 666.0. Conversely, it does offer a way to make integers explicit: Append n, declaring it as a BigInt. So the "all number primitives are bound as double, all BigInts are bound as integer" rule makes it easy for devs to control the binding (if they know the rule).

For that reason, I'd lean toward keeping the current behavior, and updating the docs to highlight the issue and to show BigInt use when working with integers:

db.exec("create table t(k text primary key, v)");
db.exec("insert into t values('3','found')");
db.prepare('select v from t where k = ?').get(3); // undefined
db.prepare('select v from t where k = ?').get(3n); // { v: 'found' }