In the field of Computer Science and Engineering, few things come close to the durability and ubiquity of SQL.
As web or mobile developers, there’s usually a RESTful API backend that serves JSON responses over HTTP. More often than not, there’s a SQL DB used in these REST API services. However if we look closely, we find that the business logic is in Java/Kotlin/Python layer, whereas the DB is usually just a data store. Most web frameworks Spring Boot, Flask, Playframework etc when used for simple CRUD, tend to complicate the setup. Simple tasks get over-engineered which could be simplified by concise SQL queries. The leaky abstractions of an ORM, and it’s impedence mismatch make things even worse1.
I’ve seen developers go out of their way to avoid writing SQL, and instead move the logic one level up. I’ve seen cases where teams have written several µServices just to avoid fine-tuning SQL DBs, and now they’re paying extra price for maintaning these µServices.
SQL is a 46 year old battle tested technology, invented by Chamberlain and Boyce after learning about the relational model from Ted Codd almost 5 decades ago. Modern databases have evolved into really powerful systems, and it’s a shame if they’re used only as a dumb data-store2. They provide a rock solid platform with contraints, triggers, and stored procedures, which gurantee data integrity. In most cases CRUD programming could benefit tremendously by actively leveraging the DB and embracing the relational model.
PostgREST is a culmination of these pragmatic ideas. It’s a wonderful piece of software written in Haskell, and is quite performant3. PostgREST is a standalone web server that smartly turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. Postgrest officially supports a wide gamut of platforms like Linux (x86 and ARM), *BSD, Windows, and can easily be run on a RaspberryPi and docker.
In this series we’ll build a simple RESTful backend service using PostgREST, and secure the service using Authentication from Auth0. This setup could be used to easily setup a REST API in 30-45 minutes. Please note that this is not a one-size-fits-all solution, and this setup works where the REST API more or less matches the Database structure.
The following UML Component diagram shows the architecture of the system.
Note: The setup works with Any Auth provider that issues JWTs and providers public keys using JWKS, like ORY Hydra, Keycloak etc.
To kickstart the tutorial, in the next section we’ll setup the PostgreSQL datbase and define the DB entities.