SQL.js: SQLite Compiled to JavaScript
sql.js.org
sql.js.org
Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB - and, would such a VFS be competitive in speed to using IndexedDB directly? Or, would it be equivalent to use Emscripten’s existing POSIX-ish filesystem backed by IndexedDB?
An IndexedDB VFS would allow sql.js to durably persist data in the browser.
Edit: The concern is always about allowing it to be easy to delete junk data.
localStorage is not durable. At Notion, we observed Chrome localStorage losing writes under load from multiple async writers. IndexedDB is the most durable option, but has quite an annoying and error-prone API, which is why it would be nice to paper over it with SQLite so browser code can use the same schemas and queries as native clients.
Side tangent: Why in 2020 do we find the state of localStorage to be acceptable?
There's two problems that I see with localStorage:
- It's too easy for someone to blow it away and lose data for a web app
- Not enough capacity to be useful for a lot of things. (10 megabytes per domain)
The design of localStorage is basically flawed, IMO. It should have been two things: volatileStorage and permanentStorage. volatileStorage would basically be exactly what localStorage is today, being very limited and not requiring any permissions. permanentStorage would be like localStorage except it would require explicit permission, allow unlimited storage, and would be more difficult to accidentally delete(separate delete dialog from Clear History).
As far as I know, we don't have anything like my proposed permanentStorage outside of web extensions, which leaves localStorage in a weird area where it's only really useful for local app settings, even though such sparse data is easy enough to just store on a server in the first place. It would still be useful for truly offline-first apps, or apps that don't require accounts, but then this space of apps is still crippled by limited storage capacity.
Or because trackers are using indexeddb to bypass Safari's anti-tracking and privacy measures.
No doubt it’ll no doubt leak some number of bits that differ between platforms and browsers and that will be used to identify and track users.
We do need better permanent storage.
What is interesting about this approach is that at least for Firefox, IndexDB is implemented using SQLite. So ultimately this approach is SQLite running in SQLite with an IndexDB layer in the middle.
I am not a fan of the IndexedDB API.
SQLite is a large, complex project by itself. Not only would you be adding it’s quirks into the spec but you’d be basically locked into a specific version of SQLite that has to be bug for bug compatible with whatever version was shipped before.
It’s quite clearly a terrible, terrible idea. And I say that as someone who was quite looking forward to what WebSQL has to offer. It’s more a reflection on “there is only one embedded SQL database suitable for use” than anything else.
But a full SQL database engine on the SSD would fall under the umbrella of computational storage, and so far everyone with the resources to put any of that into production has datasets that don't fit on a single drive. So there's less utility in having the drive speaking proper SQL, but a lot of active research into how to usefully offload some of the DB work onto compute resources that reside on the SSD itself.
(Also, microcontroller is a bit odd to use to refer to the main controller chip inside a SSD, especially a high-end enterprise SSD. It gives a completely misleading indication of scale.)
https://www.anandtech.com/show/14839/samsung-announces-stand...
There are a few neat adjacent things that it can be used for (e.g. opening big SQLite files on disk without reading it all to memory), building something like Datasette (https://github.com/simonw/datasette) that can run queries on data hosted as static files, or for (as you suggested) using SQLite in a browser with persistence.
For that particular use case, there's a bit of complexity related to mutexes and stuff in trying to prevent simultaneous browser tabs doing write operations from corrupting the database.
A few weeks ago, I built a program to help find jobs in New Zealand and Australia. It scrapes the Seek website, and allows me to make more complex queries (e.g. does not contain "right to live and work in this location")
Currently I'm running the scraper on a spare laptop manually every day or two. I'd rather set it up to work with Github Actions, but didn't get around to that yet.
> sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers)
> By default, sql.js uses wasm