Skip to content

Database Migrations

Complete guide for managing database schema changes using Alembic in Poolula Platform.

Overview

Alembic is the database migration tool for SQLModel/SQLAlchemy applications.

Why use migrations:

  • Track schema changes over time

  • Apply changes consistently across environments

  • Rollback changes if needed

  • Share schema updates with team

  • Document database evolution

Migration Workflow

1. Make model changes → 2. Generate migration → 3. Review migration → 4. Apply migration

Example: Adding a new column to the Property model

# 1. Edit core/database/models.py
class Property(SQLModel, table=True):
    ...
    zoning: Optional[str] = Field(default=None)  # NEW

# 2. Generate migration
.venv/bin/alembic revision --autogenerate -m "Add zoning column to properties"

# 3. Review generated file in alembic/versions/

# 4. Apply migration
.venv/bin/alembic upgrade head

Quick Start

Check Current Migration Status

# Show current database version
.venv/bin/alembic current

# Expected output:
# fcf071ccc323 (head)

Apply All Pending Migrations

# Upgrade to latest version
.venv/bin/alembic upgrade head

# Output:
# INFO  [alembic.runtime.migration] Running upgrade -> fcf071ccc323, initial schema

View Migration History

# Show all migrations
.venv/bin/alembic history

# Show verbose history
.venv/bin/alembic history --verbose

Alembic Setup

Project Structure

poolula-platform/
├── alembic/
│   ├── env.py              # Alembic configuration
│   ├── script.py.mako      # Migration template
│   └── versions/           # Migration files
│       └── fcf071ccc323_initial_schema.py
├── alembic.ini             # Alembic settings
└── core/database/
    ├── models.py           # SQLModel models (source of truth)
    └── connection.py       # Database connection

Configuration

alembic.ini:

[alembic]
script_location = alembic
sqlalchemy.url = sqlite:///./poolula.db

# Logging
[loggers]
keys = root,sqlalchemy,alembic

alembic/env.py:

# Imports SQLModel metadata
from core.database.models import SQLModel

target_metadata = SQLModel.metadata

Creating Migrations

Alembic compares your SQLModel models to the current database schema and generates migration code.

# Create migration with descriptive message
.venv/bin/alembic revision --autogenerate -m "Add zoning column to properties"

# Output:
# Generating /path/to/alembic/versions/abc123_add_zoning_column.py ... done

Always review autogenerated migrations! They may need manual adjustments.

Manual Migration

For complex changes that autogenerate can't handle:

# Create empty migration template
.venv/bin/alembic revision -m "Custom data transformation"

# Edit the generated file manually

Migration File Anatomy

"""Add zoning column to properties

Revision ID: abc123def456
Revises: fcf071ccc323
Create Date: 2024-11-15 10:30:00.123456
"""
from alembic import op
import sqlalchemy as sa

# Revision identifiers
revision = 'abc123def456'
down_revision = 'fcf071ccc323'  # Previous migration
branch_labels = None
depends_on = None

def upgrade():
    """Apply schema changes"""
    op.add_column('properties',
        sa.Column('zoning', sa.String(length=50), nullable=True)
    )

def downgrade():
    """Revert schema changes"""
    op.drop_column('properties', 'zoning')

Key parts:

  • revision: Unique ID for this migration

  • down_revision: Points to previous migration (creates chain)

  • upgrade(): SQL to apply changes

  • downgrade(): SQL to revert changes

Applying Migrations

Upgrade to Latest

# Apply all pending migrations
.venv/bin/alembic upgrade head

# Output shows each migration applied:
# INFO  [alembic.runtime.migration] Running upgrade fcf071ccc323 -> abc123def456, add zoning column

Upgrade to Specific Version

# Upgrade to specific revision
.venv/bin/alembic upgrade abc123def456

# Upgrade by relative number
.venv/bin/alembic upgrade +2  # Apply next 2 migrations

Downgrade (Rollback)

# Rollback one migration
.venv/bin/alembic downgrade -1

# Rollback to specific version
.venv/bin/alembic downgrade fcf071ccc323

# Rollback all migrations (dangerous!)
.venv/bin/alembic downgrade base

Dry Run (Show SQL Without Executing)

# Show SQL that would be executed
.venv/bin/alembic upgrade head --sql

# Useful for reviewing changes before applying

Common Migration Scenarios

Adding a Column

Model change:

class Property(SQLModel, table=True):
    ...
    property_manager: Optional[str] = Field(default=None)  # NEW

Generate migration:

.venv/bin/alembic revision --autogenerate -m "Add property_manager to properties"

Generated migration (example):

