11 ms·

Related graphql implementations with similar concepts: - https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ - https://hasura.io/ ht

by rgbrgb 7y ago

Related graphql implementations with similar concepts: - https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ - https://hasura.io/ https://hasura.io/ Love the idea of having APIs flow out of a single set of schema definitions. The Rails style of speccing a model, migrations, and controller/serializer or graphql types feels overly verbose and repetitive. To me the biggest thing these groups could do to speed adoption is flesh out the feature development / test story. For instance, the postgraphile examples have development scripts that constantly clear out the DB and no tests. Compared to Rails, it's hard to imagine how you'd iterate on a product. Are there other reasons this hasn't seen more widespread adoption? Is there some inherent architectural flaw or just not enough incremental benefit?

ruslan_talpa 7y ago
This is a solved problem https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit It’just the tools you linked didnt prioritize the development workflow from the point of view od a backend developer (code in files, git, tests, migrations) but from the frontend developer perspective (ui to create tables)
- ruslan_talpa 7y ago
  more on this topic here https://docs.subzero.cloud/iterative-development-workflow/ https://docs.subzero.cloud/iterative-development-workflow/
- felixyz 7y ago
  The SubZero starter kit is great, and made this whole architecture seem practical to me. Thanks for your hard work here! (I'm currently using PostGraphile, but the starter kit is still an important tool for me.)
  - BenjieGillam 7y ago
    Have you tried the PostGraphile Starter? It’s not officially released yet but you can find out more in the Discord chat #announcements channel. https://discord.gg/graphile https://discord.gg/graphile
- blondin 7y ago
  do you happen to know if there is a video presentation about this project? after skimming the docs and checking the comments here, i am still unsure what this project has to offer that you don't already have or that you can easily piece together. thanks
  - ruslan_talpa 7y ago
    How do you easely piece together a (poerfull) rest api just by having the definitions of the tables in the database? Very very old videos but you can get the core idea https://begriffs.com/posts/2014-12-30-intro-to-postgrest.html https://begriffs.com/posts/2014-12-30-intro-to-postgrest.htm... https://begriffs.com/posts/2016-03-20-postgrest-tour.html https://begriffs.com/posts/2016-03-20-postgrest-tour.html
    - blondin 7y ago
      thanks
alexrtan 7y ago
We've been using it for about a year in production across four projects and it's been a major boon for productivity because it allows our frontend developers to do simple backend features on their own. I think there are a lot of use cases where it would be a great fit for many projects and there are I'm sure a lot of companies that could save a lot of time by using it as part of their toolkit. Also it doesn't have to be all or nothing. For example, there might be an API endpoint needed where there are multiple mutations needed inside a transaction but we just route between that custom backend and postgrest using nginx. Other things we use: - dbmate for migrations: https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate - postgrest-client, a library I developed for constructing type-safe queries to Postgrest in Elm: https://github.com/alex-tan/postgrest-client https://github.com/alex-tan/postgrest-client
- bayesian_horse 7y ago
  You are addressing one of the issues on my mind: Migration. In many situations, some kind of formal migration management is obligatory and my guess is that with PostgREST you will often want to test your migrations on a current database snapshot before deploying it in production...
babayega2 7y ago
Any equivalent in MySQL? I have a legacy project in MySQL 5.1 I need to keep up. I have been using Peewee to generate the structure of the DB but would like to do some direct API call like Postgrest
- ruslan_talpa 7y ago
  https://github.com/mevdschee/php-crud-api https://github.com/mevdschee/php-crud-api
- o1lab 7y ago
  xmysql - Creates instant REST APIs over a mysql database. https://github.com/o1lab/xmysql/ https://github.com/o1lab/xmysql/ Give it a try. (Im the author)
  - avodonosov 7y ago
    o1lab, FYI, I considered xmysql recently, for my needs it's not currently enough because I'd like to have access rights management. IMHO utilizing the DB users for that is ideal - authenticate the mysql Db users in your rest service session. (PostgREST seems to do this)
    - o1lab 7y ago
      ACL and other features are work in progress. Please watch repo for updates.
    - naushit 7y ago
      Deploy Kong in front?
jayd16 7y ago
I actually think the one schema to rule them all approach is a bad one. Different parts of the stack have different use cases and require different data representations. I think it's a fundamental flaw many ORMs fall into. As for postgrest and graphQL, queries and data transformations are proof that your DB schema shouldn't be forced the the stack and onto the clients. I would argue that graphQL strived for the exact opposite of single schema and tends to 'clients should choose their own API' approach.
- ruslan_talpa 7y ago
  You haven't really looked into postgrest, it does not force the database schema on to the client. You expose a schema with views that are tailored for the frontend and can be totally different from the underlying tables that hold the actual data
  - jayd16 7y ago
    I admit I haven't but I totally agree with you. I'm attempting to say that postgrest and graphQL are _not_ examples of apis from 'a single schema definition' in that they are not the previous incarnation of that desire where an ORM would tightly couple your imperative types to your normalized schema. These new technologies have a wholistic/referential public API but they allow client, server and DB to all have different schemas and I just want to highlight the difference.
  - dathinab 7y ago
    Yes, but the constrains sql has wrt. how you represent certain datatypes can lead to problems. Sure in postgres you can fix that by a variety of compound types, and fully new types implemented as pg extension, which can scare people of. Still there are a tone of applications for which just the basic types are good enough and the server is just a thin wrapper. In the end the main requirement for efficiency using it is you skill with not basic SQL/plpgSQL (stored procedure, trigger functions, views and knowledge about the effects of the used isolation level). All well documented in postgres.
    - 7y ago
markhalonen 7y ago
I've been using Postgraphile along with Apollo Client with code-generated types for queries. When I change the db schema, I get compile errors in my client. Feels like I'm cheating
- antigirl 7y ago
  Thats just bad schema definitions then, you should have defaults and upon making mutations with only a subset of parameters it shouldnt fail, queries shouldnt fail anyway. If you are actually changing variable names then your client would fail regardless of what you're using
- latchkey 7y ago
  I'm doing something similar, but using type-graphql + apollo server + graphql-code-generator + mikro-orm (v3). I just define my model classes. Graphql resolvers look like regular rest endpoints. Everything else is generated from there and I even get compile errors for my apollo client queries too. I really love the typesafety of it all!
jablan 7y ago
> The Rails style of speccing a model, migrations, and controller/serializer or graphql types feels overly verbose and repetitive. For the record, ActiveRecord models do not specify, for example, table schemas - they are loaded from the database in the runtime, and migrations are not really necessary - they provide schema versioning with a rollback possibility, something that AFAIK the databases don't usually offer. Rails (and Ruby) is actually so non-verbose and non-repetitive, that a lot of people is criticising it for the very reason, usually complaining about it being too "magical".
- felixyz 7y ago
  > they provide schema versioning with a rollback possibility, something that AFAIK the databases don't usually offer But stand-alone migration tools like Sqitch do.[0] [0] https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch
agumonkey 7y ago
I couldn't stop thinking REST could be derived from sql.. Similarly after a week of vuejs I couldn't stop thinking redux/vuex should be an embedded sqlite ...
- choxi 7y ago
  You might find CouchDB and PouchDB interesting then. CouchDB works over REST and there's a client-side version called PouchDB that you can selectively sync between.
konschubert 7y ago
There are trade offs when you couple your front end to your database structure. Additionally, if you are using a lossy intermediate query representation instead of plain SQL, you will end up losing a lot of he database performance and find yourself doing complex joins on your front end.
- ruslan_talpa 7y ago
  when it comes to these tools, all your statements are wrong, give them 5 minutes and check our their capabilities (there is no coupling and joins in frontend) :)
