Auto Generating Migrations

Alembic can view the status of the database and compare against the table metadata in the application, generating the “obvious” migrations based on a comparison. This is achieved using the --autogenerate option to the alembic revision command, which places so-called candidate migrations into our new migrations file. We review and modify these by hand as needed, then proceed normally.

To use autogenerate, we first need to modify our env.py so that it gets access to a table metadata object that contains the target. Suppose our application has a declarative base in myapp.mymodel. This base contains a MetaData object which contains Table objects defining our database. We make sure this is loaded in env.py and then passed to EnvironmentContext.configure() via the target_metadata argument. The env.py sample script used in the generic template already has a variable declaration near the top for our convenience, where we replace None with our MetaData. Starting with:

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

we change to:

from myapp.mymodel import Base
target_metadata = Base.metadata

Note

The above example refers to the generic alembic env.py template, e.g. the one created by default when calling upon alembic init, and not the special-use templates such as multidb. Please consult the source code and comments within the env.py script directly for specific guidance on where and how the autogenerate metadata is established.

If we look later in the script, down in run_migrations_online(), we can see the directive passed to EnvironmentContext.configure():

def run_migrations_online():
    engine = engine_from_config(
                config.get_section(config.config_ini_section), prefix='sqlalchemy.')

    with engine.connect() as connection:
        context.configure(
                    connection=connection,
                    target_metadata=target_metadata
                    )

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

We can then use the alembic revision command in conjunction with the --autogenerate option. Suppose our MetaData contained a definition for the account table, and the database did not. We’d get output like:

$ alembic revision --autogenerate -m "Added account table"
INFO [alembic.context] Detected added table 'account'
Generating /path/to/foo/alembic/versions/27c6a30d7c24.py...done

We can then view our file 27c6a30d7c24.py and see that a rudimentary migration is already present:

"""empty message

Revision ID: 27c6a30d7c24
Revises: None
Create Date: 2011-11-08 11:40:27.089406

"""

# revision identifiers, used by Alembic.
revision = '27c6a30d7c24'
down_revision = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
    'account',
    sa.Column('id', sa.Integer()),
    sa.Column('name', sa.String(length=50), nullable=False),
    sa.Column('description', sa.VARCHAR(200)),
    sa.Column('last_transaction_date', sa.DateTime()),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("account")
    ### end Alembic commands ###

The migration hasn’t actually run yet, of course. We do that via the usual upgrade command. We should also go into our migration file and alter it as needed, including adjustments to the directives as well as the addition of other directives which these may be dependent on - specifically data changes in between creates/alters/drops.

What does Autogenerate Detect (and what does it not detect?)

The vast majority of user issues with Alembic centers on the topic of what kinds of changes autogenerate can and cannot detect reliably, as well as how it renders Python code for what it does detect. It is critical to note that autogenerate is not intended to be perfect. It is always necessary to manually review and correct the candidate migrations that autogenerate produces. The feature is getting more and more comprehensive and error-free as releases continue, but one should take note of the current limitations.

Autogenerate will detect:

  • Table additions, removals.

  • Column additions, removals.

  • Change of nullable status on columns.

  • Basic changes in indexes and explicitly-named unique constraints

  • Basic changes in foreign key constraints

Autogenerate can optionally detect:

  • Change of column type. This will occur if you set the EnvironmentContext.configure.compare_type parameter to True. The default implementation will reliably detect major changes, such as between Numeric and String, as well as accommodate for the types generated by SQLAlchemy’s “generic” types such as Boolean. Arguments that are shared between both types, such as length and precision values, will also be compared. If either the metadata type or database type has additional arguments beyond that of the other type, these are not compared, such as if one numeric type featured a “scale” and other type did not, this would be seen as the backing database not supporting the value, or reporting on a default that the metadata did not specify.

    The type comparison logic is fully extensible as well; see Comparing Types for details.

    Changed in version 1.4: type comparison code has been reworked such that column types are compared based on their rendered DDL, which should allow the functionality enabled by EnvironmentContext.configure.compare_type to be much more accurate, correctly accounting for the behavior of SQLAlchemy “generic” types as well as major arguments specified within types.

  • Change of server default. This will occur if you set the EnvironmentContext.configure.compare_server_default parameter to True, or to a custom callable function. This feature works well for simple cases but cannot always produce accurate results. The Postgresql backend will actually invoke the “detected” and “metadata” values against the database to determine equivalence. The feature is off by default so that it can be tested on the target schema first. Like type comparison, it can also be customized by passing a callable; see the function’s documentation for details.

