SQLite has a system that abstracts various low-level storage operations called VFS - this is the layer that allows SQLite to be portable across different operating systems and environments: https://www.sqlite.org/vfs.html
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.
What if SSDs just had an alternative interface that talked SQL? Presumably you could bake SQLite into the microcontroller and optimize perfectly for the storage scenario. Having your journaling aware of how long your supercapacitor will last in the worst case could open the door for some wild optimizations.
> Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB
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.
Similarly, would like to see SQLite work over a WASI's File System interface... best would be the ability to also support multi-process, but I don't know that it would be possible.
Google has a project called Lovefield [1] that is a SQL-like thing on top of IndexedDB. Not exactly SQLite and I'm not sure what its status is now but might be of interest.
I've actually prototyped this recently, but never got around to packaging it up into a library.
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.
What you really want for this is SQLite4--which added an abstraction layer below the database and above the VFS which could use a key-value store--but sadly I think it ended up being scrapped :(.
We use SQL.js to power the SQL course at https://www.executeprogram.com. We've encountered a couple of relatively minor hiccups, but overall it's been great. We've had thousands of users go through that course with no reported problems that are attributable to SQL.js.
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.
Very cool. I use it to teach SQL. I added a CodeMirror region for editing SQL code and have buttons that load a few example databases. The advantage of this is that students do not have to install anything, they can just visit the web site, and they can practice destructive steps like deleting rows or a table, and then just reset everything with a button click. Discovering SQL.js was a huge moment.
[+] [-] jitl|5 years ago|reply
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.
[+] [-] bob1029|5 years ago|reply
[+] [-] RcouF1uZ4gsC|5 years ago|reply
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.
[+] [-] tracker1|5 years ago|reply
[+] [-] inbx0|5 years ago|reply
[1]: https://github.com/google/lovefield
[+] [-] antimatter15|5 years ago|reply
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.
[+] [-] saurik|5 years ago|reply
https://sqlite.org/src4/doc/trunk/www/design.wiki
[+] [-] WrtCdEvrydy|5 years ago|reply
Edit: The concern is always about allowing it to be easy to delete junk data.
[+] [-] gary_bernhardt|5 years ago|reply
[+] [-] peterburkimsher|5 years ago|reply
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")
https://ifeelseek.github.io/
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.
[+] [-] bobochan|5 years ago|reply
[+] [-] malkia|5 years ago|reply
[+] [-] dzonga|5 years ago|reply
[+] [-] CarelessExpert|5 years ago|reply
[+] [-] bstar77|5 years ago|reply
[+] [-] singularity2001|5 years ago|reply
[+] [-] atmin|5 years ago|reply
> sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers)
> By default, sql.js uses wasm