5 ms·

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

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.

jdnier 7y ago
> 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.
- Izkata 7y ago
  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".
  - ruslan_talpa 7y ago
    You can’t just throw complexity at ppl from the first lines of documentation especially when talkin about new ideas
  - Doxin 7y ago
    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.
    - ruslan_talpa 7y ago
      you are confusing postgrest with postgraphile :)
      - Doxin 7y ago
        Good point. Whoops.
dragonwriter 7y ago
> 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.
- andrey_utkin 7y ago
  How do you deal with writing operations in this approach?
  - dragonwriter 7y ago
    > 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.
- toyg 7y ago
  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”.
no-s 7y ago
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.
- markbernard 7y ago
  In practice it is extremely rare to find a pure REST model. It is hard to do properly.

jdnier 7y ago

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

Izkata 7y ago
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".
- ruslan_talpa 7y ago
  You can’t just throw complexity at ppl from the first lines of documentation especially when talkin about new ideas
- Doxin 7y ago
  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.
  - ruslan_talpa 7y ago
    you are confusing postgrest with postgraphile :)
    - Doxin 7y ago
      Good point. Whoops.

dragonwriter 7y ago

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

andrey_utkin 7y ago
How do you deal with writing operations in this approach?
- dragonwriter 7y ago
  > 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.
toyg 7y ago
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”.

no-s 7y ago

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.

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