- felixyz 7y ago
  This is a common misunderstanding and I think a big reason why people are reluctant to try this type of architecture. The idea here is not to couple client code to the database structure. You should expose schemas that contain views on top of the basic schema, which provides good encapsulation and prvents both of the issues you bring up. (This was common 25 years ago, when server-side code often amounted to a db schema containing stored procedures.)
  - konschubert 7y ago
    That’s a step in the right direction but I’m not sure if it really solves the problem. But as I said, there are trade offs. I didn’t say it’s always a bad idea.
    - felixyz 7y ago
      I agree there are always trade-offs, but at least as far as "losing a lot of he database performance" and "doing complex joins on your front end", I don't think there is anything in the PostgREST approach that would push in that direction. For more background on the performance part (Oracle-specific but most of it carries over) check out http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php
brlewis 7y ago
> Are there other reasons this hasn't seen more widespread adoption? Is there some inherent architectural flaw or just not enough incremental benefit? I'm a fan of the concept and would love to have a middle layer as thin as postgrest, but authentication/authorization has been my roadblock. I still use node as an app server because of passport. I had not heard of the postgrest starter kit before today and will check out https://docs.subzero.cloud/athentication-authorization-flow/ https://docs.subzero.cloud/athentication-authorization-flow/ If they correct the typo before I get to reading it, it will be https://docs.subzero.cloud/authentication-authorization-flow/ https://docs.subzero.cloud/authentication-authorization-flow...
- ruslan_talpa 7y ago
  you could use Auth0 [1] to authenticate your clients using all the identity providers (FB/GOOGLE...etc) and then just have the api clients use that JWT token when talking to postgrest If however you do only want to authenticate the users yourself, the login function can be very simple [2] 1. http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0 http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0 2. https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/api/login.sql https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
