5 ms·

I can't speak for this project specifically but for some context, Postgraphile's way of solving this, as it only supports Postgres, is to use Postgres's Row Lev

I can't speak for this project specifically but for some context, Postgraphile's way of solving this, as it only supports Postgres, is to use Postgres's Row Level Security feature, whereby you enforce scopes and permissions at the data layer, as well as using table grants to roles specified in JWTs and such. (https://www.graphile.org/postgraphile/postgresql-schema-design/#row-level-security https://www.graphile.org/postgraphile/postgresql-schema-desi...) This project doesn't seem to have any inbuilt AuthZ functionality, so unless your database has that built in like Postgres, or you need something that's not possible in-database, I guess you just... can't.

awb 6y ago
I tied this "no backend" approach but ended up writing all of my business logic in the DB schema instead of in the app, which seemed to defeat the purpose.
- lukeramsden 6y ago
  Well if your application has business logic at all you're going to have to write business logic at some point, and I personally like the idea of keeping as much of it co-located with the data - it makes your schema the SSOT for your data structure and it's constraints and transformations. The complication is as always external services, like sending email, validating IAP receipts, things like that. For that I liked to take some of the principles from the 3factor app devised by the Hasura team, and make them asynchronous, and performed by worker processes (in my case, with graphile-worker).
  - awb 6y ago
    Agreed, I really liked having everything so close and tightly coupled with the DB. The integrations and business logic were the part I had to build traditionally and at that point it wasn't much more to query the DB from the backend rather than trying to learn how to script inside Postgres.
ecf 6y ago
I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with. The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB. In my case, I simply could not figure out how to use the Users table without having the password returned in all queries. I could turn off the automatically generated queries that Postgraphile made, but at that point why bother with it? Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer. Graphile makes https://github.com/graphile/migrate https://github.com/graphile/migrate, however, it seems incredibly brittle and is built on a workflow that will absolutely break production if it is slightly deviated from. I really liked using it! I just hope some of these developer ergonomics issues could be better handled.
- lukeramsden 6y ago
  > In my case, I simply could not figure out how to use the Users table without having the password returned in all queries The recommended way of doing this is to store anything you don't want public in a separate table with a one-to-one relationship, and then controlling access to that table through computed columns and such (https://www.graphile.org/postgraphile/postgresql-schema-design/#storing-emails-and-passwords https://www.graphile.org/postgraphile/postgresql-schema-desi...) What I can't recommend enough to people starting with Postgraphile is to check out graphile-starter, and especially it's first migration: https://github.com/graphile/starter/blob/main/%40app/db/migrations/committed/000001.sql https://github.com/graphile/starter/blob/main/%40app/db/migr... That really helped me to understand better how to structure my schema, as separation of concerns within a model by using different tables is not something that is necessary when doing a regular application-in-front-of-datastore type app. > Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer. Yes, I don't have to grapple with this issue so I don't really have much to say about this. Graphile-migrate works great for me, but I can see how it could be an issue for larger projects, although I would think that database migrations are simply tricky on their own, regardless of Postgraphile.

awb 6y ago

I tied this "no backend" approach but ended up writing all of my business logic in the DB schema instead of in the app, which seemed to defeat the purpose.

lukeramsden 6y ago
Well if your application has business logic at all you're going to have to write business logic at some point, and I personally like the idea of keeping as much of it co-located with the data - it makes your schema the SSOT for your data structure and it's constraints and transformations. The complication is as always external services, like sending email, validating IAP receipts, things like that. For that I liked to take some of the principles from the 3factor app devised by the Hasura team, and make them asynchronous, and performed by worker processes (in my case, with graphile-worker).
- awb 6y ago
  Agreed, I really liked having everything so close and tightly coupled with the DB. The integrations and business logic were the part I had to build traditionally and at that point it wasn't much more to query the DB from the backend rather than trying to learn how to script inside Postgres.

ecf 6y ago

I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with. The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB. In my case, I simply could not figure out how to use the Users table without having the password returned in all queries. I could turn off the automatically generated queries that Postgraphile made, but at that point why bother with it? Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer. Graphile makes https://github.com/graphile/migrate https://github.com/graphile/migrate, however, it seems incredibly brittle and is built on a workflow that will absolutely break production if it is slightly deviated from. I really liked using it! I just hope some of these developer ergonomics issues could be better handled.

lukeramsden 6y ago
> In my case, I simply could not figure out how to use the Users table without having the password returned in all queries The recommended way of doing this is to store anything you don't want public in a separate table with a one-to-one relationship, and then controlling access to that table through computed columns and such (https://www.graphile.org/postgraphile/postgresql-schema-design/#storing-emails-and-passwords https://www.graphile.org/postgraphile/postgresql-schema-desi...) What I can't recommend enough to people starting with Postgraphile is to check out graphile-starter, and especially it's first migration: https://github.com/graphile/starter/blob/main/%40app/db/migrations/committed/000001.sql https://github.com/graphile/starter/blob/main/%40app/db/migr... That really helped me to understand better how to structure my schema, as separation of concerns within a model by using different tables is not something that is necessary when doing a regular application-in-front-of-datastore type app. > Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer. Yes, I don't have to grapple with this issue so I don't really have much to say about this. Graphile-migrate works great for me, but I can see how it could be an issue for larger projects, although I would think that database migrations are simply tricky on their own, regardless of Postgraphile.