Autogenerate can not detect:

  • Changes of table name. These will come out as an add/drop of two different tables, and should be hand-edited into a name change instead.

  • Changes of column name. Like table name changes, these are detected as a column add/drop pair, which is not at all the same as a name change.

  • Anonymously named constraints. Give your constraints a name, e.g. UniqueConstraint('col1', 'col2', name="my_name"). See the section The Importance of Naming Constraints for background on how to configure automatic naming schemes for constraints.

  • Special SQLAlchemy types such as Enum when generated on a backend which doesn’t support ENUM directly - this because the representation of such a type in the non-supporting database, i.e. a CHAR+ CHECK constraint, could be any kind of CHAR+CHECK. For SQLAlchemy to determine that this is actually an ENUM would only be a guess, something that’s generally a bad idea. To implement your own “guessing” function here, use the sqlalchemy.events.DDLEvents.column_reflect() event to detect when a CHAR (or whatever the target type is) is reflected, and change it to an ENUM (or whatever type is desired) if it is known that that’s the intent of the type. The sqlalchemy.events.DDLEvents.after_parent_attach() can be used within the autogenerate process to intercept and un-attach unwanted CHECK constraints.

Autogenerate can’t currently, but will eventually detect:

  • Some free-standing constraint additions and removals may not be supported, including PRIMARY KEY, EXCLUDE, CHECK; these are not necessarily implemented within the autogenerate detection system and also may not be supported by the supporting SQLAlchemy dialect.

  • Sequence additions, removals - not yet implemented.

Autogenerating Multiple MetaData collections

The target_metadata collection may also be defined as a sequence if an application has multiple MetaData collections involved:

from myapp.mymodel1 import Model1Base
from myapp.mymodel2 import Model2Base
target_metadata = [Model1Base.metadata, Model2Base.metadata]

The sequence of MetaData collections will be consulted in order during the autogenerate process. Note that each MetaData must contain unique table keys (e.g. the “key” is the combination of the table’s name and schema); if two MetaData objects contain a table with the same schema/name combination, an error is raised.

Controlling What to be Autogenerated

The autogenerate process scans across all table objects within the database that is referred towards by the current database connection in use.

The list of objects that are scanned in the target database connection include:

Omitting Schema Names from the Autogenerate Process

As the above set of database objects are typically to be compared to the contents of a single MetaData object, particularly when the EnvironmentContext.configure.include_schemas flag is enabled there is an important need to filter out unwanted “schemas”, which for some database backends might be the list of all the databases present. This filtering is best performed using the EnvironmentContext.configure.include_name hook, which provides for a callable that may return a boolean true/false indicating if a particular schema name should be included:

def include_name(name, type_, parent_names):
    if type_ == "schema":
        # note this will not include the default schema
        return name in ["schema_one", "schema_two"]
    else:
        return True

context.configure(
    # ...
    include_schemas = True,
    include_name = include_name
)

Above, when the list of schema names is first retrieved, the names will be filtered through the above include_name function so that only schemas named "schema_one" and "schema_two" will be considered by the autogenerate process.

In order to include the default schema, that is, the schema that is referred towards by the database connection without any explicit schema being specified, the name passed to the hook is None. To alter our above example to also include the default schema, we compare to None as well:

def include_name(name, type_, parent_names):
    if type_ == "schema":
        # this **will* include the default schema
        return name in [None, "schema_one", "schema_two"]
    else:
        return True

context.configure(
    # ...
    include_schemas = True,
    include_name = include_name
)

Omitting Table Names from the Autogenerate Process

