I've been making a lot of use of SQLite WASM over the past few months.<p>The other day I learned that it's built into Observable notebooks now - so if you have a SQLite database file that's hosted anywhere online with open CORS headers you can load and query that directly from a notebook.<p>This notebook here uses that trick: <a href="https://observablehq.com/@simonw/mastodon-users-and-statuses-over-time" rel="nofollow">https://observablehq.com/@simonw/mastodon-users-and-statuses...</a> - I wrote more about how that all works here: <a href="https://simonwillison.net/2022/Nov/20/tracking-mastodon/" rel="nofollow">https://simonwillison.net/2022/Nov/20/tracking-mastodon/</a><p>SQLite compiled to WASM is also bundled with the Python Pyodide WebAssembly system, which is how my <a href="https://lite.datasette.io/" rel="nofollow">https://lite.datasette.io/</a> Datasette Lite application works: <a href="https://simonwillison.net/2022/May/4/datasette-lite/" rel="nofollow">https://simonwillison.net/2022/May/4/datasette-lite/</a>
I am so happy to see official support for this, particularly the work they are putting into the persistence options. There is a new browser API called the "Origin-Private FileSystem" that provides a sandboxed, domain specific, block level filesystem. It is exactly this type of use case, a client side database, that it is designed for, and in fact it is essential to make most use cases of client side WASM SQLite useful.<p>Essentially, the OPFS enables true ACID compliance, without it you have to "stop the world" and serialise the SQLite db to a string for saving in IndexedDB.<p>The trifactor of the Origin-Private FileSystem, WASM and SQLite is the missing link to make so many progressive web apps possible.<p>WASM SQLite persistance options: <a href="https://sqlite.org/wasm/doc/trunk/persistence.md" rel="nofollow">https://sqlite.org/wasm/doc/trunk/persistence.md</a>
It has been some time since I last worked extensively with SQLite; but I just started doing some benchmarking on queries against decent sized tables (e.g. a few dozen columns and at least 5M rows) for simple queries like 'SELECT * FROM <table> WHERE <column1> = 'Hello' AND <column3> < 100;'.<p>For those who work with it regularly; have you noticed any dramatic changes in query speeds in the past few years/versions? I imagine that most improvements to the code have been around new features but there may be some big speed gains in 'bread and butter operations' like queries or updates, but I am not aware of anything specific.