def upgrade():
    op.add_column('properties',
        sa.Column('property_manager', sa.String(), nullable=True)
    )

Dropping a Column

Model change:

# Remove column from model
class Property(SQLModel, table=True):
    ...
    # removed: old_field

Migration:

.venv/bin/alembic revision --autogenerate -m "Remove old_field from properties"

⚠️ Warning: Dropping columns is destructive. Data will be lost.

Renaming a Column

Autogenerate can't detect renames! You must edit the migration manually.

Model change:

class Property(SQLModel, table=True):
    ...
    # Renamed: property_type → usage_type
    usage_type: Optional[str] = Field(default=None)

Generate migration, then edit:

.venv/bin/alembic revision --autogenerate -m "Rename property_type to usage_type"

Edit the generated file:

def upgrade():
    # Autogenerate will show DROP + ADD
    # Replace with:
    op.alter_column('properties', 'property_type', new_column_name='usage_type')

def downgrade():
    op.alter_column('properties', 'usage_type', new_column_name='property_type')

Adding a Table

Create new model:

class Tenant(SQLModel, table=True):
    __tablename__ = "tenants"
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    name: str = Field(max_length=200)
    email: str = Field(max_length=200)
    property_id: UUID = Field(foreign_key="properties.id")

Generate migration:

.venv/bin/alembic revision --autogenerate -m "Add tenants table"

Changing Column Type

Model change:

# Change from Optional[str] to int
class Property(SQLModel, table=True):
    ...
    year_built: Optional[int] = Field(default=None)  # Was str

⚠️ Requires data migration:

.venv/bin/alembic revision --autogenerate -m "Change year_built to integer"

Edit migration to handle data:

def upgrade():
    # 1. Add new column
    op.add_column('properties',
        sa.Column('year_built_new', sa.Integer(), nullable=True)
    )

    # 2. Copy/convert data
    op.execute("""
        UPDATE properties
        SET year_built_new = CAST(year_built AS INTEGER)
        WHERE year_built IS NOT NULL AND year_built != ''
    """)

    # 3. Drop old column
    op.drop_column('properties', 'year_built')

    # 4. Rename new column
    op.alter_column('properties', 'year_built_new', new_column_name='year_built')

Adding Index

def upgrade():
    op.create_index('ix_properties_status', 'properties', ['status'])

def downgrade():
    op.drop_index('ix_properties_status')

Adding Foreign Key

def upgrade():
    op.create_foreign_key(
        'fk_transactions_property',  # Constraint name
        'transactions',              # Source table
        'properties',                # Target table
        ['property_id'],             # Source column
        ['id']                       # Target column
    )

Data Migrations

Sometimes you need to transform data, not just schema.

Example: Populate new column from existing data

def upgrade():
    # 1. Add column
    op.add_column('properties',
        sa.Column('full_address', sa.String(), nullable=True)
    )

    # 2. Populate from existing data
    op.execute("""
        UPDATE properties
        SET full_address = address || ', ' || city || ', ' || state
        WHERE address IS NOT NULL
    """)

    # 3. Make column non-nullable (optional)
    op.alter_column('properties', 'full_address', nullable=False)

Use raw SQL with op.execute() for data transformations.

Migration Best Practices

1. Always Review Autogenerated Migrations

# After generating, review the file
.venv/bin/alembic revision --autogenerate -m "Description"

# Open the generated file and check:
cat alembic/versions/abc123_description.py

Check for:

  • Correct operations (add/drop/alter)

  • Proper upgrade() and downgrade() functions

  • Unintended changes (autogenerate can be overzealous)

2. Test Migrations Before Applying

# Upgrade
.venv/bin/alembic upgrade head

# Verify it worked
python -c "from core.database.connection import check_connection; check_connection()"

# Test downgrade
.venv/bin/alembic downgrade -1

# Upgrade again
.venv/bin/alembic upgrade head

3. One Logical Change Per Migration

Good:

.venv/bin/alembic revision -m "Add zoning column"

Bad:

.venv/bin/alembic revision -m "Add 5 columns, change 3 types, add index"

Reason: Easier to review, rollback, and debug.

4. Write Reversible Migrations

Always implement downgrade():

def upgrade():
    op.add_column('properties', sa.Column('new_field', sa.String()))

def downgrade():
    op.drop_column('properties', 'new_field')  # ✓ Can rollback

5. Backup Before Destructive Changes

# Before dropping columns or tables
python scripts/backup.py

# Then apply migration
.venv/bin/alembic upgrade head

6. Use Descriptive Migration Messages

Good:

  • "Add tenant_id foreign key to transactions"

  • "Change property_status from string to enum"

  • "Create payment_history table"