The EnvironmentContext.configure.include_name hook is also most appropriate to limit the names of tables in the target database to be considered. If a target database has many tables that are not part of the MetaData, the autogenerate process will normally assume these are extraneous tables in the database to be dropped, and it will generate a Operations.drop_table() operation for each. To prevent this, the EnvironmentContext.configure.include_name hook may be used to search for each name within the tables collection of the MetaData object and ensure names which aren’t present are not included:

target_metadata = MyModel.metadata

def include_name(name, type_, parent_names):
    if type_ == "table":
        return name in target_metadata.tables
    else:
        return True

context.configure(
    # ...
    target_metadata = target_metadata,
    include_name = include_name,
    include_schemas = False
)

The above example is limited to table names present in the default schema only. In order to search within a MetaData collection for schema-qualified table names as well, a table present in the non default schema will be present under a name of the form <schemaname>.<tablename>. The EnvironmentContext.configure.include_name hook will present this schema name on a per-tablename basis in the parent_names dictionary, using the key "schema_name" that refers to the name of the schema currently being considered, or None if the schema is the default schema of the database connection:

# example fragment

if parent_names["schema_name"] is None:
    return name in target_metadata.tables
else:
    # build out schema-qualified name explicitly...
    return (
        "%s.%s" % (parent_names["schema_name"], name) in
        target_metadata.tables
    )

However more simply, the parent_names dictionary will also include the dot-concatenated name already constructed under the key "schema_qualified_table_name", which will also be suitably formatted for tables in the default schema as well with the dot omitted. So the full example of omitting tables with schema support may look like:

target_metadata = MyModel.metadata

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return name in [None, "schema_one", "schema_two"]
    elif type_ == "table":
        # use schema_qualified_table_name directly
        return (
            parent_names["schema_qualified_table_name"] in
            target_metadata.tables
        )
    else:
        return True

context.configure(
    # ...
    target_metadata = target_metadata,
    include_name = include_name,
    include_schemas = True
)

The parent_names dictionary will also include the key "table_name" when the name being considered is that of a column or constraint object local to a particular table.

The EnvironmentContext.configure.include_name hook only refers to reflected objects, and not those located within the target MetaData collection. For more fine-grained rules that include both MetaData and reflected object, the EnvironmentContext.configure.include_object hook discussed in the next section is more appropriate.

New in version 1.5: added the EnvironmentContext.configure.include_name hook.

Omitting Based on Object

The EnvironmentContext.configure.include_object hook provides for object-level inclusion/exclusion rules based on the Table object being reflected as well as the elements within it. This hook can be used to limit objects both from the local MetaData collection as well as from the target database. The limitation is that when it reports on objects in the database, it will have fully reflected that object, which can be expensive if a large number of objects will be omitted. The example below refers to a fine-grained rule that will skip changes on Column objects that have a user-defined flag skip_autogenerate placed into the info dictionary:

def include_object(object, name, type_, reflected, compare_to):
    if (type_ == "column" and
        not reflected and
        object.info.get("skip_autogenerate", False)):
        return False
    else:
        return True

context.configure(
    # ...
    include_object = include_object
)

Comparing and Rendering Types

The area of autogenerate’s behavior of comparing and rendering Python-based type objects in migration scripts presents a challenge, in that there’s a very wide variety of types to be rendered in scripts, including those part of SQLAlchemy as well as user-defined types. A few options are given to help out with this task.

Controlling the Module Prefix

When types are rendered, they are generated with a module prefix, so that they are available based on a relatively small number of imports. The rules for what the prefix is is based on the kind of datatype as well as configurational settings. For example, when Alembic renders SQLAlchemy types, it will by default prefix the type name with the prefix sa.:

Column("my_column", sa.Integer())

The use of the sa. prefix is controllable by altering the value of EnvironmentContext.configure.sqlalchemy_module_prefix:

def run_migrations_online():
    # ...

    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                sqlalchemy_module_prefix="sqla.",
                # ...
                )

    # ...

In either case, the sa. prefix, or whatever prefix is desired, should also be included in the imports section of script.py.mako; it also defaults to import sqlalchemy as sa.

