Repository navigation
Expose SQLite? #53264
Description
Activity
I imagine this idea will get a lot of support and a lot of opposition. If someone else handles the consensus building aspect, I'd be happy to work on the implementation side.
Reacted by Moshe Atlow, Vinicius Lourenço, Matteo Collina, Matthew McEachen, Trivikram Kamat, Sámal Rasmussen, Cangit, Jasper Kelder, Busticated, Leonardo Matos and 1 moreShall we use the same interface as https://github.com/TryGhost/node-sqlite3?
@gabrielschulhof On the rare occasion that I voluntarily write JavaScript code, I personally prefer the
better-sqlite3API, which also tends to be significantly faster.That being said, the non-blocking behavior of
node-sqlite3better matches the Node.js I/O model. If you have a slow storage medium or if you use a slow VFS, e.g., with a network resource as its backend, then the asynchronousnode-sqlite3API is a better choice. Similarly, applications that do not utilize SQLite transactions efficiently will spend a lot of time waiting for each change to be committed, which is slow even if the underlying storage is fast, so using synchronous APIs in those cases unnecessarily blocks the event loop. Applications that use well-designed transactions and that run on a very fast storage backend, on the other hand, will see a lot of unnecessary overhead from asynchronous APIs.Perhaps Node.js could allow native addons to link against the bundled SQLite dependency instead and leave the API to third-party packages.
Reacted by Cody CasterlineI think at the very least let's land the
localStoragePR which would get SQLite into the codebase. After that, my concern would be semver: if we expose SQLite, then we can't update it any faster than our release cycle, unless we make some kind of exception for it.Reacted by Aviv KellerFWIW, SQLite has followed semver since version 3.9.0 in 2015, and the current major version is still 3:
The current value for X is 3, and the SQLite developers plan to support the current SQLite database file format, SQL syntax, and C interface through at least the year 2050. Hence, one can expect that all future versions of SQLite for the next several decades will begin with "3.".
Reacted by Geoffrey Booth, Adarsh Madrecha, Sámal Rasmussen, Zzzen, Busticated and Nick OliverI would like to bring attention to esqlite by @mscdex, which outperform(s/ed) better-sqlite3 in a number of cases while being asynchronous. https://github.com/mscdex/esqlite
It looks like we have consensus around experimenting with this (i.e. prototyping a sqlite binding in core), just for further visibility I'll also ping the TSC in chat.
As for the API I personally really prefer a synchronous one over an asynchronous one since SQLite is built to run in-process. I think in the common use case this is significantly simpler and likely faster. That said, an asynchronous promise based API should also be fine.
Funnily enough 2 people asked me about this feature today - a good luck omen it's something users find interesting.
We already see different opinions on the API. If there is no "one size fits all" API, should we have one in core? If we make it possible for native addons to link against the SQLite dependency that's embedded in Node.js, then that should let third-party packages define whatever API they want without having to ship SQLite.
We already see different opinions on the API. If there is no "one size fits all" API
Currently the different opinions are basically sync vs async.
The basic API could be something like this
class Database { // path can be :memory: or the file path constructor(path: string, options?: { readonly?: boolean, create?: boolean }) {} query(statement: string): Statement; // works like prepare, like bun is doing right know open(): void; // maybe we can just open automatically? and add a option to disable auto-open close(): void; } type AllResult = Array<any>; type RunResult = void; // maybe boolean? type GetResult = any; class Statement { all(...params: any[]): Promise<AllResult>; run(...params: any[]): Promise<RunResult>; get(...params: any[]): Promise<GetResult>; // we can also introduce sync methods, like fs allSync(...params: any[]): AllResult; runSync(...params: any[]): RunResult; getSync(...params: any[]): GetResult; }
Sync has advantages, and async too, so ship both and be happy.
In the first iteration, we can stick with the easiest/simplest implementation, which is probably the sync version.
So we can iterate and add the asynchronous versions and then add more methods (pluck, iterate, values, etc...)EDIT: I didn't talk about transactions because it didn't even matter in this first iteration, is better to discuss this method when we have at least the basic methods, then we will have more knowledge on what could be the best API for exposing transactions.
If we make it possible for native addons to link against the SQLite dependency that's embedded in Node.js, then that should let third-party packages define whatever API they want without having to ship SQLite.
The point is that you don't need to use third-party packages to be able to use sqlite, but you can still do that even if we have our own API.
Reacted by Benjamin Gruenbaum and Mehmet Ali PekerWe already see different opinions on the API
We've had very little api discussion so far, I think it's fine to talk about stuff and see if we can reach consensus?
- Reacted by Bruno Heridet, Benjamin Gruenbaum, Adarsh Madrecha and Sukka
@tniessen looping back to your comment here: #53264 (comment) I tend to think the better-sqlite3 sync API is better for most cases and given SQLite's execution model and the motivation of users asking to add it to Node.js (not as a DB server replacement but for local configuration, at least that's what I've been hearing). I see your point though.
There is interesting discussion here: https://sqlite.org/forum/forumpost/7c90893579 as well.
I think it may be a good idea to reach out to the SQLite team and ask, so I went ahead and opened https://sqlite.org/forum/forumpost/96f9f78fc1
I think it makes the most sense to do something similar to the
fsmodule here and have synchronous and asynchronous (Promise based, no callbacks) APIs. For the majority of cases, synchronous APIs are good enough, but there are almost certainly cases where async will be preferred (and it is more Node-like). I have started writing some code for the DB connection already.EDIT: Very early, but here is the branch.
Reacted by Vinicius Lourenço and Kevin GibbonsAnother thing I think is worth discussing is whether or not it makes sense to expose this as its own module (
node:sqlite) or as part of another module such asnode:util.One note: if you do decide to include SQLite in Node.js, please statically compile an up-to-date SQLite library instead of relying on the host's SQLite library!
This is a problem with Python's SQLite library and Bun's SQLite library: By default they use whatever SQLite version the OS has installed, which leads to headaches. Some default SQLite builds omit features like SQLite extensions, JSON, or math functions. Or they are stuck on old version of SQLite which lack window functions,
JSONB, datetime utils, orSTRICTtables. This is even documented in Bun's docs and Python's docs.So if you statically compile a recent SQLite version, say
3.46, you'd have full control of what SQLite features are available across different platforms. And if someone wanted to use a different/more up-to-date SQLite version, they can always reach forbetter-sqlite3or another 3rd party package. This is what we do for Datasette, which is in Python - we use the standard Python SQLite library and tell people to use the 3rd party pysqlite3 package if their builtin SQLite is too old.Reacted by Tobias Nießen, Matthew McEachen, Sámal Rasmussen, Gavin Foley, alsotang and Daniel SteigerwaldReacted by Benjamin Gruenbaum and Daniel Steigerwald17 remaining items
I have a few thoughts on this.
Thoughts on an appropriate API
I designed
better-sqlite3to be easy to use for people who have never used SQLite's C API. I tried to capture the higher-level concepts into the simplest API I could think of, while still exposing most of what advanced users would want via options and utility methods. That said, I don't necessarily think that philosophy aligns with existing APIs within Node.js core. For example,node:fsis actually pretty hard to understand unless the user is already familiar with the C API of Unix/Linux-style filesystems (with the exception of a few high-level convenience methods likefs.readFile). Of course, for something built into the runtime, that low-level exposure makes sense. Therefore, I do think it would be appropriate for the API to (as much as possible) be a 1-to-1 mapping with SQLite's low-level C API, but with a few convenience methods built-in. I see no reason to depart from themethod/methodSyncnaming convention used by Node.js's core libraries.However, it should be noted that many asynchronous APIs would simply break or result in undefined behavior or unsafe memory accesses when combined with much of the behavior provided by SQLite. For example, SQLite allows you to register user-defined SQL functions in the host language (i.e., JavaScript in this case). These function would be invoked during certain SQL queries, but if those SQL queries were executed in a thread pool, it will result in unsafe cross-thread usage of JavaScript contexts (which is very, very bad). Similarly, transactions become prone to error and terrible performance when performed in an asynchronous manner, since transactions in SQLite lock the entire database. Therefore, any asynchronous API for SQLite in Node.js would need to be highly limited, exposing only the most basic functionality that SQLite offers.
To safely separate the synchronous and asynchronous worlds, I recommend exposing two different classes,
SQLiteDatabaseAsyncandSQLiteDatabaseSync, each of which represents a connection to a SQLite database, withSQLiteDatabaseSyncproviding access to the entire (or almost the entire) SQLite C API, whileSQLiteDatabaseAsyncwould only provide basic functionality like making queries. The goal here is to make it impossible for a single database connection to use both async/non-blocking methods AND advanced SQLite APIs that would have detrimental effects when invoked from the libuv thread pool.I believe an approach like this would align with the existing patterns/philosophy of Node.js's core libraries, and would satisfy the memory safety that is expected of the Node.js runtime. Sprinkle in a few high-level convenience methods, and let user-land do the rest.
Thoughts on SQLite versions
Perhaps most importantly (much more important than the API, in my opinion), is how to deal with the issue of SQLite versions. Besides the fact that the SQLite developers release new versions many times per year, SQLite is designed to be customized at compile-time, giving the user the power to enable certain advanced features, disable deprecated features that may harm performance or be unsafe, or even change the behavior of basic SQL expressions (like case-sensitivity, unicode, etc.). In
better-sqlite3, it's very common for users to need a different build of SQLite than the one provided by default. I do provide a way for users to compile a custom version of SQLite, but the process is relatively painful. If Node.js exposed SQLite to users, I think it would be crucial to solve this need. Perhaps it can be solved with dynamic linking or something. Just be aware that you will inevitably run into this.Reacted by Matthew McEachen, punkish, Stephen Lombardo, Colin Ihrig, Sámal Rasmussen, Tobias Nießen, Gavin Foley, alsotang, Alexandre Antonio Juca and mankeheavenReacted by Benjamin Gruenbaum, Oskar Okuno and Alexandre Antonio Jucait's very common for users to need a different build of SQLite than the one provided by default
I second this assertion of @JoshuaWise and think it is very important to build this capability in the node's integrated version of SQLite. I want the same (possibly customized) version of SQLite powering my node application as well as my CLI so there are no surprises when I am building my application or analyzing my data. For this reason itself, it is very important that not only can I customize my SQLite with the extensions I need, but do it in multiple locations, and as easily as possible because I am not a C programmer. Even the most obvious steps for a C programmer can be very confusing for me.
Reacted by Matthew McEachen and Stephen LombardoI have a slightly different view:
node:sqliteshould be a no-nonsense "just works" SQLite package that application developers use when they want to read/write to a SQLite database. They would mostly care about:- Having an up-to-date SQLite version
- Ability change a few settings (like
PRAGMAand runtime configuration options) - Using SQLite extensions to add new functionality (either as application defined functions or dynamically loadable extensions)
If someone wanted something outside of that, they should use a 3rd party package like
better-sqlite3. This would include:- A different SQLite version
- Custom compilation arguments
- A completely different SQLite library (ie sqlciper or libsql)
This is kindof how Python works: the builtin
sqlite3library is a "just works" version of SQLite, while if someone wanted a customized build, they'd need to reach for a 3rd party package likepysqlite3.That means that the SQLite library offered in
node:sqliteneeds to precisely define which compilation arguments to include and default settings, which is difficult. But I think offering an API that can dynamically handle multiple SQLite libraries would be more difficultIf someone wanted something outside of that, they should use a 3rd party package like
better-sqlite3. This would include:- A different SQLite version
- Custom compilation arguments
- A completely different SQLite library (ie sqlciper or libsql)
FWIW, the SQLite developers strongly recommend against having more than a single copy of SQLite per process, see How To Corrupt An SQLite Database File.
Reacted by Benjamin GruenbaumFWIW, the SQLite developers strongly recommend against having more than a single copy of SQLite per process
That seems like it would raise a more immediate question about WebStorage and its impact on code already using a userland sqlite module. How do Deno/Bun/Python etc. deal with that?
@tniessen ooh thanks for sharing, hadn't seen that before!
But in this case, if Node.js statically links a "golden" SQLite library for
node:sqlite, would other dynamically linked SQLite libraries from 3rd party npm packages (better-sqlite3, libsql, sqlcipher, etc) be effected as well? It's hard to tell from that link, they only say "linked" but don't specify if this would happen in dynamically linked version of SQLite with a statically linked version.My hunch is that there could be only 1 statically linked copy of SQLite, but other dynamically linked SQLite copies would be fine? Especially since this pattern is used in Python/Deno (which uses SQLite for localStorage + KV), but I'm no expert here
would other dynamically linked SQLite libraries from 3rd party npm packages (better-sqlite3, libsql, sqlcipher, etc) be effected as well?
I would think that Node’s internal SQLite would use different files on disk for storing the database than the files on disk used by these various packages? And if that’s the case, then I don’t see why they would interfere with each other.
right, as long as I continue to use the SQLite driver with which I created my db, I would not expect any problem. So, if I use
node:sqliteto create my files, I continue to use it with those files. And, if I use a custom version of SQLite with, say,better-sqlite3, I would continue to happily use that combo. Using the correct driver with the files they created would be my responsibility- added a commit that references this issue
on Jul 9, 2024 - added a commit that references this issue
on Jul 12, 2024 - added a commit that references this issue
on Jul 16, 2024 - added a commit that references this issue
on Jul 18, 2024
We agreed (the TSC) to allow SQLite into the project for the
localStorageAPI at #52435It would be neat if we exposed that to userland similraly to how Bun does, there is a lot of prior art of languages and runtimes embracing SQLite for a local configuration database like python, php, Tcl and others.
This issue is to discuss it and raise concerns about whether or not it makes sense to expose SQLite bindings given we already bundle it.