10 ms·

I have been using Postgraphile (similar to this one but for GraphQL) for many months. The great thing about this way to create systems is that you don't expend

I have been using Postgraphile (similar to this one but for GraphQL) for many months. The great thing about this way to create systems is that you don't expend time doing glue code, just in the real business logic. But a big pain is to maintain your database code, by example the version control of your functions. There are not suitable linters, and testing can't be done over postgres functions but must be done over GraphQL instead. Using things like this will save you months of development time!, Even if I agree there are some systems that will not be good idea to implement in this way.

snuxoll 6y ago
Lack of linters can be a pain, but testing is easily handled with pgTAP.
awb 6y ago
> Using things like this will save you months of development time! When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base. Yes, this automates boilerplate which is awesome for small, standalone apps, but in my experience I haven't seen months of development time saved with these tools.
- CuriouslyC 6y ago
  This isn't really true, in most cases you can write FDWs over foreign resources that let you query them like any other table, use them in joins, etc. Postgres is really more of an application platform than a database at this point. Just don't try to have the same PG instance be your master and your platform.
- lumberjack24 6y ago
  You might want to check Forest Admin in that case. It creates an extensible admin REST API on top of your Postgres. Comes with native CRUD routes for all your tables but you can also declare your own to refund a transaction using Stripe's API, send an email using Twilio's API... Here's an explanatory video - https://bit.ly/ForestAdminIntro5min https://bit.ly/ForestAdminIntro5min
- berkes 6y ago
  Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really. I cannot think of a situation where an API only handles CRUD data and lacks any behaviour. But, If your API really only is pushing data around, ' such tooling is usefull and probably saves a lot of time.
  - berkes 6y ago
    As awb points out: you now have a splintered codebase. I'd add that you also have hard to spec couplings and difficult to manage microservices setup. Tools like MQTT or paradigms like eventsourcing might help. But those all presume your database is a datastore. And not the heart of your businesslogic.
  - michelpp 6y ago
    > Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really. You put an item in table queue using SELECT FOR UPDATE ... SKIP LOCKED and some out of band consumer sends your email. PostgREST isn't about doing everything in the database it's about doing all the same patterns you already do but with less boilerplate.
    - mixmastamyk 6y ago
      Interesting, so how does that fit into the model definition/admin? How to do migrations for example? Have to do it all in sql, I’m guessing. May not be a win in the long run.
      - rapind 6y ago
        https://sqitch.org/ https://sqitch.org/ Is a popular migration tool or you can use whatever your used to from your framework of choice (Rails or what have you)
    - how_gauche 6y ago
      You can also use NOTIFY in your postgrest stored procedure to wake up a hypothetical backend processor with low latency. I really love this project! I recommend it to people all the time.
- michelpp 6y ago
  > When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base. Those third parties can still talk to the same database. We use this pattern all the time, PostgREST to serve the API and a whole bag of tools in various languages that work behind the scenes with their native postgres client tooling.
  - berkes 6y ago
    It sounds like it quickly becomes extremely difficult to change the datascheme then. Do you tightly orchestrate releases? Or do you simply never change the schema?
  - GordonS 6y ago
    Does PostgREST have functionality for authentication and authorization? I guess you could front it with a reverse proxy if not, but would be nice to have auth built in.
    - michelpp 6y ago
      It uses JWT, you can use third party or serve them yourself.
    - IggleSniggle 6y ago
      I haven’t used postgREST, but the user/group authorization model looks fantastic at a glance.
    - FlyingSnake 6y ago
      Yes, it has first class support for 3rd party auth using signed JWTs. I use it with Auth0 to provide social logins. [1]: http://postgrest.org/en/v7.0.0/auth.html#client-auth http://postgrest.org/en/v7.0.0/auth.html#client-auth [2]: http://postgrest.org/en/v7.0.0/ecosystem.html http://postgrest.org/en/v7.0.0/ecosystem.html
    - SahAssar 6y ago
      I use caddy-auth-portal and caddy-jwt to generate JWTs for AuthN (based on SSO) and then use postgres built in row level security for AuthZ.
- dwwoelfel 6y ago
  You could try https://onegraph.com https://onegraph.com. It won't allow you to get rid of all your backend code, but you can definitely get further!
