PostgREST

PostgREST is a standalone web server that automatically transforms a PostgreSQL database into a RESTful API. It eliminates the need to write manual backend code for CRUD (Create, Read, Update, Delete) operations by leveraging the database's existing structure, constraints, and permissions to define API endpoints and their behavior.

1. Start PostgreSQL

Either use a managed service or self-host PostgreSQL.

2. Add PostgREST Service

Add a PostgREST service to your application:

app/compose.yaml

  postgrest:
    image: postgrest/postgrest:v12.2.8
    environment:
      PGRST_DB_ANON_ROLE: anon
      PGRST_DB_URI: postgres://authenticator:${PGRST_AUTHENTICATOR_PASS:?}@postgres:5432/app

If self-hosting PostgreSQL, be sure to also connect to its network in the PostgREST service:

app/compose.yaml

services:
  postgrest:
    networks:
      - default
      - db_default

networks:
  db_default:
    external: true

Optionally in development, increase the log level:

app/compose.override.yaml

  postgrest:
    environment:
      PGRST_LOG_LEVEL: debug

2. Add Routes

app/caddy/Caddyfile

# PostgREST
handle_path /rest/* {
  reverse_proxy http://postgrest:3000
}

handle /rpc/* {
  reverse_proxy http://postgrest:3000
}

3. Add Migrations

Some changes need to be made to prepare your Postgres schema for PostgREST.

Add this to your database environment:

db/.env

PGRST_AUTHENTICATOR_PASS=pass

And expose it in the Postgres service:

services:
  postgres:
    environment:
      PGRST_AUTHENTICATOR_PASS: ${PGRST_AUTHENTICATOR_PASS:?}

And add a migration (migration filenames are only suggestions):

db/postgres/migrations/00-init_postgrest.sql

-- Initial migrations to setup PostgREST

-- Set values here to reduce the chance of env vars being logged
\set pgrst_authenticator_pass '$PGRST_AUTHENTICATOR_PASS'

-- Revoke execute on functions from public
-- See https://docs.postgrest.org/en/stable/explanations/db_authz.html#functions
alter default privileges revoke execute on functions from public;

begin;

-- Create authenticator and anonymous roles. The authenticator role is used for
-- connecting to the database. Anon is for non-authenticated users. 
create role authenticator noinherit login password :'pgrst_authenticator_pass';
create role anon;
grant anon to authenticator;  -- Allow authenticator to switch to anon.

commit;