sqlite: `statement.setReadNullAsUndefined()`

[LiviaMedeiros]

What is the problem this feature will solve?

In JS, we have two different oddballs null and undefined. In SQL, they are represented as NULL.
Inserting them to database makes them coerced to NULL; however selecting to JS realm always produces nulls, which is not always desirable.

What is the feature you are proposing to solve the problem?

Similarly to statement.setReadBigInts(enabled), we can have statement.setReadNullAsUndefined() to make reads return NULLs as undefined instead of null.

Also we can have database-wide readNullAsUndefined option in new DatabaseSync(path[, options])

What alternatives have you considered?

Drowning in boilerplate code.

[bakkot]

When does getting null instead of undefined matter?

[jac0bandres]

Honestly, this would be a good feature and I've run into a couple of cases where I have to convert SQL null types by hand. I've opened a pull request with the change: #59462.

If it gets approved there could be a database-wide option after. But this one only implements setReadNullAsUndefined() on statements.

[LiviaMedeiros]

When does getting null instead of undefined matter?

Most notably, whenever we serialize the data to store or send. In JSON, every null consumes space, increases parsing time, reduces readability.

Web API, especially having databases with sparse columns, usually should prefer not sending nulls to avoid wasting traffic.

Also this is semantically more correct behaviour when we expect business logic to add new columns that are sparse by design and should not affect previously inserted rows. hasDiscountOnApril2010Promo, bonusAttackDuring1252Event, isAiGenerated, showSpecialWarningPopup, extraParams, and other stuff that inevitably happens someday.

[geeksilva97]

Is there any use case for this? In which situation does an undefined behave in a way that, with null, it would be a problem?

I was looking into other players, and it's just a null.

• bette-sqlite: https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#binding-parameters
• node-sqlite3: https://github.com/TryGhost/node-sqlite3/blob/528e15ae605bac7aab8de60dd7c46e9fdc1fffd0/src/statement.cc#L814-L816

[jac0bandres]

Is there any use case for this? In which situation does an undefined behave in a way that, with null, it would be a problem?

Besides the use cases we've laid out in this issue and the PR discussion, here are some more sources:

Drizzle ORM request for distinct null and undefined handling: drizzle-team/drizzle-orm#2745
Another for Supabase: supabase/supabase-js#244
MongoDB distinction between the types and their use cases: https://www.mongodb.com/docs/manual/reference/bson-types/migrate-undefined/

[geeksilva97]

Is there any use case for this? In which situation does an undefined behave in a way that, with null, it would be a problem?

Besides the use cases we've laid out in this issue and the PR discussion, here are some more sources:

Drizzle ORM request for distinct null and undefined handling: drizzle-team/drizzle-orm#2745 Another for Supabase: supabase/supabase-js#244 MongoDB distinction between the types and their use cases: https://www.mongodb.com/docs/manual/reference/bson-types/migrate-undefined/

Thank you for the references.

What I saw from these links is people complaining about typing (where they'd like to use something like myprop?: string). The MongoDB link is about a migration from undefined to null.

The players I brought are more similar to what node:sqlite is.

[geeksilva97]

Would you have an opinion here @cjihrig @nodejs/sqlite ?

[cjihrig]

Would you have an opinion here

Are any other libraries actually implementing something like this? I think it's good to differentiate between feature requests and actual implementations.

That said, I'm not opposed to this as long as it doesn't break anything. I also think it's worth benchmarking since SQLITE_VALUE_TO_JS() is likely a pretty hot path, and taking a performance hit there wouldn't be a good idea for a feature that no other libraries ship.