9. Automated migrations with Alembic

Manual CREATE TABLE and ALTER TABLE works for a one-time migration. Six months later, when you need to add a column to a live database, you'll want a version-controlled trail of every schema change. Alembic is what gives you that: Git for your database schema.

Manually running CREATE TABLE and ALTER TABLE statements works for one-time migrations, but production applications need repeatable, version-controlled schema changes. Alembic is a database migration tool that tracks schema versions, generates migration scripts automatically, and applies changes safely across development, staging, and production environments.

Think of Alembic like Git for your database schema. Each migration is a commit that moves your database from one version to the next. You can upgrade to the latest version, downgrade to fix mistakes, and see the complete history of schema changes.

Installing and configuring Alembic

Start by installing Alembic and initialising it in your project directory:

Terminal 
# Install Alembic
pip install alembic

# Initialize Alembic in your project
alembic init alembic

# Creates this structure:
# alembic/
#   ├── env.py           # Configuration for connecting to database
#   ├── script.py.mako   # Template for migration scripts
#   └── versions/        # Directory for migration files
# alembic.ini            # Alembic configuration file

Configure Alembic to connect to your PostgreSQL database by editing alembic.ini:

alembic.ini 
# Find this line:
sqlalchemy.url = driver://user:pass@localhost/dbname

# Replace with your PostgreSQL connection:
sqlalchemy.url = postgresql://your_username@localhost/weather_db

# Or use environment variable (better for production):
# sqlalchemy.url = ${DATABASE_URL}

For production applications, use environment variables instead of hard-coding credentials. Edit alembic/env.py to read from the environment:

alembic/env.py 
# Near the top of env.py, add:
import os

# Find this section:
# config = context.config

# Add after it:
if os.getenv('DATABASE_URL'):
    config.set_main_option('sqlalchemy.url', os.getenv('DATABASE_URL'))

Creating your first migration

Alembic can generate migration scripts automatically by comparing your code's model definitions to the current database state. For now, we'll create a migration manually to understand how they work:

Terminal 
# Create new migration
alembic revision -m "create weather history table"

# Creates file: alembic/versions/abc123_create_weather_history_table.py

This generates a migration file with upgrade() and downgrade() functions. The upgrade function applies the change; downgrade reverses it. Open the generated file and fill it in:

alembic/versions/abc123_create_weather_history_table.py 
"""create weather history table

Revision ID: abc123
Revises:
Create Date: 2026-06-08 14:30:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic
revision = 'abc123'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    """Apply changes to database"""
    op.create_table(
        'weather_history',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('location', sa.String(length=100), nullable=False),
        sa.Column('temperature', sa.Numeric(precision=5, scale=2)),
        sa.Column('conditions', sa.Text()),
        sa.Column('timestamp', sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column('created_at', sa.TIMESTAMP(timezone=True),
                  server_default=sa.text('CURRENT_TIMESTAMP'))
    )

    # Create index
    op.create_index(
        'idx_location_timestamp',
        'weather_history',
        ['location', 'timestamp'],
        postgresql_ops={'timestamp': 'DESC'}
    )

def downgrade():
    """Reverse changes (for rollback)"""
    op.drop_index('idx_location_timestamp', table_name='weather_history')
    op.drop_table('weather_history')

Apply the migration to create the table:

Terminal 
# Apply migration (upgrade to latest)
alembic upgrade head

# Output:
# INFO  [alembic.runtime.migration] Running upgrade  -> abc123, create weather history table

One wrinkle if you've been following along: weather_history already exists in your database from Section 7, so this upgrade would fail with "relation already exists". Either run Alembic against a fresh database, or tell Alembic the existing schema already matches this version with alembic stamp head, which records the version without running the migration.

If you need to undo the migration:

Terminal 
# Downgrade by 1 version
alembic downgrade -1

# Downgrade to beginning (empty database)
alembic downgrade base

Schema evolution: adding a column

The power of Alembic shows when your schema needs to change. Imagine you want to add a humidity column to track that measurement too. Create a new migration:

Terminal 
# Create migration for schema change
alembic revision -m "add humidity column"

Open the generated file and fill it in:

alembic/versions/def456_add_humidity_column.py 
"""add humidity column

