[HN Gopher] SQLite 2022 Recap
___________________________________________________________________
SQLite 2022 Recap
Author : nalgeon
Score : 108 points
Date : 2023-01-01 18:11 UTC (4 hours ago)
(HTM) web link (sqlite.org)
(TXT) w3m dump (sqlite.org)
| PreInternet01 wrote:
| A 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...
| mulmboy wrote:
| As a counterpoint I find that nutting out the schema upfront is
| an incredibly helpful process to define the functionality of
| the app. Once you have a strict schema that models the
| application well nearly everything else just falls into place.
| Strong foundation
| HelloNurse wrote:
| With JSON blobs in simple tables a very large part of
| designing a database schema becomes designing a JSON schema,
| with different opportunities to make mistakes but
| (potentially) the same strictness.
| mulmboy wrote:
| How do you enforce the JSON schema?
| nonethewiser wrote:
| This seems awful but maybe I'm underestimating the number of
| failed apps you're dealing with. And at that rate, maybe the
| app ideas should be vetted a bit more.
| di456 wrote:
| Thanks for this explanation.
|
| I've been hacking together one-off python scripts to parse out
| bits of saved API responses, flatten the data to csv's, and
| load it to SQLite tables. Looks like I can skip all of this and
| go straight to querying the raw JSON text.
| ansgri wrote:
| 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.
| tanin wrote:
| Full outer join is my fave update.
|
| I kinda wish sqlite has more functions though.
| hiAndrewQuinn wrote:
| The stable release of the WebAssembly and JavaScript APIs strike
| me as particularly monumental. Long live SQLite!
|
| https://sqlite.org/wasm/doc/trunk/index.md
| NeutralForest wrote:
| 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.
| cldellow wrote:
| 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...
| NeutralForest wrote:
| Thanks for the explanation, the author of Datasette himself
| answered, which is a pretty cool thing I like about HN =)
| samwillis wrote:
| The intention is to make use of the new "Origin privet file
| system" api, this provides the website with a sandboxed block
| level file system on the users device that can be used for
| efficient access and writes. It will be possible for WASM
| SQLite, or any other DB engine ported to WASM, to have full
| ACID compliance.
|
| The SQLite team have been working with browser developers to
| ensure the new API is sufficient to enable all this.
|
| Honestly, and I keep going on about it, SQLite in the browser
| via WASM is the missing piece to make "offline first" PWAs a
| serious contender when deciding an architecture for an app.
|
| 2023 is going to be the year of SQLite in the browser.
|
| https://webkit.org/blog/12257/the-file-system-access-api-
| wit...
|
| https://sqlite.org/wasm/doc/trunk/persistence.md#opfs
|
| https://chromestatus.com/feature/5702777582911488
| NeutralForest wrote:
| Very interesting, seems like I have a lot of reading to do,
| thanks!
| ec109685 wrote:
| Unfortunately unlike apps, the system doesn't support
| backing up local storage managed by the browser, so if you
| get a new phone, you lose your data.
| simonw wrote:
| 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...
| NeutralForest wrote:
| Oh wow, thanks for all the links!
| maccaw wrote:
| Under persistant storage options:
|
| https://sqlite.org/wasm/doc/trunk/persistence.md
| justsomeuser wrote:
| It would be nice if OPFS (Origin Private File System) allowed
| reading/writing to an actual SQLite file on the users disk.
|
| At the moment, as I understand it, the OPFS virtual disk is
| completely isolated from the users disk.
|
| This means you cannot just lightly query a 1GB file without
| first copying the 1GB from the users filesystem to the OPFS.
|
| Any writes mean you must then copy the 1GB SQLite db file from
| OPFS to the users local filesystem too.
|
| Is this correct?
| johnny22 wrote:
| well it makes sense that the browser can't work outside of
| the opfs boundary, but shouldn't there be a way to talk to
| the opfs from outside the browser?
___________________________________________________________________
(page generated 2023-01-01 23:01 UTC)