- bowlingx 7y ago
  I am using postgraphile in library mode with keycloak and passport.js (an open source project https://www.keycloak.org/ https://www.keycloak.org/) instead of auth0 and it works great.
- bayesian_horse 7y ago
  I would expect authorization and validation to be a real pain with PostgREST (I don't know enough about it to tell definitively, though). Even row-level access control, while not widespread any way, is often not enough. Complex validation in SQL can be done, of course, but SQL isn't exactly meant for these things, IMHO.
  - ruslan_talpa 7y ago
myrryr 7y ago
We used Hasura for a while, but it was too easy for clients to write queries which would take it forever to complete. Postgrest was better, but we had trouble getting it to work with JWT, and have a reasonable permissions model. Postgrest was DAMN fast though, it was really nice to work with. In the end we had to write our own.
- maxcan 7y ago
  FYI query whitelisting is on its way in Hasura which pretty much solves the first issue
  - praveenweb 7y ago
    Query whitelisting has been available since beta 1. It is called Allow Lists :) https://docs.hasura.io/1.0/graphql/manual/deployment/allow-list.html https://docs.hasura.io/1.0/graphql/manual/deployment/allow-l... We recommend every production app to setup Allow Lists to prevent arbitrary querying.
stubish 7y ago
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
fogetti 7y ago
Inherent architectural flaws: - Complete lack of separation of concerns: you can see this by the terrible idea of simply slapping a transformation procedure to a non-specified part of the codebase. Every time you do this you end up having to maintain a codebase which has no architecturally defined place anywhere. See: https://postgrest.org/en/v6.0/how-tos/casting-type-to-custom-json.html https://postgrest.org/en/v6.0/how-tos/casting-type-to-custom... - There is no ORM that you can control. It doesn't mean that there is no transformation needed though. It just simply means that you lose control over it. All the work which were once under your control now is handled by the database layer. Of course you can go to that layer and change the logic of the framework yourself (by sending strangely formatted HTTP requests according to the doc), but then you gain nothing by using this framework. Of course this problem stems from the same fact that there are no separation of concerns. So basically the framework is simply ignoring the fact that there is an inherent object relational impedance mismatch. Of course the authors are aware that there is a mismatch but they simply sweep it under the rug by saying that: "The PostgREST URL grammar limits the kinds of queries clients can perform. It prevents arbitrary, potentially poorly constructed and slow client queries. It’s good for quality of service, but means database administrators must create custom views and stored procedures to provide richer endpoints." This will come back and bite you in the ass when you try to work with anything slightly complicated like inheritance for example. So the authors basically dump every single person in the world under the category of poor programmers, and they offer their excellence in place of your lack of skill or talent. Go, figure! I am always very skeptical when I read something like this. See: https://postgrest.org/en/v6.0/api.html#resource-embedding https://postgrest.org/en/v6.0/api.html#resource-embedding https://postgrest.org/en/v6.0/api.html#custom-queries https://postgrest.org/en/v6.0/api.html#custom-queries - No testability: pl/pgSQL is a really poor language to write unit/integration tests for, mostly because it's ancient and it's procedural and there are no native frameworks to do so. Of course you can use pl/Python and others, but then again, why would you when you can write the same thing in Python without embedding the code in Postgres, and the Python ORM would take care of having the same performance on the database level for you. - Lack of tools for migrations, etc. The list goes on.
- steve-chavez 7y ago
  Coming from a traditional layered approach I can see why this "de-layered" approach can seem wrong. After all I had to ditch the knowledge I had about Django ORM, Sequelize, HIbernate(I wish I could forget entirely about Spring/JPA books I've read but I still remember some parts). But as mentioned in other comments if you give it 5 minutes—and embrace the db—perhaps you'd see the advantages of this approach over more traditional ones. > There is no ORM that you can control You don't need an ORM to control your database. If you treat the database as the source of truth(this would be the architecturally defined place) you can do data transformations with plain and short SQL. The great thing about this is that you can leverage PostgreSQL rich functionality and extension ecosystem and you don't have to worry if your ORM supports a pg type or if you need a plugin for that. > by sending strangely formatted HTTP requests according to the doc The "strangely formattted request" wouldn't be so strange if you note that PostgreSQL cast operator is "::"(e.g. `select col::json from table`). > Python ORM would take care of having the same performance on the database level for you. That's just false. ORMs have to point to the lowest common denominator of databases. They don't use the full potential of pg(things like LATERAL joins, json_agg, etc). PostgREST queries are specifically tailored for pg and they're being constantly improved. > No testability I've shared this before, but by using pgTAP[1] you can get native SQL tests. HNers have shared migration tools in other comments. [1]: https://pgtap.org/ https://pgtap.org/
joshuakelly 7y ago
The Graphile folks are working on Graphile Migrate to improve the migrations story: https://github.com/graphile/migrate https://github.com/graphile/migrate Separately, I've got a fairly advanced setup of schema/seed lifecycle flow in a Graphile-powered application that will likely be open-sourced at some point. The simplicity of the architecture makes these things relatively easy to spin up from scratch.
- BenjieGillam 7y ago
  Thanks for mentioning Graphile Migrate. Migrations are often the biggest issue people have with something like PostgREST and PostGraphile; I’ve used a lot of the various migration tools and Graphile Migrate is the seed of my vision for a much more compelling migration story for everyone in the PostgreSQL community (especially people who don’t mind writing SQL but find the current up/down pattern too verbose/clunky as I do). There’s lots more to come, so make sure you follow the project if you’re interested.
  - anhnhoktvn 7y ago
    How does it compare with marv [0]? [0] https://www.npmjs.com/package/marv https://www.npmjs.com/package/marv
AdieuToLogic 7y ago
> Are there other reasons this hasn't seen more widespread adoption? This approach had widespread adoption in the 1990's, most notably via PowerBuilder[0]. > Is there some inherent architectural flaw or just not enough incremental benefit? Yes, there is an inherent architectural flaw with two-tiered systems in that they force a system to choose to place complexity either in the GUI or in the RDBMS. This often leads to "Jenga Programming" maintenance techniques which cause the code base to take a life of its own. It turns out that this does not scale. 0 - https://en.wikipedia.org/wiki/PowerBuilder#History https://en.wikipedia.org/wiki/PowerBuilder#History
- ruslan_talpa 7y ago
  Why do you think this is a two tier system? Even here [1] i count 4 places to put logic into besides frontend 1. https://github.com/subzerocloud/postgrest-starter-kit/blob/master/README.md https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
  - AdieuToLogic 7y ago
    > Why do you think this is a two tier system? The first sentence of site reads thusly: > PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. If there is a database on one end and a client on the other, with no intervening system between them, that is the definition of a two-tiered system.
    - ruslan_talpa 7y ago
      Sure you can use it as two tier if that does the job but it does not mean you are forced to, you can very well have logic in the proxy layer and then have logic below the db layer (workers reacting to database changes). If i apply the same logic (two tier bad) that i can say every other way of building an api is two tier since you have the frontend and then the rest of the code in php/node... the db would not really count as a later since in those scenatios ppl use it as a dumb store
bayesian_horse 7y ago
I haven't used Rails a lot, but in Django you get a lot more from the ORM models than just database queries. The Django ORM is used pervasively throughout django and third party libraries. You can compose and reuse querysets in Templates, Form validation, authorization and so on. You can create a CRUD admin interface and a REST API with almost no extra configuration. It also has a very mature migration system, meaning you can have your migrations in code, and apply them in production or in multiple environments. One problem I expect with PostgREST (haven't used it yet) is that validation and authorization may suffer. At the very least this will have to be configured either as SQL data or as SQL stored procedures, with SQL not being the strong suit of most frontend people. They probably have safeguards against SQL injections through their API. Logic validation will be harder, and Postgres only supports rudimentary permissions, so all of this has to be implemented in stored procedures, also. My main concern with PostgREST is that the database is stateful and persistent, and that you have to put more code in there. Which means every deployment is going to be a migration, no matter what. You better don't forget any of the state changes you made in your development database. Version management will certainly be harder. Please don't get me wrong. I am sure PostgREST has perfectly valid usecases, and I like Postgres a lot. I am just dropping a few thoughts on why I would prefer a traditional application between database and frontend.
steve-chavez 7y ago
> Are there other reasons this hasn't seen more widespread adoption? I think it's because PostgreSQL got major features for web development — like JSON and RLS — not too long ago and most developers are just catching up. JSON was added back in 2013[1] and RLS back in 2016[2]. [1]: https://www.postgresql.org/docs/release/9.3.0/ https://www.postgresql.org/docs/release/9.3.0/ [2]: https://www.postgresql.org/docs/release/9.5.0/ https://www.postgresql.org/docs/release/9.5.0/
- Benjamin_Dobell 7y ago
  JSON support wasn't all that great until JSONB columns were added in December 2014[1]. I think that was when devs started taking it seriously. [1] https://www.postgresql.org/docs/9.4/release-9-4.html https://www.postgresql.org/docs/9.4/release-9-4.html

by rgbrgb 7y ago

ruslan_talpa 7y ago

This is a solved problem https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit It’just the tools you linked didnt prioritize the development workflow from the point of view od a backend developer (code in files, git, tests, migrations) but from the frontend developer perspective (ui to create tables)

ruslan_talpa 7y ago
more on this topic here https://docs.subzero.cloud/iterative-development-workflow/ https://docs.subzero.cloud/iterative-development-workflow/
felixyz 7y ago
The SubZero starter kit is great, and made this whole architecture seem practical to me. Thanks for your hard work here! (I'm currently using PostGraphile, but the starter kit is still an important tool for me.)
- BenjieGillam 7y ago
  Have you tried the PostGraphile Starter? It’s not officially released yet but you can find out more in the Discord chat #announcements channel. https://discord.gg/graphile https://discord.gg/graphile
blondin 7y ago
do you happen to know if there is a video presentation about this project? after skimming the docs and checking the comments here, i am still unsure what this project has to offer that you don't already have or that you can easily piece together. thanks
- ruslan_talpa 7y ago
  How do you easely piece together a (poerfull) rest api just by having the definitions of the tables in the database? Very very old videos but you can get the core idea https://begriffs.com/posts/2014-12-30-intro-to-postgrest.html https://begriffs.com/posts/2014-12-30-intro-to-postgrest.htm... https://begriffs.com/posts/2016-03-20-postgrest-tour.html https://begriffs.com/posts/2016-03-20-postgrest-tour.html
  - blondin 7y ago
    thanks

alexrtan 7y ago

We've been using it for about a year in production across four projects and it's been a major boon for productivity because it allows our frontend developers to do simple backend features on their own. I think there are a lot of use cases where it would be a great fit for many projects and there are I'm sure a lot of companies that could save a lot of time by using it as part of their toolkit. Also it doesn't have to be all or nothing. For example, there might be an API endpoint needed where there are multiple mutations needed inside a transaction but we just route between that custom backend and postgrest using nginx. Other things we use: - dbmate for migrations: https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate - postgrest-client, a library I developed for constructing type-safe queries to Postgrest in Elm: https://github.com/alex-tan/postgrest-client https://github.com/alex-tan/postgrest-client

bayesian_horse 7y ago
You are addressing one of the issues on my mind: Migration. In many situations, some kind of formal migration management is obligatory and my guess is that with PostgREST you will often want to test your migrations on a current database snapshot before deploying it in production...

babayega2 7y ago

Any equivalent in MySQL? I have a legacy project in MySQL 5.1 I need to keep up. I have been using Peewee to generate the structure of the DB but would like to do some direct API call like Postgrest

ruslan_talpa 7y ago
https://github.com/mevdschee/php-crud-api https://github.com/mevdschee/php-crud-api
o1lab 7y ago
xmysql - Creates instant REST APIs over a mysql database. https://github.com/o1lab/xmysql/ https://github.com/o1lab/xmysql/ Give it a try. (Im the author)
- avodonosov 7y ago
  o1lab, FYI, I considered xmysql recently, for my needs it's not currently enough because I'd like to have access rights management. IMHO utilizing the DB users for that is ideal - authenticate the mysql Db users in your rest service session. (PostgREST seems to do this)
  - o1lab 7y ago
    ACL and other features are work in progress. Please watch repo for updates.
  - naushit 7y ago
    Deploy Kong in front?

jayd16 7y ago

I actually think the one schema to rule them all approach is a bad one. Different parts of the stack have different use cases and require different data representations. I think it's a fundamental flaw many ORMs fall into. As for postgrest and graphQL, queries and data transformations are proof that your DB schema shouldn't be forced the the stack and onto the clients. I would argue that graphQL strived for the exact opposite of single schema and tends to 'clients should choose their own API' approach.

ruslan_talpa 7y ago
You haven't really looked into postgrest, it does not force the database schema on to the client. You expose a schema with views that are tailored for the frontend and can be totally different from the underlying tables that hold the actual data
- jayd16 7y ago
  I admit I haven't but I totally agree with you. I'm attempting to say that postgrest and graphQL are _not_ examples of apis from 'a single schema definition' in that they are not the previous incarnation of that desire where an ORM would tightly couple your imperative types to your normalized schema. These new technologies have a wholistic/referential public API but they allow client, server and DB to all have different schemas and I just want to highlight the difference.
- dathinab 7y ago
  Yes, but the constrains sql has wrt. how you represent certain datatypes can lead to problems. Sure in postgres you can fix that by a variety of compound types, and fully new types implemented as pg extension, which can scare people of. Still there are a tone of applications for which just the basic types are good enough and the server is just a thin wrapper. In the end the main requirement for efficiency using it is you skill with not basic SQL/plpgSQL (stored procedure, trigger functions, views and knowledge about the effects of the used isolation level). All well documented in postgres.
  - 7y ago

markhalonen 7y ago

I've been using Postgraphile along with Apollo Client with code-generated types for queries. When I change the db schema, I get compile errors in my client. Feels like I'm cheating

antigirl 7y ago
Thats just bad schema definitions then, you should have defaults and upon making mutations with only a subset of parameters it shouldnt fail, queries shouldnt fail anyway. If you are actually changing variable names then your client would fail regardless of what you're using
latchkey 7y ago
I'm doing something similar, but using type-graphql + apollo server + graphql-code-generator + mikro-orm (v3). I just define my model classes. Graphql resolvers look like regular rest endpoints. Everything else is generated from there and I even get compile errors for my apollo client queries too. I really love the typesafety of it all!

jablan 7y ago

> The Rails style of speccing a model, migrations, and controller/serializer or graphql types feels overly verbose and repetitive. For the record, ActiveRecord models do not specify, for example, table schemas - they are loaded from the database in the runtime, and migrations are not really necessary - they provide schema versioning with a rollback possibility, something that AFAIK the databases don't usually offer. Rails (and Ruby) is actually so non-verbose and non-repetitive, that a lot of people is criticising it for the very reason, usually complaining about it being too "magical".

felixyz 7y ago
> they provide schema versioning with a rollback possibility, something that AFAIK the databases don't usually offer But stand-alone migration tools like Sqitch do.[0] [0] https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch

agumonkey 7y ago

I couldn't stop thinking REST could be derived from sql.. Similarly after a week of vuejs I couldn't stop thinking redux/vuex should be an embedded sqlite ...

choxi 7y ago
You might find CouchDB and PouchDB interesting then. CouchDB works over REST and there's a client-side version called PouchDB that you can selectively sync between.

konschubert 7y ago

There are trade offs when you couple your front end to your database structure. Additionally, if you are using a lossy intermediate query representation instead of plain SQL, you will end up losing a lot of he database performance and find yourself doing complex joins on your front end.

ruslan_talpa 7y ago
when it comes to these tools, all your statements are wrong, give them 5 minutes and check our their capabilities (there is no coupling and joins in frontend) :)
felixyz 7y ago
This is a common misunderstanding and I think a big reason why people are reluctant to try this type of architecture. The idea here is not to couple client code to the database structure. You should expose schemas that contain views on top of the basic schema, which provides good encapsulation and prvents both of the issues you bring up. (This was common 25 years ago, when server-side code often amounted to a db schema containing stored procedures.)
- konschubert 7y ago
  That’s a step in the right direction but I’m not sure if it really solves the problem. But as I said, there are trade offs. I didn’t say it’s always a bad idea.
  - felixyz 7y ago
    I agree there are always trade-offs, but at least as far as "losing a lot of he database performance" and "doing complex joins on your front end", I don't think there is anything in the PostgREST approach that would push in that direction. For more background on the performance part (Oracle-specific but most of it carries over) check out http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php

brlewis 7y ago

> Are there other reasons this hasn't seen more widespread adoption? Is there some inherent architectural flaw or just not enough incremental benefit? I'm a fan of the concept and would love to have a middle layer as thin as postgrest, but authentication/authorization has been my roadblock. I still use node as an app server because of passport. I had not heard of the postgrest starter kit before today and will check out https://docs.subzero.cloud/athentication-authorization-flow/ https://docs.subzero.cloud/athentication-authorization-flow/ If they correct the typo before I get to reading it, it will be https://docs.subzero.cloud/authentication-authorization-flow/ https://docs.subzero.cloud/authentication-authorization-flow...

ruslan_talpa 7y ago
you could use Auth0 [1] to authenticate your clients using all the identity providers (FB/GOOGLE...etc) and then just have the api clients use that JWT token when talking to postgrest If however you do only want to authenticate the users yourself, the login function can be very simple [2] 1. http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0 http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0 2. https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/api/login.sql https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
bowlingx 7y ago
I am using postgraphile in library mode with keycloak and passport.js (an open source project https://www.keycloak.org/ https://www.keycloak.org/) instead of auth0 and it works great.
bayesian_horse 7y ago
I would expect authorization and validation to be a real pain with PostgREST (I don't know enough about it to tell definitively, though). Even row-level access control, while not widespread any way, is often not enough. Complex validation in SQL can be done, of course, but SQL isn't exactly meant for these things, IMHO.
- ruslan_talpa 7y ago

myrryr 7y ago

We used Hasura for a while, but it was too easy for clients to write queries which would take it forever to complete. Postgrest was better, but we had trouble getting it to work with JWT, and have a reasonable permissions model. Postgrest was DAMN fast though, it was really nice to work with. In the end we had to write our own.

maxcan 7y ago
FYI query whitelisting is on its way in Hasura which pretty much solves the first issue
- praveenweb 7y ago
  Query whitelisting has been available since beta 1. It is called Allow Lists :) https://docs.hasura.io/1.0/graphql/manual/deployment/allow-list.html https://docs.hasura.io/1.0/graphql/manual/deployment/allow-l... We recommend every production app to setup Allow Lists to prevent arbitrary querying.

stubish 7y ago

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

fogetti 7y ago

Inherent architectural flaws: - Complete lack of separation of concerns: you can see this by the terrible idea of simply slapping a transformation procedure to a non-specified part of the codebase. Every time you do this you end up having to maintain a codebase which has no architecturally defined place anywhere. See: https://postgrest.org/en/v6.0/how-tos/casting-type-to-custom-json.html https://postgrest.org/en/v6.0/how-tos/casting-type-to-custom... - There is no ORM that you can control. It doesn't mean that there is no transformation needed though. It just simply means that you lose control over it. All the work which were once under your control now is handled by the database layer. Of course you can go to that layer and change the logic of the framework yourself (by sending strangely formatted HTTP requests according to the doc), but then you gain nothing by using this framework. Of course this problem stems from the same fact that there are no separation of concerns. So basically the framework is simply ignoring the fact that there is an inherent object relational impedance mismatch. Of course the authors are aware that there is a mismatch but they simply sweep it under the rug by saying that: "The PostgREST URL grammar limits the kinds of queries clients can perform. It prevents arbitrary, potentially poorly constructed and slow client queries. It’s good for quality of service, but means database administrators must create custom views and stored procedures to provide richer endpoints." This will come back and bite you in the ass when you try to work with anything slightly complicated like inheritance for example. So the authors basically dump every single person in the world under the category of poor programmers, and they offer their excellence in place of your lack of skill or talent. Go, figure! I am always very skeptical when I read something like this. See: https://postgrest.org/en/v6.0/api.html#resource-embedding https://postgrest.org/en/v6.0/api.html#resource-embedding https://postgrest.org/en/v6.0/api.html#custom-queries https://postgrest.org/en/v6.0/api.html#custom-queries - No testability: pl/pgSQL is a really poor language to write unit/integration tests for, mostly because it's ancient and it's procedural and there are no native frameworks to do so. Of course you can use pl/Python and others, but then again, why would you when you can write the same thing in Python without embedding the code in Postgres, and the Python ORM would take care of having the same performance on the database level for you. - Lack of tools for migrations, etc. The list goes on.

steve-chavez 7y ago
Coming from a traditional layered approach I can see why this "de-layered" approach can seem wrong. After all I had to ditch the knowledge I had about Django ORM, Sequelize, HIbernate(I wish I could forget entirely about Spring/JPA books I've read but I still remember some parts). But as mentioned in other comments if you give it 5 minutes—and embrace the db—perhaps you'd see the advantages of this approach over more traditional ones. > There is no ORM that you can control You don't need an ORM to control your database. If you treat the database as the source of truth(this would be the architecturally defined place) you can do data transformations with plain and short SQL. The great thing about this is that you can leverage PostgreSQL rich functionality and extension ecosystem and you don't have to worry if your ORM supports a pg type or if you need a plugin for that. > by sending strangely formatted HTTP requests according to the doc The "strangely formattted request" wouldn't be so strange if you note that PostgreSQL cast operator is "::"(e.g. `select col::json from table`). > Python ORM would take care of having the same performance on the database level for you. That's just false. ORMs have to point to the lowest common denominator of databases. They don't use the full potential of pg(things like LATERAL joins, json_agg, etc). PostgREST queries are specifically tailored for pg and they're being constantly improved. > No testability I've shared this before, but by using pgTAP[1] you can get native SQL tests. HNers have shared migration tools in other comments. [1]: https://pgtap.org/ https://pgtap.org/

joshuakelly 7y ago

The Graphile folks are working on Graphile Migrate to improve the migrations story: https://github.com/graphile/migrate https://github.com/graphile/migrate Separately, I've got a fairly advanced setup of schema/seed lifecycle flow in a Graphile-powered application that will likely be open-sourced at some point. The simplicity of the architecture makes these things relatively easy to spin up from scratch.

BenjieGillam 7y ago
Thanks for mentioning Graphile Migrate. Migrations are often the biggest issue people have with something like PostgREST and PostGraphile; I’ve used a lot of the various migration tools and Graphile Migrate is the seed of my vision for a much more compelling migration story for everyone in the PostgreSQL community (especially people who don’t mind writing SQL but find the current up/down pattern too verbose/clunky as I do). There’s lots more to come, so make sure you follow the project if you’re interested.
- anhnhoktvn 7y ago
  How does it compare with marv [0]? [0] https://www.npmjs.com/package/marv https://www.npmjs.com/package/marv

AdieuToLogic 7y ago

> Are there other reasons this hasn't seen more widespread adoption? This approach had widespread adoption in the 1990's, most notably via PowerBuilder[0]. > Is there some inherent architectural flaw or just not enough incremental benefit? Yes, there is an inherent architectural flaw with two-tiered systems in that they force a system to choose to place complexity either in the GUI or in the RDBMS. This often leads to "Jenga Programming" maintenance techniques which cause the code base to take a life of its own. It turns out that this does not scale. 0 - https://en.wikipedia.org/wiki/PowerBuilder#History https://en.wikipedia.org/wiki/PowerBuilder#History

ruslan_talpa 7y ago
Why do you think this is a two tier system? Even here [1] i count 4 places to put logic into besides frontend 1. https://github.com/subzerocloud/postgrest-starter-kit/blob/master/README.md https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
- AdieuToLogic 7y ago
  > Why do you think this is a two tier system? The first sentence of site reads thusly: > PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. If there is a database on one end and a client on the other, with no intervening system between them, that is the definition of a two-tiered system.
  - ruslan_talpa 7y ago
    Sure you can use it as two tier if that does the job but it does not mean you are forced to, you can very well have logic in the proxy layer and then have logic below the db layer (workers reacting to database changes). If i apply the same logic (two tier bad) that i can say every other way of building an api is two tier since you have the frontend and then the rest of the code in php/node... the db would not really count as a later since in those scenatios ppl use it as a dumb store

bayesian_horse 7y ago

I haven't used Rails a lot, but in Django you get a lot more from the ORM models than just database queries. The Django ORM is used pervasively throughout django and third party libraries. You can compose and reuse querysets in Templates, Form validation, authorization and so on. You can create a CRUD admin interface and a REST API with almost no extra configuration. It also has a very mature migration system, meaning you can have your migrations in code, and apply them in production or in multiple environments. One problem I expect with PostgREST (haven't used it yet) is that validation and authorization may suffer. At the very least this will have to be configured either as SQL data or as SQL stored procedures, with SQL not being the strong suit of most frontend people. They probably have safeguards against SQL injections through their API. Logic validation will be harder, and Postgres only supports rudimentary permissions, so all of this has to be implemented in stored procedures, also. My main concern with PostgREST is that the database is stateful and persistent, and that you have to put more code in there. Which means every deployment is going to be a migration, no matter what. You better don't forget any of the state changes you made in your development database. Version management will certainly be harder. Please don't get me wrong. I am sure PostgREST has perfectly valid usecases, and I like Postgres a lot. I am just dropping a few thoughts on why I would prefer a traditional application between database and frontend.

steve-chavez 7y ago

> Are there other reasons this hasn't seen more widespread adoption? I think it's because PostgreSQL got major features for web development — like JSON and RLS — not too long ago and most developers are just catching up. JSON was added back in 2013[1] and RLS back in 2016[2]. [1]: https://www.postgresql.org/docs/release/9.3.0/ https://www.postgresql.org/docs/release/9.3.0/ [2]: https://www.postgresql.org/docs/release/9.5.0/ https://www.postgresql.org/docs/release/9.5.0/

Benjamin_Dobell 7y ago
JSON support wasn't all that great until JSONB columns were added in December 2014[1]. I think that was when devs started taking it seriously. [1] https://www.postgresql.org/docs/9.4/release-9-4.html https://www.postgresql.org/docs/9.4/release-9-4.html