4 ms·

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

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.

stuartaxelowen 8y ago
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.
- CodesInChaos 8y ago
  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.
lsorber 8y ago
Anyone from the GraphQL camp care to weigh in against this argument?
- tzahola 8y ago
  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.
  - steve-chavez 8y ago
    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 https://postgrest.org/en/v5.0/api.html#stored-procedures for more details.
    - tzahola 8y ago
      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.
      - steve-chavez 8y ago
        You can treat SQL code as any other code and version your schema with git. Migrations can be handled with https://sqitch.org https://sqitch.org, and you can also reduce some of the work with https://www.apgdiff.com 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.
icebraining 8y ago
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?
- hucker 8y ago
  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.
  - icebraining 8y ago
    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?
    - hucker 8y ago
      Good point, that's true. But yes you could, using row-level security. See https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html https://www.postgresql.org/docs/current/static/ddl-rowsecuri... and https://postgrest.org/en/v4.4/auth.html#users-and-groups https://postgrest.org/en/v4.4/auth.html#users-and-groups
- andrewingram 8y ago
  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.
Munksgaard 8y ago
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-design/ https://www.graphile.org/postgraphile/postgresql-schema-desi...

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.

stuartaxelowen 8y ago

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.

CodesInChaos 8y ago
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.

lsorber 8y ago

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

tzahola 8y ago
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.
- steve-chavez 8y ago
  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 https://postgrest.org/en/v5.0/api.html#stored-procedures for more details.
  - tzahola 8y ago
    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.
    - steve-chavez 8y ago
      You can treat SQL code as any other code and version your schema with git. Migrations can be handled with https://sqitch.org https://sqitch.org, and you can also reduce some of the work with https://www.apgdiff.com 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.

icebraining 8y ago

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?

hucker 8y ago
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.
- icebraining 8y ago
  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?
  - hucker 8y ago
    Good point, that's true. But yes you could, using row-level security. See https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html https://www.postgresql.org/docs/current/static/ddl-rowsecuri... and https://postgrest.org/en/v4.4/auth.html#users-and-groups https://postgrest.org/en/v4.4/auth.html#users-and-groups
andrewingram 8y ago
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.

Munksgaard 8y ago

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-design/ https://www.graphile.org/postgraphile/postgresql-schema-desi...