Database migrations

Olympus has migrations across several components: Kratos, Hydra, and Athena each have their own migration system.

Kratos migrations

podman exec ciam-kratos kratos migrate sql up

Kratos's migrations are baked into the binary; they run against the configured DB on each version upgrade.

For dry-run / preview:

podman exec ciam-kratos kratos migrate sql up --dry-run

Hydra migrations

podman exec ciam-hydra hydra migrate sql up postgres://...

Same pattern.

Athena migrations

Athena uses Drizzle Kit:

podman exec ciam-athena bunx drizzle-kit migrate

Migrations are SQL files in athena/migrations/. Auto-generated from schema edits via drizzle-kit generate.

Pre-upgrade migrations

Before bumping a service version, check the changelog for migration requirements:

# Kratos 1.1 → 1.2
# (read CHANGELOG)
# Run migration:
podman exec ciam-kratos kratos migrate sql up
# Then upgrade:
sed -i 's/oryd\/kratos:v1.1/oryd\/kratos:v1.2/' docker-compose.yml
podman-compose up -d ciam-kratos

If you forget to migrate, Kratos refuses to start with "schema mismatch."

Zero-downtime pattern

For breaking schema changes, use expand/contract:

Phase 1: Expand

-- Add new column nullable
ALTER TABLE identities ADD COLUMN new_email TEXT;

Old code still works (new column ignored).
New code can read/write old or new column.

Deploy new code that writes to both email and new_email.

Phase 2: Backfill

UPDATE identities SET new_email = email WHERE new_email IS NULL;

Now both columns have data.

Phase 3: Contract

ALTER TABLE identities ALTER COLUMN new_email SET NOT NULL;
ALTER TABLE identities DROP COLUMN email;
ALTER TABLE identities RENAME COLUMN new_email TO email;

Deploy code that reads email (formerly new_email).

Three deploys but zero downtime, fully reversible at each phase.

Long-running migrations

A migration that takes 10+ minutes locks the table. Bad for prod.

For Postgres-specific tricks:

Index without lock

CREATE INDEX CONCURRENTLY ix_identities_role ON identities ((traits->>'role'));

Builds without exclusive lock. Slower than CREATE INDEX but doesn't block.

Caveat: if it fails midway, you get an invalid index. Drop and retry.

Add column without default

-- Slow (rewrites table):
ALTER TABLE big_table ADD COLUMN x TEXT DEFAULT 'foo';

-- Fast (no rewrite):
ALTER TABLE big_table ADD COLUMN x TEXT;
ALTER TABLE big_table ALTER COLUMN x SET DEFAULT 'foo';
-- Then backfill in batches.

Postgres 11+ no longer requires rewrite for new columns with constant defaults, check your version.

Backfill in batches

DO $$
DECLARE batch_size INT := 1000; affected INT;
BEGIN
  LOOP
    UPDATE big_table SET x = compute(x) WHERE x IS NULL LIMIT batch_size;
    GET DIAGNOSTICS affected = ROW_COUNT;
    EXIT WHEN affected = 0;
    PERFORM pg_sleep(0.1);  -- breath
  END LOOP;
END $$;

Migration on Kratos's behalf

Kratos uses fizz (a Go migration library). Each migration is a numbered SQL file. To preview what a Kratos upgrade will do:

podman exec ciam-kratos ls /home/ory/migrations/

Sorted by version, you can read upcoming migrations.

Rollbacks

Migrations are forward-only by default. To "undo," you need an inverse migration:

-- 20260515_add_role.up.sql
ALTER TABLE identities ADD COLUMN role TEXT;

-- 20260515_add_role.down.sql
ALTER TABLE identities DROP COLUMN role;

Then:

podman exec ciam-athena bunx drizzle-kit migrate-down

Kratos and Hydra don't have built-in down migrations. For their schemas, design forward-only.

Best practices

Always test migrations against a copy of prod data first (use a recent backup).
Run migrations during low-traffic hours.
Have a backup taken right before.
Monitor pg_stat_activity during long migrations.
Plan for revert: what's your action if it fails partway? Often: restore from backup.

Audit trail

Migrations table tracks what's been run:

SELECT * FROM schema_migration ORDER BY version DESC LIMIT 10;

Each ran migration is recorded. Re-running migrate is idempotent.

Database migrations

On this page