Skip to content

migrations.md

Latest commit

 

History

History
344 lines (250 loc) · 11.8 KB

migrations.md

File metadata and controls

344 lines (250 loc) · 11.8 KB
Raw

🗄️ Database Migrations Manual (Alembic)

This manual provides instructions and operational runbooks for managing PostgreSQL database schema migrations in DGG-PM using Alembic across both Development and Production environments.

📑 Table of Contents

  1. Architecture & Overview
  2. Development Workflow
  3. Production Deployment & Runbook
  4. CLI Command Quick Reference

1. Architecture & Overview

DGG-PM uses Alembic integrated natively into its Hexagonal Architecture:

  • Configuration File: alembic.ini at repository root.
  • Migration Directory: src/adapters/db/migrations/
    • env.py: Asynchronous migration environment utilizing SQLAlchemy's async engine over asyncpg.
    • script.py.mako: Revision template matching modern Python 3.13 typing standards.
    • versions/: Sequential migration files (e.g., 0001_initial_schema.py).
  • Database Engine: Direct reuse of DATABASE_URL (postgresql+asyncpg://...). No secondary synchronous DB drivers (psycopg2) are needed.
  • Metadata Target: Base.metadata (src.adapters.db.tables).

2. Development Workflow

Environment Setup

Development commands can be run via make shortcuts or directly with uv run:

# Apply pending migrations
make db-migrate
# or: uv run alembic upgrade head

Local environment variables are managed in .env. Ensure your DATABASE_URL is set:

DATABASE_URL=postgresql+asyncpg://postgres:postgrespassword@localhost:5432/dgg_pm
AUTO_RUN_MIGRATIONS=true

Note

AUTO_RUN_MIGRATIONS defaults to false in code to prevent multi-replica startup concurrency races in production. It is set to true in .env for seamless local development. When true, starting the application via make run automatically checks and applies pending migrations on startup.

Creating a New Migration

Follow this 4-step workflow whenever modifying domain entities or table schemas:

Step 1: Update Table Definitions

Modify or add ORM tables in src/adapters/db/tables.py. For example, adding a new column:

class TaskTable(Base):
    ...
    estimated_hours = Column(Integer, nullable=True)

Step 2: Autogenerate the Revision

Generate a new migration script using the helper command or raw Alembic:

# Using the helper command (automatically assigns next 4-digit sequential ID, e.g. 0002)
make db-revision MSG="add_estimated_hours_to_tasks"
# or: uv run python scripts/generate_revision.py -m "add_estimated_hours_to_tasks"

# Or using raw alembic directly (requires explicit --rev-id for sequential naming)
uv run alembic revision --autogenerate --rev-id "0002" -m "add_estimated_hours_to_tasks"

This creates a new file under src/adapters/db/migrations/versions/0002_add_estimated_hours_to_tasks.py.

Step 3: Inspect and Refine the Migration Script

Important

Always review autogenerated migration scripts before committing. Autogenerate detects table, column, index, and constraint changes, but does not detect table renames (which appear as DROP + CREATE) or data backfills.

Check the upgrade() and downgrade() functions:

def upgrade() -> None:
    op.add_column("tasks", sa.Column("estimated_hours", sa.Integer(), nullable=True))


def downgrade() -> None:
    op.drop_column("tasks", "estimated_hours")

Step 4: Run Linting and Formatting

Ensure the migration script passes Ruff checks:

make format
make lint

Applying & Testing Migrations Locally

Apply all pending migrations to bring your local database up to head:

make db-migrate
# or: uv run alembic upgrade head

Verify current revision:

uv run alembic current

Inspect revision history:

uv run alembic history --verbose

Verify schema alignment and check for unmigrated drift:

make db-check
# or: uv run alembic check

Run the automated test suite (including migration validation tests):

uv run pytest tests/test_migrations.py
make test

Tip

Continuous Integration (CI): All migrations, schema drift verification (alembic check), and downgrade/upgrade rollback roundtrips are automatically tested against a live PostgreSQL 16 service container on every push and PR via GitHub Actions (.github/workflows/ci.yml).

Rolling Back & Downgrading

To verify that your downgrade function works cleanly:

# Step back 1 revision
uv run alembic downgrade -1

# Verify schema
uv run alembic current

# Re-apply to head
make db-migrate

Resetting & Seeding the Development Database

When developing locally, you can wipe and rebuild the database cleanly:

# Wipe schema and apply all migrations fresh
make db-clear

# Wipe schema, apply migrations to head, and seed mock projects/squads/tasks
make db-reset

clear_db.py executes DROP SCHEMA public CASCADE; CREATE SCHEMA public; and runs Alembic migrations from scratch, guaranteeing a clean state.

3. Production Deployment & Runbook

Migration Execution Strategies

In production deployments, you can choose between two operational models:

Strategy AUTO_RUN_MIGRATIONS When to Use Advantages
Strategy A: Container Entrypoint / Job (Recommended) false Multi-replica clusters (Kubernetes, AWS ECS, Docker Swarm) Eliminates migration concurrency races across replicas.
Strategy B: App Startup Hook true Single-container deployments (Docker Compose) Fully automatic zero-touch deployments.

Running Migrations via Docker

Approach 1: Pre-Flight Execution Before Starting App (Recommended)

Before rolling out updated application containers, optionally run a schema verification pass and apply migrations in a standalone one-off container:

# 1. (Recommended) Run schema drift verification pass against target DB
docker compose run --rm app alembic check

# 2. Run migrations using the production app container image
docker compose run --rm app alembic upgrade head

# 3. After migrations succeed, deploy/restart the app service
docker compose up -d --no-deps app

Approach 2: Interactive Execution Inside Running Container

If the app container is already running:

docker compose exec app alembic upgrade head

Check current revision inside container:

docker compose exec app alembic current

Zero-Downtime Migration Guidelines (Expand/Contract)

To perform zero-downtime updates in production without locking tables or breaking running application instances:

  1. Adding Columns:

    • Always make new columns nullable=True or supply a server default: 
      sa.Column("custom_label", sa.String(50), nullable=True)
    • If a column must ultimately be nullable=False:
      • Phase 1 (Expand): Add as nullable. Deploy app code that writes the new field.
      • Phase 2 (Backfill): Backfill existing rows with non-null values.
      • Phase 3 (Contract): Apply migration op.alter_column('tasks', 'custom_label', nullable=False).

  2. Renaming Columns or Tables:

    • Never use op.alter_column rename in a single release—old running containers will fail immediately.
    • Use the Expand/Contract pattern:
      • Add new column.
      • Deploy code writing to both old and new columns.
      • Backfill old values into new column.
      • Switch code to read from new column.
      • Drop old column in a subsequent release.

  3. Indexes:

    • For high-volume production tables, create indexes concurrently to prevent read/write locks: 
      with op.get_context().autocommit_block():
    op.create_index("ix_tasks_custom_search", "tasks", ["title"], postgresql_concurrently=True)

Production Rollback Runbook

If a deployment encounters critical issues and schema changes must be rolled back:

Step 1: Check Current Revision Status

docker compose exec app alembic current

Step 2: Downgrade to the Previous Revision

# Downgrade by one revision:
docker compose exec app alembic downgrade -1

# Or downgrade to a specific known stable revision ID:
docker compose exec app alembic downgrade 0001

Step 3: Re-deploy the Previous Application Container Image

docker compose up -d --build app

Step 4: Verify Application Logs

docker compose logs -f --tail 100 app

Troubleshooting & Disaster Recovery

1. relation already exists on Existing Database

Cause: The database contains tables created before Alembic was introduced, and alembic_version is missing. Solution:

  • DGG-PM's run_migrations() startup hook automatically detects unversioned tables and stamps the database to head.
  • If running manually: 
    alembic stamp head

2. Multiple Heads Detected (alembic.util.exc.CommandError: Multiple head revisions are present)

Cause: Two developers created branches and generated migrations off the same parent without merging revisions. Solution:

  1. Check the heads: 
    alembic heads
  2. Merge the branches into a single head revision: 
    alembic merge -m "merge_branch_a_and_branch_b" <rev_id_1> <rev_id_2>
  3. Apply the merged head: 
    alembic upgrade head

3. Migration Failed Halfway Through

Cause: DDL or data migration failed mid-script. PostgreSQL wraps DDL in transactions, so failed migrations are rolled back atomically. Solution:

  1. Fix the error in the migration script.
  2. Ensure alembic current matches the last successful revision.
  3. Re-run alembic upgrade head.

4. CLI Command Quick Reference

Action Local Dev (Makefile / uv) Docker / Production
Apply all pending migrations make db-migrate (or uv run alembic upgrade head) docker compose run --rm app alembic upgrade head
Generate autodetected migration make db-revision MSG="<msg>" alembic revision --autogenerate -m "<msg>"
Check for schema drift against ORM make db-check (or uv run alembic check) docker compose run --rm app alembic check
Check current database revision uv run alembic current docker compose exec app alembic current
Show revision history uv run alembic history docker compose exec app alembic history
Rollback one revision uv run alembic downgrade -1 docker compose exec app alembic downgrade -1
Stamp database to head without running DDL uv run alembic stamp head docker compose exec app alembic stamp head
Wipe & rebuild clean database make db-clear (Restricted to dev/test environments)
Wipe, rebuild & re-seed test data make db-reset (Restricted to dev/test environments)