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...
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
I worked at a startup who did the whole "every table schema is "id, data" where data is a JSON blob with all the data." I loved it at first. For initial development, it felt so freeing and so productive.
Then one of the devs left and I inherited his code, and started to realize exactly how much the rigid schema helps make an app more robust and more maintainable. I will never, ever take that shortcut again. The production outages/downtime caused by database issues that never should have been an issue in the first place (like a corner case row that missing data? Spreading rampant defensive nil checks out across every function that checks any field of a record since any at any time might be inconsistent or nil) contributed to killing the company by pissing off customers.
Elixir/Phoenix does help a lot though because I still treat most rows like JSON objects, but under the hood it's a normal rigid postgres schema. Best of both worlds IMHO.
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.
We are talking about the internal database of some application: its schema is something that should be designed well, not enforced defensively like a schema for validating undependable inputs.
Similarly one of my most important projects reads a lot of api calls returning serialized json. Those calls are expensive so I have, over time, tried many complicated cacheing mechanism.
These days though it's _so_much_simpler_and_cleaner_ to just wrap the call in a decorator that caches the request to sqlite3 and only makes the call if the cache is stale.
I don't worry about parsing the results or doing any of the heavy lifting right away - just cache the json.
Sqlite is so good at querying those blobs and is so fast it's just not worth munging them. Nice.
And using something like datasette to prototype queries for some of the more complicated tree structures is a breeze.
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.
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.
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.
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...