4 ms·

you don't expose your tables directly, you expose a schema that consists only of views and stored procedures. If you really need a totally different version the

you don't expose your tables directly, you expose a schema that consists only of views and stored procedures. If you really need a totally different version then you jsut exppose a new schema but more often it's the same situation as in graphql ecosystem, you jsut add a new column/view/procedure and don't delete the old one. Postgrest has the same power to describe what you want as a graphql api woudl have (by using it's select parameter)

mlthoughts2018 7y ago
It’s interesting that the project’s own tutorials do not discuss any of that and not only demonstrate making the API schema exactly equal to table schemas, but go further and claim that adding intermediate business logic or using tools that mediate between the data and business logic, like ORMs, are bad abstractions that should be intentionally avoided. I mean, I agree with the strategy you state about views or stored procedures, but those are just in-database ways of achieving the same kinds of things you might prefer to write in a different language (thus ORM or query engine) because it puts the app or business logic all into the same version controlled system, leverages programming language ecosystems and tools that are often way more valuable than raw database programming (even in Postgres), etc. Basically, if PostgREST needs you to do the old tricks of views & stored procedures to manage an abstraction layer that safely allows the underlying data schema to change, I just don’t see the benefit over doing this in a much better language ecosystem, like Python, and using much better web server tools to generate the APIs. PostgREST looks much more useful for quick prototypes, internal use cases where schema breakage might be OK occasionally, or just mirroring & monitoring data as-is for ops and diagnostics. From a performance perspective, it might be fast enough for production, but that’s almost never as big a concern as managing the intermediate abstraction layer and associated app tooling. Does not look like a good idea for production applications that need an intermediate API layer adapting the data to the use case.
- ruslan_talpa 7y ago
  Why would you prefer that? In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes. In the Db you "define" how it should look/behave/ and who has access. As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude). I know your reaction comes form years of tutorials telling you not to do this, but this was way back when mysql didn't even have views, things are a lot different now, databases are not dumb stores. Give it 5 minutes and try a small project, you'll be surprised by the power
  - mlthoughts2018 7y ago
    > “In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes.” No, it could be functional, declarative, OO, whatever, depending on the language used. Personally, after a lot of years of Haskell & Scala experience in large companies, I think functional & declarative programming are way overhyped, and these types of designs do not actually offer the benefits they are claimed to. Given this, I see zero reason to care if the abstraction layer is some OOP tool. That is absolutely fine. > “As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude).” This is comically wrong. I remember the ~100 line long MSSQL stored procedure just to compute (poorly) the median of a column. Expressing things in the languages supported with the database (even in Postgres, even with language extensions) requires waaaaay more code than using application tools in the application language. I remember how much easier our lives became at an old finance job when we finally ported a ton of stored procedures to instead use pandas for math operations in Python. The cost of passing the data through processing jobs that required serializing it to servers where the jobs could run, transforming it, then transporting it back, was absolutely worth it because we could remove thousands and thousands of lines of stored procedures that were incredibly bug prone, impossible to debug, and totally lacking the linear algebra and analytics functionality we needed to expand the app. Moving the logic out of the database was 100% motivated by making it safer, less buggy, simpler & easier to test code, and gaining expressive functionality totally impossible to express in the database.
    - ruslan_talpa 7y ago
      I guess each person's oppinion is colored by personal past experiences, i too can come up with stories of how backend apis with 10s of thousands of lines got reduced to a handfull of views and stored procedures. But it's true this aproach does work when you have to do linear algebra on your data (my feeling is that this requirement does not comes up in so many projects). On the debuging part it's a bit true, the workflow is not as polished (debuging in db) as oposed to other envs. About the "fast" part, i would not say "comically wrong", if it were, there'd be no reason for sql beyond "select * from"
