Comment by atom_arranger | Hacker News Reader

Comment by atom_arranger | Hacker News Reader

Parent Full thread

I've used Postgraphile to do this. GraphQL not REST, same idea though, autogenerated API from stored procedures, it's pretty neat.

Pros:

  - Can query DB multiple times, conditionally, without making multiple trips to DB, since your code for a certain procedure is all in the DB.
  - Procedures are accessible using any DB client.

Cons:

  - Version control of these procedures is not as nice as normal code. Graphile Starter has some tools for snapshotting the DB schema that help, but the DX is still not great.
  - Scaling your DB is more costly than scaling compute, so from a cost/scaling perspective this might not be the best idea.

I'd be nervous about the testability/verifiability of it. I like treating the DB as infrastructure, and I get nervous when the infrastructure gets too smart.

Maybe I'm just stuck in the olden days and haven't yet embraced the brave new world where no one can run a full local instance because it depends on queues and storage backends and whatever else supplied by a cloud vendor. But even in my little world, I feel like I experience this with overly-smart Jenkins pipelines that can't really be executed except in production or an expensive-to-maintain clone of production.

I think the sweet spot for Database smartness is ensuring data integrity, at close to domain level.

Having views instead of tables (hiding the actual implementation details), having procedures to interface with underlying tables and having procedures to ensure data integrity for incoming changes is a great use for procedures.

Too often, I see databases used as simply storage boxes, when they are capable of being much more.

I think most people have seen the app server crash and burn while the data is nice and safe in the DB. Seems sketchy to merge them. But some some usage it would be simpler.

Reply on news.ycombinator.com