For user-defined types, that is, any custom type that is not within the sqlalchemy. module namespace, by default Alembic will use the value of __module__ for the custom type:

Column("my_column", myapp.models.utils.types.MyCustomType())

The imports for the above type again must be made present within the migration, either manually, or by adding it to script.py.mako.

The above custom type has a long and cumbersome name based on the use of __module__ directly, which also implies that lots of imports would be needed in order to accommodate lots of types. For this reason, it is recommended that user-defined types used in migration scripts be made available from a single module. Suppose we call it myapp.migration_types:

# myapp/migration_types.py

from myapp.models.utils.types import MyCustomType

We can first add an import for migration_types to our script.py.mako:

from alembic import op
import sqlalchemy as sa
import myapp.migration_types
${imports if imports else ""}

We then override Alembic’s use of __module__ by providing a fixed prefix, using the EnvironmentContext.configure.user_module_prefix option:

def run_migrations_online():
    # ...

    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                user_module_prefix="myapp.migration_types.",
                # ...
                )

    # ...

Above, we now would get a migration like:

Column("my_column", myapp.migration_types.MyCustomType())

Now, when we inevitably refactor our application to move MyCustomType somewhere else, we only need modify the myapp.migration_types module, instead of searching and replacing all instances within our migration scripts.

Affecting the Rendering of Types Themselves

The methodology Alembic uses to generate SQLAlchemy and user-defined type constructs as Python code is plain old __repr__(). SQLAlchemy’s built-in types for the most part have a __repr__() that faithfully renders a Python-compatible constructor call, but there are some exceptions, particularly in those cases when a constructor accepts arguments that aren’t compatible with __repr__(), such as a pickling function.

When building a custom type that will be rendered into a migration script, it is often necessary to explicitly give the type a __repr__() that will faithfully reproduce the constructor for that type. This, in combination with EnvironmentContext.configure.user_module_prefix, is usually enough. However, if additional behaviors are needed, a more comprehensive hook is the EnvironmentContext.configure.render_item option. This hook allows one to provide a callable function within env.py that will fully take over how a type is rendered, including its module prefix:

def render_item(type_, obj, autogen_context):
    """Apply custom rendering for selected items."""

    if type_ == 'type' and isinstance(obj, MySpecialType):
        return "mypackage.%r" % obj

    # default rendering for other objects
    return False

def run_migrations_online():
    # ...

    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                render_item=render_item,
                # ...
                )

    # ...

In the above example, we’d ensure our MySpecialType includes an appropriate __repr__() method, which is invoked when we call it against "%r".

The callable we use for EnvironmentContext.configure.render_item can also add imports to our migration script. The AutogenContext passed in contains a datamember called AutogenContext.imports, which is a Python set() for which we can add new imports. For example, if MySpecialType were in a module called mymodel.types, we can add the import for it as we encounter the type:

def render_item(type_, obj, autogen_context):
    """Apply custom rendering for selected items."""

    if type_ == 'type' and isinstance(obj, MySpecialType):
        # add import for this type
        autogen_context.imports.add("from mymodel import types")
        return "types.%r" % obj

    # default rendering for other objects
    return False

The finished migration script will include our imports where the ${imports} expression is used, producing output such as:

from alembic import op
import sqlalchemy as sa
from mymodel import types

def upgrade():
    op.add_column('sometable', Column('mycolumn', types.MySpecialType()))

Comparing Types

The default type comparison logic will work for SQLAlchemy built in types as well as basic user defined types. This logic is only enabled if the EnvironmentContext.configure.compare_type parameter is set to True:

context.configure(
    # ...
    compare_type = True
)

Note

The default type comparison logic (which is end-user extensible) currently (as of Alembic version 1.4.0) works by comparing the generated SQL for a column. It does this in two steps-

  • First, it compares the outer type of each column such as VARCHAR or TEXT. Dialect implementations can have synonyms that are considered equivalent- this is because some databases support types by converting them to another type. For example, NUMERIC and DECIMAL are considered equivalent on all backends, while on the Oracle backend the additional synonyms BIGINT, INTEGER, NUMBER, SMALLINT are added to this list of equivalents

  • Next, the arguments within the type, such as the lengths of strings, precision values for numerics, the elements inside of an enumeration are compared. If BOTH columns have arguments AND they are different, a change will be detected. If one column is just set to the default and the other has arguments, Alembic will pass on attempting to compare these. The rationale is that it is difficult to detect what a database backend sets as a default value without generating false positives.

