Expose SQLite?

[benjamingr]

We agreed (the TSC) to allow SQLite into the project for the localStorage API at #52435

It 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.

[cjihrig]

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.

[gabrielschulhof]

Shall we use the same interface as https://github.com/TryGhost/node-sqlite3?

[tniessen]

@gabrielschulhof On the rare occasion that I voluntarily write JavaScript code, I personally prefer the better-sqlite3 API, which also tends to be significantly faster.

That being said, the non-blocking behavior of node-sqlite3 better 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 asynchronous node-sqlite3 API 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.

[GeoffreyBooth]

I think at the very least let's land the localStorage PR 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.

[cjihrig]

FWIW, 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.".

[KhafraDev]

I 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

[benjamingr]

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.

[tniessen]

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.

[H4ad]

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.

[benjamingr]

We 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?

[GeoffreyBooth]

For reference:

• https://bun.sh/docs/api/sqlite
• https://docs.deno.com/runtime/manual/basics/connecting_to_databases#sqlite

[benjamingr]

@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

[cjihrig]

I think it makes the most sense to do something similar to the fs module 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.

[cjihrig]

Another 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 as node:util.

[asg017]

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, or STRICT tables. 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 for better-sqlite3 or 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.