Bad:

  • "Update database"

  • "Changes"

  • "Migration 2"

Troubleshooting

"Target database is not up to date"

Error:

ERROR [alembic.util.messaging] Target database is not up to date.

Solution:

# Apply pending migrations
.venv/bin/alembic upgrade head

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

Error:

ERROR [alembic.util.messaging] Can't locate revision identified by 'abc123'

Solution:

Migration file was deleted or repo is out of sync:

# Show current version
.venv/bin/alembic current

# Manually set version (dangerous - only if you know what you're doing)
.venv/bin/alembic stamp head

Migration Conflicts (Multiple Heads)

Error:

ERROR [alembic.util.messaging] Multiple head revisions are present

Solution:

Create a merge migration:

.venv/bin/alembic merge -m "Merge divergent branches" abc123 def456
.venv/bin/alembic upgrade head

Autogenerate Doesn't Detect Change

Common causes:

  • Column has same name and type

  • Alembic can't detect renames

  • Change is in data, not schema

Solution:

Create manual migration:

.venv/bin/alembic revision -m "Manual change description"
# Edit the generated file manually

SQLite Limitations

SQLite doesn't support:

  • ALTER COLUMN (change type, add constraint)

  • DROP COLUMN in some cases

Workaround: Use table recreation pattern:

def upgrade():
    # 1. Create new table with desired schema
    op.create_table('properties_new', ...)

    # 2. Copy data
    op.execute('INSERT INTO properties_new SELECT * FROM properties')

    # 3. Drop old table
    op.drop_table('properties')

    # 4. Rename new table
    op.rename_table('properties_new', 'properties')

Better: Switch to PostgreSQL for production if you need advanced ALTER operations.

Migration Workflow Example

Complete workflow for adding a feature:

1. Create Feature Branch

git checkout -b add-tenant-management

2. Update Models

# core/database/models.py
class Tenant(SQLModel, table=True):
    __tablename__ = "tenants"
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    name: str
    email: str
    property_id: UUID = Field(foreign_key="properties.id")
    lease_start: date
    lease_end: date

3. Generate Migration

.venv/bin/alembic revision --autogenerate -m "Add tenants table"

4. Review Migration

# Open generated file
cat alembic/versions/abc123_add_tenants_table.py

# Check upgrade/downgrade functions

5. Test Migration

# Apply migration
.venv/bin/alembic upgrade head

# Verify table was created
sqlite3 poolula.db ".schema tenants"

# Test rollback
.venv/bin/alembic downgrade -1

# Verify table was dropped
sqlite3 poolula.db ".tables"

# Re-apply
.venv/bin/alembic upgrade head

6. Commit Changes

git add core/database/models.py alembic/versions/abc123_add_tenants_table.py
git commit -m "Add Tenant model and database table

- Created Tenant SQLModel with lease tracking
- Generated Alembic migration for tenants table
- Foreign key relationship to properties
"

7. Share with Team

git push origin add-tenant-management
# Team members run: .venv/bin/alembic upgrade head

Production Considerations

Staging First

# 1. Test on staging database
DATABASE_URL=postgresql://staging .venv/bin/alembic upgrade head

# 2. Verify application works

# 3. Apply to production
DATABASE_URL=postgresql://production .venv/bin/alembic upgrade head

Zero-Downtime Migrations

Strategy for production:

  1. Add new column (nullable)
op.add_column('properties', sa.Column('new_field', sa.String(), nullable=True))

  1. Deploy code that writes to both old and new columns

  2. Backfill data

op.execute('UPDATE properties SET new_field = old_field WHERE new_field IS NULL')
  1. Make column non-nullable
op.alter_column('properties', 'new_field', nullable=False)

  1. Deploy code that only uses new column

  2. Drop old column

op.drop_column('properties', 'old_field')

Migration in CI/CD

# .github/workflows/deploy.yml
- name: Apply database migrations
  run: |
    .venv/bin/alembic upgrade head
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Advanced Topics

Branching

For parallel development with migrations:

# Create branch from specific revision
.venv/bin/alembic revision -m "Feature A" --head=abc123

# Merge branches later
.venv/bin/alembic merge -m "Merge features" def456 ghi789

Offline SQL Generation

Generate SQL scripts for DBA review:

# Generate SQL file
.venv/bin/alembic upgrade head --sql > migration.sql

# DBA reviews and applies manually

Custom Migration Path

Skip specific migrations:

# Upgrade to specific version, skipping problematic migration
.venv/bin/alembic upgrade abc123
.venv/bin/alembic stamp xyz789  # Mark as if we applied xyz789
.venv/bin/alembic upgrade head

External Resources