[SQL] Client-side SQL in JS: Using the File System Access API

I'd like to create/use and store an SQLite (or any SQL) DB locally on the client, from the browser. This used to be impossible until approx late 2022 / early 2023, but now the File System Access API allows for direct file read/writes and is supported in most/all browsers.

(FYI my primary goals: my app works with data that users do not want transferred off their machine (for their own reasons), and it works with data that users want to manage backup/restore themselves - e.g. they're already storing similar data in their own SCM, and they want it together)

All I can find so far:

1. SQLite has a VFS backend that uses File System Access API - but only uses the cutdown "OPFS" sub-system. (I don't understand why they chose this - maybe OPFS gives better performance? - but it removes a lot of the core power of FSSA. e.g. OPFS does not store to hard-disk, it stores to a proprietary virtual location within the browser. OPFS by design prevents you from backup/restore/synch the database file. It also has a history of 'accidentally' deleting files occasionally - not common but reported by multiple people)
2. Using the File System Access API to wrap an SQLite DB - OK, works in theory, but used directly it only allows read-all/write-all semantics, so your SQL DB is going to be extremely slow (you're manually copying the whole thing to/from disk on every transaction?).
3. Using the API directly - write your own database library, reinventing the wheel, and spending years trying to implement a complete SQL.

Is there an option I'm missing, or something I've misunderstood? Is there another way to achieve this?

Continue reading...