6 ms·
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://s
by simonw 2y ago
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-storage-in-every-durable-object/ 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/50a4bfdb294333eec1ba4749661934521af19e6fc0790a6189696607f67c2b54?t=h 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.
- dustinchilson 2y agoAre you thinking something like https://electric-sql.com/ https://electric-sql.com/
- PUSH_AX 2y agoWhat’s the catch with this thing?
- T-Winsnes 2y agoThe security model is challenging, as it relies on Postgres users for iam. Your users essentially log directly into your db
- dumbo-octopus 2y agoIsn’t Postgres a fairly capable IAM provider, all things considered? I’d their access control mechanisms at least as much as a run of the mill external backend’s.
- T-Winsnes 2y agoFor basic auth it works well, but the challenge comes when you need to integrate with oidc, need to enforce mfa, enable sso etc. session invalidation is also quite complicated. You need an identity middle man in front of the Postgres identity to tackle these and validate that the session is still active. Last time I looked at electric it was a big challenge to integrate such a service. This might have improved since then however
- infogulch 2y agoYou can see what this means specifically from the docs: https://electric-sql.com/docs/guides/auth https://electric-sql.com/docs/guides/auth
- fcanesin 2y agoIsn't a more advanced/production version of this what the combo of couchdb and pouchdb do since several years ago?
- simonw 2y agoYeah this kind of thing has certainly been tried before, I feel like SQLite WASM plus WAL might be an interesting twist on the idea.
- ncruces 2y agoThere 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 agoAs 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 agoWAL: 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
- Fabricio20 2y agoI think OrbitDB [1] is the closest we have to a functional version of this right now? Unsure, but the concept is really cool! [1]: https://github.com/orbitdb/orbitdb/blob/main/docs/GETTING_STARTED.md https://github.com/orbitdb/orbitdb/blob/main/docs/GETTING_ST...
- billywhizz 2y agohi simon. i direct messaged you on twitter about a PoC i did of this in aug 2022, but never heard back - i thought you might have been interested. my twitter handle is justjs14. i have some code i would have to dig out that did this very thing - it allows you to open a SQLite db in browser using sqlite (with a VFS) compiled to wasm (not the official WASM build), make changes and both push and pull WALs to and from a server (or indeed browser to browser would be possible both manually or over WebRTC). it even works with github pages if you give the browser client a github token to work with. if you are interested, feel free to ping me and i can see if i can get this up and running from scratch again. i did a ton of experiments with this approach around then and i think it could be useful for a subset of applications at least. there's also a working demo of the pull functionality only here: https://just.billywhizz.io/sqlite/demo/#https://just.billywhizz.io/sqlite/demo/db/chinook.db https://just.billywhizz.io/sqlite/demo/#https://just.billywh...
- adhamsalama 2y ago> (or indeed browser to browser would be possible both manually or over WebRTC) I have just done something similar in the past week, but without the WAL. It's pretty much an alternative to online spreadsheets for me. http://github.com/adhamsalama/sqlite-wasm-webrtc http://github.com/adhamsalama/sqlite-wasm-webrtc
- billywhizz 2y agocool. will check this out. i think it's an interesting approach and allows all sorts of very low rent interactivity as long as you don't need super high throughput or expect lots of contention.
- adhamsalama 2y agoThanks! I purposefully replicate data and queries only, so to change the shown data for everyone you have to run a query, so after the import process (which can be slow for large files), queries are replicated fast enough IMO because it's just a small string sent over WebRTC, so in theory it should be faster to run a query over a big dataset and see its result appear for all peers than a centralized app like Google Sheets, but I haven't benchmarked it yet.
- conradev 2y agoLiteVFS can be compiled in a browser and sync with LiteFS cloud: https://github.com/superfly/litevfs https://github.com/superfly/litevfs It needs to be run from a worker, though
- hiccuphippo 2y agoThis sounds like what meteor was 10+ years ago but with sqlite instead of mongodb.
- nesarkvechnep 2y agoNothing like it, actually.
- matharmin 2y agoUnlike 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 https://sqlite.org/sessionintro.html
- adhamsalama 2y agoI used SQLite compiled to WebAssembly in the browser and replicated it (without the WAL) using WebRTC to create a way to collaborate on databases in the browser instead of using apps like Google Sheets. There's no server required (other than the WebRTC signaling server), so it's private and secure too. http://github.com/adhamsalama/sqlite-wasm-webrtc http://github.com/adhamsalama/sqlite-wasm-webrtc
- j12a 2y agoI didn't fully understand this idea of hydration but I also got recently interested in leveraging Wasm, in context of running parts of backend logic using it (eg. regular templating and some Htmx endpoints) to allow certain offline features with regular server-side web frameworks that could use similar Sqlite Wasm datastore as OP. - Full Django example https://github.com/m-butterfield/django_webassembly https://github.com/m-butterfield/django_webassembly - Minimal Rust example with client-server separation using macros https://github.com/richardanaya/wasm-service https://github.com/richardanaya/wasm-service - Clojure Electric using more macros for something similar (without wasm) https://github.com/hyperfiddle/electric https://github.com/hyperfiddle/electric - Recent M$ web tooling starting with letter B? In last few years, surprisingly many parts of Python ecosystem to have gained Wasm support that can leverage this also. Obvious ML related ones and even game libraries like Pygame and Pyxels. Kivy support (multi-touch UI library) should not be too far out either now that SDL2 library has added support. Seems like there's many new possibilities for running code in any computer, optionally sandboxed in web browser to avoid platform bureaucracy or taxes.