[PYTHON] Notes on using Alembic

Alembic is a migration tool for sqlalchemy, a python orm. There aren't many articles in Japanese, and I often didn't realize that I was using it, so I'll write down the point.

Alembic：http://alembic.zzzcomputing.com/en/latest/

I want to handle multiple model files

When there are multiple model files, it is necessary to combine the metadata of the Base class. http://liuhongjiang.github.io/hexotech/2015/10/14/alembic-support-multiple-model-files/ The method described in this blog works. In my case, I tried using dynamic import.

env.py

import importlib
from sqlalchemy.schema import MetaData

target_models =[
    'path.to.models',
    'another.models'
]

def import_model_bases():
    """import all target models base metadatas."""
    lst = list(map(
        lambda x: importlib.import_module(x).Base.metadata,
        target_models
    ))
    return lst


def combine_metadata(lst):
    m = MetaData()
    for metadata in lst:
        for t in metadata.tables.values():
            t.tometadata(m)
    return m

target_metadata = combine_metadata(import_model_bases())

Does not detect type change

If you keep the same column name, it will not detect the type change. It seems that it is set so by default. http://stackoverflow.com/questions/17174636/can-alembic-autogenerate-column-alterations This is also modified as in this article.

env.py

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    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,
            compare_type=True #add to
        )

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

Judge Boolean and Tinyint as different types

Now that you're aware of the type change, it's a problem when dealing with Boolean types. Boolean is set by tinyint in mysql, but when comparing, it is judged that the type is different and it tries to delete and create columns every time. Also, changing the type from tinyint to integer does not detect it well.

• https://bitbucket.org/zzzeek/alembic/issues/46/mysqltinyint-display_width-1-vs-saboolean
• http://alembic.zzzcomputing.com/en/latest/autogenerate.html#comparing-types

If you fix it with the feeling that it is written with reference to here, it will work. I tried as follows.

env.py

#add to
from sqlalchemy import engine_from_config, types
from sqlalchemy.dialects import mysql

def my_compare_type(context, inspected_column,
                    metadata_column, inspected_type, metadata_type):
    """my compser type for mysql."""
    if isinstance(inspected_type, mysql.TINYINT) and\
       isinstance(metadata_type, types.Boolean):
        return False
    if isinstance(inspected_type, mysql.TINYINT) and\
       isinstance(metadata_type, types.Integer):
        return True
    return None

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    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,
            compare_type=my_compare_type #Change
        )

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

Since alembic has many functions and many functions that I haven't used yet, I will add what I was addicted to later.