Python Alembic Autogenerate with SQLAlchemy

When you use python alembic autogenerate with sqlalchemy, you can generate migration scripts from your SQLAlchemy models instead of writing them by hand. This article explains how to set up autogenerate correctly, what it actually detects, and where it commonly goes wrong.

Why Use Alembic Autogenerate with SQLAlchemy

Alembic is a lightweight database migration tool that works directly with SQLAlchemy. Autogenerate compares the current database schema against the state of your SQLAlchemy models and produces a migration script that reflects the differences. This saves time and reduces the chance of missing a column or constraint when you change a model.

However, autogenerate is not magic. It relies on your models being accurately represented in a MetaData object and on your database being reachable for comparison. Understanding how it works under the hood helps you trust the output and know when to edit it manually.

Initial Alembic Setup for SQLAlchemy

Before autogenerate can work, you need a working Alembic environment. Install Alembic and SQLAlchemy, then initialize Alembic in your project:

pip install alembic sqlalchemy
alembic init alembic

This creates an alembic/ directory with an env.py file and a versions/ folder. The env.py file is the core of Alembic's integration with your application. It defines how Alembic connects to your database and where it finds your model metadata.

For autogenerate to see your models, you must import your SQLAlchemy models and attach their MetaData to the target_metadata variable. A typical setup looks like this:

# alembic/env.py
from alembic import context
from sqlalchemy import engine_from_config, pool

# Import your models and metadata
from myapp.models import Base

target_metadata = Base.metadata

If you have multiple metadata objects, you can combine them, but the simplest approach is to use a single declarative base for all models.

Configuring Autogenerate in env.py

The env.py file also controls whether autogenerate is enabled. The run_migrations_online function typically contains a call to context.configure(). To enable autogenerate, you must pass compare_type=True and optionally compare_server_default if you want to detect column type and server default changes. Without these, autogenerate only detects additions, removals, and renames of tables and columns.

A minimal online migration configuration looks like:

# alembic/env.py (online section)
def run_migrations_online():
    configuration = config.get_section(config.config_ini_section)
    connectable = engine_from_config(
        configuration,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
        )

        with context.begin_transaction():
            context.run_migrations()

Enabling compare_type makes autogenerate detect changes in column types, such as from String(50) to String(100). compare_server_default detects changes to server-side defaults. Both are useful, but they can produce noisy migrations if you have legacy columns that don't exactly match your model definitions.

Running Autogenerate and Reviewing the Migration

Once your environment is configured, you can generate a new migration with:

alembic revision --autogenerate -m "add user email"

Alembic will connect to the database, inspect the current schema, compare it to target_metadata, and write a new migration file in versions/. The file contains upgrade() and downgrade() functions with the detected operations.

You should always review the generated migration before applying it. Autogenerate may miss things like indexes on foreign keys, partial indexes, or check constraints that aren't defined in your models. It also may generate operations that you don't want, such as dropping a column that you renamed but didn't tell Alembic about. Reviewing the script is the only way to catch these issues.

For example, if you rename a column in your model, autogenerate will see it as a drop and an add, not a rename. You need to edit the migration to use alter_column with new_column_name to preserve data. This is a common pitfall.

Handling Naming Conventions and Metadata

Alembic's autogenerate relies on consistent naming conventions for constraints and indexes. If your models don't use a naming convention, autogenerate may not detect changes correctly because the database generates its own names for constraints. To avoid this, define a naming convention on your MetaData object.

from sqlalchemy import MetaData

NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata = MetaData(naming_convention=NAMING_CONVENTION)

Then use this metadata as the base for your declarative models:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base(metadata=metadata)

Without a naming convention, Alembic may generate migrations that drop and recreate constraints every time you run autogenerate, because the generated names differ between runs. Setting a convention makes autogenerate stable and predictable.

Common Autogenerate Pitfalls and How to Fix Them

Autogenerate is powerful, but it has known limitations. One common issue is that it doesn't detect changes to table comments, column comments, or certain index types. Another is that it may not handle server_default changes correctly unless you enable compare_server_default. Even then, default expressions that are written differently but evaluate to the same value (e.g., CURRENT_TIMESTAMP vs now()) may be flagged as changes.

Another pitfall is using autogenerate with a database that already has tables not represented in your models. Autogenerate will not drop those tables by default, but it may generate operations that conflict with them. You can set include_object in context.configure() to filter which objects Alembic considers.

For example, to ignore all tables that start with an underscore:

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and name.startswith("_"):
        return False
    return True

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    include_object=include_object,
)

This is useful when you have database-level tables that are managed by other tools.

Autogenerate in Production Workflows

In production, you should never run alembic revision --autogenerate directly against the live database. Instead, generate the migration against a local or staging database that mirrors production, review the script, commit it to version control, and then apply it to production using alembic upgrade head.

Autogenerate is a starting point, not a final answer. It works best when your models are well-defined and your naming conventions are consistent. For complex schema changes, such as data migrations or multi-step alterations, you will need to write custom migration code. Autogenerate can still be useful for the initial schema generation, but you should treat its output as a draft.

A common production pattern is to keep autogenerate enabled in development, but to require manual review for any migration that touches a table with existing data. You can enforce this with a code review process or by using Alembic's --sql flag to generate SQL without executing it, then inspecting that SQL.

Making Autogenerate More Reliable with Server Defaults

When you enable compare_server_default, Alembic will attempt to compare the server default in the database with the server_default argument in your model. This can be tricky because databases normalize default expressions. For example, sa.text("CURRENT_TIMESTAMP") may appear as CURRENT_TIMESTAMP in PostgreSQL, but as now() in MySQL. To avoid false positives, you can provide a custom comparison function using compare_server_default parameter.

def compare_server_default(autogen_context, inspected_column, metadata_column, inspected_default, metadata_default, rendered_metadata_default):
    # Normalize both defaults before comparison
    if inspected_default and metadata_default:
        return inspected_default.strip().lower() == metadata_default.strip().lower()
    return inspected_default == metadata_default

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    compare_server_default=compare_server_default,
)

This function receives the default values as strings and allows you to implement your own equality logic. It's a powerful way to reduce noise in autogenerate output, especially when you work with multiple database backends.

When Autogenerate Cannot Detect a Change

There are schema changes that autogenerate simply cannot detect because they are not represented in SQLAlchemy model definitions. For example, changes to indexes on expressions, partial indexes, or indexes with custom operators are not reflected in the MetaData unless you define them using SQLAlchemy's Index construct with the appropriate parameters. Even then, autogenerate may not compare all index attributes.

Similarly, autogenerate does not detect changes to table storage options, partitioning, or other database-specific features. If you rely on such features, you must write migration code manually or extend Alembic's comparison logic with custom compare_type, compare_indexes, or compare_constraints functions. These hooks are documented in Alembic's API and give you full control over what autogenerate reports.

For most projects, the default autogenerate behavior is sufficient. But knowing its limits helps you avoid surprises when you run it after a major model refactor. Always inspect the generated migration and test it against a copy of your production schema before applying it to the real database.