pdf-quiz-generator/docs/migrations.md

Database migrations (Alembic)

Overview

A migration is a versioned, ordered change to the database schema — adding a column, renaming a table, changing a type. Each change lives in a Python file under backend/alembic/versions/. Alembic records applied migrations in an alembic_version table inside Postgres and only runs files that have not yet been applied.

Before Alembic was introduced, the schema was bootstrapped via Base.metadata.create_all() in backend/app/main.py, which only creates missing tables — it never alters existing ones. Every column change required a manual ALTER TABLE. Alembic replaces that workflow: schema changes become versioned, reversible, and reproducible across environments.

Current setup

• backend/alembic.ini contains no hardcoded URL; backend/alembic/env.py injects DATABASE_URL from the container's environment.
• The live production DB is stamped at the latest revision in backend/alembic/versions/.
• Base.metadata.create_all() is still called at startup as a safety net for fresh deploys. Do not remove it without first generating a complete baseline migration from the current live schema.

Workflow

# Current revision / available heads
docker compose exec backend alembic current
docker compose exec backend alembic heads

# Create a new migration (auto-diffs SQLAlchemy models against the live DB)
docker compose exec backend alembic revision --autogenerate -m "add some column"
# Review the generated file under backend/alembic/versions/ BEFORE applying

# Apply pending migrations
docker compose exec backend alembic upgrade head

# Roll back the last migration
docker compose exec backend alembic downgrade -1

# Mark the DB as being at a revision without running anything (use with care)
docker compose exec backend alembic stamp <revision>

After adding a migration in development, rebuild the backend image so the new file is baked into the container:

docker compose build backend celery
docker compose up -d backend celery --force-recreate

When to write a migration

Any schema change:

• New column, dropped column, renamed field
• New table, dropped table
• Altered index
• New foreign key
• Changed nullability or default value

Always: edit model → generate migration → review → apply → commit both files together.

Gotchas

• Migrations run inside a transaction. A failed migration rolls back cleanly, so the DB stays consistent.
• --autogenerate does not catch everything. The following must be hand-edited into the generated file:
  • server_default changes
  • CHECK constraints
  • Enum value additions
  • Data migrations (moving rows around as part of a schema change)
• The alembic_version table should only ever have one row. Multiple rows indicate branched heads — run alembic merge to reconcile.
• Never edit a migration after it has been applied to a shared environment — write a new migration instead.

Rollback patterns

# Undo the last migration
docker compose exec backend alembic downgrade -1

# Jump to a specific revision (by hash prefix)
docker compose exec backend alembic downgrade 9bac7bf02e38

# Jump all the way back to an empty schema (rarely desired in production)
docker compose exec backend alembic downgrade base