Comment by steve-chavez | Hacker News Reader

Comment by steve-chavez | Hacker News Reader

Parent Full thread

You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns:

> Security: we don’t want people messing up our data

Row level security + roles/grants should be enough to cover this one.

> On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold.

With a timeout, what you'll need is to request the expensive queries in parallel to ddos the service. Would be better to filter by the query plan, in PostgreSQL there's an extension for this https://github.com/pgexperts/pg_plan_filter. Though pg also has a `statement_timeout`.

> Hiding implementation details

You can expose a dedicated schema for you API that doesn't contain a single table but only views and stored functions. Migrating this schema should be easy thanks to transactional DDL.

That being said, I agree with the sentiment about GraphQL, besides the extra query language, there's an additional impedance mismatch since now you have to map your DB schema to the GraphQL schema.

I'm always surprised that PostgREST isn't the industry standard for creating an API. It looks like it would save so much work and the corner cases can be handled without writing code in a different language.

What am I missing?

I tried it, and still have it running on a little toy service I built. It's cool, but IMO, it doesn't really seem production-ready for a large-scale service. Last time I updated it, I had to spend a full day sorting out the changes they made to how the server config and user authentication works. Last I checked, they don't even have a suggested script to run it as a service. Going by the contributor graph, it's still essentially a 1-person project.

IIRC, this was what the connection poolers (pgpool, pgbouncer), and perhaps all now-popular postgres extensions/enhancements, looked like in their initial years, as well.

In the context of a "large-scale service", my guess is that there's a chicken-and-egg problem, where an operators of such a service has to decide they want it to be production-ready enough to throw money and/or engineering resources at the project, which can attract yet more contributors.

> a chicken-and-egg problem

The way I see it. Large corporations have the golden egg laying hen. And plebian project like PostgREST have little to no chance.

If I had to guess, this is due to a combination of only being a few years old and marketing (initially, the major version number was 0).

PostgraphQL

I don’t think mapping your DB is necessarily a disadvantage.

It provides a lot of benefits in modern web- and mobile-development which is where you’d use something like GraphQL.

JavaScript works better when it knows the DB schemes, not only is it more efficient for your developers but it’s also higher quality because it allows fewer fuckups. If you’re building your DB schemes anyway, you may as well take advantage of GraphQLs other features.

And it’s in a familiar language, any web developer can do a GraphQL query, and it’ll be efficient too. I think most developers can do a SQL queries too, but not always efficient. You can get a long way by going to W3 or google and hacking your way through a query to get what you want. Our business analytics and our LEAN process people do it, but I’ve seen some real horrors build that way by people who knew SQL but not databases.

Build what you want, but there is a reason GraphQL is popular.

But how do you solve a typical multi-tenant SaaS with users and teams? You can‘t create roles for every login user because (s)he may have special access control rights for every project (s)he is participating in.

Can you give a good example? I've found that the combination of row level security and role inheritance in Postgres can handle some fairly complex cases.

Reply on news.ycombinator.com