SQLite 3.40.0 with WASM Support | Hacker News Reader

SQLite 3.40.0 with WASM Support

sqlite.org

76 pointsconductor15 comments

Recent threads:

https://news.ycombinator.com/item?id=33628136

https://news.ycombinator.com/item?id=33374402

I've been making a lot of use of SQLite WASM over the past few months.

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.

This notebook here uses that trick: https://observablehq.com/@simonw/mastodon-users-and-statuses... - I wrote more about how that all works here: https://simonwillison.net/2022/Nov/20/tracking-mastodon/

SQLite compiled to WASM is also bundled with the Python Pyodide WebAssembly system, which is how my https://lite.datasette.io/ Datasette Lite application works: https://simonwillison.net/2022/May/4/datasette-lite/

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.

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.

The trifactor of the Origin-Private FileSystem, WASM and SQLite is the missing link to make so many progressive web apps possible.

WASM SQLite persistance options: https://sqlite.org/wasm/doc/trunk/persistence.md

We can finally resurrect WebSQL after 12 years! :-)

> The trifactor of the Origin-Private FileSystem, WASM and SQLite is the missing link to make so many progressive web apps possible.

FWIW, that's exactly our sentiment within the sqlite team. People have been using sqlite with browser-side JS since at least as far back as 2012, but availability of OPFS was the catalyst which piqued our interests in jumping on the WASM bandwagon.

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;'.

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.

I can only hope that WAL2 and BEGIN CONCURRENT support is coming soon.

Same. Do you have any guess when this is likely going main branch?

No idea but both branches, my understanding is, have existed for years.

Definitely hope it gets merged into main.

It can be compiled to WASI but there are no instructions on how to use zig cc to do it?

For what it is worth, I also care about building this with zig. https://github.com/sagemathinc/cowasm/blob/main/packages/sql...

> It can be compiled to WASI but there are no instructions on how to use zig cc to do it?

We (sqlite team) are currently working on patches to get it building properly with wasi-sdk. When built as-is with wasi it's essentially non-functional due to missing POSIX I/O emulation bits. i think we can expect to see that build land by the 3.41 release.

I hacked a bit of that in here: https://github.com/rcarmo/wasi-sqlite

Right now I can run SQLite on my iPad under a-Shell pretty well, but there seems to be a bug in the REPL (not sure where) and memory usage skyrockets.

Would love to see this working in a CLI environment.

Does this make how Cloudflare/Fly.io use of SQLite easier now?

SQLite on Cloudflare: https://blog.cloudflare.com/d1-open-alpha/

Reply on news.ycombinator.com