Repository navigation
SQLite bind booleans #57862
Description
Activity
Please note I only propose automatic conversion for boolean parameters to 0 and 1, not the other way around parsing results to booleans.
This seems like it would lead to an awkward API.
Reacted by RenéPlease note I only propose automatic conversion for boolean parameters to 0 and 1, not the other way around parsing results to booleans.
This seems like it would lead to an awkward API.
@cjihrig open to suggestions! It is unclear to me how to reliably know which columns in a result to convert to boolean types—would you:
- infer the columns automatically based on only the presence of 0s, 1s, and nulls in the column
- have the user define the columns to convert in the statement
- some other way like sqlite internals that I’m unaware of
Regardless, I think allowing booleans as inputs would be helpful. As for outputs, I’d love to learn more about how that could work.
It would be useful to know how all of the other libraries handle this case. I think it would be awkward if we supported writing, but not reading booleans. It would be even worse if we were the only library doing so.
This seems like it would lead to an awkward API.
I agree. This might end up being a good addition, but I also +1 being careful about these changes.
SQLite supports booleans!
I think this would be better summarised as "sqlite3 doesn't support booleans, but provides
TRUEandFALSEas convenient keywords". There is no underlying boolean data type, and the sqlite3 API itself does not allow for binding boolean values, only integers/reals.The general (albeit not unanimous) consensus is that sqlite3 drivers only bind numeric values to numeric parameters, and reject booleans. I wouldn't personally add my support for this proposal.
One thing that may be worth considering separately (which would also address this intended usage case) is whether there's the appetite to implement something like Python's adapter callbacks, to allow users to customise the transformation of non-SQLite types to valid SQLite values.
Reacted by Gürgün Dayıoğlu, Jurj Andrei George, WillAvudim, johnpyp and Bart LouwersThere has been no activity on this feature request for 5 months. To help maintain relevant open issues, please add the never-stale
Issues and PRs exempt from automated stale handling. label or close this issue if it should be closed. If not, the issue will be automatically closed 6 months after the last non-automated comment.
For more information on how the project manages feature requests, please consult the feature request management document.Not stale!
It seems there is:
In the same way that BigInts and Null can be coalesced to the correct types, it would be nice for booleans to be coalesced to integers when binding values too.
I think it would be useful to coerce Booleans to 0 or 1 on the way in but only on the way out if it was required to explicitly set a flag to have it happen as it would break things big time. At the moment I have calls full of
isaCondition?1:0that could be avoided.There is another "nice to have" helpers in a similar vein. I have a function called
nullIf0len(field)that some projects use all over the place. It could not be universal, but being able to set this to happen on a database connection basis.I think that rather than add individual handling options for every fathomable usage case across the ecosystem, there's probably more of a case for some sort of adapter facility for users to serialise and deserialise their data according to their own design.
node:sqliteshould transform Boolean values to1and0going into the database, not throw an Error. SQLite already transformstrueandfalsekeywords to1and0, the driver should match this behavior. Going out of the database leave it as 1 and 0. SQLite has flexible types, thenode:sqlitedriver is very inflexible in this case. The driver should be helpful not get in the way.@jlarmstrongiv: "allowing booleans as inputs would be helpful"
@akc42: "would be useful to coerce Booleans to 0 or 1 on the way in"
@mike-git374: "transform Boolean values to 1 and 0 going into the database"import { DatabaseSync } from 'node:sqlite'; const db = new DatabaseSync(':memory:'); db.exec('create table t (c)'); const insert = db.prepare('insert into t values (?)'); // should not throw Error, bind 1 and 0 insert.run(true); insert.run(false); console.log(db.prepare('select * from t').all()); // [ // { c: 1 }, // { c: 0 } // ]
Reacted by John L. Armstrong IV@jlarmstrongiv @cjihrig I did some digging and it looks like sqlite3 is one of the few drivers that explicitly handles this transformation. In their native layer (src/statement.cc#L196), they map JS Booleans to 1 or 0 before binding them to the standard SQLite C API.
I’ve verified this behavior with a quick test:
// sqlite3 handles the 'true' boolean by coercing it to 1 internally db2.run('INSERT INTO users (name, isActive) VALUES (?, ?)', ['Ali', true], (err) => { if (err) throw err; });
If we want to align with this DX, I’m happy to put together a PR to handle this coercion, Please let me know
Reacted by John L. Armstrong IV, mike-git374 and Colin IhrigReacted by John L. Armstrong IVSqlite itself maps
trueandfalsekeywords to 1 and 0, so yes Node.js sqlite should do the same. Throwing errors for usingtrueandfalseis not helpful to the user. @thisalihassan yes that would be helpfulFWIW the official
@sqlite.org/sqlite-wasmpackage also transparently transforms JavaScript valuestrueandfalseto1and0.Reacted by Colin Ihrig, wanton7 and mike-git374This 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.Not stale, needs to be fixed, PR ready #62001
Reacted by John L. Armstrong IV- added 2 commits that reference this issue
on Aug 25, 2026
What is the problem this feature will solve?
Currently, all booleans need to be transformed from
trueto1andfalseto0to bind them as SQLite parameters.Trying to use a boolean results in an error:
What is the feature you are proposing to solve the problem?
SQLite supports booleans! I propose automatically transforming booleans to numbers, in the same way SQLite treats the boolean keywords:
Please note I only propose automatic conversion for boolean parameters to 0 and 1, not the other way around parsing results to booleans.
What alternatives have you considered?
Writing a wrapper or manually handling the conversion.