Repository navigation
sqlite: inconsistent undefined bind to null #61824
Description
Activity
While coding this option I see that
node:sqlitealready 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:
- make the behavior consistent and bind
undefinedJS values to NULL - add option
bindUndefinedToNullto make the remaining cases ofundefinedvalues 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.
- make the behavior consistent and bind
- changed the title
[-]sqlite: add option `bindUndefinedToNull`[/-][+]sqlite: inconsistent undefined bind to null[/+]on Feb 26, 2026 I will note that the official SQlite WASM implementation also binds
undefinedto 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.
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.- added a commit that references this issue
on Sep 1, 2026 - added a commit that references this issue
on Sep 8, 2026 - added a commit that references this issue
on Sep 16, 2026
Problem
Currently if any "anonymous parameters" or "named parameters" have an
undefinedJS 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 optionbindUndefinedToNullto new DatabaseSync(path, options) and database.prepare(sql, options)This option would bind anyundefinedvalues tonull. This is helpful in many cases.undefinedmost naturally maps tonullin 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:
Alternatives
The alternative is to do
param ?? nullfor every possible undefined value, but this is not very nice:Compare to:
Related
The inverse of this issue:
readNullAsUndefined#59457 with PR #61472SQLite bind booleans#57862 with PR #62001SQLite bind ArrayBuffer#61396I 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.