Postgres Migrations

This is a simple system for performing database schema migrations in SuperStack when self-hosting PostgreSQL.

It consists of:

1. A migrations directory for your SQL scripts.
2. A migrate script to perform migrations and keep track of completed ones.
3. A startup.sh which runs migrations it at first startup.
4. Some adjustments to the Postgres image.

1. Create directory for SQL scripts

mkdir -p db/postgres/migrations

2. Create a migrate script

mkdir -p db/postgres/bin

db/postgres/bin/migrate

#!/bin/bash
set -euo pipefail

MIGRATIONS_DIR=/etc/superstack/migrations
APPLIED_MIGRATIONS_FILE="/var/lib/postgresql/data/.applied_migrations"

touch "$APPLIED_MIGRATIONS_FILE"

# Get sorted lists of all and applied migration filenames (no paths)
all_files=$(printf '%s\n' "$MIGRATIONS_DIR"/*.sql | xargs -n1 basename | sort)
applied_files=$(sort "$APPLIED_MIGRATIONS_FILE")
pending_files=$(comm -23 <(echo "$all_files") <(echo "$applied_files"))

# Apply pending migrations
if [[ -n "$pending_files" ]]; then
  while IFS= read -r filename; do
    echo "-- $filename" >&2
    envsubst < "$MIGRATIONS_DIR/$filename" | \
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
    echo "$filename" >> "$APPLIED_MIGRATIONS_FILE"
  done <<< "$pending_files"
else
  echo "Nothing to deploy" >&2
fi

Make it executable:

chmod +x db/postgres/bin/migrate

3. Run migrations at startup

Add the following script to run migrations at startup:

mkdir -p db/postgres/docker-entrypoint-initdb.d

db/postgres/docker-entrypoint-initdb.d/startup.sh

#!/bin/bash

exec migrate

Make it executable:

chmod +x db/postgres/docker-entrypoint-initdb.d/startup.sh

4. Customize Postgres

Add a Dockerfile to customise your Postgres image:

db/postgres/Dockerfile

FROM postgres:17

COPY docker-entrypoint-initdb.d /docker-entrypoint-initdb.d
COPY migrations /etc/superstack/migrations
COPY bin /postgres-bin
ENV PATH="/postgres-bin:$PATH"

# gettext is needed for envsubst
RUN apt-get update && apt-get install -y \
  gettext

# Cleanup after the install command
RUN rm -rf /var/lib/apt/lists/* /var/cache/apt/archives/*

# Lastly, set the WORKDIR - This is the default anyway but it may be changed
# above by the user when installing extensions
WORKDIR /var/lib/postgresql

For development, mount some directories into the Postgres service:

db/compose.override.yaml

services:
  postgres:
    volumes:
      - ./postgres/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d:ro
      - ./postgres/migrations:/etc/superstack/migrations:rw
      - ./postgres/bin:/postgres-bin:ro

Lastly, remove the image: postgres from your Postgres service (or change it to your own container registry url) and add a build: section:

services:
  postgres:
    build:
      context: ./postgres

That's it!

✍️ Writing Migrations

Each file should be:

• An .sql file
• Numbered in order (e.g. 00-init.sql, 01-extensions.sql, 02-auth.sql)
• Written in plain SQL
• But can include environment variables.

Here's an example migration script:

begin;

create schema api;
set search_path to api;

create table director (
  id serial primary key,
  name text not null
);

create table movie (
  id serial primary key,
  name text not null,
  director_id integer references director(id)
);

commit;

💡 Tip: It’s best to place your application tables inside a dedicated schema (like api) rather than the default public schema.

▶️ Applying Migrations

When the Postgres container starts with no existing data, SuperStack will automatically run migrations once.

After the first startup, migrations will only run if you manually apply them.

To apply your migrations, run:

bin/postgres migrate

This command will:

1. Apply new migrations, in filename order.
2. Record applied migrations in a file named .applied_migrations.

Already-applied scripts are skipped on subsequent runs.

💡 bin/postgres is a small script that effectively aliases docker compose exec postgres

🔁 Transactions

Use begin; and commit; to wrap statements in a transaction. This ensures that all changes are applied atomically. Any statements outside of transactions will be auto-committed.

Avoid wrapping non-transactional operations in a transaction — these will cause errors if used inside begin ... commit. Examples of non-transactional statements include:

ALTER SYSTEM
CREATE DATABASE
CREATE EXTENSION
CREATE ROLE
CREATE TABLESPACE
DROP DATABASE
DROP EXTENSION
DROP TABLESPACE

Using Environment Variables

Env vars can be used in migrations like this:

\set pgrst_authenticator_pass '$PGRST_AUTHENTICATOR_PASS'

create role authenticator noinherit login password :'pgrst_authenticator_pass';

Putting them into variables first with \set reduces the chance of them being logged.

Suggested File Layout

SuperStack doesn’t enforce any particular migration file names or layout, but here’s a simple structure you might adopt:

00-init_postgrest.sql     # Setup PostgREST (see PostgREST wiki page)
01-create_extensions.sql  # "create extension" statements
02-public_functions.sql   # Common functions used by other schemas/migrations
03-auth_schema.sql
04-core_schema.sql
05-jobs_schema.sql
10-api_schema.sql         # The exposed schema, Views and RPC functions
11-...                    # Further iterations
90-roles.sql
91-grants.sql

While developing, you can reset and rebuild the database from scratch as often as needed:

docker compose down --volumes
docker compose up -d

Once you’ve deployed to production (or another persistent environment), avoid recreating the database. Instead:

• Add new migrations starting from 06-... onwards.
• Apply them with:

bin/postgres migrate

Or in other environments where bin/postgres isn't available:

docker compose exec postgres migrate

This approach keeps early development simple while providing a clear, ordered history once the database must be preserved.

🔄 Nuke Everything

If you want to start fresh, wipe your database and re-run all migrations from scratch:

docker compose down --volumes
docker compose up -d