Migrations

Database migrations track and apply schema changes to your database.

Prerequisites

  • Advanced Alchemy installed with cli extra

  • Configuration file created (see CLI)

Understanding Migrations

Migrations provide:

  • Version control for database schema

  • Incremental schema changes

  • Rollback capabilities

  • Team coordination for schema updates

  • Database-agnostic schema definitions

Advanced Alchemy uses Alembic for migration management.

Migration Workflow

Follow this workflow for schema changes:

Step 1: Modify Models

Make changes to your SQLAlchemy models:

from advanced_alchemy.base import UUIDAuditBase
from sqlalchemy.orm import Mapped, mapped_column

class User(UUIDAuditBase):
    username: Mapped[str] = mapped_column(unique=True)
    email: Mapped[str] = mapped_column(unique=True)  # Added field

Step 2: Generate Migration

Create migration file with autogenerate:

alchemy make-migrations -m "add user email field" --config path.to.alchemy-config.config

Output:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'user.email'
  Generating /path/to/migrations/versions/abc123_add_user_email_field.py ...  done

Alembic autogenerate detects changes automatically.

Step 3: Review Generated Migration

Open the migration file and verify changes:

migrations/versions/abc123_add_user_email_field.py
"""add user email field

Revision ID: abc123
Revises: def456
Create Date: 2025-10-18 12:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = 'abc123'
down_revision = 'def456'
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('email', sa.String(), nullable=False))
    op.create_unique_constraint(None, 'user', ['email'])
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'user', type_='unique')
    op.drop_column('user', 'email')
    # ### end Alembic commands ###

Review checklist:

  • Column additions/removals correct

  • Constraints properly defined

  • Indexes created where needed

  • downgrade() reverses upgrade()

Step 4: Apply Migration

Apply migration to database:

alchemy upgrade --config path.to.alchemy-config.config

Output:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade def456 -> abc123, add user email field

Migration applied successfully.

Step 5: Verify Schema

Check current database revision:

alchemy show-current-revision --config path.to.alchemy-config.config

Output:

abc123 (head)

Verify schema in database:

psql -d mydb -c "\d+ user"

mysql -e "DESCRIBE user;"

sqlite3 mydb.db ".schema user"

Autogenerate Capabilities

Alembic autogenerate detects:

Table Changes

  • Table additions

  • Table removals

  • Table renames (detected as drop + add)

Column Changes

  • Column additions

  • Column removals

  • Column type modifications

  • Nullable/not-null changes

Constraint Changes

  • Unique constraints

  • Foreign key constraints

  • Check constraints

  • Primary key changes

Index Changes

  • Index additions

  • Index removals

  • Index modifications

Autogenerate Limitations

Manual migration required for:

Renamed Columns

Detected as drop + add, not rename:

# Manual migration needed
op.alter_column('user', 'old_name', new_column_name='new_name')
Renamed Tables

Detected as drop + add:

# Manual migration needed
op.rename_table('old_table', 'new_table')
Server Default Changes

Not detected by autogenerate:

# Manual migration needed
op.alter_column('user', 'created_at', server_default=func.now())
Data Migrations

Requires manual implementation:

# Manual data transformation
connection = op.get_bind()
connection.execute(
    sa.text("UPDATE user SET email = username || '@example.com' WHERE email IS NULL")
)
Column Comment Changes

Not tracked:

# Manual migration needed
op.alter_column('user', 'username', comment='User login name')

Rollback

Downgrade to previous revision:

alchemy downgrade --config path.to.alchemy-config.config

Downgrade specific steps:

# Downgrade 1 revision
alchemy downgrade -1 --config path.to.alchemy-config.config

# Downgrade to specific revision
alchemy downgrade abc123 --config path.to.alchemy-config.config

# Downgrade to base (all migrations removed)
alchemy downgrade base --config path.to.alchemy-config.config

Rollback limitations:

  • Data loss possible if downgrade() drops columns

  • Irreversible operations (truncate, drop table)

  • Custom data migrations may not be reversible

  • Always backup before downgrading

Technical Constraints

Migration Detection

Alembic compares metadata to database state:

# ✅ Correct - model registered with metadata
from advanced_alchemy.base import UUIDAuditBase

class User(UUIDAuditBase):  # Registered via orm_registry
    username: Mapped[str]

# ❌ Incorrect - model not in metadata
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):  # Different metadata, won't be detected
    __tablename__ = "user"
    username: Mapped[str]

Use Advanced Alchemy base classes for proper metadata registration.

Relationship Detection

Viewonly relationships not in migrations:

# Migration includes posts relationship (no viewonly)
class Post(BigIntAuditBase):
    tags: Mapped[List["Tag"]] = relationship(
        secondary=post_tag,
        back_populates="posts"
    )

# Migration ignores posts relationship (viewonly=True)
class Tag(BigIntAuditBase):
    posts: Mapped[List[Post]] = relationship(
        secondary=post_tag,
        back_populates="tags",
        viewonly=True  # Not tracked in migrations
    )

Viewonly relationships don’t affect database schema.

Migration File Conflicts

Multiple developers may create conflicting migrations:

migrations/versions/
├── abc123_feature_a.py  # down_revision: xyz789
└── def456_feature_b.py  # down_revision: xyz789 (conflict!)

Resolve using merge command:

alchemy merge heads -m "merge feature branches" --config path.to.alchemy-config.config

Common Issues

Migration Not Detected

Problem: Model changes not detected by autogenerate

Causes:

  • Model not imported in Alembic env.py

  • Model uses different metadata registry

  • Using viewonly=True relationships

  • Changing server_default values

Solution:

migrations/env.py
# Ensure models imported

# Use correct metadata
target_metadata = orm_registry.metadata  # Advanced Alchemy registry

Migration Fails to Apply

Problem: alchemy upgrade fails with error

Common causes:

Existing Data Violates Constraints

ERROR: column "email" contains null values

Solution: Add data migration before constraint:

def upgrade():
    # Add column as nullable first
    op.add_column('user', sa.Column('email', sa.String(), nullable=True))

    # Populate data
    connection = op.get_bind()
    connection.execute(
        sa.text("UPDATE user SET email = username || '@example.com'")
    )

    # Add constraint
    op.alter_column('user', 'email', nullable=False)

Column Already Exists

ERROR: column "email" of relation "user" already exists

Solution: Migration already applied or partially applied. Check revision:

alchemy show-current-revision --config path.to.alchemy-config.config

Database Dialect Incompatibility

ERROR: syntax error at or near "IF EXISTS"

Solution: Use database-agnostic Alembic operations, not raw SQL.

Multiple Heads (Branch Conflict)

Problem: alchemy upgrade reports multiple heads

FAILED: Multiple head revisions are present; please merge

Solution: Merge branches:

# Check heads
alchemy heads --config path.to.alchemy-config.config

# Merge all heads
alchemy merge heads -m "merge branches" --config path.to.alchemy-config.config

# Apply merged migration
alchemy upgrade --config path.to.alchemy-config.config

Database-Specific Considerations

PostgreSQL

  • Supports transactional DDL (rollback schema changes)

  • CONCURRENTLY for indexes (requires manual migration)

  • ENUM type changes require manual handling

MySQL

  • Non-transactional DDL (cannot rollback schema changes)

  • Foreign keys require InnoDB engine

  • ALTER TABLE locks tables during migration

SQLite

  • Limited ALTER TABLE support

  • Cannot drop columns (requires table recreation)

  • Foreign key constraints disabled by default

Oracle

  • Sequence management for primary keys

  • NUMBER type for integers

  • Requires manual IDENTITY handling

Next Steps

See Commands for complete command reference.