5 ms·
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 the
by PreInternet01 4y ago
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...
- phoobahr 4y agoSimilarly 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.
- ansgri 4y agoFinally! 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.
- mulmboy 4y agoAs 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 4y agoWith 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 4y agoHow do you enforce the JSON schema?
- HelloNurse 4y agoWe 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.
- tenken 4y agoIn the application layer
- breeze1990 4y agodefine schema with data class and you have tools like protobuf/thrift. And I find it really unnecessary to map every attribute to a SQL column.
- wenus 4y agoNutting?
- Rebelgecko 4y agohttps://www.macmillandictionary.com/us/dictionary/american/nut_2 https://www.macmillandictionary.com/us/dictionary/american/n...
- nonethewiser 4y agoThis 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 4y agoThanks 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.