sqlite: inconsistent undefined bind to null

[mike-git374]

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 #61824 (comment)

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 with PR #61472
SQLite bind booleans #57862 with PR #62001
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.

[mike-git374]

While coding this option I see that node:sqlite already partially implements this feature, so the current implementation is a bit inconsistent:

// c1 = undefined
let c1, c2 = 2, c3 = 3;
insertAnonParam.run(c1, c2, c3); // throws error
insertNamedParam.run({ c1, c2, c3 }); // throws error
insertNamedParam.run({ c2, c3 }); // does NOT throw error, c1 binds to NULL

The SQLite C Interface states that "Unbound parameters are interpreted as NULL", so a non-existent JS value in a named parameter object (which is considered undefined) already binds to NULL.

I see two options from here:

1. make the behavior consistent and bind undefined JS values to NULL
2. add option bindUndefinedToNull to make the remaining cases of undefined values bind to NULL

Personally I prefer to go with # 1 which is simpler and more consistent with existing behavior and does not require an additional option. It's possible people would disagree and prefer to make this an option, but then you still have the inconsistent behavior. I prefer to assume the user intentionally provided an undefined value in which case the obvious binding is to NULL. If the user does not want undefined values binding to NULL they can check the value before executing the statement.

[mike-git374]

I will note that the official SQlite WASM implementation also binds undefined to NULL for both named (object) and unnamed (array) parameters:

a value of undefined as an array or object property when binding an array/object is treated the same as null.

[github-actions]

This issue has been marked as stale due to 90 days of inactivity.
It will be automatically closed in 30 days if no further activity occurs. If this is still relevant, please leave a comment or update it to keep it open.