←back to thread

Sqlite3 WebAssembly

(sqlite.org)
506 points whatever3 | 3 comments | 15 Oct 24 17:45 UTC | HN request time: 0s | 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(8): >>41852040 #>>41852194 #>>41853089 #>>41854540 #>>41854586 #>>41854654 #>>41855596 #>>41856415 #
ncruces ◴[15 Oct 24 21:02 UTC] No.41853089[source]▶
There are many layers of that's not how it works at play here.

In-memory SQLite databases don't use WAL. Wasm (and browser Wasm, in particular) doesn't support anything like the shared memory APIs SQLite wants for its WAL mode.

Litestream requires a very precise WAL setup to work (which just so happens to work with the default native SQLite setup, but is hard to replicate with Wasm).

Cloudflare Durable Objects may have been inspired by Litestream but works very differently (as do LiteFS, Turso, etc…)

The general idea of streaming changes from SQLite would work, but it's a lot of work, and the concurrency model of in-browser Wasm will make it challenging to implement.

(I wrote that forum post some time ago, and have WAL working in a server side Wasm build of SQLite, but none of the options to make it work would make much sense, or be possible, in browser)

replies(3): >>41853607 #>>41853645 #>>41853823 #
jauntywundrkind ◴[15 Oct 24 22:12 UTC] No.41853645[source]▶
Then don't use in-memory sqlite? Use file backed sqlite but have your wasm implementation of those "system calls" just be to memory?

I dunno, feels like you're coming down too hard.

replies(1): >>41854002 #
1. ncruces ◴[15 Oct 24 23:01 UTC] No.41854002[source]▶
File backed SQLite in a browser? Do you mean like OPFS?

https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-wal

Again, just because the all the Lego pieces sound like they should all just fit together, doesn't mean that they will.

The VFS mechanism was primarily designed to make SQLite easy to port to multiple OSes. WAL mode is hard to port everytime you step away from a more traditional OS.

“We have SQLite in the browser, let's just stream the WAL like the Litestream hack” does not add up.

It's not impossible, but it surely took a lot of effort at Cloudflare (or at Fly, or Turso) to get there. And neither of them opened it up sufficiently to help us do the same. In a browser.

replies(1): >>41854206 #
2. andrewf ◴[15 Oct 24 23:35 UTC] No.41854206[source]▶
I haven't looked but I bet a lot of the WAL complexity comes down to supporting consistency and durability guarantees, neither of which you necessarily need for your in-browser use case.
replies(1): >>41856431 #
3. ncruces ◴[16 Oct 24 07:20 UTC] No.41856431[source]▶
Not really, or it depends. That complexity is dealt with by SQLite.

The complexity for you comes from trying to reuse their (battle tested) implementation when (as I wrote above) the primitives they depend upon were not meant to make porting to a browser sandbox easy.

And the problems there are the specific concurrency model they depend upon: communicate by sharing memory.

Then, you're either working at the wrong abstraction level (and it shows), or you're patching and gutting SQLite.

SQLite is meant to work with files, file locks, shared memory, fsync and mmap.

It also doesn't work out great if you try to persist to an object store, to a KV store, or…

I'm repeating myself, but yeah. You can make it work. Others have made it work. But it's still a lot of work, and you're throwing away a lot of what makes SQLite… SQLite.