- philwelch 7y ago
  So there are different schools of thought about using relational databases here and I think this is where a lot of the tension comes from. I think everyone agrees in principle that in a service-oriented architecture you need well-defined, safe, hardened interfaces between services. In an ORM world, the assumption seems to be that the database itself isn't really a service with a well-defined interface, but rather a private data store that just accepts whatever SQL you throw at it. But what if you think of the database itself as a service? If that's the case, then your service interface should definitely not be arbitrary SQL. This is where you introduce views and stored procedures, which change your DB from a private implementation detail that you have to hide behind a service boundary to a service that sets its own boundaries. In this world, your REST services have an HTTP client to make service calls to each other, and they have a Postgres client to make 'service calls' to your database. PostgREST is just a deterministic proxy that adapts one service protocol to another, the same way you would use grpc-gateway if you had gRPC services that you wanted to call from REST clients. I don't think PostgREST obviates the need to write intermediate API layers, at least if those intermediate API layers are doing anything interesting. It may obviate the need to write API layers that only parameterize SQL statements and serialize JSON responses. But that's a good thing to obviate. And yeah, you should definitely version control your DB schemas, views, and stored procedures. We aren't barbarians :)
  - ruslan_talpa 7y ago
    Very well said (I'll have to remember this way of explaining it). IMO in most projects, the intermediate layers never do anything interesting so that's why postgrest (as a proxy) is a good fit
    - philwelch 7y ago
      > IMO in most projects, the intermediate layers never do anything interesting so that's why postgrest (as a proxy) is a good fit I try to avoid generalizations about "most projects", because different people have different experiences and it's hard to make a good argument about which case is more typical. At best I think you can lay out the toolbox and explain where PostgREST fits in the toolbox. Whether or not you should use it on a particular project depends on the particular project, and I have no idea what mlthoughts2018 is working on or has worked on in the past, so that's an entirely different question :)
korijn 7y ago
Thanks for elaborating. That sounds like a valid strategy.

mlthoughts2018 7y ago

It’s interesting that the project’s own tutorials do not discuss any of that and not only demonstrate making the API schema exactly equal to table schemas, but go further and claim that adding intermediate business logic or using tools that mediate between the data and business logic, like ORMs, are bad abstractions that should be intentionally avoided. I mean, I agree with the strategy you state about views or stored procedures, but those are just in-database ways of achieving the same kinds of things you might prefer to write in a different language (thus ORM or query engine) because it puts the app or business logic all into the same version controlled system, leverages programming language ecosystems and tools that are often way more valuable than raw database programming (even in Postgres), etc. Basically, if PostgREST needs you to do the old tricks of views & stored procedures to manage an abstraction layer that safely allows the underlying data schema to change, I just don’t see the benefit over doing this in a much better language ecosystem, like Python, and using much better web server tools to generate the APIs. PostgREST looks much more useful for quick prototypes, internal use cases where schema breakage might be OK occasionally, or just mirroring & monitoring data as-is for ops and diagnostics. From a performance perspective, it might be fast enough for production, but that’s almost never as big a concern as managing the intermediate abstraction layer and associated app tooling. Does not look like a good idea for production applications that need an intermediate API layer adapting the data to the use case.

ruslan_talpa 7y ago
Why would you prefer that? In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes. In the Db you "define" how it should look/behave/ and who has access. As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude). I know your reaction comes form years of tutorials telling you not to do this, but this was way back when mysql didn't even have views, things are a lot different now, databases are not dumb stores. Give it 5 minutes and try a small project, you'll be surprised by the power
- mlthoughts2018 7y ago
  > “In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes.” No, it could be functional, declarative, OO, whatever, depending on the language used. Personally, after a lot of years of Haskell & Scala experience in large companies, I think functional & declarative programming are way overhyped, and these types of designs do not actually offer the benefits they are claimed to. Given this, I see zero reason to care if the abstraction layer is some OOP tool. That is absolutely fine. > “As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude).” This is comically wrong. I remember the ~100 line long MSSQL stored procedure just to compute (poorly) the median of a column. Expressing things in the languages supported with the database (even in Postgres, even with language extensions) requires waaaaay more code than using application tools in the application language. I remember how much easier our lives became at an old finance job when we finally ported a ton of stored procedures to instead use pandas for math operations in Python. The cost of passing the data through processing jobs that required serializing it to servers where the jobs could run, transforming it, then transporting it back, was absolutely worth it because we could remove thousands and thousands of lines of stored procedures that were incredibly bug prone, impossible to debug, and totally lacking the linear algebra and analytics functionality we needed to expand the app. Moving the logic out of the database was 100% motivated by making it safer, less buggy, simpler & easier to test code, and gaining expressive functionality totally impossible to express in the database.
  - ruslan_talpa 7y ago
    I guess each person's oppinion is colored by personal past experiences, i too can come up with stories of how backend apis with 10s of thousands of lines got reduced to a handfull of views and stored procedures. But it's true this aproach does work when you have to do linear algebra on your data (my feeling is that this requirement does not comes up in so many projects). On the debuging part it's a bit true, the workflow is not as polished (debuging in db) as oposed to other envs. About the "fast" part, i would not say "comically wrong", if it were, there'd be no reason for sql beyond "select * from"
philwelch 7y ago
So there are different schools of thought about using relational databases here and I think this is where a lot of the tension comes from. I think everyone agrees in principle that in a service-oriented architecture you need well-defined, safe, hardened interfaces between services. In an ORM world, the assumption seems to be that the database itself isn't really a service with a well-defined interface, but rather a private data store that just accepts whatever SQL you throw at it. But what if you think of the database itself as a service? If that's the case, then your service interface should definitely not be arbitrary SQL. This is where you introduce views and stored procedures, which change your DB from a private implementation detail that you have to hide behind a service boundary to a service that sets its own boundaries. In this world, your REST services have an HTTP client to make service calls to each other, and they have a Postgres client to make 'service calls' to your database. PostgREST is just a deterministic proxy that adapts one service protocol to another, the same way you would use grpc-gateway if you had gRPC services that you wanted to call from REST clients. I don't think PostgREST obviates the need to write intermediate API layers, at least if those intermediate API layers are doing anything interesting. It may obviate the need to write API layers that only parameterize SQL statements and serialize JSON responses. But that's a good thing to obviate. And yeah, you should definitely version control your DB schemas, views, and stored procedures. We aren't barbarians :)
- ruslan_talpa 7y ago
  Very well said (I'll have to remember this way of explaining it). IMO in most projects, the intermediate layers never do anything interesting so that's why postgrest (as a proxy) is a good fit
  - philwelch 7y ago
    > IMO in most projects, the intermediate layers never do anything interesting so that's why postgrest (as a proxy) is a good fit I try to avoid generalizations about "most projects", because different people have different experiences and it's hard to make a good argument about which case is more typical. At best I think you can lay out the toolbox and explain where PostgREST fits in the toolbox. Whether or not you should use it on a particular project depends on the particular project, and I have no idea what mlthoughts2018 is working on or has worked on in the past, so that's an entirely different question :)

korijn 7y ago

Thanks for elaborating. That sounds like a valid strategy.