6 ms·

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

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)

digdugdirk 2y ago
As someone who uses sqlite fairly regularly, but doesn't understand what most of those paragraphs mean, do you have any recommendations for learning resources? I'm gathering that I need to learn about: - WAL - Shared Memory APIs - Concurrency models - Durable Objects?
- wyager 2y ago
  WAL: Write ahead log, common strategy for DBs (sqlite, postgres, etc.) to improve commit performance. Instead of fsync()ing every change, you just fsync() a log file that contains all the changes and then you can fsync() the actual changes at your leisure Shared memory API: If you want to share (mutable) data between multiple processes, you need some kind of procedure in place to manage that. How do you get a reference to the data to multiple processes, how do you make sure they don't trample each other's writes, etc. Concurrency model: There are many different ways you can formalize concurrent processes and the way they interact (message passing, locking, memory ordering semantics, etc.). Different platforms will expose different concurrency primitives that may not work the same way as other platforms and may require different reasoning or code structure Durable objects - I think this is some Cloudflare service where they host data that can be read or modified by your users This is all from memory, but IME, GPT is pretty good for asking about concepts at this level of abstraction
  - digdugdirk 2y ago
    Thank you! And side note on your last point - I've been burned too many times by confident hallucinations to trust my foundational learning to GPT. I hope someday that will improve, but for now ChatGPT is as trustworthy as an evening chat with someone at the bar. ... Someone who has been drinking since happy hour.
    - infogulch 2y ago
      I was curious and Grok 2 seemed to do pretty good: https://x.com/i/grok/share/c2qCdF2wwIx7AHz0U1f2u8dTO https://x.com/i/grok/share/c2qCdF2wwIx7AHz0U1f2u8dTO
    - globular-toast 2y ago
      If you'd like a trustworthy overview, the book Designing Data-Intensive Applications by Martin Kleppmann is a classic. I really hope we get an updated version, but the fundamentals all still hold anyway.
      - rahoulb 2y ago
        Upvote for that book. I read it a few months ago and was really impressed with how easy it was to read. It starts out with simple stuff, like serialising data as JSON vs XML. But it moves into complex areas - like how replication and WALs work, including different ways of handling consensus when using leader-leader replication and how Spanner needs atomic clocks to handle it. But even the complex stuff was explained in a way that I understood, which is an immense achievement.
        globular-toast 2y ago
        Yep, this is my number 1 "I wish I'd read this X years ago" book. I'm someone who has been doing this stuff for almost two decades without really knowing this is what I'm doing. I used to think what I was going to do was systems level programming like operating systems and maybe the database systems themselves (e.g. postgres, datomic etc.). But for whatever reason my entire career (so far, but I don't see it changing) has been building data systems for businesses and users. I read the book from cover to cover and half of it was like "ohh... that's how that works, that's what I'm doing wrong" and the other half was "shit, this is something I kinda knew after trying and failing for years, and someone has just written it down in a way I never could".
- throwaway2037 2y ago
  WAL - Shared Memory APIs: https://sqlite.org/wal.html https://sqlite.org/wal.html concurrency model of in-browser Wasm: https://medium.com/@quincarter/wasm-is-awsm-and-multi-threading-the-web-is-not-so-impossible-297870a6c10 https://medium.com/@quincarter/wasm-is-awsm-and-multi-thread... Cloudflare Durable Objects: https://www.cloudflare.com/developer-platform/durable-objects/ https://www.cloudflare.com/developer-platform/durable-object...
jauntywundrkind 2y ago
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.
jchanimal 2y ago
If you like, solving these sort of problems, we are tackling them at Fireproof. Our database API is modeled on CouchDB and MongoDB, but our storage abstractions are along the lines of what you need to build the multi writer WAL you describe. More details here https://jsr.io/@fireproof/encrypted-blockstore https://jsr.io/@fireproof/encrypted-blockstore
rockwotj 2y ago
I believe that streaming changes from SQLite is what https://sqlsync.dev/ https://sqlsync.dev/ is
- ncruces 2y ago
  Yep, that doesn't use WAL mode at all, and it's a decent amount of work. It uses a custom VFS, memory journal mode, and bypasses SQLite for optimistic concurrency handling. I dunno how it handles crash safety, or if it even uses the same on disk file format. My point here is not that this is impossible, it's that SQlite's WAL implementation isn't meant to be pluggable, and Litestream is a very clever hack, that requires a very specific setup to work well. It takes a fair amount of work to replicate that in other environments.
lfmunoz4 2y ago
"The general idea of streaming changes from SQLite would work, but it's a lot of work" Seems easy to me, just store/stream all update sql statements. That should have all information needed and don't have to mess with WAL data format etc.
- ncruces 2y ago
  Yeah, I wonder why no one thought of that.
- LukeLambert 2y ago
  The big problem with statement-based replication is that many queries are non-deterministic. e.g. Inserting a row with current_timestamp or random()

digdugdirk 2y ago

As someone who uses sqlite fairly regularly, but doesn't understand what most of those paragraphs mean, do you have any recommendations for learning resources? I'm gathering that I need to learn about: - WAL - Shared Memory APIs - Concurrency models - Durable Objects?

wyager 2y ago
WAL: Write ahead log, common strategy for DBs (sqlite, postgres, etc.) to improve commit performance. Instead of fsync()ing every change, you just fsync() a log file that contains all the changes and then you can fsync() the actual changes at your leisure Shared memory API: If you want to share (mutable) data between multiple processes, you need some kind of procedure in place to manage that. How do you get a reference to the data to multiple processes, how do you make sure they don't trample each other's writes, etc. Concurrency model: There are many different ways you can formalize concurrent processes and the way they interact (message passing, locking, memory ordering semantics, etc.). Different platforms will expose different concurrency primitives that may not work the same way as other platforms and may require different reasoning or code structure Durable objects - I think this is some Cloudflare service where they host data that can be read or modified by your users This is all from memory, but IME, GPT is pretty good for asking about concepts at this level of abstraction
- digdugdirk 2y ago
  Thank you! And side note on your last point - I've been burned too many times by confident hallucinations to trust my foundational learning to GPT. I hope someday that will improve, but for now ChatGPT is as trustworthy as an evening chat with someone at the bar. ... Someone who has been drinking since happy hour.
  - infogulch 2y ago
    I was curious and Grok 2 seemed to do pretty good: https://x.com/i/grok/share/c2qCdF2wwIx7AHz0U1f2u8dTO https://x.com/i/grok/share/c2qCdF2wwIx7AHz0U1f2u8dTO
  - globular-toast 2y ago
    If you'd like a trustworthy overview, the book Designing Data-Intensive Applications by Martin Kleppmann is a classic. I really hope we get an updated version, but the fundamentals all still hold anyway.
    - rahoulb 2y ago
      Upvote for that book. I read it a few months ago and was really impressed with how easy it was to read. It starts out with simple stuff, like serialising data as JSON vs XML. But it moves into complex areas - like how replication and WALs work, including different ways of handling consensus when using leader-leader replication and how Spanner needs atomic clocks to handle it. But even the complex stuff was explained in a way that I understood, which is an immense achievement.
      - globular-toast 2y ago
        Yep, this is my number 1 "I wish I'd read this X years ago" book. I'm someone who has been doing this stuff for almost two decades without really knowing this is what I'm doing. I used to think what I was going to do was systems level programming like operating systems and maybe the database systems themselves (e.g. postgres, datomic etc.). But for whatever reason my entire career (so far, but I don't see it changing) has been building data systems for businesses and users. I read the book from cover to cover and half of it was like "ohh... that's how that works, that's what I'm doing wrong" and the other half was "shit, this is something I kinda knew after trying and failing for years, and someone has just written it down in a way I never could".
throwaway2037 2y ago
WAL - Shared Memory APIs: https://sqlite.org/wal.html https://sqlite.org/wal.html concurrency model of in-browser Wasm: https://medium.com/@quincarter/wasm-is-awsm-and-multi-threading-the-web-is-not-so-impossible-297870a6c10 https://medium.com/@quincarter/wasm-is-awsm-and-multi-thread... Cloudflare Durable Objects: https://www.cloudflare.com/developer-platform/durable-objects/ https://www.cloudflare.com/developer-platform/durable-object...

jauntywundrkind 2y ago

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.

jchanimal 2y ago

If you like, solving these sort of problems, we are tackling them at Fireproof. Our database API is modeled on CouchDB and MongoDB, but our storage abstractions are along the lines of what you need to build the multi writer WAL you describe. More details here https://jsr.io/@fireproof/encrypted-blockstore https://jsr.io/@fireproof/encrypted-blockstore

rockwotj 2y ago

I believe that streaming changes from SQLite is what https://sqlsync.dev/ https://sqlsync.dev/ is

ncruces 2y ago
Yep, that doesn't use WAL mode at all, and it's a decent amount of work. It uses a custom VFS, memory journal mode, and bypasses SQLite for optimistic concurrency handling. I dunno how it handles crash safety, or if it even uses the same on disk file format. My point here is not that this is impossible, it's that SQlite's WAL implementation isn't meant to be pluggable, and Litestream is a very clever hack, that requires a very specific setup to work well. It takes a fair amount of work to replicate that in other environments.

lfmunoz4 2y ago

"The general idea of streaming changes from SQLite would work, but it's a lot of work" Seems easy to me, just store/stream all update sql statements. That should have all information needed and don't have to mess with WAL data format etc.

ncruces 2y ago
Yeah, I wonder why no one thought of that.
LukeLambert 2y ago
The big problem with statement-based replication is that many queries are non-deterministic. e.g. Inserting a row with current_timestamp or random()