Changed in version 1.4.0: Added the text and keyword comparison for column types

Alternatively, the EnvironmentContext.configure.compare_type parameter accepts a callable function which may be used to implement custom type comparison logic, for cases such as where special user defined types are being used:

def my_compare_type(context, inspected_column,
            metadata_column, inspected_type, metadata_type):
    # return False if the metadata_type is the same as the inspected_type
    # or None to allow the default implementation to compare these
    # types. a return value of True means the two types do not
    # match and should result in a type change operation.
    return None

context.configure(
    # ...
    compare_type = my_compare_type
)

Above, inspected_column is a sqlalchemy.schema.Column as returned by sqlalchemy.engine.reflection.Inspector.reflect_table(), whereas metadata_column is a sqlalchemy.schema.Column from the local model environment. A return value of None indicates that default type comparison to proceed.

Additionally, custom types that are part of imported or third party packages which have special behaviors such as per-dialect behavior should implement a method called compare_against_backend() on their SQLAlchemy type. If this method is present, it will be called where it can also return True or False to specify the types compare as equivalent or not; if it returns None, default type comparison logic will proceed:

class MySpecialType(TypeDecorator):

    # ...

    def compare_against_backend(self, dialect, conn_type):
        # return True if this type is the same as the given database type,
        # or None to allow the default implementation to compare these
        # types. a return value of False means the given type does not
        # match this type.

        if dialect.name == 'postgresql':
            return isinstance(conn_type, postgresql.UUID)
        else:
            return isinstance(conn_type, String)

Warning

The boolean return values for the above compare_against_backend method, which is part of SQLAlchemy and not Alembic,are the opposite of that of the EnvironmentContext.configure.compare_type callable, returning True for types that are the same vs. False for types that are different.The EnvironmentContext.configure.compare_type callable on the other hand should return True for types that are different.

The order of precedence regarding the EnvironmentContext.configure.compare_type callable vs. the type itself implementing compare_against_backend is that the EnvironmentContext.configure.compare_type callable is favored first; if it returns None, then the compare_against_backend method will be used, if present on the metadata type. If that returns None, then a basic check for type equivalence is run.

New in version 1.4.0: - added column keyword comparisons and the type_synonyms property.

Applying Post Processing and Python Code Formatters to Generated Revisions

Revision scripts generated by the alembic revision command can optionally be piped through a series of post-production functions which may analyze or rewrite Python source code generated by Alembic, within the scope of running the revision command. The primary intended use of this feature is to run code-formatting tools such as Black or autopep8, as well as custom-written formatting and linter functions, on revision files as Alembic generates them. Any number of hooks can be configured and they will be run in series, given the path to the newly generated file as well as configuration options.

The post write hooks, when configured, run against generated revision files regardless of whether or not the autogenerate feature was used.

New in version 1.2.

Note

Alembic’s post write system is partially inspired by the pre-commit tool, which configures git hooks that reformat source files as they are committed to a git repository. Pre-commit can serve this role for Alembic revision files as well, applying code formatters to them as they are committed. Alembic’s post write hooks are useful only in that they can format the files immediately upon generation, rather than at commit time, and also can be useful for projects that prefer not to use pre-commit.

Basic Formatter Configuration

The alembic.ini samples now include commented-out configuration illustrating how to configure code-formatting tools to run against the newly generated file path. Example:

[post_write_hooks]

# format using "black"
hooks=black

black.type = console_scripts
black.entrypoint = black
black.options = -l 79

