4 ms·

PostGraphile [1] is a framework for generating a GraphQL API based on the tables in your database; as a result, good database design is crucial. Graphile Starte

PostGraphile [1] is a framework for generating a GraphQL API based on the tables in your database; as a result, good database design is crucial. Graphile Starter [2] is a quickstart project that demonstrates best practices for getting up and running with PostGraphile quickly. In particular, check out the SQL migration file in that project [3]. It demonstrates: 1. Dividing up tables so that one user can have more than one email address 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user 3. Dividing tables across multiple schemas for additional security 4. SQL comments for documentation 5. SQL functions for common functionality, such as logging in or verifying an email It’s a fascinating project, and well worth a look! [1](https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/) [2](https://github.com/graphile/starter https://github.com/graphile/starter) [3](https://github.com/graphile/starter/blob/master/%40app/db/migrations/committed/000001.sql https://github.com/graphile/starter/blob/master/%40app/db/mi...)

pimmen 7y ago
Users having multiple addresses is something I've cursed a lot over. I work in a team that does data analytics for a news publishing company, and our print business is still very important. Unfortunately, in our database over print customers users are basically addresses because you don't really need to know how many people are receiving your paper as a distributor, only where and how many papers. Since it's also been a safe assumption for a century that people share newspapers with each other, market research was done street by street to inform ad buyers of which markets we reached. Many people have more than one home. Some people take out another subscription for a relative. This mapped very awkwardly to digital subscribers who we had individual data on. We were able to join databases in a way that sort of works through more or less (mostly less) comfortable assumptions. The queries are not pretty.
- jacques_chester 7y ago
  There's a whole subfield of information science dedicated to basically this exact problem: entity resolution. Hilariously, it has dozens of names, because it just comes up in so many places for so many people. It appears that "record linkage" is the term that has won the top spot at Wikipedia: https://en.wikipedia.org/wiki/Record_linkage https://en.wikipedia.org/wiki/Record_linkage
  - eska 7y ago
    Record linkage seems to be unrelated. While OP isn't sure how to segregate and join data, he has perfect joining capability through unique indices. Record linkage seems to be concerned with joins that aren't guaranteed to be correct because there are no unique keys.
- debian3 7y ago
  Multiple email support seems indeed complex, event the most popular CRM on the market doesn’t support this feature even if it’s requested a lot. https://success.salesforce.com/ideaview?id=08730000000BrPIAA0 https://success.salesforce.com/ideaview?id=08730000000BrPIAA...
ltbarcly3 7y ago
If you are going to auto generate an api for a database, just use SQL. Adding extra steps between you and a database with no encapsulation is just adding extra steps for no reason. One of the key things you need to do in good database design is to map business verbs to API endpoints in something like a 1-1 way. Having an api endpoint that is essentially "insert this row to the database" is just cargo culting. There is already an API for that, it's called SQL.
- markhalonen 7y ago
  I'm using Postgraphile in production on 2 real-world projects and it saves me an incredible amount of time. I spend 90%+ of my time on the front-end because the api is all automated. Postgrahile allows you to rename/disable api endpoints if needed. Postgraphile rocks. And it's written in modualar Javascript, so I can hack it if I need to. Unlike Hasura.
  - ltbarcly3 7y ago
    Im saying it does something that is a bad idea in the first place. You are saying "yea, but it does it with so little effort".
    - markhalonen 7y ago
      No I'm not agreeing with you at all. It's a great idea, and my clients and bank account agree with me.
      - ltbarcly3 7y ago
        This is frustrating. There are lots of ways to do software. Some are widely considered by experts to be good, some are not so good. Then there is a thing called business. You can certainly sell software that is built using bad practices, and honestly nobody will probably complain so long as it works. It might not be quite as maintainable, it might require more effort to add features, and it might be necessary to completely rewrite that software in 5 years when other parts of the system change. Terrible software is bought all the time, and that's not even really a problem. Even though you sold it and your customers are happy, there still are things you can probably learn, right?
nicktrocado92 7y ago
>> 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user I'm interested in PostGraphile, but i have a question: How do apply permissions when your user table is different from postgres user systems? i only have handful of users that have permissions spaning a lot of tables. Do i need to create one postgres user for each of my application users?
- BenjieGillam 7y ago
  Absolutely not, this is a common misconception. Have a read of this: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...

pimmen 7y ago

Users having multiple addresses is something I've cursed a lot over. I work in a team that does data analytics for a news publishing company, and our print business is still very important. Unfortunately, in our database over print customers users are basically addresses because you don't really need to know how many people are receiving your paper as a distributor, only where and how many papers. Since it's also been a safe assumption for a century that people share newspapers with each other, market research was done street by street to inform ad buyers of which markets we reached. Many people have more than one home. Some people take out another subscription for a relative. This mapped very awkwardly to digital subscribers who we had individual data on. We were able to join databases in a way that sort of works through more or less (mostly less) comfortable assumptions. The queries are not pretty.

jacques_chester 7y ago
There's a whole subfield of information science dedicated to basically this exact problem: entity resolution. Hilariously, it has dozens of names, because it just comes up in so many places for so many people. It appears that "record linkage" is the term that has won the top spot at Wikipedia: https://en.wikipedia.org/wiki/Record_linkage https://en.wikipedia.org/wiki/Record_linkage
- eska 7y ago
  Record linkage seems to be unrelated. While OP isn't sure how to segregate and join data, he has perfect joining capability through unique indices. Record linkage seems to be concerned with joins that aren't guaranteed to be correct because there are no unique keys.
debian3 7y ago
Multiple email support seems indeed complex, event the most popular CRM on the market doesn’t support this feature even if it’s requested a lot. https://success.salesforce.com/ideaview?id=08730000000BrPIAA0 https://success.salesforce.com/ideaview?id=08730000000BrPIAA...

ltbarcly3 7y ago

If you are going to auto generate an api for a database, just use SQL. Adding extra steps between you and a database with no encapsulation is just adding extra steps for no reason. One of the key things you need to do in good database design is to map business verbs to API endpoints in something like a 1-1 way. Having an api endpoint that is essentially "insert this row to the database" is just cargo culting. There is already an API for that, it's called SQL.

markhalonen 7y ago
I'm using Postgraphile in production on 2 real-world projects and it saves me an incredible amount of time. I spend 90%+ of my time on the front-end because the api is all automated. Postgrahile allows you to rename/disable api endpoints if needed. Postgraphile rocks. And it's written in modualar Javascript, so I can hack it if I need to. Unlike Hasura.
- ltbarcly3 7y ago
  Im saying it does something that is a bad idea in the first place. You are saying "yea, but it does it with so little effort".
  - markhalonen 7y ago
    No I'm not agreeing with you at all. It's a great idea, and my clients and bank account agree with me.
    - ltbarcly3 7y ago
      This is frustrating. There are lots of ways to do software. Some are widely considered by experts to be good, some are not so good. Then there is a thing called business. You can certainly sell software that is built using bad practices, and honestly nobody will probably complain so long as it works. It might not be quite as maintainable, it might require more effort to add features, and it might be necessary to completely rewrite that software in 5 years when other parts of the system change. Terrible software is bought all the time, and that's not even really a problem. Even though you sold it and your customers are happy, there still are things you can probably learn, right?

nicktrocado92 7y ago

>> 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user I'm interested in PostGraphile, but i have a question: How do apply permissions when your user table is different from postgres user systems? i only have handful of users that have permissions spaning a lot of tables. Do i need to create one postgres user for each of my application users?

BenjieGillam 7y ago
Absolutely not, this is a common misconception. Have a read of this: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...