Building a BaaS using PostgreSQL

Pros

• easy to get started
• sensible defaults, avoid decision fatigue

Cons

• hard to customize
• lot of code

Abstraction

• we take a lot of backend stuff for granted
• trend: backend becoming more and more easy to setup, ex. GraphQL(content)
• frontend is getting more ready to consume REST API, ex. flavour of the month js frameworks popping up(form)
• more separation bet. form and content

Thin WAI written in Haskell on top of psql

Generic interface between application and webserver

No configuration or dependencies, just 1 executable for your favorite platform!

Usage: postgrest DB_URL (-a|--anonymous ROLE) [-s|--schema NAME]
                 [-p|--port PORT] [-j|--jwt-secret SECRET] [-o|--pool COUNT]
                 [-m|--max-rows COUNT]
  PostgREST 0.3.1.1 / create a REST API to an existing Postgres database

Available options:
  -h,--help                Show this help text
  DB_URL                   (REQUIRED) database connection string, e.g.
                           postgres://user:pass@host:port/db
  -a,--anonymous ROLE      (REQUIRED) postgres role to use for non-authenticated
                           requests
  -s,--schema NAME         schema to use for API routes (default: "public")
  -p,--port PORT           port number on which to run HTTP
                           server (default: 3000)
  -j,--jwt-secret SECRET   secret used to encrypt and decrypt JWT
                           tokens (default: "secret")
  -o,--pool COUNT          max connections in database pool (default: 10)
  -m,--max-rows COUNT      max rows in response (default: "infinity")

Follows the UNIX philosophy

The semantics

tables/views map to routes

• POST ~ INSERT
• GET ~ SELECT
• GET /1?foo=bar ~ SELECT WHERE foo=bar
• PATCH ~ UPDATE
• PUT ~ UPSERT
• DELETE ~ DELETE

Pagination/range headers ~ LIMIT and OFFSET

Auth ~ user roles

..because it's the emacs of databases!

"Postgres is the Emacs of databases." Craig Kerstiens https://speakerdeck.com/craigkerstiens/postgres-demystified-1

Supports json as first class citizen

A lot less verbose than XML

Native javascript!

Programmability

• plsql, plv8 etc
• fat models, thin controllers.

SSL

Always use ssl to serve rest apis, no exceptions!

Implementation

Use Nginx

Versioning

Why version APIs?

APIs keep changing

Avoid hair splitting

Implementation

Was done initially using schemas

Now offloaded to Nginx

• different schemas running in different ports
• nginx configured to route accordingly

Authentication

JWT

Open standard for passing claims between 2 parties.

User does HTTP requests with a role claim

Postgrest will switch to that role for the duration of the request

JWT other benefits

One key to rule them all.

Pushes everything to client.

Row level security

ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_todos ON todos  
  USING (author = basic_auth.current_email());

CREATE POLICY authors_eigencreate ON todos FOR INSERT
  WITH CHECK (
    author = basic_auth.current_email()
  );

CREATE POLICY authors_eigenedit ON todos FOR update
  USING (author = basic_auth.current_email())
  WITH CHECK (
    author = basic_auth.current_email()
  );

CREATE POLICY authors_eigendelete ON todos FOR delete
  USING (author = basic_auth.current_email());

Can be emulated for postgres <=9.4

http://blog.2ndquadrant.com/emulating-row-security-in-postgresql-9-4/

Self describing

GET /

Rudimentary HATEOS

Get only what you need

You can customize which columns are returned using the select parameter:

GET /people?select=age,height,weight

JSON drill-down

GET /people?employees->3->>id=eq.2

Other cool stuff

Bulk inserts

Set Content-Type: text/csv and do:

POST /people name,age,height J Doe,62,70 Jonas,10,55

Bulk updates

Mark all people whose age < 13 as "child".

PATCH /people?age=lt.13 { "person_type": "child" }