4 ms·

SQLite 3.40.0 with WASM Support

76 points by conductor 4y ago

tiffanyh 4y ago
Does this make how Cloudflare/Fly.io use of SQLite easier now?
- jgrahamc 4y ago
  SQLite on Cloudflare: https://blog.cloudflare.com/d1-open-alpha/ https://blog.cloudflare.com/d1-open-alpha/
samwillis 4y ago
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 https://sqlite.org/wasm/doc/trunk/persistence.md
- Scaevolus 4y ago
  We can finally resurrect WebSQL after 12 years! :-)
- sgbeal 4y ago
  > 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.
simonw 4y ago
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-over-time 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/ 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/ https://lite.datasette.io/ Datasette Lite application works: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/
pvg 4y ago
Recent threads: https://news.ycombinator.com/item?id=33628136 https://news.ycombinator.com/item?id=33628136 https://news.ycombinator.com/item?id=33374402 https://news.ycombinator.com/item?id=33374402
tiffanyh 4y ago
I can only hope that WAL2 and BEGIN CONCURRENT support is coming soon.
- ak39 4y ago
  Same. Do you have any guess when this is likely going main branch?
  - tiffanyh 4y ago
    No idea but both branches, my understanding is, have existed for years. Definitely hope it gets merged into main.
didgetmaster 4y ago
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.
jedisct1 4y ago
It can be compiled to WASI but there are no instructions on how to use zig cc to do it?
- williamstein 4y ago
  For what it is worth, I also care about building this with zig. https://github.com/sagemathinc/cowasm/blob/main/packages/sqlite https://github.com/sagemathinc/cowasm/blob/main/packages/sql...
- sgbeal 4y ago
  > 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.
  - rcarmo 4y ago
    I hacked a bit of that in here: https://github.com/rcarmo/wasi-sqlite 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.

76 points by conductor 4y ago

tiffanyh 4y ago

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

jgrahamc 4y ago
SQLite on Cloudflare: https://blog.cloudflare.com/d1-open-alpha/ https://blog.cloudflare.com/d1-open-alpha/

samwillis 4y ago

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 https://sqlite.org/wasm/doc/trunk/persistence.md

Scaevolus 4y ago
We can finally resurrect WebSQL after 12 years! :-)
sgbeal 4y ago
> 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.

simonw 4y ago

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-over-time 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/ 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/ https://lite.datasette.io/ Datasette Lite application works: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/

pvg 4y ago

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

tiffanyh 4y ago

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

ak39 4y ago
Same. Do you have any guess when this is likely going main branch?
- tiffanyh 4y ago
  No idea but both branches, my understanding is, have existed for years. Definitely hope it gets merged into main.

didgetmaster 4y ago

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.

jedisct1 4y ago

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

williamstein 4y ago
For what it is worth, I also care about building this with zig. https://github.com/sagemathinc/cowasm/blob/main/packages/sqlite https://github.com/sagemathinc/cowasm/blob/main/packages/sql...
sgbeal 4y ago
> 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.
- rcarmo 4y ago
  I hacked a bit of that in here: https://github.com/rcarmo/wasi-sqlite 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.