wa-sqlite

That's good to know, thanks! It makes this quite realistic, which might mean a good/great OPFS VFS (like Roy's) actually starts to make sense. https://github.com/rhashimoto/wa-sqlite/discussions Unfortunately I'm not knowledgeable enough on the browser side of things to advance something like that, and have quite a bit on my plate already. But if there's interest I can help demystify the SQLite/VFS side of things. Feel free to (e.g.) open a GitHub discussion on my repo to track this.

Hi all! I wanted to share a library I wrote that I think is needed to make OPFS actually useful. For context, OPFS is the Origin Private File System, giving your app a orgin-scoped high performance file system, ideal for stuff like sqlite or data intensive local-first apps. However, if you have multiple windows or tabs, using the same FileHandle will break your app, since there can only be one FileHandle per file. This library attempts to fix this by doing some MessagePort acrobatics between tabs, and keeps only one worker alive managing access to the file system. It’s informed by this discussion on wa-sqlite: https://github.com/rhashimoto/wa-sqlite/discussions/81 Hope somebody will find this useful!

You do not want to use IndexedDB, you should avoid IndexedDB if you can make live work without it. IndexedDB rows need to be very coarse grained, each operation with the IndexedDB API has very high overhead compared to something like walking a LSM tree in RocksDB or something. If the index does fit in memory, then the best move would be to store and load the entire index from a single or a few IndexedDB rows. If you need to index a lot of documents in a way that doesn't fit in memory, I recommend you consider sqlite3, stored in either OPFS or on IndexedDB, and use sqlite's FTS5 full text search stuff for the index. There are several sqlite backends available (see https://github.com/rhashimoto/wa-sqlite/tree/master/src/exam...), something like their IDB VFS will store each block in the sqlite file as a row in IndexedDB essentially batching

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 appreciat

I have been working on one. If you're interested in working on it or contributing, feel free to chime in here: https://github.com/rhashimoto/wa-sqlite/discussions/154 This essentially requires that we import the sqlite emscripten build via an extern C header in wasm bindgen, and then we need to re-implement the VFS in rust while compiling it in multi-threaded mode to allow for shared array buffer access. After that is all done, we will be able to access SQLite rows as raw wasm bytes. That gives us the ability to implement a rust-sqlite style wrapper or integration. There would still not be some of the niceties such as connection pooling, but in wasm you likely want to use the db in exclusive mode.

If you're interested in contributing to that, here is a good place to start (in early discussion stages): https://github.com/rhashimoto/wa-sqlite/discussions/154

It's a very useful feature of sqlite and it also works great in-browser using wa-sqlite[0]. Example, if anyone's curious [1]. [0]: https://github.com/rhashimoto/wa-sqlite [1]: https://github.com/iansinnott/prompta/blob/master/src/lib/mi...

> OPFS doesn’t come with graceful handling of concurrency out of the box. Developers should be aware of this and design around it. There's a multiple readers and writers proposal [0]. It's been "position: positive" by Firefox [1], implemented in Chrome [2], and ignored by Webkit [3] (of course). 0: https://github.com/whatwg/fs/blob/main/proposals/MultipleReadersWriters.md 1: https://github.com/mozilla/standards-positions/issues/861 2: https://chromestatus.com/feature/5172892632875008 3: https://github.com/WebKit/standards-positions/issues/238 Love the shoutout to Roy Hashimoto. He's been writing VFSs for SQLite-on-the-browser and perf testing them. He's recently wrote "IDBMirrorVFS", which "is a new example VFS that keeps all SQLite files in memory while persisting to IndexedDB". It has remarkable performance, of course.

Not strictly related to the podcast, but if you'd like to have a local-first browser application running sqlite check out wa-sqlite. It can use both IndexedDB and OPFS as a backend filesystem. [1]: https://github.com/rhashimoto/wa-sqlite

The word on the street is that https://github.com/rhashimoto/wa-sqlite is nearly production ready, closing in on the neat 1.0.0 release, with its IDBBatchAtomic engine highly recommended at https://www.powersync.com/blog/sqlite-persistence-on-the-web. You can try out the benchmark https://rhashimoto.github.io/wa-sqlite/demo/benchmarks.html. And the other contestant is Dexie, stable https://dexie.org/. If you were to branch into a new venture today, which one would you pick? And why?

It seems indeed very nice, the examples are clear and everything works from async. I tried with Deno, and now experimenting with my own VFS. It does seem to suffer from maintainer problems too though, and I don't blame Roy Hashimoto for that. I wouldn't want to maintain such an obvious wrapper when it should be a task for SQLite's team to upstream the changes. Roy Hashimoto doesn't want to maintain it as an NPM package for instance, as it is just an experiment: https://github.com/rhashimoto/wa-sqlite/issues/12 "Low traffic is a happy place - I don't have any motivation to mess with that."

We now have absurd-sql, wa-sqlite, and the official SQLite project all running in a browser. Their tradeoffs are discussed here https://www.powersync.com/blog/sqlite-persistence-on-the-web That article was written in July, and in the intervening time, Chrome's announced an intent to ship "Multiple Readers and Writers in File System Access API" aka `readwrite-unsafe` [0] in v121 (stable in 2 weeks) which could help improve SQLite's performance even more. [0] https://chromestatus.com/feature/5172892632875008 Edit: It looks like wa-sqlite already has a prototype VFS taking advantage of the above feature https://github.com/rhashimoto/wa-sqlite/discussions/116

This is the WASM blob and it's 1.1 MB uncompressed. https://github.com/rhashimoto/wa-sqlite/blob/master/dist/wa-.... No issues - it's cached by cloudflare. We're using IndexedDB. Here's a writeup on alternatives https://github.com/rhashimoto/wa-sqlite/issues/85 and a benchmark https://rhashimoto.github.io/wa-sqlite/demo/benchmarks.html

Hey, thank you :) We currently use IndexedDB for the virtual filesystem underneath the wa-sqlite [1] WASM SQLite driver in the browser. But yup, that means a ~1.1MB WASM download. We'll definitely stay with SQLite in the client. One of our key principles is full SQL support (any Postgres supported SQL on the server, any SQLite supported SQL on the client). So we won't go pure IndexedDB without a database on top. There's a lot of cool stuff going on with OPFS and WASM SQLite atm, so the technical options are evolving fast. [1] https://github.com/rhashimoto/wa-sqlite