sqlite: add option bindUndefinedToNull #61824
Description
Problem
Currently if any "anonymous parameters" or "named parameters" have an undefined JS value when binding to sqlite statement it will throw an error.
Proposal
Add option bindUndefinedToNull to new DatabaseSync(path[, options]) and database.prepare(sql[, options])
This option would bind any undefined values to null. This is helpful in many cases. undefined most naturally maps to null in sqlite. I would argue this should be the default behavior, as the sqlite driver should be as helpful as possible, throwing an error should be a last resort, but I am ok with making this an option.
Example:
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:', { bindUndefinedToNull: true });
db.exec('CREATE TABLE t (c1, c2, c3)');
const insertAnonParam = db.prepare('INSERT INTO t VALUES (?, ?, ?)');
const insertNamedParam = db.prepare('INSERT INTO t VALUES ($c1, $c2, $c3)');
// c1 is undefined
let c1, c2 = 2, c3 = 3;
insertAnonParam.run(c1, c2, c3);
insertNamedParam.run({ c1, c2, c3 });
insertNamedParam.run({ c2, c3 });
console.log(db.prepare('SELECT * FROM t').all());
// { c1: null, c2: 2, c3: 3 }
// { c1: null, c2: 2, c3: 3 }
// { c1: null, c2: 2, c3: 3 }Alternatives
The alternative is to do param ?? null for every possible undefined value, but this is not very nice:
insertAnonParam.run(c1 ?? null, c2 ?? null, c3 ?? null)
insertNamedParam.run({ c1: c1 ?? null, c2: c2 ?? null, c3: c3 ?? null })
insertNamedParam.run({ c1: obj.c1 ?? null, c2: obj.c2 ?? null, c3: obj.c3 ?? null })Compare to:
// bindUndefinedToNull = true
insertAnonParam.run(c1, c2, c3)
insertNamedParam.run({ c1, c2, c3 })
insertNamedParam.run(obj)Related
The inverse of this issue: readNullAsUndefined #59457
SQLite bind booleans #57862
SQLite bind ArrayBuffer #61396
I think these primitive JS values (undefined, Boolean, ArrayBuffer) should map to their equivalent sqlite values and not throw errors, or at least have the option to do it.