Add database migrations to your service

A service that applies its schema every time it starts is fine for a first table and wrong for everything after it: there is no record of what was applied, no way back, and the schema changes while the old release is still serving. This tutorial replaces that with migrator, the platform’s migration runner. By the end you will have a versioned migration directory, you will have applied and rolled it back against a real Postgres, and your chart will run migrations as a deploy step rather than startup code.

Expect about thirty minutes.

Prerequisites

Set a shell variable for the image so the commands below stay short:

export MIGRATOR=registry.gitlab.com/gitlab-org/theseus/migrator:v1

Step 1: Turn the startup schema into a first migration

A schema-at-startup service ships db/schema.sql and applies it on boot. Create the migration directory and scaffold the first pair of files:

mkdir -p db/migrations
docker run --rm -v "$PWD/db/migrations:/migrations" $MIGRATOR new create_greetings

The output names two files. The version prefix is a timestamp, so yours will differ:

/migrations/20260928101500_create_greetings.sql
/migrations/down/20260928101500_create_greetings.down.sql

Move the schema into the forward file, dropping the IF NOT EXISTS. A migration runs exactly once, so idempotency guards only hide mistakes:

-- db/migrations/20260928101500_create_greetings.sql
CREATE TABLE greetings (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT        NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Write the backward migration by hand. It is reviewed like any other SQL:

-- db/migrations/down/20260928101500_create_greetings.down.sql
DROP TABLE greetings;

Keep db/schema.sql for now; sqlc still generates from it. You will stop applying it at startup in Step 8.

Step 2: Validate the directory

docker run --rm -v "$PWD/db/migrations:/migrations" $MIGRATOR validate

Validation is offline. It checks that every forward file has a down/ counterpart and vice versa, that names follow <version>_<name>.sql, that versions are unique, and that every file parses as PostgreSQL. The output is one JSON line per violation with the file and the rule, or nothing and exit 0.

Try breaking it: delete the down file and run validate again. You get a missing-down violation naming the forward file. Restore it before moving on.

Step 3: Apply against a local Postgres

Start a throwaway database and run the migration into it:

docker network create migrate-demo
docker run -d --rm --name pg --network migrate-demo \
  -e POSTGRES_USER=app -e POSTGRES_PASSWORD=app -e POSTGRES_DB=app postgres:16

docker run --rm --network migrate-demo \
  -v "$PWD/db/migrations:/migrations" \
  -e DATABASE_URL=postgres://app:app@pg:5432/app?sslmode=disable \
  $MIGRATOR up

up validates first, connects, takes a database lock so only one runner applies at a time, and applies every pending migration in version order. Every run ends with a single summary line:

{"msg":"migrator finished","command":"up","outcome":"success","exit_code":0,"applied":1,"to_version":"20260928101500","duration_s":0.41}

Ask the database what it thinks:

docker run --rm --network migrate-demo \
  -v "$PWD/db/migrations:/migrations" \
  -e DATABASE_URL=postgres://app:app@pg:5432/app?sslmode=disable \
  $MIGRATOR status

{"current_version":"20260928101500","target_version":"20260928101500","pending":[],"applied":["20260928101500"],"partial":false}

Run up a second time. It applies nothing and exits 0. That is the property the deploy relies on: a re-run hook, or two runners racing each other, is harmless.

Step 4: Add a second migration

docker run --rm -v "$PWD/db/migrations:/migrations" $MIGRATOR new add_greeting_language

-- db/migrations/<version>_add_greeting_language.sql
ALTER TABLE greetings ADD COLUMN language TEXT NOT NULL DEFAULT 'en';

-- db/migrations/down/<version>_add_greeting_language.down.sql
ALTER TABLE greetings DROP COLUMN language;

Run up again. The summary reports applied: 1 and status lists both versions.

Step 5: Roll back, then forward again

Rollback is an operator action, never automatic. Roll back to the first version by passing its number:

docker run --rm --network migrate-demo \
  -v "$PWD/db/migrations:/migrations" \
  -e DATABASE_URL=postgres://app:app@pg:5432/app?sslmode=disable \
  $MIGRATOR down --to-version 20260928101500

down runs the second migration’s .down.sql and deletes its history row in one transaction. status now shows one applied and one pending. Run up once more and you are back at the head. This round trip, up, down, up, is the check worth running in CI on every change to the directory.

Step 6: Watch the guard catch an edited migration

Edit the first migration after it has been applied, for example add a comment to create_greetings.sql, then run up:

"error_message":"applied migration 20260928101500 (20260928101500_create_greetings.sql) has changed since it was applied"

Exit code 1, nothing applied. Migrator stores a hash of every file it applies and refuses to continue if history has been rewritten. Revert the edit. The rule to take away: an applied migration is immutable, and a change of mind is a new migration.

Stop the database when you are done:

docker rm -f pg && docker network rm migrate-demo

Step 7: Package the migrations as a derived image

Migrations travel to production as an image layered on the migrator base, built and tagged alongside the service image. Add db/Dockerfile:

FROM registry.gitlab.com/gitlab-org/theseus/migrator:v1
COPY db/migrations/ /migrations/

Build it locally to prove it works. The image’s entrypoint is migrator, so validate needs no volume this time:

docker build -f db/Dockerfile -t my-service-migrations:dev .
docker run --rm my-service-migrations:dev validate

The base image is published for linux/amd64 and linux/arm64, so this build is native on an Apple Silicon machine, and the image runs on an arm64 development cluster as well as in CI and production.

In CI this is a second job next to the existing build, using the same .docker_buildx_base from common-ci-tasks. Tag it with the release tag: the chart hook you add next resolves the image at .Chart.AppVersion, so ${APP_VERSION} (from the chart-versions job), not ${CI_COMMIT_SHORT_SHA}:

build:migrations:
  stage: release
  extends: [.docker_buildx_base]
  needs:
    - job: "📝 [.runway/fairway.yaml] Helm chart versions"
      artifacts: true
  variables:
    DOCKER_BUILD_FILE: db/Dockerfile
  before_script:
    - test -n "$APP_VERSION"
    - export DOCKER_DESTINATION="${CI_REGISTRY_IMAGE}/migrations:${APP_VERSION}"

Step 8: Run the migrations before the app starts

Migrations run as a Helm hook (pre-upgrade,post-install) that ships inside your chart. Fairway generates the chart from .runway/fairway.yaml but leaves anything under chart/templates/custom/ alone, and the CI component packages it with the rest. The template is maintained in the migrator repository and is copied unchanged:

mkdir -p chart/templates/custom
curl -sSfL -o chart/templates/custom/migrate-job.yaml \
  https://gitlab.com/gitlab-org/theseus/migrator/-/raw/main/contrib/fairway/migrate-job.yaml

Tell it where your migrations image lives, in the manifest:

# .runway/fairway.yaml
spec:
  values:
    custom:
      migrations:
        repository: registry.gitlab.com/<group>/<service>/migrations

What the file does, in brief:

• Declares a Job annotated helm.sh/hook: pre-upgrade,post-install: on upgrades Helm runs it before applying the new Deployment and fails the upgrade if it exits non-zero; on the chart’s very first install it runs after the resources are applied (the ConfigMap and secret it mounts do not exist earlier), retrying while infrastructure finishes bootstrapping.
• Runs your migrations image at .Chart.AppVersion, the same release tag as the app image.
• Mounts the same three things the app container gets: the infrastructure ConfigMap at /etc/labkit, the database secret under /secrets/infrastructure/postgresql, and a writable /tmp. Migrator’s LabKit Postgres client reads the connection from those mounts, so no credentials appear anywhere in your repository.

Commit chart/templates/custom/migrate-job.yaml. If your generated chart sits in chart/ locally, render the hook to see the Job it produces:

helm template svc ./chart --show-only templates/custom/migrate-job.yaml \
  --set infrastructure.postgresql.secretRef.name=example \
  --set custom.migrations.repository=registry.gitlab.com/<group>/<service>/migrations

If your service still applies its schema at startup, remove that now. Older scaffolds registered an applier in cmd/server/main.go:

// delete this line
a.Register(&store.SchemaApplier{Client: pgClient})

Services scaffolded from the current template have no startup applier, so there is nothing to remove.

From here the schema is the migration directory’s responsibility. On every upgrade the hook runs up first. Exit 0 means the schema is at the target version and the rollout proceeds. Non-zero halts it: the new Deployment is never applied and the old release keeps serving.

What you built

• A db/migrations/ directory that is the single source of truth for your schema, with a hand-written reverse for every step.
• A derived image that carries those migrations through the same pipeline as your service.
• A migration hook in your chart that runs those migrations on the first install and before each upgrade, and blocks the rollout when it fails.

What’s next

• Run database migrations with migrator for the CI checks, adopting a database that already has a schema, and rollback in production.
• The migrator README is the contract reference: file rules, CLI flags, environment variables, exit codes.
• Decision: database migrations ship as a forward/backward contract records why the pattern looks like this.