I’d like to create/use/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:
- 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)
- 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?).
- 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?