3 ms·
My code automatically creates a column for each key in the JSON object, adding any columns that are missing. If a key represents a nested object, I dump JSON f
by simonw 5y ago
My code automatically creates a column for each key in the JSON object, adding any columns that are missing.
If a key represents a nested object, I dump JSON for that object into a text column (since SQLite doesn't have a separate JSON type).
What I'm doing doesn't feel different enough from "dump it all in a JSON column" to justify different terminology.
Here's an example of my results: https://github-to-sqlite.dogsheep.net/github https://github-to-sqlite.dogsheep.net/github
- tomnipotent 5y agoI consider schema-on-read to be the property of a system that retains the original response of a remote system call (mostly) unmodified, such that any derived values can be re-created to account for mistakes/omissions. Doesn't necessarily mean that you have to project your schema dynamically every time from the original data, just that you have that option. If you're able to (mostly) re-create the original response and recover from any error, I think that's close enough to schema-on-read though the pedant could disagree (easy examples of fp precision come to mind). I think of schema-on-write in the context of data from a remote system being copied to a local system such that mistakes/omissions require fetching data from the remote system to reconcile. It sounds like you've also tackled dynamic schema migration from JSON graphs to Sqlite relations, minimizing relation count by keeping nested graph objects as their JSON string representation.
- simonw 5y agoThat's a really clear explanation, thanks! I think what I'm doing counts as schema-on-read then: the tables I am creating can be used to recreate the exact API response that I recorded, because there is a one-to-one mapping between the columns and the keys in that original JSON.