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 Postgres

Either use a managed service or self-host PostgreSQL.

2. Add the PostgREST Service

Add to the environment file:

app/.env

# PostgREST
PGRST_AUTHENTICATOR_PASS=pass

Add a PostgREST service to your application:

app/compose.yaml

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

If self-hosting Postgres, also connect to its network:

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 your database schema prepare it for PostgREST. To do this we add a database migration.

This section assumes you're self-hosting Postgres and using the migrations system described here. Otherwise adapt this to your setup.

Add PGRST_AUTHENTICATOR_PASS to the Postgres environment:

db/compose.yaml

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

Create the migration script:

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;
alter default privileges revoke select, insert, update, delete on tables from public;
alter default privileges revoke usage, select on sequences 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;