Revision ID: def456
Revises: abc123
Create Date: 2026-06-08 15:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

revision = 'def456'
down_revision = 'abc123'  # Points to previous migration
branch_labels = None
depends_on = None

def upgrade():
    """Add humidity column"""
    op.add_column(
        'weather_history',
        sa.Column('humidity', sa.Integer())
    )

def downgrade():
    """Remove humidity column"""
    op.drop_column('weather_history', 'humidity')

Apply the migration:

Terminal 
# Apply new migration
alembic upgrade head

# Check current version
alembic current

# See migration history
alembic history

Why Alembic matters

Five reasons this pays back the small upfront cost the moment your schema needs to change again:

  • Version control. Every schema change is tracked, documented, and reversible.
  • Team coordination. Developers run migrations to sync their local databases with production schema.
  • Deployment safety. Migrations run automatically during deployment, ensuring the production database matches the application code.
  • Rollback capability. If a deployment fails, you can downgrade the database to match the previous code version.
  • Data preservation. Alembic handles schema changes without losing existing data.

Automatic migration generation

Writing migrations manually teaches you what's happening under the hood, but Alembic's real power comes from automatic migration generation. Alembic can compare your Python model definitions to your current database state and generate the migration code for you.

This requires defining your models using SQLAlchemy (Alembic's parent library). Save the following as models.py at the project root:

models.py 
from sqlalchemy import Column, Integer, String, Numeric, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

Base = declarative_base()

class WeatherHistory(Base):
    __tablename__ = 'weather_history'

    id = Column(Integer, primary_key=True)
    location = Column(String(100), nullable=False)
    temperature = Column(Numeric(5, 2))
    conditions = Column(String)
    humidity = Column(Integer)  # Added earlier by the manual migration (def456)
    pressure = Column(Numeric(6, 2))  # New column to add via autogenerate
    timestamp = Column(
        TIMESTAMP(timezone=True),
        server_default=func.current_timestamp(),
        nullable=False
    )

Update alembic/env.py to import your models:

alembic/env.py (updated) 
# Add near the top of env.py
from models import Base

# Find this line:
# target_metadata = None

# Replace with:
target_metadata = Base.metadata

Now Alembic can detect schema changes automatically:

Terminal 
# Alembic compares your models to current database and generates migration
alembic revision --autogenerate -m "add pressure column"

# Output shows what it detected:
# INFO  [alembic.autogenerate.compare] Detected added column 'weather_history.pressure'
# Generating /path/to/alembic/versions/xyz789_add_pressure_column.py

The generated file looks like this:

alembic/versions/xyz789_add_pressure_column.py 
"""add pressure column

Revision ID: xyz789
Revises: def456
Create Date: 2026-06-08 16:30:00.000000

"""
from alembic import op
import sqlalchemy as sa

revision = 'xyz789'
down_revision = 'def456'

def upgrade():
    # Alembic generated this automatically!
    op.add_column('weather_history',
                  sa.Column('pressure', sa.Numeric(6, 2), nullable=True))

def downgrade():
    op.drop_column('weather_history', 'pressure')

Manual vs autogenerate

Both approaches have their place. Three rules of thumb separate them:

  • Manual migrations. Best for learning. Gives you full control. Necessary for complex data migrations or when you don't use SQLAlchemy models.
  • Autogenerate. What most teams actually use. Faster, less error-prone for schema changes, requires SQLAlchemy models. Always review the generated migration before applying. Alembic can't detect everything (a renamed column, for example, looks to it like a drop + add).
  • Hybrid (the production norm). Use autogenerate for schema changes, but always review and edit the generated file. For complex data transformations, write manual migrations. Most production teams use a hybrid approach: autogenerate creates the skeleton; developers refine it.

Apply the autogenerated migration the same way as the manual ones:

Terminal 
# Apply migration
alembic upgrade head

# Check current database version
alembic current

# View full migration history
alembic history --verbose

You've now seen the full Alembic loop: init, write a migration, apply it, evolve the schema, autogenerate from models. The final sub-page is a short review of everything in this chapter, plus a five-question quiz to consolidate.