SQLite WASM: Something subtle in the browser
blog.kebab-ca.se
blog.kebab-ca.se
Kudos to the SQLite team. It was a joy to implement.
I also see that load-db.js is loading the known search index values into localStorage ... do you have some tooling for creating this file from a known SQLite base file or was it just handrolled from a localStorage already holding a valid DB?
EDIT: Looking in the code I found this const db = new sqlite3.oo1.DB({ filename: 'local', // The 't' flag enables tracing. flags: 'r', vfs: 'kvvfs' });
Googling lead me to the "official" SQLite WASM pages which otherwise don't appear too prominently in search results for whatever reason. This (https://sqlite.org/wasm/doc/trunk/about.md) seems like a good starting point and notes that both sql.js and absurd-sql are in fact inspirations
The process for building the database is a bit complex. I want to support all browsers, so unfortunately need to use local storage to back it up. Firefox has a while to go before it supports the Origin Private File System, but once it does so the build will be a lot smoother.
I build the index as part of the site’s CI (using nix), by running SQLite-WASM in deno to pre-load local storage. I then extract the keys from local storage and populate them as part of the site load using the hand-rolled load-db.js file.
SQLite WASM does have better support for importing / exporting databases on OPFS, so this process should be simpler as soon as I can move to it.
I’ll write a follow up post at some point on the implementation details.
I have a current project that is starting out by building a client-side SQLite DB (currently in Tauri but WASM SQLite would be an option as well) that would benefit from an option to offload building really large DBs so a shared server process. Knowing same or very similar code could run in Deno opens up some really interesting possibilities.
I also think the UX for WebSQL isn't great and a modern async/promises based interface would have been better (even if queries themselves non-blocking and serialized on another thread). Combined with the File System Access API, this could be really useful though.
Currently toying with a Rust/Tauri project, and debating on using the SQLite plugin to do the data access in the UI, or in the rust side, then serialize the requests across more manually. Since I'm dealing with other services, will have to do a lot of that anyway.
If you've looked more closely and know that not to be the case would like to hear what you've seen but my understanding is that anything that leaves the WebView sandbox is using RPC to make calls to Rust.
I don't understand why this is a problem. It's in literally everything but my browsers, is 1/18th the size of Apple's home page (500 KB, while Chrome is a 1GB binary on my machine), and is tested far better than any browser.
> …which was a problem for any browser that could not just bundle SQLite.
What's a scenario where a browser can't just bundle SQLite?
"Beyond HTML5: Database APIs and the Road to IndexedDB" (June 2010) https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
The correct abstraction is WASM and the now no longer missing piece, the OPFS, which allows you to build any ACID compliment database engines you want.
Take for example DuckDB, without WASM and the OPFS that wouldn't be possible. We are going to see an explosion in "local first" development as a result of these new primaries build on top of many different database engines.
SQLite Wasm in the browser backed by the Origin Private File System - https://news.ycombinator.com/item?id=34352935 - Jan 2023 (208 comments)
SQLite 3.40.0 with WASM Support - https://news.ycombinator.com/item?id=33696837 - Nov 2022 (15 comments)
SQLite Release 3.40.0 - https://news.ycombinator.com/item?id=33628136 - Nov 2022 (16 comments)
SQLite in the browser with WASM/JS - https://news.ycombinator.com/item?id=33374402 - Oct 2022 (198 comments)
At least Firefox OPFS is under development and looking at the tracking ticket seems to have accelerated in January
I think this ignores modern JIT based javascript engines like v8. They're very good at optimizing hot path javascript code as native code. WASM should be easier to optimize give the lack of variability and permissiveness in calls. BUt as far as I know, this hasn't been done yet for WASM so it runs more slowly than it could.
So, I guess there's a goldilocks app size where this is a good solution.
Provided the WASM binary is cached by the browser (is it?), that would be equal to an additional web page load on the first visit.
Though given the first couple of links on the current front page that are local (Ask HN) so small because this site is tight, video content, etc, have payloads of 4.2MByte (a Washington Post article), 2.2MByte (something on the Economist), 4.5MByte (Wired), 4.7MByte (the-odin), ..., if that 780K+101K is doing something genuinely useful it perhaps isn't that big compared to the bad standard set elsewhere!
For a serious use, storing the DB engine and data in local resources rather than reloading each time which the devtools network profiler suggests is happening, would be a good idea (preferably only transferring a diff when there is an update).
Developers often have an out-of-date perspective on what is big these days and the realistic performance impacts. Especially with HTTP2 and multiple assets loading async.
But the DB size is indeed the big question here.
The upfront network cost will always be larger in comparison to independent queries, however the benefit will be less total network use after some threshold of searches due to eliminating the overhead of independent searches over the network. The other benefit is vastly improved latency of all searches after the initial page load.
Whether or not this makes sense is entirely subjective, it depends on the DB size, frequency of changes to affected data and the expected user behaviour... i.e is it extremely likely they will be making multiple searches.
I've not tried SQLite in the browser yet, but have used effectively the same solution for an internal tool by downloading all of the metadata needed for any possible queries from an SQL database. This worked very well under a certain size, making lightning fast, low latency local searches in the browser... but did not scale due to the particulars of the DB and the user case... eventually the initial metadata payload was not tolerable as the DB grew and the changes were frequent, requiring a new payload on every load of the page (unlike the authors use case), and so I switched to backend queries, the latency for individual queries is worse, but the total experience is better since the initial load is the same.
That hurt.