Hacker News Reader

Comment by simplecomplex | Hacker News Reader

Why spend time building an API at all? Just point Postgrest at your PostreSQL database and you get a REST api for free. There’s similar projects for graphql and other DBs.

View on HN

Totally agree. Sprinkle in some row level security and you've got an app server with data isolated at the DB level, and get the power of SQL for building out endpoints. Compared to this graphql feels a little like engineering theatre.

Which leaks a lot of implementation details through the public API, preventing changes without breaking existing clients. Which in practice means that you can't change anything anymore once you have more than a handful of customers. At least if you don't have the market power of a facebook who can say "Adapt your client code or it'll stop working. We don't give a fuck."

Another big issue is that it forces the client to understand a lot about how your application works. While an API can abstract that and conveniently offer various computed properties which output what the client needs.

You can do the same with GraphQL and Postgraphile.

The added benefit is that you can use the same schema for your client and server.

There's a nice guide to setting up a whole system here: https://www.graphile.org/postgraphile/postgresql-schema-desi...

Isn't GraphQL intended to be used directly by the frontend app (with direct access by any potential malicious user), whereas Postgrest is more for a backend app, that can be trusted with more control?

There's nothing in PostgREST that stops you from limiting control so that even anonymous users can use it safely. I've used PostgREST for user-facing APIs with success, but it requires some knowledge about the postgres access control model.

EDIT: And "Just point Postgrest at your PostreSQL database" is rarely a good idea in my experience, I usually have (versioned) API-schemas containing views, so that I can change my underlying data schema at will without borking the API.

Anonymous seems easier, since you can treat them as a single user. But could you do something like HN as a frontend app talking directly to a Postgrest API?

Good point, that's true. But yes you could, using row-level security. See https://www.postgresql.org/docs/current/static/ddl-rowsecuri... and https://postgrest.org/en/v4.4/auth.html#users-and-groups

I have a GraphQL field that converts tweet-like content from the database into token lists so that individual clients don't need to implement their own tweet-parsing logic. I have many other examples.

If it was really feasible to bind our databases directly to our APIs, we wouldn't need to hire anyone except DBAs and front-end developers. But backend business logic is a thing. You may not need it, but most people do.

The database is more than just tables, for your particular example you could use a computed column https://postgrest.org/en/v5.0/api.html#computed-columns.

In general, custom business logic can be done through stored procedures/views/computed columns.

"Your scientists were so preoccupied with whether or not they could, they didn’t stop to think if they should."

You can do a lot in the database, a lot more than most developers realise, that doesn't mean it's a good idea.

Anyone from the GraphQL camp care to weigh in against this argument?

If your endpoints are backed 100% by a db, then you can get by with the postgrest approach.

However GraphQL resolvers can do much more beyond a simple DB query. E.g. you can call other services and combine their reponses. Or calculate the n-th digit of Pi. Or whatever you want to do.

The DB can do much more than simple queries, you can use stored procedures for any custom business logic, you can even use PL/Python in them.

See https://postgrest.org/en/v5.0/api.html#stored-procedures for more details.

I know, but stored procedures are a PITA in general. Last time I’ve checked there was no widespread standard way of version control for stored procedures, other than DROP PROCEDURE followed by CREATE PROCEDURE, which makes blue/green deloyment impossible/awkward.

You can treat SQL code as any other code and version your schema with git. Migrations can be handled with https://sqitch.org, and you can also reduce some of the work with https://www.apgdiff.com that will generate CREATE OR REPLACE function statements for you.

Also have to mention that when using PostgREST we encourage you to decouple your data schema(where your tables are) from your api schema(only views, stored procedures, computed columns), that way you can version your schemas(having "v1" schema, "v2", etc) and prevent breaking changes.

Reply on news.ycombinator.com