Repository navigation
sqlite: bind parameters the same way better-sqlite3 does. #67353
Description
What is the problem this feature will solve?
Hey 👋
Currently, node:sqlite is close to being a drop-in replacement for better-sqlite3 in query builders, ORMs and drivers, which is great, except parameters are bound differently. Integrations written against better-sqlite3 pass parameters as an array, e.g. Kysely, Knex and Prisma 7:
https://github.com/kysely-org/kysely/blob/67263069a242600e914e8a9e03b118e8cbf4267c/src/dialect/sqlite/sqlite-driver.ts#L117
https://github.com/knex/knex/blob/e25d54bcb707714a17f5a5744eba5c4246bb4d1d/lib/dialects/better-sqlite3/index.js#L52
https://github.com/prisma/prisma/blob/0b596ffecfcc55e37d6a7095c03193e2d28b8090/packages/adapter-better-sqlite3/src/better-sqlite3.ts#L90
node:sqlite treats an array as a named parameters object, with keys '0', '1', etc., so none of them can accept a node:sqlite Database without changes, and wrappers have to adapt every call, e.g. MikroORM 7:
better-sqlite3 flattens arrays into anonymous parameters, in any position, and accepts a single named parameters object in any position:
node:sqlite only treats the first argument as a potential named parameters object:
Lines 3859 to 3860 in 18b32a3
import { Database } from 'node:sqlite'
const db = new Database(':memory:')
db.prepare('select ? as a, ? as b').all([1, 2])
// Error: Unknown named parameter '0'
db.prepare('select ? as a, ? as b, ? as c').all(1, [2, 3])
// Error: Provided value cannot be bound to SQLite parameter 2.
db.prepare('select :x as x, ? as a').all(2, { x: 1 })
// Error: Provided value cannot be bound to SQLite parameter 3.
const stmt = db.prepare('select ? as a, ? as b')
stmt.setAllowUnknownNamedParameters(true)
stmt.all([1, 2])
// [ { a: null, b: null } ] 😬better-sqlite3 |
node:sqlite |
|
|---|---|---|
all(1, 2) |
✅ | ✅ |
all([1, 2]) |
✅ | ❌ Unknown named parameter '0' |
all([1, 2], 3) |
✅ | ❌ Unknown named parameter '0' |
all(1, [2, 3]) |
✅ | ❌ Provided value cannot be bound to SQLite parameter 2. |
all([1], [2, 3]) |
✅ | ❌ Unknown named parameter '0' |
all({ x: 1 }, 2) |
✅ | ✅ |
all(2, { x: 1 }) |
✅ | ❌ Provided value cannot be bound to SQLite parameter 3. |
all([2, 3], { x: 1 }) |
✅ | ❌ Unknown named parameter '0' |
all({ x: 1 }, [2, 3]) |
✅ | ❌ Provided value cannot be bound to SQLite parameter 2. |
This was raised from a different angle (stack size) in #57932, which was closed as stale.
What is the feature you are proposing to solve the problem?
all(), get(), iterate() and run() should bind parameters the same way better-sqlite3 does: arrays are flattened into anonymous parameters, in any position, and a single plain object, in any position, is named parameters.
Every call shape this changes currently throws, or silently binds NULL with setAllowUnknownNamedParameters(true), so no working code relies on the current behavior.
Together with #67352 (reader), this lets Kysely's better-sqlite3 dialect run on a node:sqlite Database as is: migrations, insert ... returning, raw queries, streaming, transactions and introspection all pass with just those two differences shimmed.
What alternatives have you considered?
Only accepting an array as the first argument. It covers the most common call shape, but integrations that use the other shapes would still need changes.
Spreading in userland, statement.all(...parameters). It works up to SQLite's default limit of 32766 parameters, but it's exactly the downstream change we want to avoid: every better-sqlite3-based integration has to special-case node:sqlite, and switching their call sites to spread breaks wrappers that implement the array signature.
Activity