Hacker News Reader

Comment by singingwolfboy | Hacker News Reader

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/)

[2](https://github.com/graphile/starter)

[3](https://github.com/graphile/starter/blob/master/%40app/db/mi...)

View on HN

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.

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.

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".

No I'm not agreeing with you at all.

It's a great idea, and my clients and bank account agree with me.

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?

It's not generally safe to expose SQL to untrusted clients. For example, PostgreSQL 12.2 was released yesterday and fixed a security issue where `ALTER ... DEPENDS ON EXTENSION` did not have any privilege check whatsoever. SQL is also not at all well suited for the needs of frontend web app developers - just ask Facebook about their experiences with FQL! Using an API that's more ergonomic for the frontend, such as GraphQL, backed by a language which is optimised for the backend, such as SQL, is the best of both worlds.

Yes of course you cant allow uncontrolled sql execution, but an api that just maps to crud operations isn't good either.

Counterpoint yes it is.

Not according to https://publications.opengroup.org/standards/soa

There is a lot of context lost in generalities so I admit you have to look at every specific situation, but in general CRUD means pushing business logic down to the client (which is generally some kind of code running in a browser or mobile app), which is the opposite of everything good in the world.

Agreed: an API that _just_ maps to CRUD operations isn’t good. I’m not advocating for that, neither is singingwolfboy, and the starter repo he’s linked to basically does not use them: there are only 4 CRUD mutations, all the others are custom. I rarely use CRUD operations in PostGraphile, mostly I use custom mutations either defined in SQL or TypeScript.

No idea why Benjie is getting down voted, SQL from a client is a bad idea, and writing billions and billions of CRUD endpoints is soul-draining.

Postgrahile is the best of both worlds, providing a nice GraphQL interface on top of your database.

If you decide you need to write crud endpoints, you have your database still. It's a zero-cost abstraction, which is wild.

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.

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

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.

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=08730000000BrPIAA...

>> 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?

Absolutely not, this is a common misconception. Have a read of this: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...

Reply on news.ycombinator.com