steve-chavez 6y ago
Regarding linters, check plpgsql_check[1]. Also, as snuxoll mentioned, for tests there's pgTAP. Here's a sample project[2] that has some tests using it. [1]: https://github.com/okbob/plpgsql_check https://github.com/okbob/plpgsql_check [2]: https://github.com/steve-chavez/socnet/blob/master/tests/anons.sql https://github.com/steve-chavez/socnet/blob/master/tests/ano...
ggregoire 6y ago
> But a big pain is to maintain your database code, by example the version control of your functions. The solution I came with is to have a git repository in which each schema is represented by a directory and each table, view, function, etc… by a .sql file containing its DDL. Every time I make a change in the database I make the change in the repository. It doesn't automate anything, it doesn't save me time in the process of modifying the database, it's actually a lot of extra work, but I think it's worth it. If I want to know when, who, why and how a table has been modified over the last 2 years, I just check the logs, commits dates, authors and messages, and display the diffs If I want to see exactly what changed.
- Twisell 6y ago
  On one of my oldest postgreSQL project which I usually edit on live database I also added version control in the form of a one liner pg_dump call that backup --schema-only. This way whenever I make a change I call this one liner, overwrite previous sql dump in git repo, then stage all and commit. This way il also enjoy diff over time for my schemas.
- 411111111111111 6y ago
  Liquibase supports plain sql files (just needs one comment for author at the start) and custom folder structures, so if you actually want to take it one step further, do check it out ;) It could not only be great for documentation purposes but also actually help maintenance by making sure that all statements are executed in all environments
  - gauravphoenix 6y ago
    +1 for Liquibase. I love the simplicity of it.
ruslan_talpa 6y ago
The things you said are a pain (or can’t be done) here is how you do it https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit Specifically, testing the sql side with sql (pgtap) and having your sql code in files that get autoloaded to your dev db when saved. Migrations are autogenerated* with apgdiff and managed with sqitch. The process has it’s rough edges but it makes developing with this kind of stack much easier * you still have to review the generated migration file and make small adjustments but it allows you to work with many entities at a time in your schema without having to remember to reflect the change in a specific migration file
- rapind 6y ago
  I’ve used pgtap. It works but it’s not awesome if your coming from something like rspec. So... I just used rspec to test my functions instead (pgtap let’s you easily test types and signatures too, but I was more interested in unit testing function results given specific inputs). I’m sure you could argue either way. Just adding this as an option to consider.
tobyhede 6y ago
I've looked quite extensively at Postgraphile and the extensive dependency on database functions and sql is an issue. Really hard to write tests and SQL itself is not the greatest programming language. The whole setup lacks so many of the affordances of modern environments.
- lukeramsden 6y ago
  > SQL itself is not the greatest programming language SQL has been the biggest flaw in this stack for me. I love using PostgREST/Postgraphile et al, but actually writing the SQL is just... eh. Maybe (lets hope) EdgeDB's EdgeQL or something similar could rectify this. The same Postgres core database and introspection for Postg{REST,raphile} but with a much improved devx
  - tobyhede 6y ago
    I was wondering of the V8 engine integration would be something to play with, but think ot just adds inefficiency and doesn't really fix some of the core problems.
  - rishav_sharan 6y ago
    Why would you need to write SQL if you are using grapql on postgraphile? Graphql queries are much simpler anyway.
    - lukeramsden 6y ago
      Because you have to write the schema and role and row-based security rules in SQL.
w1 6y ago
How is Postgraphile different from Hasura?
- rattray 6y ago
  One thing is that it's open source, not proprietary. You can run it on your own servers, modify it, etc, for free. Another thing is it's Node, and can be used as a plug-in to your express app. This can make it easier to customize and extend over time. Eg; have parts of your schema be postgraphile and parts be custom JS.
  - gavinray 6y ago
    Hasura isn't proprietary though? It's OSS software, code lives on the GH repo: https://github.com/hasura/graphql-engine https://github.com/hasura/graphql-engine
    - rattray 6y ago
      My apologies! I'm not sure why I misremembered that. Perhaps I was confusing it with Prisma... Which appears to be open-source as well now. Hmm, my mind must be playing tricks on me. Regardless, thank you for the correction!
- xdanger 6y ago
  Biggest difference I think is that Hasura doesn't use RLS for security. It has it's own privileges/roles implementation. Postgraphile kinda works in postgresql, hasura works with postgresql.
- lukeramsden 6y ago
  Not enormously. The biggest difference is Hasuras console, which Postgraphile lacks. However I don't really see that as an advantage in favour of Hasura, postgraphile + graphile-worker + graphile-migrate (all by the same author) has worked so much better for me than Hasura.