Above, we configure hooks to be a single post write hook labeled "black". Note that this label is arbitrary. We then define the configuration for the "black" post write hook, which includes:

  • type - this is the type of hook we are running. Alembic includes a hook runner called "console_scripts", which is specifically a Python function that uses subprocess.run() to invoke a separate Python script against the revision file. For a custom-written hook function, this configuration variable would refer to the name under which the custom hook was registered; see the next section for an example.

The following configuration options are specific to the "console_scripts" hook runner:

  • entrypoint - the name of the setuptools entrypoint that is used to define the console script. Within the scope of standard Python console scripts, this name will match the name of the shell command that is usually run for the code formatting tool, in this case black.

  • options - a line of command-line options that will be passed to the code formatting tool. In this case, we want to run the command black /path/to/revision.py -l 79. By default, the revision path is positioned as the first argument. In order specify a different position, we can use the REVISION_SCRIPT_FILENAME token as illustrated by the subsequent examples.

    Note

    Make sure options for the script are provided such that it will rewrite the input file in place. For example, when running autopep8, the --in-place option should be provided:

    [post_write_hooks]
hooks = autopep8
autopep8.type = console_scripts
autopep8.entrypoint = autopep8
autopep8.options = --in-place REVISION_SCRIPT_FILENAME

  • cwd - optional working directory from which the console script is run.

When running alembic revision -m "rev1", we will now see the black tool’s output as well:

$ alembic revision -m "rev1"
  Generating /path/to/project/versions/481b13bc369a_rev1.py ... done
  Running post write hook "black" ...
reformatted /path/to/project/versions/481b13bc369a_rev1.py
All done! ✨ 🍰 ✨
1 file reformatted.
  done

Hooks may also be specified as a list of names, which correspond to hook runners that will run sequentially. As an example, we can also run the zimports import rewriting tool (written by Alembic’s author) subsequent to running the black tool, using a configuration as follows:

[post_write_hooks]

# format using "black", then "zimports"
hooks=black, zimports

black.type = console_scripts
black.entrypoint = black
black.options = -l 79 REVISION_SCRIPT_FILENAME

zimports.type = console_scripts
zimports.entrypoint = zimports
zimports.options = --style google REVISION_SCRIPT_FILENAME

When using the above configuration, a newly generated revision file will be processed first by the “black” tool, then by the “zimports” tool.

Alternatively, one can run pre-commit itself as follows:

[post_write_hooks]

hooks = pre-commit

pre-commit.type = console_scripts
pre-commit.entrypoint = pre-commit
pre-commit.options = run --files REVISION_SCRIPT_FILENAME
pre-commit.cwd = %(here)s

(The last line helps to ensure that the .pre-commit-config.yaml file will always be found, regardless of from where the hook was called.)

Writing Custom Hooks as Python Functions

The previous section illustrated how to run command-line code formatters, through the use of a post write hook provided by Alembic known as console_scripts. This hook is in fact a Python function that is registered under that name using a registration function that may be used to register other types of hooks as well.

To illustrate, we will use the example of a short Python function that wants to rewrite the generated code to use tabs instead of four spaces. For simplicity, we will illustrate how this function can be present directly in the env.py file. The function is declared and registered using the write_hooks.register() decorator:

from alembic.script import write_hooks
import re

@write_hooks.register("spaces_to_tabs")
def convert_spaces_to_tabs(filename, options):
    lines = []
    with open(filename) as file_:
        for line in file_:
            lines.append(
                re.sub(
                    r"^(    )+",
                    lambda m: "\t" * (len(m.group(1)) // 4),
                    line
                )
            )
    with open(filename, "w") as to_write:
        to_write.write("".join(lines))

Our new "spaces_to_tabs" hook can be configured in alembic.ini as follows:

[alembic]

# ...

# ensure the revision command loads env.py
revision_environment = true

[post_write_hooks]

hooks = spaces_to_tabs

spaces_to_tabs.type = spaces_to_tabs

When alembic revision is run, the env.py file will be loaded in all cases, the custom “spaces_to_tabs” function will be registered and it will then be run against the newly generated file path:

$ alembic revision -m "rev1"
  Generating /path/to/project/versions/481b13bc369a_rev1.py ... done
  Running post write hook "spaces_to_tabs" ...
  done