KAHIBARO
Discord Login Register ☰
☰

12.14. Alembic

Table of Contents

Why Alembic Matters

When you build a real application, your database schema will change many times. You will:

If you do this manually with raw ALTER TABLE statements, you quickly get:

Alembic solves this by giving you versioned database migrations for SQLAlchemy based projects.

With Alembic you:

Alembic is the standard migration tool for SQLAlchemy. If you use SQLAlchemy in production, you should use Alembic or some other migration tool, never manual SQL only.

This chapter focuses on how to use Alembic, not on designing schemas or writing SQL. Those topics are covered in other chapters.

Core Concepts

Alembic is a migration engine. Its main concepts are:

ConceptDescription
MigrationA small step that changes the schema, for example, add a column.
RevisionA migration file, identified by a unique revision ID.
UpgradeApply a migration, move schema to a newer version.
DowngradeReverse a migration, move schema to an older version.
Revision historyA chain of revisions, similar to git commits.
HeadThe latest revision in your migration history.
Alembic environmentThe configuration and script folder that defines how migrations run.

Each Alembic revision file contains two functions:

python 
def upgrade():
    # code that applies the migration
def downgrade():
    # code that reverses the migration

Alembic also maintains a special table in your database called something like alembic_version. It stores the current revision of the schema for that database.

Setting Up Alembic in a Project

Installing Alembic

Alembic is a Python package. Install it in your project environment:

bash 
pip install alembic

You should run all Alembic commands from your project root, inside your virtual environment.

Initializing Alembic

To create Alembic configuration and migrations directory:

bash 
alembic init alembic

This creates:

Typical project tree after initializing:

text 
my_app/
    app/
        __init__.py
        models.py
        db.py
    alembic/
        env.py
        script.py.mako
        versions/
    alembic.ini
    requirements.txt

Configuring Alembic

Alembic needs to know:

  1. How to connect to your database.
  2. How to import your SQLAlchemy Base and models, for autogeneration.

Database URL in `alembic.ini`

Open alembic.ini. You will see something like:

ini 
[alembic]
script_location = alembic
sqlalchemy.url = driver://user:pass@localhost/dbname

There are two main patterns.

Pattern 1: Put URL directly here

For simple projects or local testing:

ini 
sqlalchemy.url = postgresql+psycopg2://user:password@localhost:5432/mydb

Pattern 2: Read URL from environment or your code

More common in real applications. You leave sqlalchemy.url blank or dummy, and configure inside env.py.

Example alembic.ini:

ini 
sqlalchemy.url = driver_not_used://

Then in alembic/env.py you load environment variables or import from your app.

Configuring `env.py` to use your models

Open alembic/env.py. Find something like:

python 
from alembic import context
from sqlalchemy import engine_from_config, pool
# this is the Alembic Config object, which provides
# access to the values within the .ini file
config = context.config
target_metadata = None

You must set target_metadata to the MetaData of your SQLAlchemy models.

Assume you have something like this in app/db.py:

python 
from sqlalchemy.orm import declarative_base
Base = declarative_base()

And your models in app/models.py inherit from Base.

Then modify env.py:

python 
import os
import sys
from logging.config import fileConfig
from alembic import context
from sqlalchemy import engine_from_config, pool
# Add project root to sys.path so imports work
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from app.db import Base  # import your Base
config = context.config
fileConfig(config.config_file_name)
# Here, tell Alembic about your models
target_metadata = Base.metadata

Now Alembic can see your tables and generate migrations from them.

Reading the database URL from your app

If your app defines a DATABASE_URL or similar, you can reuse it.

Example app/db.py:

