How To Use Alembic for Database Migrations in Python

Intermediate

Adding a column to a live database sounds simple until you realize you need to do it on a production server with real user data, make the change repeatable across three developer laptops and a staging environment, and be able to roll it back if something goes wrong. Running raw ALTER TABLE statements by hand works once. It does not work as a team workflow across environments, and it leaves no record of what changed, when, or why.

Alembic is the database migration tool built specifically for SQLAlchemy. It tracks your schema version in a dedicated table, generates migration scripts automatically by comparing your models to the live database, and lets you apply (upgrade) or reverse (downgrade) changes one step at a time. Alembic is the standard migration tool for SQLAlchemy-based projects and is used in production by frameworks like FastAPI with SQLModel, and Flask with Flask-SQLAlchemy. Installing it is a single command: pip install alembic sqlalchemy.

In this tutorial you’ll set up Alembic in a real project, create and run migrations, handle common schema changes (add column, rename, add index), write manual migrations for data transformations, and use the downgrade mechanism safely. By the end you’ll have a versioned, team-friendly database workflow.

Alembic Migrations: Quick Example

Part of the Python Database Hub. See the full hub for related Python tutorials.

Here is the fastest path from a blank project to a running migration. These are terminal commands, not Python scripts.

# Terminal commands

# Install dependencies
pip install alembic sqlalchemy

# Initialize Alembic in your project
alembic init alembic

# This creates:
#   alembic/           -- migration scripts directory
#   alembic/env.py     -- Alembic configuration (edit this to point at your DB)
#   alembic/versions/  -- migration script files go here
#   alembic.ini        -- Alembic settings file

# After configuring alembic.ini and env.py (see next section)...

# Generate a migration from model changes
alembic revision --autogenerate -m "add users table"

# Apply the migration
alembic upgrade head

# Check current version
alembic current

# Roll back one step
alembic downgrade -1

Output of alembic upgrade head:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> a3f92b1c4d8e, add users table

Every migration is identified by a unique revision ID (the a3f92b1c4d8e part). Alembic records the current revision in a special table (alembic_version) in your database, so it always knows which migrations have run and which haven’t. The hash shown in upgrade head is the latest migration script’s revision ID.

What is Alembic and How Does It Work?

Alembic works by maintaining a chain of migration scripts. Each script has an upgrade() function that applies a change (add a column, create a table) and a downgrade() function that reverses it. Scripts are linked in a revision chain, and Alembic walks that chain to get from wherever your database currently is to wherever you want it to be.

Concept	What it means
Revision	A unique ID identifying one migration script
Head	The latest revision (most up-to-date)
Base	The initial state (no migrations applied)
alembic_version	Table Alembic creates in your DB to track current revision
upgrade	Apply one or more migrations forward
downgrade	Reverse one or more migrations backward
autogenerate	Auto-detect schema changes by comparing models to DB

The --autogenerate flag is Alembic’s most powerful feature: it imports your SQLAlchemy models, inspects the live database, and generates the migration script automatically. However, autogenerate cannot detect everything — it misses data changes, stored procedures, and some constraint types — so always review generated scripts before running them.

Setting Up Alembic in a Project

Alembic needs to know two things: the database URL to connect to, and where to find your SQLAlchemy models. Both are configured in alembic.ini and alembic/env.py.

# models.py -- your SQLAlchemy models
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.orm import DeclarativeBase
from datetime import datetime

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False, unique=True)
    username = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    title = Column(String(500), nullable=False)
    body = Column(String, nullable=False)
    author_id = Column(Integer, nullable=False)

# alembic/env.py -- edit the target_metadata line and database URL

import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

# Import your models Base so autogenerate can see the schema
from models import Base

config = context.config
fileConfig(config.config_file_name)

# This is the key line -- point autogenerate at your models
target_metadata = Base.metadata

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()

run_migrations_online()

# alembic.ini -- set your database URL
# Find the sqlalchemy.url line and update it:
# sqlalchemy.url = sqlite:///./myapp.db
# For PostgreSQL: postgresql://user:pass@localhost/mydb
# For MySQL:      mysql+pymysql://user:pass@localhost/mydb

The critical step is setting target_metadata = Base.metadata in env.py. This is what enables autogenerate to compare your Python models against the live database schema. If you skip this, autogenerate produces empty migration scripts. Make sure models.py is importable from env.py — add your project root to sys.path as shown above if needed.

