Skip to main content

GDL Local Migrations

How to run Prisma migrations against the local gdl-postgres databases (sanctum_catalog, sanctum_characters) without falling into the drift trap.

TL;DR​

# Set the connection URLs (matches docker-compose.dev.yml host port 5434)
export GDL_CATALOG_DATABASE_URL="postgresql://sanctum:sanctum@localhost:5434/sanctum_catalog"
export GDL_CHARACTERS_DATABASE_URL="postgresql://sanctum:sanctum@localhost:5434/sanctum_characters"

# Apply pending migrations
pnpm db:migrate:gdl

# If it fails with drift, nuke and rebuild
pnpm db:reset:gdl

Why this exists​

gdl-postgres was originally populated via prisma db push / SQLx during early dev. That bypasses the migration history, so by the time the project adopted prisma migrate deploy, the schema and the _prisma_migrations table no longer agreed.

Prod handles the one-time baseline in services/gdl-node/scripts/migrate-catalog.sh (and the characters equivalent) — but those scripts were written for ECS and assumed Secrets-Manager-injected env vars. There was no clean local entry point.

Architecture​

One shared runner, two thin wrappers, the same code path in prod and dev.

services/gdl-node/scripts/
├── _run-prisma-migrate.sh   ← shared runner (prod + --local modes)
├── migrate-catalog.sh        ← wrapper: sanctum_catalog
└── migrate-characters.sh     ← wrapper: sanctum_characters

The wrappers are 5 lines each. ECS task definitions keep calling them with no args (prod mode). Local dev calls them with --local, which:

  • Trusts the GDL_CATALOG_DATABASE_URL / GDL_CHARACTERS_DATABASE_URL env var as-is (no SSL injection, no URL-from-secrets construction).
  • Fails fast with a helpful error if the URL env var is unset.

Commands​

All scripts are exposed via pnpm. From the repo root:

CommandWhat it does
pnpm db:migrate:gdlMigrate both catalog and characters DBs
pnpm db:migrate:gdl:catalogCatalog only
pnpm db:migrate:gdl:charactersCharacters only
pnpm db:reset:gdlReset both DBs (drop + re-apply all migrations)
pnpm db:reset:gdl:catalogReset catalog only
pnpm db:reset:gdl:charactersReset characters only

Or directly from services/gdl-node/:

pnpm db:migrate           # both
pnpm db:migrate:catalog   # catalog only
pnpm db:reset             # both, nuked

Drift policy: reset, don't patch​

If db:migrate:gdl fails with errors like relation "..." does not exist or "schema drifted", run pnpm db:reset:gdl. Dev data is disposable.

The runner does not auto-resolve drifted migrations. Marking a migration as applied just because "the column already exists" silently hides any other effects of that migration — missing indexes, FKs, defaults, data backfills, triggers. You end up with a green _prisma_migrations table and a subtly wrong schema, which is the worst possible outcome.

The Prisma-blessed answer to local drift is prisma migrate reset. We use it.

Going forward​

  • Local dev: prisma migrate dev (creates + applies new migrations during schema work). pnpm db:migrate:gdl is for catching up an existing local DB to current master.
  • Prod / CI: prisma migrate deploy (applies only, never generates). This is what migrate-catalog.sh already does on ECS.
  • prisma db push: prototyping only, on throwaway DBs. Do not use against gdl-postgres going forward.