All Articles

TIL: PostgREST & Postgraphile

Developers often seek ways to make their codebase as homogenous as possible, in the belief that it’s easier to maintain an application when everything looks and behaves similarly. This can be true to an extent - it’s why we settle on certain architectural patterns, code styling norms, etc.

This is partly why backend developers lean heavily on ORMs - it’s easier to think in your target language alone, rather than worry about maintaining SQL with C#, Python, etc.

But the more I’ve learned about SQL, the more I wonder: isn’t it odd that we build further abstractions on top of an already declarative language like SQL?

I recently discovered a couple tools that pose a different solution: what if we generate our API from our SQL schema?

PostgREST and Postgraphile do just this - point them at a live Postgres database, and you instantly have a working API (RESTful or GraphQL, depending on which tool you choose).

Need to specify some unique business logic that’s not handled by default CRUD operations? Since these tools are based on Postgres, you can use language extensions such as PLV8 to use JavaScript in your stored procedures & functions, allowing you to treat your Postgres database like your personal serverless platform.

Amazingly, you can even use JWT authentication and can use Postgres’ row level security for authorization.