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

Edit: The existing implementation has inconsistent behavior in binding undefined to null. The new proposal is to make this behavior consistent, see comment below https://github.com/nodejs/node/issues/61824#issuecomment-3967772897

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:

// bindUnd