Mysql create table with auto incrementing id giving error.It's become my go to ORM and has excellent support for sqlite extensions like json1. If you're on the Python side of things, check out peewee. I also used to use little JSON/CSV files for small projects but after getting comfortable with sqlite + json1 and having many experiences where I end up exporting that data to sqlite anyway, I just go straight to storing everything in sqlite and using JSON columns when I don't have time to design a proper relational database and/or when I want to save JSON to disk in an easily queryable and modifiable fashion.
But once you get that under your belt, I think you'll be impressed with how quickly you can move with this approach. So be prepared to sit with the json1 docs + stackoverflow for a little while. I will say getting used to the json1 query syntax can be a little confusing at first since it doesn't quite feel like SQL syntax nor dictionary/object sort of syntax - it's more like jq. Then I can create new columns and backfill them using queries against the JSON columns if need be. This way I can easily get data my boss is looking for in the short term using the non-JSON columns, then can (usually) easily pull additional information using queries on the JSON columns so I don't need to rescrape the same pages. For each site/service scraped, the general flow is to capture a set of key variables then stash other data in one or more JSON columns. I've been using sqlite w/ JSON columns for doing a series of web scraping projects at work. Having the catch-all JSON column also means we can add an object property in our backend for web clients, and then later when we roll the feature out to native apps the data is already there - it just needs a migration to make it efficient to query. When we introduce a new query pattern to the client, we can run a number of different migration strategies to extract existing data from the JSON column and put it into a newly created column, or use a virtual column/index. So, we put un-queried object properties into catch-all JSON columns. Over time, those needs change - as do the shape of the upstream API data source. There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. Most of the rows come from the back-end API and serve as a local cache. Why spend the engineering cycles in the SQL client?įor a real world example - at Notion we have a SQLite database running in our client apps. In essence, you are getting CQRS (command/query representation separation) for “free”, built into the storage system. The client code can be a “dumb pipe” on the write side and just dump whatever received data into the SQL database.
This seems like a pure win to me for clients that need to cache data but never update it. Using virtual columns like this encodes the “normalizing” lens into the SQL schema in a purely-functional way. You would have three things to keep track of - the upstream data source (the thing supplying the JSON, might not be under your control), the lenses in your SQL client source code, and the SQL database schema. Adding bidirectional lenses to “normalize” and “denormalize” data before it enters and after it exits a SQL database means you need to maintain those lenses separately from the SQL database schema.
0 kommentar(er)
