Database Migrations with Alembic

CHAP uses Alembic to manage database schema changes. Migrations run automatically when you start the application with docker compose up.

Prerequisites

Make sure the database is running:

docker compose -f compose.yml -f compose.dev.yml up -d postgres

The compose.dev.yml file exposes the postgres port on localhost for development, allowing you to run Alembic commands locally and connect with database tools like pgAdmin or DBeaver.

Alembic will use the default database URL (postgresql://root:thisisnotgoingtobeexposed@localhost:5432/chap_core) when running locally. If you need to connect to a different database, set the CHAP_DATABASE_URL environment variable:

export CHAP_DATABASE_URL="postgresql://user:password@host:port/database"

Quick Start: Making Schema Changes

1. Modify the Database Model

Edit the SQLModel class in chap_core/database/:

# chap_core/database/dataset_tables.py
class DataSet(DataSetBase, table=True):
    # Add a new field
    new_field: Optional[str] = None

2. Generate a Migration

uv run alembic revision --autogenerate -m "add_new_field_to_dataset"

This creates a new file in alembic/versions/ with the detected schema changes.

3. Review the Migration

Open the generated file and verify the upgrade() and downgrade() functions are correct:

def upgrade() -> None:
    op.add_column('dataset', sa.Column('new_field', sa.String(), nullable=True))

def downgrade() -> None:
    op.drop_column('dataset', 'new_field')

4. Test Locally

# Apply the migration
uv run alembic upgrade head

# Check current version
uv run alembic current

# If needed, rollback
uv run alembic downgrade -1

5. Commit and Deploy

git add alembic/versions/*.py
git commit -m "Add new_field to dataset table"

When deployed, the migration runs automatically on startup.

Common Commands

# Show current migration version
uv run alembic current

# Show migration history
uv run alembic history

# Apply all pending migrations
uv run alembic upgrade head

# Rollback one migration
uv run alembic downgrade -1

# Create empty migration (for data changes)
uv run alembic revision -m "migrate_old_data"

How Migrations Work

When you run docker compose up, the system runs migrations in this order:

  1. Custom migrations - For backward compatibility with older database versions (v1.0.17 and earlier)

  2. Table creation - Creates any new tables

  3. Alembic migrations - Applies schema changes from alembic/versions/

This hybrid approach ensures smooth upgrades from older versions while using a standard migration tool for future changes.

Note: The custom migration system in chap_core/database/database.py (steps 1 and 2) is a temporary solution for backward compatibility. Once all production instances have upgraded past v1.0.17, we can remove _run_generic_migration() and _run_v1_0_17_migrations() in a future release, leaving only Alembic for all schema management.

Best Practices

  • Always review autogenerated migrations before committing

  • Test migrations locally before pushing

  • Write descriptive messages using imperative mood (e.g., “add_column” not “added_column”)

  • Don’t modify committed migrations - create a new one instead

  • Test rollback to ensure downgrade() works

Data Migrations

For changes that require updating existing data (not just schema):

# Create empty migration
uv run alembic revision -m "migrate_user_data"

Edit the file to add custom SQL or Python logic:

def upgrade() -> None:
    # Update existing data
    op.execute("UPDATE users SET status = 'active' WHERE created > '2024-01-01'")

def downgrade() -> None:
    # Reverse if possible
    op.execute("UPDATE users SET status = NULL WHERE created > '2024-01-01'")

Troubleshooting

Autogenerate detects unwanted changes

This usually means the database schema doesn’t match your models. Either:

  • The database has old columns that should be removed

  • Your model types don’t match the database types

Review the migration and adjust as needed.

Migration fails with “revision not found”

Your local alembic/versions/ is out of sync with the database:

# Pull latest migrations
git pull

# Try again
uv run alembic upgrade head

Learn More