SQLite 2022 Recap
sqlite.org
SQLite 2022 Recap
1–10 of 41 posts
Re: SQLite 2022 Recap
#2The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite!
Re: SQLite 2022 Recap
#3The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md
I don't understand the use case? From the docs [https://sqlite.org/wasm/doc/trunk/demo-123.md], the database disappears on page reload on top of requiring using workers for longer running processes.
Re: SQLite 2022 Recap
#4A pretty boring and stable year in SQLite land, which is just how I like it.
The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy:
1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to in-app objects;
2. Once a semblance of a schema materializes, use -> and ->> to create `VIEW`s with actual field names and corresponding data types, and update in-app SELECT queries to use those. At this point, it's also safe to start communicating database details to other developers requiring read access to the app data;
3. As needed, convert those `VIEW`s to actual `TABLE`s, so INSERT/UPDATE queries can be converted as well, and developers-that-are-not-me can start updating app data.
The interesting part here is that step (3) is actually not required for, like, 60% of successful apps, and (of course) for 100% of failed apps, saving hundreds of hours of upfront schema/database development time. Basically, you get 'NoSQL/YOLO' benefits for initial development, while still being able to communicate actual database details once things get serious...
Re: SQLite 2022 Recap
#5The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
You can still persist them; you just have to wire it up yourself. Using workers is a best practice for any CPU-bound task, so that's not a drawback by itself in my mind.
It's good for single-page applications. Many datasets are relatively small -- pushing them to the client is reasonable. In exchange, you get zero-latency querying and can build very responsive UIs that can use SQL, versus writing REST APIs or GraphQL APIs.
Taken to an extreme, it permits publishing datasets that can be queried with no ongoing server-side expenses.
A wild example: Datasette is a Python service that makes SQLite databases queryable via the web. It turns out that since you can compile Python and SQLite to WASM, you can run Datasette entirely in the user's browser [1]. The startup time is brutal, because it's literally simulating the `pip install`, but a purpose-built SPA wouldn't have this problem.
[1]: https://lite.datasette.io/?url=https%3A%2F%2Fcongress-legisl...
Re: SQLite 2022 Recap
#6The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser.
Plenty of interesting databases fit into less than a MB even.
I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser: https://simonwillison.net/2022/May/4/datasette-lite/ - here's an article showing how that can be useful: https://simonwillison.net/2022/Aug/21/scotrail/
It's also available in Observable notebooks, which is really handy. Here's a project I built on top of that: https://simonwillison.net/2022/Nov/20/tracking-mastodon/ - notebook here: https://observablehq.com/@simonw/mastodon-users-and-statuses...
Re: SQLite 2022 Recap
#7The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
Under persistant storage options:
Re: SQLite 2022 Recap
#8Earlier quoted context omitted.
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser. Plenty of interesting databases fit into less than a MB even. I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser…
Oh wow, thanks for all the links!
Re: SQLite 2022 Recap
#9Earlier quoted context omitted.
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
You can still persist them; you just have to wire it up yourself. Using workers is a best practice for any CPU-bound task, so that's not a drawback by itself in my mind. It's good for single-page applications. Many datasets are relatively small -- pushing them to the client is reasonable. In exchange, you get zero-latency querying and can build very responsive UIs that can use SQL, versus writing REST APIs or GraphQL…
Thanks for the explanation, the author of Datasette himself answered, which is a pretty cool thing I like about HN =)
Re: SQLite 2022 Recap
#10A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…
Finally! I've had good experience following similar process with Postgres (start with obvious key fields and a json "data" then promote components of "data" to columns; maybe use expression indexes to experiment with optimizations). Good to know this is now possible in sqlite without ugly function calls.