3 ms·

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

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.

ncruces 2y ago
File backed SQLite in a browser? Do you mean like OPFS? https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-wal 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.
- andrewf 2y ago
  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.
  - ncruces 2y ago
    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.
- jauntywundrkind 2y ago
  Emscripten's default file system provider is memfs, in memory. Maybe there would be some challenges, some spec limitations using that, but I strongly expect it closer to a weekend or two of hacking to get some special weird mystic quirkiness that WAL relies on than some long ordeal that keeps going on endlessly (to get sqlite running with WAL). https://emscripten.org/docs/api_reference/Filesystem-API.html#filesystem-api-memfs https://emscripten.org/docs/api_reference/Filesystem-API.htm... OPFS is interesting tech but again a red herring misdirecting from what had been raised, using an in-memory filesystem like the default thing that emscripten (the default toolchain) does.
  - ncruces 2y ago
    I… really don't get this. The people on SQLite, employed to work on this full time for over a year, have this to say (on the link I posted above): “Because the WASM build does not have shared memory APIs, activating WAL requires that a client specifically activate exclusive-locking mode for a db handle immediately after opening it, before doing anything else with it… “WAL mode does not provide any concurrency benefits in this environment. On the contrary, the requirement for exclusive locking eliminates all concurrency support…” I personally worked to implement shared memory WAL for a server side Wasm port of SQLite. But random internet poster decides to “strongly expect it closer to a weekend or two of hacking.” Please, do me a favor and do spend that weekend or two for the benefit of the rest of us all. It'll sincerely be much appreciated. PS: it was a random internet poster¹ (who's been posting in this thread) who helped me figure out how to implement shared memory WAL for my port. It still took way more than “a weekend or two.” So if you do figure out how to crack this, I'm sure that people who've been trying for the past year² will definitely appreciate it. 1: https://github.com/ncruces/go-sqlite3/discussions/69 https://github.com/ncruces/go-sqlite3/discussions/69 2: https://github.com/rhashimoto/wa-sqlite https://github.com/rhashimoto/wa-sqlite
    - jauntywundrkind 2y ago
      > WAL mode does not provide any concurrency benefits in this environment. Except we aren't interested in concurrency or performance benefits of WAL; we want it for something else entirely (replication).

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.

ncruces 2y ago

File backed SQLite in a browser? Do you mean like OPFS? https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-wal 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.

andrewf 2y ago
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.
- ncruces 2y ago
  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.
jauntywundrkind 2y ago
Emscripten's default file system provider is memfs, in memory. Maybe there would be some challenges, some spec limitations using that, but I strongly expect it closer to a weekend or two of hacking to get some special weird mystic quirkiness that WAL relies on than some long ordeal that keeps going on endlessly (to get sqlite running with WAL). https://emscripten.org/docs/api_reference/Filesystem-API.html#filesystem-api-memfs https://emscripten.org/docs/api_reference/Filesystem-API.htm... OPFS is interesting tech but again a red herring misdirecting from what had been raised, using an in-memory filesystem like the default thing that emscripten (the default toolchain) does.
- ncruces 2y ago
  I… really don't get this. The people on SQLite, employed to work on this full time for over a year, have this to say (on the link I posted above): “Because the WASM build does not have shared memory APIs, activating WAL requires that a client specifically activate exclusive-locking mode for a db handle immediately after opening it, before doing anything else with it… “WAL mode does not provide any concurrency benefits in this environment. On the contrary, the requirement for exclusive locking eliminates all concurrency support…” I personally worked to implement shared memory WAL for a server side Wasm port of SQLite. But random internet poster decides to “strongly expect it closer to a weekend or two of hacking.” Please, do me a favor and do spend that weekend or two for the benefit of the rest of us all. It'll sincerely be much appreciated. PS: it was a random internet poster¹ (who's been posting in this thread) who helped me figure out how to implement shared memory WAL for my port. It still took way more than “a weekend or two.” So if you do figure out how to crack this, I'm sure that people who've been trying for the past year² will definitely appreciate it. 1: https://github.com/ncruces/go-sqlite3/discussions/69 https://github.com/ncruces/go-sqlite3/discussions/69 2: https://github.com/rhashimoto/wa-sqlite https://github.com/rhashimoto/wa-sqlite
  - jauntywundrkind 2y ago
    > WAL mode does not provide any concurrency benefits in this environment. Except we aren't interested in concurrency or performance benefits of WAL; we want it for something else entirely (replication).