proposal: should node expose a persistent KV API

[cjihrig]

What is the problem this feature will solve?

Embedding sqlite in Node was proposed in 2019. There was not a lot of enthusiasm at the time. It's now been over four years, and since then at least three other runtimes have emerged with some functionality like this:

• Deno embeds sqlite and uses it to power their KV API. In the cloud they keep the same KV API, but use FoundationDB instead of sqlite.
• Bun embeds sqlite and exposes it as their bun:sqlite API.
• Cloudflare Workers exposes their own KV API.

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

Embedding sqlite in the binary and providing an API similar to Deno KV or Bun's sqlite module.

Using the SQLite Amalgamation makes the embedding pretty simple. On my machine, embedding sqlite with a bare bones binding caused the binary size to increase from 93.45MB to 94.82MB (at commit dac5f29).

Other outstanding questions:

• Does sqlite compile on all of Node's supported platforms? If not, there are also wasm builds available, though I would prefer native.
• What would the public API looks like? Deno's KV does a better job at abstracting away sqlite because they swap out underlying databases in the cloud. Bun provides a more powerful SQL interface, but that also has a better chance of becoming a leaky abstraction.

What alternatives have you considered?

Status quo. Continue using better-sqlite3, sqlite3, etc. modules from npm.

[tpoisseau]

About second and third point. SQLite is also officially available in WASM

https://www.sqlite.org/download.html
https://sqlite.org/wasm/doc/trunk/index.md

I think if sqlite is integrated to Node.js (This is what I would prefer), focus must be on proper binding with low-level API and higher level API to interact with SQL (connection with DB, prepare statement, cursor, proper integration with javascript features like iterator pattern, choose between sync or async mode).

A KV API (like Deno) should not be a target for this kind of proposal. This kind of API is a bit to high and out of scope of a sqlite integration.

[mscdex]

While lots of things would be nice to have in node core so you wouldn't have to install them separately, I think databases are one of those things best left to userland, mostly because I think node should be focused on as few things as possible so we can do them very well and not become a kitchen sink platform.

However, specifically in the case of sqlite (speaking as someone who maintains an sqlite addon) there are also at least these issues I can think of offhand:

• Sqlite has a lot of compile-time knobs that would affect available features and performance characteristics. Due to this, there would realistically be no way to satisfy all users and may result in GH issues.

• Sqlite is missing some features that would be desirable to general users (e.g. encryption). Adding those would mean needing to instead rely on forks or having to maintain our own fork/patches.

• As with many types of databases and their client drivers, there are lots of opinionated ways to present APIs in such client drivers, some of which can have performance implications.

[Xstoudi]

I disagree with @mscdex about how databases are not handled at all in Node versus how they are on other ecosystems and their kitchen sink platforms. Yet let's admit sqlite stands on a particular place in the relational database world.

As a one of the general users, having SQLite shipped builtin felt missing. The features that could be missing are mostly nice to have, better-sqlite3 API is nice to use (probably why Bun inspired from it) and I think it would be a good place to start.

[fabiospampinato]

IMO this is an unnecessary burden, the more stuff added to core the more time spent maintaining it, the bundled version of sqlite might not be the one the user wants to use, different compilations flags from the ones that the user wants to use may be used etc.

I think it'll be better to focus on shipping an FFI, then Node could talk to any native version of sqlite, or to other interesting binaries, efficiently with low overhead.

[fox1t]

I like the proposal, and I am definitely on the KV API side. I see no value in just providing SQLite with a “raw” SQL API to interact with it.

[anonrig]

We can also use sqlite as a basis for the window.localStorage API similar to what Deno implements.

[atomicwrite]

I say one up deno and since sqlite is faster at storing files than a file system, up them by having it as the base of a virtual file system that you can abstract to replace with cloud ones if desired

https://www.sqlite.org/fasterthanfs.html

[jasnell]

Let's not worry about competing with other runtimes at all on this and base the decision solely on whether this will provide benefit to users. I think it would so I'm fine with it.

That said, Cloudflare has a KV API (backed by sqllite in workerd but production uses a completely different backend). Deno has a different kind of thing they also call KV but it's definitely not the same as Cloudflare's. Bun has something also. And, as @anonrig points out there's localStorage to think about. It would be a shame to introduce Yet Another KV API that is incompatible with everything else so while I definitely agree this is something to explore, I think we need to put some careful consideration into what API we implement here.

At the very least, it's worth experimenting with.

So... what are the use cases this would be used for?

[Jarred-Sumner]

We’ve considered doing localStorage in Bun, but I’m not convinced sqlite is the best approach for it. Something like LMDB makes more sense, particularly if the DB is not on a network drive. It becomes a challenge with servers, but it’s useful for CLIs or cases where you don’t need multiple servers to use the same data store

[atomicwrite]

I am hesitant to express this sentiment in a conversation involving individuals notorious for appropriating ideas, however, caching rdbms record from postgres or mysql to a local sqlite instance is how like 90% of real web back end optimize and reduce their cloud bill.

Especially mid 1m-5m contract companies. Every serialization, every network byte is metered. People hire out these people because the cost of their rewrite is cheaper than cloud bills. This is a trend.

If a record can have a ttl of 30 seconds even it can shave numbers off their bandwidth. Then there is the whole circus of deciding which data can be used this way. (Read/Write but low update tables).

Putting in a kv that is just a lower abstraction of sqlite with out the added benefits is 10ft view. Since it's happening at "mom and pop" shops frequently then it will roll over. Furthermore if you look at what companies like litestream are doing, using sqlite's replicating on a dumb aws bucket.. it's really amazing. Typical costs are only about $1 per month.

[mscdex]

@atomicwrite I don't think anyone here is arguing that sqlite is not useful or capable. I believe the discussion is mostly about whether it makes sense to embed sqlite into node core.

[cjihrig]

After thinking about this more, I'm still not completely convinced that this is something we should implement, but it's definitely worth discussing.

I think the only way this makes sense is by building an abstraction on top of sqlite. That helps with any issues around users wanting to customize sqlite. It also makes swapping out sqlite for something else a lot easier. But, if we were to implement a KV type API, I think the point about "is sqlite the right choice" is valid.

what are the use cases this would be used for?

Off the top of my head:

• Anything you would currently install a third party module for would now be a bit simpler.
• Caching.
• Local/session storage.
• Potentially IndexedDB, but I don't think this is an API we would want to support.
• Cookies.
• Offline applications.
• The point about a virtual file system is interesting, but I doubt that is something we would add to core.

In my opinion, the coolest use case is having a persistence API locally that is replaced with something else in the cloud (similar to Deno and Cloudflare). Unfortunately, that doesn't really translate for Node.

If anyone else has some interesting use cases, I would love to hear them.