# Writing Migrations

> Create and manage Alembic database migrations with safe upgrade and downgrade paths.

- Skill: `7a336e6e/writing-migrations` (Agent Skill)
- Install (CLI): `npx skillmds@latest add 7a336e6e/writing-migrations`
- Raw SKILL.md: https://api.skillmd.com/api/skills/7a336e6e/writing-migrations/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: 7a336e6e (https://skillmd.com/u/7a336e6e)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/7a336e6e/writing-migrations

---


## Goal

Generate reliable, reversible Alembic migrations that evolve the database schema incrementally. Every migration must include both an upgrade and a downgrade path, and must be reviewed before being applied.

## When to Use

- Adding, modifying, or removing database tables or columns
- Creating or dropping indexes and constraints
- Changing column types or default values
- Any schema change that needs to be tracked and reversible

## Instructions

## 1. Generate the Migration

Use Alembic's autogenerate feature as a starting point, then review and edit the output.

```bash
alembic revision --autogenerate -m "add status column to tasks"
```

This creates a file like `20250115_143022_add_status_column_to_tasks.py`. The naming convention is: timestamp prefix followed by an imperative description of the change.

## 2. Review the Autogenerated Code

Never trust autogenerate blindly. It may miss certain changes or produce incorrect operations. Always open the generated file and verify:

- All intended changes are present
- No unintended changes are included
- Data types and constraints are correct
- The downgrade function correctly reverses the upgrade

## 3. Write the Migration

A well-structured migration includes both `upgrade()` and `downgrade()` functions.

```python
"""add status column to tasks

Revision ID: a1b2c3d4e5f6
Revises: f6e5d4c3b2a1
Create Date: 2025-01-15 14:30:22.000000
"""
from alembic import op
import sqlalchemy as sa

revision = "a1b2c3d4e5f6"
down_revision = "f6e5d4c3b2a1"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "tasks",
        sa.Column(
            "status",
            sa.String(length=20),
            nullable=False,
            server_default="pending",
        ),
    )
    op.create_index("ix_tasks_status", "tasks", ["status"])


def downgrade() -> None:
    op.drop_index("ix_tasks_status", table_name="tasks")
    op.drop_column("tasks", "status")
```

## 4. Handle Data Migrations Separately

Schema migrations change structure. Data migrations change content. Keep them in separate migration files to maintain clarity and reversibility.

```python
# Schema migration: add the column
def upgrade() -> None:
    op.add_column("users", sa.Column("full_name", sa.String(200), nullable=True))


# Separate data migration: populate the column
def upgrade() -> None:
    op.execute(
        "UPDATE users SET full_name = display_name WHERE full_name IS NULL"
    )
```

After the data migration, create a third migration to add the NOT NULL constraint if needed.

## 5. Test Forward and Backward

Always test both directions before merging:

```bash
# Apply the migration
alembic upgrade head

# Verify the schema change
# ...

# Roll back the migration
alembic downgrade -1

# Verify clean rollback
# ...

# Re-apply for final state
alembic upgrade head
```

## 6. Handle Common Patterns

### Renaming a Column

```python
def upgrade() -> None:
    op.alter_column("tasks", "name", new_column_name="title")

def downgrade() -> None:
    op.alter_column("tasks", "title", new_column_name="name")
```

### Adding a Non-Nullable Column to an Existing Table

```python
def upgrade() -> None:
    # Step 1: Add as nullable with a default
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=True))
    # Step 2: Backfill existing rows
    op.execute("UPDATE tasks SET priority = 0 WHERE priority IS NULL")
    # Step 3: Set NOT NULL
    op.alter_column("tasks", "priority", nullable=False)

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

## Constraints

### ✅ Do
- Test every migration forward (upgrade) and backward (downgrade)
- Keep each migration small and focused on a single logical change
- Review autogenerated output before applying
- Use server_default for new non-nullable columns on existing tables
- Include index creation in the same migration as the column it covers
- Run migrations in a transaction (Alembic default) so failures roll back cleanly


### ❌ Don't
- Modify an existing migration after it has been applied to any environment
- Put data transforms in the same file as schema changes
- Skip writing a downgrade function
- Use raw SQL in migrations when Alembic operations are available
- Create migrations that depend on application code imports
- Squash migrations without team agreement


## Output Format

Produce an Alembic migration Python file with the standard header (revision, down_revision, description), an `upgrade()` function, and a `downgrade()` function. Include inline comments explaining non-obvious operations.

## Dependencies

- [Designing Schemas](../designing-schemas/SKILL.md) — the schema definition that migrations implement
- [Git Workflow](../../shared/git-workflow/SKILL.md) — commit migration files following the branch and commit conventions