8 ms·

Just wanted to give a huge shout-out to PostgREST: https://postgrest.com https://postgrest.com https://github.com/begriffs/postgrest https://github.com/begrif

Just wanted to give a huge shout-out to PostgREST: https://postgrest.com https://postgrest.com https://github.com/begriffs/postgrest https://github.com/begriffs/postgrest You can get many of the benefits of GraphQL using postgrest's resource embedding: https://postgrest.com/en/v4.1/api.html#resource-embedding https://postgrest.com/en/v4.1/api.html#resource-embedding We're using it in production. PS: To be clear, you can't expose it directly to your users. We wrap it in a proxy service that provides authentication and authorization, and parses and transforms the users' URL queries destined for PostgREST. We also apply some transformations to the data coming back from PostgREST, such as encoding our internal UUIDs. It may sound complicated, but it's actually only about 200 lines of Erlang.

ruslan_talpa 9y ago
where are you using it? (and you can even get GraphQL on top of it :P https://subzero.cloud https://subzero.cloud)
ice109 9y ago
i don't understand how one would use this? obviously you're not just exposing this api to users right? are you just using it as a substitute for an orm or data access layer in your own app?
- ruslan_talpa 9y ago
  I bet they ar doing exactly that, exposing the api supported by PostgREST :) https://github.com/subzerocloud/postgrest-starter-kit/wiki/PostgreSQL-Concepts#views https://github.com/subzerocloud/postgrest-starter-kit/wiki/P... Edit: seems they are not, ... oh well :)
  - ice109 9y ago
    business logic in sql seems like a bad idea (especially since you can't connect it to other services? or can you make ajax requests in sql?).
    - ruslan_talpa 9y ago
      you can if you really want to, but you are not supposed to. this tool gives you a way to talk to your database, not other systems. to talk to 3rd party systems you use other components in the stack (openresty/rabbitmq) https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit
    - vosper 9y ago
      Business logic in SQL is a bad idea because it's much easier (for most people, anyway) to write and test it in a normal programming language. Especially if you want to have nice things like logging, breakpoints / inspection, composition / reuse, nice exception handling, etc..
      - ruslan_talpa 9y ago
        developer tooling is indeed not on the same level as other languages but don't say it does not exist https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html logic that directly relates to the data being stored is much easier to express in the database (constraints), resulting in a big reduction of code which means small simple functions that almost don't need debugging. also what people call business logic often means fetching data from the db, processing it in some way and returning it. This is what views are for, they are definitions, so there's nothing to test there, except performance maybe.
        vosper 9y ago
        > logic that directly relates to the data being stored is much easier to express in the database (constraints), resulting in a big reduction of code which means small simple functions that almost don't need debugging. I think this is true for SQL experts (provided you're on the right database platform, of course - Postgres probably, MySQL and SQLite I doubt it) but it's not true for people with expertise in other language(s), which is the majority of people using the database. I can write arbitrarily complex business logic in Python and Javascript, which I am very familiar with. I would struggle to do that with SQL, and I know that all of my colleagues would struggle to understand what I had done. And I'm not unfamiliar with RDBMS - I've been working with them for years. Maybe that's a reflection on me, but I'd say it's been true of 95% of people I've ever worked with.
- Twisell 9y ago
  As I see it PostgREST might finally be a way for backend database developer to easily provide and manage external web API that can then be used by all magical multi-headed unicorn powered and sparkling star enabled web framework of next month. Errr... sorry... I ranted... Another point of view would be that it could help frontend developer hook to a well defined API and never again have to dive into the seven hell madness of the underlying database ruled by a crazy monk that call himself dbadmin and draw mysterious SQL optimisation spell on the walls while yelling ACCCCIIIIDDDD! in the darkest hour of the night. Humm... Damn... I guess I will be downvoted... Sorry --'
zitterbewegung 9y ago
I looked at PosgREST for a backend for my Startup. Documentation was confusing, I couldn't see a coherent way to use it securely and the examples of other sites on Github were confusing. GraphQL at this time seems higher quality and also would be a good feature for PostgREST.
- ruslan_talpa 9y ago
  working on a coherent way here https://github.com/subzerocloud/postgrest-starter-kit/wiki https://github.com/subzerocloud/postgrest-starter-kit/wiki writing docs is hard but what do you expect :), GraphQL is FB funded, PostgREST is 3-4 guys, no backing.
