←back to thread

Sqlite3 WebAssembly

(sqlite.org)
647 points whatever3 | 1 comments | 15 Oct 24 17:45 UTC | HN request time: 0.233s | source
Show context
simonw ◴[15 Oct 24 18:59 UTC] No.41851934[source]▶
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.

replies(10): >>41852040 #>>41852194 #>>41853089 #>>41854540 #>>41854586 #>>41854654 #>>41855596 #>>41856415 #>>41857000 #>>41858635 #
1. matharmin ◴[16 Oct 24 07:17 UTC] No.41856415[source]▶
Unlike other opinions here I do think it is technically feasible to stream a copy of the WAL - it just has to be implemented in the VFS. "Shared memory" could be a SharedArrayBuffer, or just a normal buffer if you only have one database connection open at a time (for example in a SharedWorker, which is already common). It may not be simple to implement, but definitely possible.

The biggest actual issue is that it will capture block-level changes, not row-level changes. This means it can work to replicate a complete database, but partial sync (e.g. sharing some rows with other users) won't be feasible.

To get row-level changes for partial sync, you need to use something like triggers or the SQLite session extension [1]. For PowerSync we just use triggers on the client side. I find that works really well, and I haven't found any real downsides to that except perhaps for the work of maintaining the triggers.

[1]: https://sqlite.org/sessionintro.html