Hacker News Reader

Comment by stubish | Hacker News Reader

I think the approach is fine for prototyping, but the architectural flaw that can make it a bad choice for production systems is that it ties your APIs to your data model. When you need to evolve your API or extend your data model, you get to choose between dealing with potentially massive data migrations and downtime, or having a third layer of glue in the form of database views. You probably would need to adopt a rule of no direct-to-table access, and a separate schema per API version containing views, and dealing with the pain of rewriting the views for all the supported API versions when you need to change your data model. Or scale the database horizontally, with views on foreign data wrappers that pull from the real data sources. Which I think this sort of solution is marketed as a way for a Database Administrator (singular) to expose an API, rather than a scalable approach to application development.

View on HN

> You probably would need to adopt a rule of no direct-to-table access

That's like one of the oldest RDBMS best practices: no direct to table access, with every application (or class of business user with direct access to the DB) having access through a tailored selection of views so that apps/users are largely isolated from DB changes.

> and a separate schema per API version containing views

You'd probably only need a separate schema for semver-major API versions, minor versions would necessarily be supersets which could be accommodate by backwards-compatible schema extension.

> and dealing with the pain of rewriting the views for all the supported API versions when you need to change your data model.

With a well-designed normalized relational model, most changes to the base model are adding attributes or tables, which will have zero impact on view definitions to maintain an existing API. The next most common is factoring an existing attribute out to a different table because what was conceived of as a 1:1 relationship becomes 1:N, which requires adding a join to view defs where that attribute is involved.

How do you deal with writing operations in this approach?

> How do you deal with writing operations in this approach?

The same as read operations, with views, which can be, in Postgres, automatically updatable if they are a simple thin layer over base tables, and otherwise can be made updatable by means of appropriate triggers defining the meaning of INSERT/UPDATE/DELETE on them just as the view definition defines the meaning of SELECT.

It’s funny how “best practices” get mirrored basically everywhere up and down the stack. Everything at some point will have some way of decoupling data-access and data-structure, you just choose the level you want to do it at (toolkit, ORM, network api, database, schema/view, tablespace, filesystem...) and the number and granularity of layers, picking what you are most comfortable with.

It’s like, no matter how low-level you go, at some point someone had a problem with components being tightly coupled and introduced an indirection layer “for next time”.

> You probably would need to adopt a rule of no direct-to-table access, and a separate schema per API version containing views, and dealing with the pain of rewriting the views for all the supported API versions when you need to change your data model.

My understanding is there is no direct-to-table access with PostgREST; you're only ever interacting with views. Keep your view api versions in separate schemas. If you change your tables, yes, you'll need to update your views, but I don't see how that's much different than having to modify ORM code to accommodate schema changes.

The examples in these docs start with querying tables, and only afterwards go "oh, it also works with views, if you need more complex filtering".

You can’t just throw complexity at ppl from the first lines of documentation especially when talkin about new ideas

Right, and from my experience the automatic CRUD endpoints don't deal well with views unless you annotate the view with postgrest-specific comments, which seems rather icky to me.

you are confusing postgrest with postgraphile :)

Good point. Whoops.

I'd love to point out that in a strictly RESTful design, your client is supposed to be discovering the data model from queries, not embedding it in code. Ergo data and schema migrations should be no trouble at all.

Not sure I understand how any other approach to provisioning an data API is going to be able to avoid requiring a data model.

In practice it is extremely rare to find a pure REST model. It is hard to do properly.

Reply on news.ycombinator.com