- begriffs 9y ago
  Fair point about the docs, I've been putting reference information in them over time, but it could use more guided tutorials. Last week I began working to improve that, by writing a series of tutorials. Here's the first: https://postgrest.com/en/v4.1/tutorials/tut0.html https://postgrest.com/en/v4.1/tutorials/tut0.html More to come, including tutorials about row-level security and authentication.
ryeguy 9y ago
IMO modeling your api directly off of your database tables is often an anti-pattern. One of the biggest mistakes I see people make is assuming their api's data model has to match their database's. It's common to introduce resources on the api side that may not have their own table in the database. For example, you could have a table that has a "type" flag (to model some kind of inheritance), but on the api you expose these as separate resources. Or you could have a larger resource on the api, but it's actually backed by several tables. Graphql is also more generic and allows you to fetch from multiple data sources. These could be databases, apis, or wherever. You write code that fulfills each column and graphql assembles a single object. I could see postgrest being ok for prototyping or simple apis, but it seems like one of those things that you'd grow out of eventually.
- ruslan_talpa 9y ago
  Have you heard of views and FDW?
  - ryeguy 9y ago
    Views are only a solution for reads, not writes. FDWs are not an elegant solution to anything related to this. Think microservices that already have an api defined.
    - ruslan_talpa 9y ago
      Your original comment said it's bad to couple your api with tables so I am agreeing with you, use views to decouple. for writes, you can use stored procedures for corner cases although most of the apps work with one model at a time, so auto-updatable views work most of the time. So if we take into account that most apps talk to one database, i have most of it covered with simple views since usually you have a 80/20 read/write split? I'll take that. FDW are very elegant for reading data from other systems. If you can read from twitter https://github.com/umitanuki/twitter_fdw https://github.com/umitanuki/twitter_fdw you can read from anything although I don't understand why you brought up microservices, this is one one of the microservices (that is talking to the db), it's not the thing trying to unite all microservices. You can have GraphQL on top of PostgREST just fine (https://subzero.cloud https://subzero.cloud).
ruslan_talpa 9y ago
Why can't you expose it to your users and why is PostgreSQL bases authorization not good enough?
ice109 9y ago
when you say proxy do you mean literally nginx/something or some go/java/etc. http server?

ruslan_talpa 9y ago

where are you using it? (and you can even get GraphQL on top of it :P https://subzero.cloud https://subzero.cloud)

ice109 9y ago

i don't understand how one would use this? obviously you're not just exposing this api to users right? are you just using it as a substitute for an orm or data access layer in your own app?

ruslan_talpa 9y ago
I bet they ar doing exactly that, exposing the api supported by PostgREST :) https://github.com/subzerocloud/postgrest-starter-kit/wiki/PostgreSQL-Concepts#views https://github.com/subzerocloud/postgrest-starter-kit/wiki/P... Edit: seems they are not, ... oh well :)
- ice109 9y ago
  business logic in sql seems like a bad idea (especially since you can't connect it to other services? or can you make ajax requests in sql?).
  - ruslan_talpa 9y ago
    you can if you really want to, but you are not supposed to. this tool gives you a way to talk to your database, not other systems. to talk to 3rd party systems you use other components in the stack (openresty/rabbitmq) https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit
  - vosper 9y ago
    Business logic in SQL is a bad idea because it's much easier (for most people, anyway) to write and test it in a normal programming language. Especially if you want to have nice things like logging, breakpoints / inspection, composition / reuse, nice exception handling, etc..
    - ruslan_talpa 9y ago
      developer tooling is indeed not on the same level as other languages but don't say it does not exist https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html logic that directly relates to the data being stored is much easier to express in the database (constraints), resulting in a big reduction of code which means small simple functions that almost don't need debugging. also what people call business logic often means fetching data from the db, processing it in some way and returning it. This is what views are for, they are definitions, so there's nothing to test there, except performance maybe.
      - vosper 9y ago
        > logic that directly relates to the data being stored is much easier to express in the database (constraints), resulting in a big reduction of code which means small simple functions that almost don't need debugging. I think this is true for SQL experts (provided you're on the right database platform, of course - Postgres probably, MySQL and SQLite I doubt it) but it's not true for people with expertise in other language(s), which is the majority of people using the database. I can write arbitrarily complex business logic in Python and Javascript, which I am very familiar with. I would struggle to do that with SQL, and I know that all of my colleagues would struggle to understand what I had done. And I'm not unfamiliar with RDBMS - I've been working with them for years. Maybe that's a reflection on me, but I'd say it's been true of 95% of people I've ever worked with.