Creating and Running Migrations

Once configured, the typical migration workflow is three commands: generate, review, apply.

# Terminal -- migration workflow

# Step 1: Generate a migration from current model state
alembic revision --autogenerate -m "create users and posts tables"
# Output: Generating /project/alembic/versions/a1b2c3d4e5f6_create_users_and_posts_tables.py

# Step 2: Review the generated file (always do this before applying)
cat alembic/versions/a1b2c3d4e5f6_create_users_and_posts_tables.py

Here is what a typical auto-generated migration script looks like:

# alembic/versions/a1b2c3d4e5f6_create_users_and_posts_tables.py
"""create users and posts tables

Revision ID: a1b2c3d4e5f6
Revises:
Create Date: 2026-05-16 08:30:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = 'a1b2c3d4e5f6'
down_revision = None   # None means this is the first migration
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(length=255), nullable=False),
        sa.Column('username', sa.String(length=100), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
    )
    op.create_table(
        'posts',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=500), nullable=False),
        sa.Column('body', sa.String(), nullable=False),
        sa.Column('author_id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
    )

def downgrade() -> None:
    op.drop_table('posts')
    op.drop_table('users')

# Step 3: Apply the migration
alembic upgrade head

# Verify current state
alembic current
# INFO  [alembic.runtime.migration] Running upgrade  -> a1b2c3d4e5f6 (head)
# a1b2c3d4e5f6 (head)

# Show full history
alembic history --verbose

The down_revision = None indicates this is the first migration in the chain. Subsequent migrations will reference this revision ID in their own down_revision, forming the chain. Never edit a migration script after it has been applied to any shared environment — create a new migration instead.

Common Schema Change Operations

Most real-world migrations involve adding columns, creating indexes, or renaming things. Here are migration scripts for the most common operations, showing both the upgrade() and downgrade() halves.

# Manual migration script: add columns and index
# alembic/versions/b2c3d4e5f6a7_add_user_bio_and_index.py

"""add bio to users and index on email

Revision ID: b2c3d4e5f6a7
Revises: a1b2c3d4e5f6
Create Date: 2026-05-16 09:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = 'b2c3d4e5f6a7'
down_revision = 'a1b2c3d4e5f6'
branch_labels = None
depends_on = None

def upgrade() -> None:
    # Add a nullable column with a server-side default
    op.add_column('users', sa.Column('bio', sa.Text(), nullable=True))

    # Add a column with NOT NULL -- requires a server_default for existing rows
    op.add_column('users', sa.Column('is_active', sa.Boolean(),
                                     nullable=False, server_default=sa.true()))

    # Create an index for faster email lookups
    op.create_index('ix_users_email', 'users', ['email'], unique=False)

    # Add a foreign key column to posts
    op.add_column('posts', sa.Column('published_at', sa.DateTime(), nullable=True))
    op.create_index('ix_posts_author_id', 'posts', ['author_id'])

def downgrade() -> None:
    op.drop_index('ix_posts_author_id', table_name='posts')
    op.drop_column('posts', 'published_at')
    op.drop_index('ix_users_email', table_name='users')
    op.drop_column('users', 'is_active')
    op.drop_column('users', 'bio')

The server_default=sa.true() is critical when adding a NOT NULL column to a table that already has rows. Without a default, the database will reject the ALTER because existing rows would have a NULL value in a NOT NULL column. Use server_default for the database-level default and default for the Python-level default (used by SQLAlchemy when inserting new rows). After adding the column with a server default, you can remove the default in a follow-up migration if desired.

Real-Life Example: Adding a Subscription System to an Existing App

Here is a realistic scenario: adding a subscription tier system to an existing user table, including a data migration that sets a default tier for all existing users.

