Database Migrations

Overview

The project uses Alembic for database migrations with PostgreSQL.

Basic Commands

# Show current migration
uv run alembic current

# Show migration history
uv run alembic history --verbose

# Upgrade to latest
uv run alembic upgrade head

# Downgrade one migration
uv run alembic downgrade -1

# Create new migration
uv run alembic revision --autogenerate -m "description"

Migration Workflow

When to Create Migration

Create a migration when: 1. Adding new table 2. Adding/modifying column in existing table 3. Adding/modifying index 4. Any schema change

Creating Migration

# 1. Ensure model is updated
# 2. Create migration
uv run alembic revision --autogenerate -m "add_recipient_id_to_package"

# 3. Review generated migration in alembic/versions/
# 4. Apply migration
uv run alembic upgrade head

Best Practices

1. Always review generated migration before applying
2. Test migrations on local DB first
3. Never modify existing migrations - create new one
4. Include rollback logic in upgrade() function

Common Issues

Issue: Multiple Heads

Symptoms:

Multiple heads are detected

Solution:

# Merge heads
uv run alembic merge head1 head2 -m "merge_description"

# Or stamp to specific revision
uv run alembic stamp <revision_id>

Issue: Migration Not Applying

Symptoms:

sqlalchemy.exc.ProgrammingError: column does not exist

Solution:

# Check if migration is applied
uv run alembic current

# Manually apply specific migration
uv run alembic upgrade <revision>

Migration Files Location

saas-backend/alembic/versions/
├── xxx_initial_schema.py
├── xxx_add_column.py
└── ...

Rollback Guidelines

For production: 1. Always have rollback script ready 2. Test rollback in staging first 3. Document any data migration steps