djrobstep 6y ago
I don't use postgraphile myself, but as the author of a schema diff tool, I know a lot of people use schema diff tools to manage function changes automatically, and seems to work pretty well for them.

snuxoll 6y ago

Lack of linters can be a pain, but testing is easily handled with pgTAP.

awb 6y ago

> Using things like this will save you months of development time! When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base. Yes, this automates boilerplate which is awesome for small, standalone apps, but in my experience I haven't seen months of development time saved with these tools.

CuriouslyC 6y ago
This isn't really true, in most cases you can write FDWs over foreign resources that let you query them like any other table, use them in joins, etc. Postgres is really more of an application platform than a database at this point. Just don't try to have the same PG instance be your master and your platform.
lumberjack24 6y ago
You might want to check Forest Admin in that case. It creates an extensible admin REST API on top of your Postgres. Comes with native CRUD routes for all your tables but you can also declare your own to refund a transaction using Stripe's API, send an email using Twilio's API... Here's an explanatory video - https://bit.ly/ForestAdminIntro5min https://bit.ly/ForestAdminIntro5min
berkes 6y ago
Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really. I cannot think of a situation where an API only handles CRUD data and lacks any behaviour. But, If your API really only is pushing data around, ' such tooling is usefull and probably saves a lot of time.
- berkes 6y ago
  As awb points out: you now have a splintered codebase. I'd add that you also have hard to spec couplings and difficult to manage microservices setup. Tools like MQTT or paradigms like eventsourcing might help. But those all presume your database is a datastore. And not the heart of your businesslogic.
- michelpp 6y ago
  > Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really. You put an item in table queue using SELECT FOR UPDATE ... SKIP LOCKED and some out of band consumer sends your email. PostgREST isn't about doing everything in the database it's about doing all the same patterns you already do but with less boilerplate.
  - mixmastamyk 6y ago
    Interesting, so how does that fit into the model definition/admin? How to do migrations for example? Have to do it all in sql, I’m guessing. May not be a win in the long run.
    - rapind 6y ago
      https://sqitch.org/ https://sqitch.org/ Is a popular migration tool or you can use whatever your used to from your framework of choice (Rails or what have you)
  - how_gauche 6y ago
    You can also use NOTIFY in your postgrest stored procedure to wake up a hypothetical backend processor with low latency. I really love this project! I recommend it to people all the time.
michelpp 6y ago
> When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base. Those third parties can still talk to the same database. We use this pattern all the time, PostgREST to serve the API and a whole bag of tools in various languages that work behind the scenes with their native postgres client tooling.
- berkes 6y ago
  It sounds like it quickly becomes extremely difficult to change the datascheme then. Do you tightly orchestrate releases? Or do you simply never change the schema?
- GordonS 6y ago
  Does PostgREST have functionality for authentication and authorization? I guess you could front it with a reverse proxy if not, but would be nice to have auth built in.
  - michelpp 6y ago
    It uses JWT, you can use third party or serve them yourself.
  - IggleSniggle 6y ago
    I haven’t used postgREST, but the user/group authorization model looks fantastic at a glance.
  - FlyingSnake 6y ago
    Yes, it has first class support for 3rd party auth using signed JWTs. I use it with Auth0 to provide social logins. [1]: http://postgrest.org/en/v7.0.0/auth.html#client-auth http://postgrest.org/en/v7.0.0/auth.html#client-auth [2]: http://postgrest.org/en/v7.0.0/ecosystem.html http://postgrest.org/en/v7.0.0/ecosystem.html
  - SahAssar 6y ago
    I use caddy-auth-portal and caddy-jwt to generate JWTs for AuthN (based on SSO) and then use postgres built in row level security for AuthZ.
dwwoelfel 6y ago
You could try https://onegraph.com https://onegraph.com. It won't allow you to get rid of all your backend code, but you can definitely get further!

steve-chavez 6y ago

Regarding linters, check plpgsql_check[1]. Also, as snuxoll mentioned, for tests there's pgTAP. Here's a sample project[2] that has some tests using it. [1]: https://github.com/okbob/plpgsql_check https://github.com/okbob/plpgsql_check [2]: https://github.com/steve-chavez/socnet/blob/master/tests/anons.sql https://github.com/steve-chavez/socnet/blob/master/tests/ano...

ggregoire 6y ago

