Hacker News Reader

Comment by ryeguy | Hacker News Reader

IMO modeling your api directly off of your database tables is often an anti-pattern. One of the biggest mistakes I see people make is assuming their api's data model has to match their database's. It's common to introduce resources on the api side that may not have their own table in the database. For example, you could have a table that has a "type" flag (to model some kind of inheritance), but on the api you expose these as separate resources. Or you could have a larger resource on the api, but it's actually backed by several tables.

Graphql is also more generic and allows you to fetch from multiple data sources. These could be databases, apis, or wherever. You write code that fulfills each column and graphql assembles a single object.

I could see postgrest being ok for prototyping or simple apis, but it seems like one of those things that you'd grow out of eventually.

View on HN

If you treat PostgREST as an ORM, then you can easily decouple the database data model from the API representation. I imagine there are scenarios in which PostgREST would outperform certain ORMs implemented in dynamically-typed languages.

This is exactly my usecase. I replaced my NodeJs ORM layer with PostgREST and express apis layer with Aws ApiGateway.

Have you heard of views and FDW?

Views are only a solution for reads, not writes. FDWs are not an elegant solution to anything related to this. Think microservices that already have an api defined.

Many views are "auto updatable" in Postgres, meaning the db knows how to seamlessly pass updates down to the underlying relation. https://www.postgresql.org/docs/current/static/rules-views.h...

For more updates to more complicated views (such as some joins), you can use a trigger as also described in the linked docs above.

Your original comment said it's bad to couple your api with tables so I am agreeing with you, use views to decouple. for writes, you can use stored procedures for corner cases although most of the apps work with one model at a time, so auto-updatable views work most of the time. So if we take into account that most apps talk to one database, i have most of it covered with simple views since usually you have a 80/20 read/write split? I'll take that.

FDW are very elegant for reading data from other systems. If you can read from twitter https://github.com/umitanuki/twitter_fdw you can read from anything although I don't understand why you brought up microservices, this is one one of the microservices (that is talking to the db), it's not the thing trying to unite all microservices. You can have GraphQL on top of PostgREST just fine (https://subzero.cloud).

Also doesn't PostgREST handle PostgreSQL custom upgradable view?

For instance, I already use this feature to present jsonb attributes as columns and edit them with a tool that is unaware of jsonb (QGIS). (NB: But not in a PostgREST context, using native connection)

Reply on news.ycombinator.com