Sqlite3 WebAssembly

Something that would be really fun would be to run SQLite in-memory in a browser but use the same tricks as Litestream and Cloudflare Durable Objects (https://simonwillison.net/2024/Oct/13/zero-latency-sqlite-st...) to stream a copy of the WAL log to a server (maybe over a WebSocket, though intermittent fetch() POST would work too).

Then on subsequent visits use that server-side data to rehydrate the client-side database.

From https://sqlite.org/forum/info/50a4bfdb294333eec1ba4749661934... is looks like WAL mode is excluded from the default SQLite WASM build so you would have to go custom with that.

Slight point of confusion: that page says:

> These components were initially released for public beta with version 3.40 and will tentatively be made API-stable with the 3.41 release, pending community feedback.

But the most recent release of SQLite is 3.46.1 (from 2024-08-13)

Presumably they are now "API-stable" but the page hasn't been updated yet.

It would be great if the SQLite team published an official npm package bundling the WASM version, could be a neat distribution mechanism for them. (UPDATE: They do, see replies to this post.)

My favourite version of SQLite-in-WASM remains the Pyodide variant, which has been around since long before the official SQLite implementation. If you use Pyodide you get a WASM SQLite for free as part of the Python standard library - I use that for https://lite.datasette.io/ and you can also try it out on https://pyodide.org/en/stable/console.html

    import sqlite3
    print(sqlite3.connect(':memory:').execute(
        'select sqlite_version()'
    ).fetchall())

That returns 3.39.0 from 2022-06-25 so Pyodide could do with a version bump. Looks like it inherits that version from emscripten: https://github.com/emscripten-core/emscripten/blob/main/tool...

For use in Golang, I really like ncruces wasm SQLite package - https://github.com/ncruces/go-sqlite3 . Unlike cznic's go package (which is great, btw), the wasm version works well on OpenBSD and the like.

WebSQL should've just been Sqlite and the whole offline-first (and general app storage) ecosystem would've been so much nicer.

Is there any hope of that happening? Instead of abstracting and over specifying sqlite, can the spec just specify a version of the SQLite API browsers should support and roll the version periodically?

I've been really interested in the local-first landscape lately but embedding SQLite seems really heavy-weight compared to using the browser's built-in storage APIs (in particular, IndexedDB) and it seems to be what most of the main open source libraries do. I'm interested to see a open-source solution (with sync) which provides an SQLite-like API but for the browser's native storage rather than trying to embed another executable in Web Assembly.

I wasn't able to tell from a quick look through the page: could someone help me understand the use cases here?

More specifically, would this be able to be a "replacement" for indexedDB? Does the data persist, or do I need to keep the sqlite file in the filesytemAPI (or indexedDB/localstorage) myself?

I was trying to get this working in a rust ecosystem some time ago but none of the blessed.rs sql (rusqlite, sqlx) wrappers seem to take advantage of it yet and wrapping it yourself is a bit tricky since when I was trying I couldn't figure out a way to to get emscripten wasm code to play nice with wasm32-unknown-unknown without some kind of JS wrapper which then requires implementing the interface those crates expect and exposing it from JS. Once that is done in rust itll be great there too!

So after downloading from the official downloads page and stripping away all the mjs files and "bundler-friendly" files, a minimal sqlite wasm dependency will be about 1.3MB.

For an in-browser app, that seems a bit much but of course wasm runs in other places these days where it might make more sense.

I used the wasm build of sqlite and the Chicory runtime to create a pure JVM executed sqlite library: https://github.com/dylibso/sqlite-zero

It's more of an experiment than an attempt to make something production ready, though I could see it being useful to bring dependency-less sqlite tooling to the JVM ecosystem.

WASM ATM is IMHO most useful for VSCode Extension, where it can help to avoid the dependency nightmare that nodejs modules with native code cause.

Half-OT:

What's your opinion on SQLite in-memory vs plain objects/arrays?

When would you use which and why?

As a general question, in what scenarios is it more beneficial to send the full DB and let the browser handle the queries? Maybe phrased a better way - when would I use this to improve a user experience over the traditional server-hosted db model?

The CORS restrictions / needing SharedArrayBuffer support kinda stinks.

There is no way to use Sqlite3 off-thread without memory sharing? Couldn't postMessage work to pass data to the sqlite thread by using the third Transfer argument?

Would postMessage transfer allow memory to be stored in a sqlite wasm database running a worker off-thread?

Refering to this implementation's docs: https://github.com/sqlite/sqlite-wasm

i’ve been looking for a Tanstack Query style library that is backed by Sqlite (backed by OPFS or some other browser storage) and syncs with an API in the background. Does anything like that exist? i’ve seen ElectricSQL and other sync engines but they are a bit opinionated. I’m pretty new to local-first but i feel like the developer ergonomics are not quite there yet

Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser

I definitely have to put sqlite in https://exaequOS.com

After years of being able to run SQLite on my mobile phone, my tv, my router and gaming consoles, I can finally run it on my browser. Which also happens to be running on the most powerful machine I own

How long until we see WebAssembly/WebGPU become a platform independent choice for deploying server side code as well?

See also https://github.com/electric-sql/pglite (REPL at https://pglite.dev/repl/)

(Previously discussed 7 months ago: https://news.ycombinator.com/item?id=39477457)

Whats needed is a rust-wasm compatible library that can use this.

I’m working on a hobby-project that uses IndexedDB for persistent client-side storage, and it really feels like W3C made some very bad design decision and than instead of fixing they they have just given up on the standard. Issues like not being able to index values in objects in arrays [1] (not even in fixed position e.g. "key.path.[0].value") despite almost a decade of developers asking for it, a very limited query syntax, and even the documentation on MDN seems of very lower quality than the rest of the web docs.

I’m happy that we are actually be able to use SQL in the browser now (although I would rather skip the MBs of the bundle bloat). But I feel like the standards committee will now have even less of a reason to fix the very broken state of IndexedDB.

1: https://github.com/w3c/IndexedDB/issues/35

I like it