undefined | Better HN

0 pointstomnipotent4y ago0 comments

If you're taking a JSON API response structure and creating an exact 1-to-1 mapping to a SQL schema, you're doing schema on write.

If you store the JSON in a JSONB column, and then create projections as needed by parsing fields from the BLOB, then you have schema on read.

0 comments

4 comments · 2 top-level

simonw4y ago· 2 in thread

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

tomnipotentOP4y ago

I 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.

simonw4y ago

That'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.

jtsiskin4y ago

Your using the database definition of “schema” which is orthogonal to the definition in the article. Use the example of the browser’s bookmarks list. The browser is storing that with a DB schema. Yet an ever growing list of bookmarks has no “conceptual/knowledge base” schema

j / k navigate · click thread line to collapse