Skip to main content

Alembic Autogenerate: Auto-Detect Schema Changes

Alembic's autogenerate feature compares your SQLAlchemy ORM models to your database schema and writes migration code automatically. When you add a column to a model, run alembic revision --autogenerate, and Alembic generates the SQL to add that column. This saves you from writing boilerplate SQL by hand and is the fastest path for teams using SQLAlchemy.

How Autogenerate Works​

Alembic inspects two things: (1) your SQLAlchemy model definitions and (2) the current database schema. It computes the diff—what exists in your models but not the database, what's in the database but not your models—and generates Python code in the migration file that bridges the gap. The generated code uses Alembic's op object (a builder API) to express schema changes in a database-agnostic way.

For example, if you add email = Column(String(100)) to your User model, autogenerate produces:

op.add_column('users', sa.Column('email', sa.String(100), nullable=True))

This works on PostgreSQL, MySQL, SQLite, and other databases without modification.

Step 1: Define Your SQLAlchemy Models​

Start with a clean models file. Use the SQLAlchemy 2.0+ declarative syntax:

# app/models.py
from sqlalchemy import Column, Integer, String, DateTime, create_engine
from sqlalchemy.orm import DeclarativeBase
from datetime import datetime

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True)
    created_at = Column(DateTime, default=datetime.utcnow)

Alembic's autogenerate reads this definition and compares it to your database schema.

Tell Alembic where your models live by importing your Base in migrations/env.py:

# migrations/env.py
from app.models import Base  # Your declarative base

# ... (existing config code) ...

target_metadata = Base.metadata  # Critical: without this, autogenerate can't see your models

The target_metadata is the source of truth for what your schema should look like. Without it, Alembic can't auto-detect changes.

Step 3: Create an Autogenerated Migration​

Modify your model to add a column:

# app/models.py
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(100), unique=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    phone = Column(String(20))  # NEW COLUMN

Then run:

alembic revision --autogenerate -m "Add phone column to users"

Alembic inspects your models, finds the new phone column, and creates a migration file like 002_add_phone_column_to_users.py:

# migrations/versions/002_add_phone_column_to_users.py
def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))

def downgrade():
    op.drop_column('users', 'phone')

Open the file and review it. The generated code is usually correct, but autogenerate has limits (see below).

Step 4: Review and Apply​

Always review generated migrations before applying them, especially in production. Check:

  • Are the column types correct?
  • Is the nullable parameter what you expect?
  • Are indexes or unique constraints properly handled?

If the migration looks correct, apply it:

alembic upgrade head

What Autogenerate Can Detect​

Alembic's autogenerate detects:

  • New tables — creates op.create_table() calls
  • Dropped tables — creates op.drop_table() calls
  • New columns — creates op.add_column() calls
  • Dropped columns — creates op.drop_column() calls
  • Column type changes — creates op.alter_column() calls
  • Nullable changes — updates op.alter_column()
  • Unique constraints — creates op.create_unique_constraint() calls
  • Indexes — creates op.create_index() calls
  • Foreign keys — creates op.create_foreign_key() calls

What Autogenerate CANNOT Detect​

Autogenerate cannot automatically detect:

  • Column renames — Alembic sees a drop and an add; you must edit the migration to use op.alter_column(new_column_name=…)
  • Table renames — use op.rename_table() manually
  • Data transformations — moving data between columns or computing new values requires custom Python in the migration
  • Check constraints — use op.create_check_constraint() in the migration manually
  • Custom collations — not always detected; add manually if needed

For these cases, you edit the generated migration by hand.

Example: Rename a Column Correctly​

If you rename user_name to full_name in your model, autogenerate generates:

# Wrong autogenerate output (just drops and adds)
def upgrade():
    op.drop_column('users', 'user_name')
    op.add_column('users', sa.Column('full_name', sa.String(50)))

This loses data! Edit it to:

# Correct manual edit
def upgrade():
    op.alter_column('users', 'user_name', new_column_name='full_name')

def downgrade():
    op.alter_column('users', 'full_name', new_column_name='user_name')

Best Practices for Autogenerate​

  1. Always review generated migrations. Autogenerate is a time-saver, not a replacement for thinking.
  2. Commit model changes and migrations together. When you add a column to a model, commit the model file and the migration file in the same commit.
  3. Run migrations on a test database first. Before applying to production, run alembic upgrade head on a staging copy of your database and verify correctness.
  4. Use version control for your migrations. Never modify an applied migration; create a new one.
  5. Disable autogenerate for complex changes. If you're doing custom data transformations, write the migration from scratch (see next article).

Autogenerate Configuration​

You can configure autogenerate behavior in alembic.ini. For example, to ignore certain columns:

# alembic.ini
[alembic]
sqlalchemy.url = sqlite:///./app.db
include_schemas = true
render_as_batch = true  # For SQLite, allows complex alter operations

Key Takeaways​

  • Autogenerate compares your SQLAlchemy models to your database and writes migration code automatically
  • Use alembic revision --autogenerate -m "message" after changing your models
  • Always review generated migrations for correctness before applying them
  • Autogenerate handles most schema changes (new/dropped columns, tables, constraints) but not all (renames, data transforms)
  • For complex changes, edit the generated migration or write it from scratch

Frequently Asked Questions​

What if autogenerate produces an empty migration?​

If you run alembic revision --autogenerate and the resulting file has an empty upgrade(), Alembic found no differences between your models and your database. This usually means your database already matches your models. You can delete the empty migration file (from the versions/ folder) and run again after making a real change.

Can I autogenerate migrations for an existing database?​

Yes. Use alembic stamp <revision> to mark the current database state as an initial revision without creating a migration file. Then add columns to your models and autogenerate normally. This approach works if you have a legacy database and want to start tracking changes going forward.

How do I handle data migrations with autogenerate?​

Autogenerate is schema-only; it cannot write the logic to migrate data (e.g., split name into first_name and last_name). Write these migrations by hand using the op.execute() function or Python loops. See the next article on manual migrations.

Why is my autogenerate producing SQL instead of Python?​

Make sure target_metadata is set to Base.metadata in env.py. Without it, Alembic can't read your models and falls back to reading your database schema only (which is slower and less reliable).

Further Reading​