# alembic/versions/c3d4e5f6a7b8_add_subscription_tiers.py
"""add subscription tiers table and user tier column

Revision ID: c3d4e5f6a7b8
Revises: b2c3d4e5f6a7
Create Date: 2026-05-16 10:00:00.000000
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column
from sqlalchemy import String, Integer

revision = 'c3d4e5f6a7b8'
down_revision = 'b2c3d4e5f6a7'

def upgrade() -> None:
    # 1. Create the subscription_tiers lookup table
    op.create_table(
        'subscription_tiers',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(50), nullable=False, unique=True),
        sa.Column('monthly_price_cents', sa.Integer(), nullable=False),
    )

    # 2. Seed the lookup data (data migration inside schema migration)
    tiers_table = table('subscription_tiers',
                        column('id', Integer),
                        column('name', String),
                        column('monthly_price_cents', Integer))
    op.bulk_insert(tiers_table, [
        {'id': 1, 'name': 'free', 'monthly_price_cents': 0},
        {'id': 2, 'name': 'pro', 'monthly_price_cents': 999},
        {'id': 3, 'name': 'enterprise', 'monthly_price_cents': 4999},
    ])

    # 3. Add tier_id to users with a default of 1 (free)
    op.add_column('users',
        sa.Column('tier_id', sa.Integer(),
                  sa.ForeignKey('subscription_tiers.id'),
                  nullable=False,
                  server_default='1'))

    # 4. Create an index for tier-based queries
    op.create_index('ix_users_tier_id', 'users', ['tier_id'])

def downgrade() -> None:
    op.drop_index('ix_users_tier_id', table_name='users')
    op.drop_column('users', 'tier_id')
    op.drop_table('subscription_tiers')

# Apply all pending migrations
alembic upgrade head

# Verify
alembic current
# c3d4e5f6a7b8 (head)

The op.bulk_insert() call seeds the lookup data as part of the migration, so the data migration runs atomically with the schema migration. The table() and column() helpers from sqlalchemy.sql let you reference tables without importing your model classes — this is important because model classes can change after the migration is written, but the migration must always produce the same result when re-run. Never import model classes directly inside migration scripts.

Frequently Asked Questions

What does alembic autogenerate miss?

Autogenerate detects table creation, table removal, column additions, column removals, column type changes, and unique constraint changes on most databases. It does NOT detect: changes to stored procedures, changes to data (you need data migrations for those), changes to indexes on some databases, changes to schema comments, or changes made outside of SQLAlchemy (e.g., manual ALTER TABLE commands run directly). Always review generated migration scripts before applying them.

Is it safe to downgrade in production?

Downgrading can be risky if the migration involved adding columns with data, dropping tables, or making irreversible data changes. Always test your downgrade function on a copy of production data before relying on it as a real rollback strategy. For critical migrations, write the downgrade function even if you never plan to use it — it forces you to think through the reversal and can save you in an emergency. For truly irreversible changes (e.g., dropping a table), add a comment and raise NotImplementedError in the downgrade function to make the intentionality explicit.

What does ‘multiple heads’ mean?

If two developers each create a migration from the same base revision, Alembic has two “heads” — two migration chains that diverged at the same point. You need to merge them with alembic merge heads, which creates a new merge revision that depends on both heads. This is analogous to a merge commit in Git. The merge revision has an empty upgrade/downgrade and simply links the chains back together.

How do I run migrations with zero downtime?

Zero-downtime migrations require careful ordering. The safe pattern is: first add the new column as nullable (no application change needed), then deploy the application code that writes to the new column, then backfill existing rows, then add the NOT NULL constraint. Never add a NOT NULL column without a server default in a single migration on a large production table — it locks the table during the ALTER on most databases. Tools like pg_repack for PostgreSQL can help with large table changes.

How do I use different database URLs for dev and production?

Set the database URL from an environment variable in env.py instead of hardcoding it in alembic.ini. Add this to env.py: config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"]). Then in CI/CD and production, set DATABASE_URL as a secret environment variable. Never put production credentials in alembic.ini — that file is typically committed to version control.

Conclusion

Alembic gives you a versioned, reproducible way to evolve your database schema over time. You learned to set up Alembic with alembic init, configure env.py to point at your SQLAlchemy models, generate migrations with alembic revision --autogenerate, apply them with alembic upgrade head, and roll back with alembic downgrade -1. The real-life example showed how to combine schema changes with seed data in a single atomic migration.

The most important habit to build is always reviewing autogenerated scripts before applying them and always writing correct downgrade functions. With those two practices in place, Alembic becomes the safety net that lets you evolve your database with confidence rather than dread.

The official Alembic documentation with a tutorial, cookbook, and API reference is at alembic.sqlalchemy.org.

Related Articles

• How To Use SQLAlchemy 2.0 ORM with Python
• How To Connect Python to PostgreSQL with psycopg
• How To Use SQLite with Python: A Complete Guide