> But a big pain is to maintain your database code, by example the version control of your functions. The solution I came with is to have a git repository in which each schema is represented by a directory and each table, view, function, etc… by a .sql file containing its DDL. Every time I make a change in the database I make the change in the repository. It doesn't automate anything, it doesn't save me time in the process of modifying the database, it's actually a lot of extra work, but I think it's worth it. If I want to know when, who, why and how a table has been modified over the last 2 years, I just check the logs, commits dates, authors and messages, and display the diffs If I want to see exactly what changed.

Twisell 6y ago
On one of my oldest postgreSQL project which I usually edit on live database I also added version control in the form of a one liner pg_dump call that backup --schema-only. This way whenever I make a change I call this one liner, overwrite previous sql dump in git repo, then stage all and commit. This way il also enjoy diff over time for my schemas.
411111111111111 6y ago
Liquibase supports plain sql files (just needs one comment for author at the start) and custom folder structures, so if you actually want to take it one step further, do check it out ;) It could not only be great for documentation purposes but also actually help maintenance by making sure that all statements are executed in all environments
- gauravphoenix 6y ago
  +1 for Liquibase. I love the simplicity of it.

ruslan_talpa 6y ago

The things you said are a pain (or can’t be done) here is how you do it https://github.com/subzerocloud/postgrest-starter-kit https://github.com/subzerocloud/postgrest-starter-kit Specifically, testing the sql side with sql (pgtap) and having your sql code in files that get autoloaded to your dev db when saved. Migrations are autogenerated* with apgdiff and managed with sqitch. The process has it’s rough edges but it makes developing with this kind of stack much easier * you still have to review the generated migration file and make small adjustments but it allows you to work with many entities at a time in your schema without having to remember to reflect the change in a specific migration file

rapind 6y ago
I’ve used pgtap. It works but it’s not awesome if your coming from something like rspec. So... I just used rspec to test my functions instead (pgtap let’s you easily test types and signatures too, but I was more interested in unit testing function results given specific inputs). I’m sure you could argue either way. Just adding this as an option to consider.

tobyhede 6y ago

I've looked quite extensively at Postgraphile and the extensive dependency on database functions and sql is an issue. Really hard to write tests and SQL itself is not the greatest programming language. The whole setup lacks so many of the affordances of modern environments.

lukeramsden 6y ago
> SQL itself is not the greatest programming language SQL has been the biggest flaw in this stack for me. I love using PostgREST/Postgraphile et al, but actually writing the SQL is just... eh. Maybe (lets hope) EdgeDB's EdgeQL or something similar could rectify this. The same Postgres core database and introspection for Postg{REST,raphile} but with a much improved devx
- tobyhede 6y ago
  I was wondering of the V8 engine integration would be something to play with, but think ot just adds inefficiency and doesn't really fix some of the core problems.
- rishav_sharan 6y ago
  Why would you need to write SQL if you are using grapql on postgraphile? Graphql queries are much simpler anyway.
  - lukeramsden 6y ago
    Because you have to write the schema and role and row-based security rules in SQL.

w1 6y ago

How is Postgraphile different from Hasura?

rattray 6y ago
One thing is that it's open source, not proprietary. You can run it on your own servers, modify it, etc, for free. Another thing is it's Node, and can be used as a plug-in to your express app. This can make it easier to customize and extend over time. Eg; have parts of your schema be postgraphile and parts be custom JS.
- gavinray 6y ago
  Hasura isn't proprietary though? It's OSS software, code lives on the GH repo: https://github.com/hasura/graphql-engine https://github.com/hasura/graphql-engine
  - rattray 6y ago
    My apologies! I'm not sure why I misremembered that. Perhaps I was confusing it with Prisma... Which appears to be open-source as well now. Hmm, my mind must be playing tricks on me. Regardless, thank you for the correction!
xdanger 6y ago
Biggest difference I think is that Hasura doesn't use RLS for security. It has it's own privileges/roles implementation. Postgraphile kinda works in postgresql, hasura works with postgresql.
lukeramsden 6y ago
Not enormously. The biggest difference is Hasuras console, which Postgraphile lacks. However I don't really see that as an advantage in favour of Hasura, postgraphile + graphile-worker + graphile-migrate (all by the same author) has worked so much better for me than Hasura.

djrobstep 6y ago

I don't use postgraphile myself, but as the author of a schema diff tool, I know a lot of people use schema diff tools to manage function changes automatically, and seems to work pretty well for them.