python 
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
DATABASE_URL = os.getenv("DATABASE_URL")
engine = create_engine(DATABASE_URL, future=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Base = declarative_base()

In alembic/env.py, instead of using the sqlalchemy.url from the ini, you can set it:

python 
from app.db import DATABASE_URL
config.set_main_option("sqlalchemy.url", DATABASE_URL)

A minimal env.py section might look like:

python 
from alembic import context
from sqlalchemy import engine_from_config, pool
from app.db import Base, DATABASE_URL
config = context.config
config.set_main_option("sqlalchemy.url", DATABASE_URL)
target_metadata = Base.metadata
def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()
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()
if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

You do not need to fully understand offline vs online mode now. For most use cases you will run migrations in online mode with a live database.

Creating Migrations

There are two main ways to create migrations:

  1. Autogenerate from your SQLAlchemy models.
  2. Manual migrations that you write yourself.

You often combine both.

Making your first autogeneration

Assume:

Run:

bash 
alembic revision --autogenerate -m "create initial tables"

Alembic will:

  1. Compare your Base.metadata (what models say should exist) with the actual database.
  2. Generate a migration script in alembic/versions/ with a random looking revision ID in the filename.

Example filename:

text 
alembic/versions/1975ea83b712_create_initial_tables.py

Open the created file. You will see something like:

python 
"""create initial tables"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic
revision = '1975ea83b712'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(), nullable=False, unique=True),
        sa.Column('hashed_password', sa.String(), nullable=False),
    )
def downgrade():
    op.drop_table('users')

op is Alembic’s helper object. You use it to declare schema changes.

Always open and review autogenerated migrations. Never trust autogeneration blindly. Fix issues by editing the migration file before running it.

Common operations with `op`

Here are typical examples of Alembic operations that you will use.

Create a new table

python 
def upgrade():
    op.create_table(
        'posts',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('content', sa.Text, nullable=False),
        sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id'), nullable=False),
    )
def downgrade():
    op.drop_table('posts')

Add a new column

python 
def upgrade():
    op.add_column('users', sa.Column('full_name', sa.String(200), nullable=True))
def downgrade():
    op.drop_column('users', 'full_name')

Rename a column

SQLAlchemy does not have a direct cross database rename. Alembic has op.alter_column with new_column_name, but not all databases support it. Example for PostgreSQL:

python 
def upgrade():
    op.alter_column('users', 'full_name', new_column_name='name')
def downgrade():
    op.alter_column('users', 'name', new_column_name='full_name')

Add an index

python 
def upgrade():
    op.create_index('ix_users_email', 'users', ['email'], unique=True)
def downgrade():
    op.drop_index('ix_users_email', table_name='users')

Add a foreign key constraint

python 
def upgrade():
    op.create_foreign_key(
        'fk_posts_user_id_users',
        source_table='posts',
        referent_table='users',
        local_cols=['user_id'],
        remote_cols=['id'],
        ondelete='CASCADE',
    )
def downgrade():
    op.drop_constraint('fk_posts_user_id_users', 'posts', type_='foreignkey')

Execute raw SQL

Only when necessary, for example complex operations:

python 
def upgrade():
    op.execute("UPDATE users SET full_name = '' WHERE full_name IS NULL")
def downgrade():
    # sometimes hard or impossible to fully reverse
    pass

Running Migrations

Once you have migration files, you apply them to the database.

Apply all pending migrations

bash 
alembic upgrade head

This will:

Upgrade to a specific revision

You can upgrade only to a particular revision ID:

bash 
alembic upgrade 1975ea83b712

Alembic understands also relative steps:

bash 
# Go one revision forward
alembic upgrade +1
# Go three revisions forward
alembic upgrade +3

Downgrade (rollback) migrations

To undo the last migration:

bash 
alembic downgrade -1

To go all the way back to the base (no migrations):

bash 
alembic downgrade base

Be very careful when downgrading in production, because:

Check current revision

bash 
alembic current

This prints the current revision of the connected database.

See history of revisions

bash 
alembic history

You will see something like:

text 
1975ea83b712 -> 3b1ae6349c66, add posts table
<base> -> 1975ea83b712, create initial tables

Typical Workflow with Alembic

Here is a practical step by step workflow for schema changes.

Scenario 1: Create initial schema

  1. Define your models using SQLAlchemy:
python 
   # app/models.py
   from sqlalchemy import Column, Integer, String
   from .db import Base
   class User(Base):
       __tablename__ = "users"
       id = Column(Integer, primary_key=True, index=True)
       email = Column(String, unique=True, nullable=False, index=True)
       hashed_password = Column(String, nullable=False)
  1. Initialize and configure Alembic, as described earlier.
  2. Autogenerate initial migration:
bash 
   alembic revision --autogenerate -m "create users table"
  1. Review the migration file, adjust if necessary.
  2. Apply the migration:
bash 
   alembic upgrade head

Now the database has the users table.

Scenario 2: Add a new column

You decide to add is_active to User.

  1. Change the model:
python 
   class User(Base):
       __tablename__ = "users"
       id = Column(Integer, primary_key=True, index=True)
       email = Column(String, unique=True, nullable=False, index=True)
       hashed_password = Column(String, nullable=False)
       is_active = Column(Boolean, nullable=False, server_default="true")
  1. Autogenerate:
bash 
   alembic revision --autogenerate -m "add is_active to users"
  1. Open the new migration:
python 
   def upgrade():
       op.add_column(
           'users',
           sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False)
       )
   def downgrade():
       op.drop_column('users', 'is_active')
  1. Apply it:
bash 
   alembic upgrade head

Now all environments that run this migration will have the new column.

Scenario 3: Data migration with schema change

You need to split a full_name column into first_name and last_name.

  1. Update your models to use first_name and last_name, and maybe keep full_name temporarily.
  2. Manually write a migration that:
    • Adds the new columns.
    • Copies and splits data.
    • (Optionally) drops the old column.

Example migration:

python 
from alembic import op
import sqlalchemy as sa
revision = '6677b0f8e2c0'
down_revision = '3b1ae6349c66'
branch_labels = None
depends_on = None
def upgrade():
    op.add_column('users', sa.Column('first_name', sa.String(100), nullable=True))
    op.add_column('users', sa.Column('last_name', sa.String(100), nullable=True))
    # Example simple split: assume full_name is "First Last"
    connection = op.get_bind()
    users = connection.execute(sa.text("SELECT id, full_name FROM users")).fetchall()
    for user in users:
        full_name = user.full_name or ""
        parts = full_name.split(" ", 1)
        first = parts[0] if len(parts) >= 1 else ""
        last = parts[1] if len(parts) == 2 else ""
        connection.execute(
            sa.text(
                "UPDATE users SET first_name = :first, last_name = :last WHERE id = :id"
            ),
            {"first": first, "last": last, "id": user.id},
        )
    # Optionally drop old column
    op.drop_column('users', 'full_name')
def downgrade():
    op.add_column('users', sa.Column('full_name', sa.String(200), nullable=True))
    connection = op.get_bind()
    users = connection.execute(
        sa.text("SELECT id, first_name, last_name FROM users")
    ).fetchall()
    for user in users:
        full = (user.first_name or "") + " " + (user.last_name or "")
        full = full.strip()
        connection.execute(
            sa.text(
                "UPDATE users SET full_name = :full WHERE id = :id"
            ),
            {"full": full, "id": user.id},
        )
    op.drop_column('users', 'first_name')
    op.drop_column('users', 'last_name')

This example shows that migrations are full Python scripts. You can read and write data directly when needed, not only alter schema.

Good Practices for Using Alembic

Keep migrations small and focused

Each revision should do one logical change, for example:

This makes upgrades and debugging easier.

Do not edit old migrations that already ran in shared environments

Once a migration has been:

Do not change its contents. Instead, create a new migration that fixes or adjusts the schema.

Changing old migrations after they ran can:

Always review autogenerated scripts

Autogeneration is helpful but not perfect. It can:

For example, if you only reorder columns in your model class, Alembic might try to recreate columns. That is not what you want. You must inspect and fix the resulting operations.

Use meaningful migration messages

When creating revisions:

bash 
alembic revision --autogenerate -m "add index on user email"

This message goes into the migration file and appears in alembic history. It is your documentation for the schema history.

Run migrations as part of deployment

A common production deployment flow:

  1. Build the new app image or package.
  2. Deploy it (but maybe keep it not serving traffic yet).
  3. Run alembic upgrade head to update the database schema.
  4. Start serving traffic with the new version.

This keeps app and database schema in sync.

Example: Integrating Alembic with a FastAPI + SQLAlchemy Project

Suppose you have:

text 
app/
    db.py
    models.py
    main.py
alembic/
    env.py
    versions/
alembic.ini

app/db.py:

python 
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql+psycopg2://user:pass@localhost/db")
engine = create_engine(DATABASE_URL, future=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Base = declarative_base()

app/models.py:

python 
from sqlalchemy import Column, Integer, String, Boolean
from .db import Base
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    is_active = Column(Boolean, nullable=False, server_default="true")

Configure alembic/env.py:

python 
import os
import sys
from logging.config import fileConfig
from alembic import context
from sqlalchemy import engine_from_config, pool
# Add project root to sys.path
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from app.db import Base, DATABASE_URL
config = context.config
fileConfig(config.config_file_name)
# Use DATABASE_URL from app
config.set_main_option("sqlalchemy.url", DATABASE_URL)
target_metadata = Base.metadata
def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()
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()
if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Workflow:

  1. Set DATABASE_URL in your environment.
  2. Generate initial migration:
bash 
   alembic revision --autogenerate -m "create users table"
  1. Review and run:
bash 
   alembic upgrade head
  1. Any time you change models, repeat:
bash 
   alembic revision --autogenerate -m "describe your change"
   alembic upgrade head

Summary

With a solid grasp of Alembic, you can evolve your database schema safely as your backend application grows.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3440 / 24h
Impressum / Imprint
Datenschutz / Data Policy