wa-sqlite vs official sqlite WASM ports

[tantaman]

https://discord.com/channels/989870439897653248/1148054860869161100

---

So SQLite interfaces with the OS via a VFS: https://www.sqlite.org/vfs.html

In the case of the browser, there's a bunch of different ways to implement a VFS.

First choice is undelying storage:

• Save pages into IndexedDB?
• Save pages into OPFS?

IndexedDB supports relaxed durability meaning you can return from a write before the write is committed to disk. For certain applications this is a desirable trait. The official SQLite build doesn't support IndexedDB so this isn't an option there.

Second choice is managing concurrency. OPFS currently only allows one access handle to be open at a time meaning that readers lock out writers and vice-versa. It is also rather expensive to acquire an OPFS handle.

wa-sqlite adds a layer of WebLocks (https://developer.mozilla.org/en-US/docs/Web/API/Web_Locks_API) on top of OPFS in order to improve concurrency. The basic idea being that read and write requests can be managed by weblocks onto an already open file handle.

The official SQLite build does not have this extra layer which means you cannot open the same database in two tabs (or workers or whatever). Very unfortunate. Source: https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-locking

If the same page is opened in two tabs, the second tab will hit a locking error the moment it tries to open the same OPFS-hosted database!

This is a deal breaker, imo, me and why I've not spent much time with the official sqlite wasm build.

Third choice is whether or not the WASM build can use async APIs (background: https://emscripten.org/docs/porting/asyncify.html)

APIs for persistence in the browser are async. Those APIs can be made synchronous when running inside a worker through various tricks however that incurs a cost since every read and write from the main thread must cross to a worker then wasm then back again.

---

tantaman's discussion of technical differences between the SQLite builds is quite well done. I'll add a couple non-technical differences worth considering. First, the official SQLite main OPFS VFS won't work across all the major browsers until Safari/iOS 17 (releasing in a month or so), while wa-sqlite has cross-browser support with OPFS from March 2023 and with IndexedDB from March 2022. Second, the official SQLite build is backed by a real organization and is being promoted by the Google Chrome team, while wa-sqlite is a solo effort by someone not especially interested in mass adoption (and the accompanying support load).

---

Thanks for explaining this. I don't really get how this works together

OPFS currently only allows one access handle to be open at a time
wa-sqlite adds a layer of WebLocks on top of OPFS in order to improve concurrency

As far as I understand, wa-sqlite can keep one file open for multiple connections and manage access using locks, but doesn't this require some kind of single shared context (SharedWorker?) where OPFS file is open and other contexts just communicate with it when they need to read/write to file?

---

there's two ways. One is what you allude to, a shared context.

The other is using WebLocks to manage lock handoff of the file between processes. AFAIK, OPFS has no way to notify a caller when the file is available (unlocked) so the official SQLite build polls the file while it is locked and, if it took too many retries, fails. wa-sqlite uses WebLocks to notify callers when the file is available so they don't need to poll or retry indefinitely.