In Codd, we trust

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.

  • Part 1: Configuring PostgreSQL, and defining the SQL entities
  • Part 2: Configuring Postgrest
  • Part 3: Integrating Auth0
  • Part 4: Test driving the setup using an iOS app.

Architecture

The following UML Component diagram shows the architecture of the system.

  • The Client invokes Auth0 SDK to login and get a signed JWT
  • Client makes HTTP requests with the JWT in HTTP headers
  • Postgrest service checks the JWT signature using public keys
  • If the key’s are verified, postgrest allows the operation

Prerequisites

  • Docker: We’ll use docker-compose as a building block
  • Auth0 Account: Auth0 provides free accounts which we’ll use for social login
  • SQL: You can brush up your SQL skills with this excellent tutorial at DataSchool
  • Optional: A Mac to run the iOS app to login.

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.

  1. Ted Neward’s popular essay sheds light on the problems with ORMs. ↩︎

  2. A nice overview of why SQL rocks even after 5 decades of discovery ↩︎

  3. TL;DR: Subsecond response times for up to 2000 requests/sec on Heroku free tier. More details here ↩︎