Hacker News Reader

Comment by simonw | Hacker News Reader

I think there are some reasonable ways to avoid the schema brittleness that people are concerned about with this.

Firstly, acknowledge that in this model your schema IS your API. Don't include anything in the database that is being replicated that isn't considered to be documented, tested and stable.

With that in place, API changes become the same as changes to a JSON API or similar: you can only make backwards-compatible changes, like adding new columns or adding new tables.

If you do need to make a backwards incompatible change there are ways to patch over it. Ship a brand new table with the new design, and include a SQL view that matches the name of the old table and exposes a compatible subset of it.

View on HN

Maybe it's better to have views in the first place, and to require (though it can only be enforced at an administrative level) external services to query against those views.

We won't have to do this switch to a view, we've paid that upfront.

We can then include a version number in the view name, similar to how we do with APIs. So if we introduce changes we can keep services which didn't need that change outside the blast radius.

And we have a limited ability to enforce contracts and declare some columns internal.

Even better, use stored procedures / routines on top of views and only query the stored procedures from the frontend.

I like it, but the only benefit we've retained at that point is eliminating round trips. Which is pretty significant. But we're still engaging in API design, we're just doing it in Sqlite.

That is an appealing idea but on second thought, haven’t you just shifted the problem to: how do I reliably replicate logical updates from my operational tables (internal, constantly changing) to my clean, public-facing tables?

Basically, your public tables become materialized views over the “dirty” tables, and I’ve never met a mature, reliable implementation of materialized views, or even an immature one that is feature complete. (I would love to be wrong on the internet with this one!)

That's just working around an artificially and unnecessarily introduced constraint. There's little zero upside to doing it this way. It's not even more convenient since it incurs pretty heavy refactor costs for even small changes.

This is correct, but there are very few people with experience with safe database modelling like this, and they may not even realize the technique exists.

The same is true for APIs, too. Making changes to a JSON API that won't break existing clients isn't necessarily an obvious skill.

But it can be learned, as can backwards-compatible database schemas.

Reply on news.ycombinator.com