Skip to content

Database Migrations with Alembic

This project uses Alembic for database schema migrations. Alembic provides version control for your database schema, allowing safe evolution of the database structure over time.

Overview

Previous approach: The project previously used SQLModel.metadata.create_all() which had limitations: - ❌ No migration history or rollback capability - ❌ Cannot safely modify existing tables - ❌ Risk of data loss when changing schemas - ❌ No team synchronization for schema changes

Current approach: Alembic migrations provide: - ✅ Version-controlled migration history - ✅ Safe schema evolution without data loss - ✅ Rollback support for failed migrations - ✅ Automatic migration generation from model changes - ✅ Team synchronization (everyone applies same migrations) - ✅ Production-safe deployment workflow

Quick Start

Applying Migrations

Apply all pending migrations to bring your database up to date:

make migrate

This runs alembic upgrade head and applies all migrations that haven't been applied yet.

Creating New Migrations

When you modify a SQLModel (add/remove/modify fields), generate a new migration:

make migration msg="Add email field to Author"

This will: 1. Auto-detect changes between your models and current database schema 2. Generate a new migration file in app/storage/migrations/versions/ 3. Create both upgrade() and downgrade() functions

Important: Always review the generated migration before applying it!

Viewing Migration Status

Check which migration is currently applied:

make migration-current

View full migration history:

make migration-history

Rolling Back Migrations

Roll back the most recent migration:

make rollback

This runs alembic downgrade -1 to revert the last migration.

Migration Workflow

1. Modify Your Model

Edit a model file (e.g., app/models/author.py):

class Author(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str | None = None  # NEW FIELD

2. Generate Migration

make migration msg="Add email field to Author"

3. Review Generated Migration

Check the generated file in app/storage/migrations/versions/:

def upgrade() -> None:
    # ### commands auto generated by Alembic ###
    op.add_column('author', sa.Column('email', sa.String(), nullable=True))
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic ###
    op.drop_column('author', 'email')
    # ### end Alembic commands ###

Important checks: - Verify the operations match your intent - Check for data loss risks (e.g., dropping columns) - Ensure nullable constraints are correct - Review default values for new required fields

4. Apply Migration

make migrate

5. If Issues Occur, Rollback

make rollback

Advanced Usage

Manual Migration Creation

For complex migrations that can't be auto-generated:

uv run alembic revision -m "Custom migration description"

Then manually edit the generated file to add your custom operations.

Downgrade to Specific Version

uv run alembic downgrade <revision_id>

Upgrade to Specific Version

uv run alembic upgrade <revision_id>

Stamp Database Without Running Migrations

If you have an existing database that matches a specific migration:

make migration-stamp rev="<revision_id>"

Or stamp to the latest:

make migration-stamp rev="head"

This is useful when: - Migrating from the old create_all() approach to Alembic - Setting up a database that was manually created - Recovering from migration issues

Initial Setup (For New Developers)

  1. Clone the repository
  2. Start services: 
    make start  # Starts PostgreSQL, Redis, Keycloak, etc.
  3. Apply migrations: 
    make migrate

That's it! Your database schema is now up to date.

Production Deployment

Safe Deployment Process

  1. Test migrations locally: 

    make migrate

  2. Review migration on staging environment

  3. Backup production database before applying migrations

  4. Apply migrations during maintenance window: 

    make migrate

  5. Verify application functionality

  6. If issues occur: 

    make rollback

Zero-Downtime Migrations

For zero-downtime deployments, follow this pattern:

Phase 1: Add new column (nullable) 

# Migration 1: Add nullable column
email: str | None = None

Phase 2: Backfill data 

# Migration 2: Populate email field
def upgrade():
    # Custom data migration
    op.execute("UPDATE author SET email = name || '@example.com'")

Phase 3: Make non-nullable (if needed) 

# Migration 3: Add NOT NULL constraint
def upgrade():
    op.alter_column('author', 'email', nullable=False)

Troubleshooting

"Target database is not up to date"

# Check current version
make migration-current

# Apply pending migrations
make migrate

"Can't locate revision identified by 'xyz'"

The migration file might have been deleted. Check app/storage/migrations/versions/ directory.

"FAILED: Multiple head revisions are present"

This happens when multiple branches exist in migration history. Merge them:

uv run alembic merge heads -m "Merge migration branches"

Migration Conflicts

If you get conflicts because someone else created a migration:

  1. Pull latest code
  2. Regenerate your migration: 
    rm app/storage/migrations/versions/your_migration.py
make migration msg="Your description"

Reset Migration History (Development Only!)

WARNING: This will delete all migration history. Never do this in production!

# Drop all tables
# Recreate database
# Regenerate initial migration
make migration msg="Initial migration"
make migrate

Configuration

Database Connection

Alembic automatically uses the database URL from app/settings.py:

DATABASE_URL = f"postgresql+asyncpg://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

This is configured in app/storage/migrations/env.py:

config.set_main_option("sqlalchemy.url", app_settings.DATABASE_URL)

Adding New Models

When you create a new model, import it in app/storage/migrations/env.py:

from app.models.author import Author  # noqa: F401
from app.models.book import Book  # noqa: F401  # ADD NEW IMPORTS

This ensures Alembic can detect the model and generate migrations for it.

Best Practices

✅ Do

  • Always review generated migrations before applying
  • Test migrations on development/staging before production
  • Backup production database before running migrations
  • Use descriptive migration messages: make migration msg="Add user email verification"
  • Keep migrations small and focused on one logical change
  • Commit migration files to version control
  • Add custom data migrations when needed (e.g., backfilling data)

❌ Don't

  • Never edit applied migrations - create a new migration instead
  • Never delete migration files that have been applied to production
  • Don't skip reviewing auto-generated migrations
  • Don't make breaking changes without a rollout plan
  • Don't drop columns without checking for data
  • Never use make rollback in production without careful consideration

Common Operations

Add a Column

# Model change
class Author(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    bio: str | None = None  # NEW

make migration msg="Add bio to Author"
make migrate

Remove a Column

# Model change - remove field
class Author(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    # bio removed

make migration msg="Remove bio from Author"
# Review the migration - it will drop the column!
make migrate

Rename a Column

Alembic can't auto-detect renames (it sees drop + add). Do it manually:

def upgrade() -> None:
    op.alter_column('author', 'name', new_column_name='full_name')

def downgrade() -> None:
    op.alter_column('author', 'full_name', new_column_name='name')

Change Column Type

# Model change
class Author(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=200)  # Changed from default

make migration msg="Increase author name length"
make migrate

Add Index

# Model change
class Author(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)  # Add index

make migration msg="Add index on author name"
make migrate

Additional Resources

Support

If you encounter issues with migrations:

  1. Check this documentation
  2. Review the Alembic documentation
  3. Check migration history: make migration-history
  4. Verify current version: make migration-current
  5. Ask the team for help if stuck