4 ms·

What’s the benefit of using this over just using a small framework like Flask/Express with a Postgres lib?

doh 7y ago
Derek recently wrote about it [0]. The fact that you can have the thinnest client between your front-end and backend makes things incredibly flexible. If you learn PG properly you can do 100% of data preparation on the database side and just expose it through an API. If you decide to change something, you change a view and it's now whatever you just did. No code changes, no redeployments. [0] https://sivers.org/pg2 https://sivers.org/pg2
- ruslan_talpa 7y ago
  That is all true but postgrest goes a step further then Dereks approach (where you need to basically write a lot of stored procedures) and gives the fronend access to safe subset of SQL, so that frontend is not strictly limited to the capabilites of the available procedures that live in the db (thus eliminating the need for most of them)
  - doh 7y ago
    Derek's approach is one that can be carried between many old version of PG. Today, you can achieve a lot more with generated columns, (materialized) views, partitioning and other fun features added in recent versions. In any way, we used this approach in our company dealing with billions of rows of data and this allowed us to scale way past our "weight class".
    - ruslan_talpa 7y ago
      Thanks for this. People are always skeptical of this approach, not because the tried it and failed (or even thought 5 minutes about it) but because they read some blogpost somewhere. Not to say though that this solves everything, there are cases where it does not work (as someone commented correctly and gave an example where they needed to use linear algebra over the data)
      - doh 7y ago
        That's what generated columns [0] are for ;) There is very little you can't do on the side of the database these days. And if all of it fails, then stored procedures and triggers can solve the rest. We [1] as a rule don't allow any data to be post-processed once they leave the database and we run database of 100TB+ with trillions of rows with absolutely no problems. [0] https://www.postgresql.org/docs/current/ddl-generated-columns.html https://www.postgresql.org/docs/current/ddl-generated-column... [1] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
takeda 7y ago
It's micro-service like, or some other crap? I had situation where was implementing something quite simple - an URL shortener. I didn't use PostgREST, but I decided to use ORM, because it was simple CRUD operation. It had an option to either use generated url or allow user to specify a custom one. And it worked as expected. But then once completed I decided to add extra functionality, for example extra statistics, like what IPs were accessing it and how much. Adding expiration times etc. I realized that ORM encouraged me to implement all of my logic in the application even when I actually would put less load on the database and made things simpler if I would let the database do many things for me and use types and functionality provided to me. I am not taking here about using stored procedures, I could do all operations as 1 at most 2 SQL statements. While ORM had to send multiple. In the end I dropped SQLAlchemy (this was python code) and just use psycopg2 directly didn't even bother with wrappers, just used built-in pools. It was also easier for me to make my code use two endpoints for reading and writing, so I can scale my code better. I realized that ORM did not save me much code at all, it was the same amount of code with or without it, and without ORM I had greater control of what I wanted to do. I previously believed that ORM was standing in a way when your application gets bigger, but my belief was ORM was good for small projects. Here I realized that it doesn't bring much benefits even for simple projects. I think REST interface like this is doubling down on what ORM tries to do. Maybe it could be beneficial in places that don't have libraries to communicate with a database and only can make http requests?
- ruslan_talpa 7y ago
  It's not doubling on the ORM part, it's not a orm replacement, it's a replacement for the entire api layer (that uses orm internally). I agree with the other point, this is meant to be used when your client can not ahve direct access to the db (browsers, 3rd parties). If you can connect to the db and use SQL, do that.
- FreelanceX 7y ago
  You should try SqlAlchemy Core. It does not have the drawbacks of an ORM but still saves you the trouble of writing sql queries as raw strings.

doh 7y ago

Derek recently wrote about it [0]. The fact that you can have the thinnest client between your front-end and backend makes things incredibly flexible. If you learn PG properly you can do 100% of data preparation on the database side and just expose it through an API. If you decide to change something, you change a view and it's now whatever you just did. No code changes, no redeployments. [0] https://sivers.org/pg2 https://sivers.org/pg2

ruslan_talpa 7y ago
That is all true but postgrest goes a step further then Dereks approach (where you need to basically write a lot of stored procedures) and gives the fronend access to safe subset of SQL, so that frontend is not strictly limited to the capabilites of the available procedures that live in the db (thus eliminating the need for most of them)
- doh 7y ago
  Derek's approach is one that can be carried between many old version of PG. Today, you can achieve a lot more with generated columns, (materialized) views, partitioning and other fun features added in recent versions. In any way, we used this approach in our company dealing with billions of rows of data and this allowed us to scale way past our "weight class".
  - ruslan_talpa 7y ago
    Thanks for this. People are always skeptical of this approach, not because the tried it and failed (or even thought 5 minutes about it) but because they read some blogpost somewhere. Not to say though that this solves everything, there are cases where it does not work (as someone commented correctly and gave an example where they needed to use linear algebra over the data)
    - doh 7y ago
      That's what generated columns [0] are for ;) There is very little you can't do on the side of the database these days. And if all of it fails, then stored procedures and triggers can solve the rest. We [1] as a rule don't allow any data to be post-processed once they leave the database and we run database of 100TB+ with trillions of rows with absolutely no problems. [0] https://www.postgresql.org/docs/current/ddl-generated-columns.html https://www.postgresql.org/docs/current/ddl-generated-column... [1] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex

takeda 7y ago

It's micro-service like, or some other crap? I had situation where was implementing something quite simple - an URL shortener. I didn't use PostgREST, but I decided to use ORM, because it was simple CRUD operation. It had an option to either use generated url or allow user to specify a custom one. And it worked as expected. But then once completed I decided to add extra functionality, for example extra statistics, like what IPs were accessing it and how much. Adding expiration times etc. I realized that ORM encouraged me to implement all of my logic in the application even when I actually would put less load on the database and made things simpler if I would let the database do many things for me and use types and functionality provided to me. I am not taking here about using stored procedures, I could do all operations as 1 at most 2 SQL statements. While ORM had to send multiple. In the end I dropped SQLAlchemy (this was python code) and just use psycopg2 directly didn't even bother with wrappers, just used built-in pools. It was also easier for me to make my code use two endpoints for reading and writing, so I can scale my code better. I realized that ORM did not save me much code at all, it was the same amount of code with or without it, and without ORM I had greater control of what I wanted to do. I previously believed that ORM was standing in a way when your application gets bigger, but my belief was ORM was good for small projects. Here I realized that it doesn't bring much benefits even for simple projects. I think REST interface like this is doubling down on what ORM tries to do. Maybe it could be beneficial in places that don't have libraries to communicate with a database and only can make http requests?

ruslan_talpa 7y ago
It's not doubling on the ORM part, it's not a orm replacement, it's a replacement for the entire api layer (that uses orm internally). I agree with the other point, this is meant to be used when your client can not ahve direct access to the db (browsers, 3rd parties). If you can connect to the db and use SQL, do that.
FreelanceX 7y ago
You should try SqlAlchemy Core. It does not have the drawbacks of an ORM but still saves you the trouble of writing sql queries as raw strings.