Twisell 9y ago
As I see it PostgREST might finally be a way for backend database developer to easily provide and manage external web API that can then be used by all magical multi-headed unicorn powered and sparkling star enabled web framework of next month. Errr... sorry... I ranted... Another point of view would be that it could help frontend developer hook to a well defined API and never again have to dive into the seven hell madness of the underlying database ruled by a crazy monk that call himself dbadmin and draw mysterious SQL optimisation spell on the walls while yelling ACCCCIIIIDDDD! in the darkest hour of the night. Humm... Damn... I guess I will be downvoted... Sorry --'

zitterbewegung 9y ago

I looked at PosgREST for a backend for my Startup. Documentation was confusing, I couldn't see a coherent way to use it securely and the examples of other sites on Github were confusing. GraphQL at this time seems higher quality and also would be a good feature for PostgREST.

ruslan_talpa 9y ago
working on a coherent way here https://github.com/subzerocloud/postgrest-starter-kit/wiki https://github.com/subzerocloud/postgrest-starter-kit/wiki writing docs is hard but what do you expect :), GraphQL is FB funded, PostgREST is 3-4 guys, no backing.
begriffs 9y ago
Fair point about the docs, I've been putting reference information in them over time, but it could use more guided tutorials. Last week I began working to improve that, by writing a series of tutorials. Here's the first: https://postgrest.com/en/v4.1/tutorials/tut0.html https://postgrest.com/en/v4.1/tutorials/tut0.html More to come, including tutorials about row-level security and authentication.

ryeguy 9y ago

IMO modeling your api directly off of your database tables is often an anti-pattern. One of the biggest mistakes I see people make is assuming their api's data model has to match their database's. It's common to introduce resources on the api side that may not have their own table in the database. For example, you could have a table that has a "type" flag (to model some kind of inheritance), but on the api you expose these as separate resources. Or you could have a larger resource on the api, but it's actually backed by several tables. Graphql is also more generic and allows you to fetch from multiple data sources. These could be databases, apis, or wherever. You write code that fulfills each column and graphql assembles a single object. I could see postgrest being ok for prototyping or simple apis, but it seems like one of those things that you'd grow out of eventually.

ruslan_talpa 9y ago
Have you heard of views and FDW?
- ryeguy 9y ago
  Views are only a solution for reads, not writes. FDWs are not an elegant solution to anything related to this. Think microservices that already have an api defined.
  - ruslan_talpa 9y ago
    Your original comment said it's bad to couple your api with tables so I am agreeing with you, use views to decouple. for writes, you can use stored procedures for corner cases although most of the apps work with one model at a time, so auto-updatable views work most of the time. So if we take into account that most apps talk to one database, i have most of it covered with simple views since usually you have a 80/20 read/write split? I'll take that. FDW are very elegant for reading data from other systems. If you can read from twitter https://github.com/umitanuki/twitter_fdw https://github.com/umitanuki/twitter_fdw you can read from anything although I don't understand why you brought up microservices, this is one one of the microservices (that is talking to the db), it's not the thing trying to unite all microservices. You can have GraphQL on top of PostgREST just fine (https://subzero.cloud https://subzero.cloud).

ruslan_talpa 9y ago

Why can't you expose it to your users and why is PostgreSQL bases authorization not good enough?

ice109 9y ago

when you say proxy do you mean literally nginx/something or some go/java/etc. http server?