Setting up alembic to detect the column length change

I love alembic, I love the way it simplifies our lives by generating bite size deltas to tables in our projects for simple and controlled migrations.

And today, I encountered an interesting problem with alembic on a project I am working on.

Consider the following example

models.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


class User(Model):
    __tablename__ = 'users'

    id = Column(types.Integer, primary_key=True)

    # ... omitted for brevity

    # authorization
    authorized = Column(types.Boolean(), default=False, server_default='f')
    access_token = Column(types.String(128), server_default='')
    refresh_token = Column(types.String(128), server_default='')

    # ... omitted for brevity

As simple as they come, we got some fields for tokens that are 128 char long, and all should be well. Unfortunately it turns out that the tokens should be 256 characters long. The change is as simple as it comes, just replace 128 with 256

models.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


class User(Model):
    __tablename__ = 'users'

    id = Column(types.Integer, primary_key=True)

    # ... omitted for brevity

    # authorization
    authorized = Column(types.Boolean(), default=False, server_default='f')
    access_token = Column(types.String(256), server_default='')
    refresh_token = Column(types.String(256), server_default='')

    # ... omitted for brevity

And then run the alembic to auto generate the migration with the usual command

bash

1

alembic revision --autogenerate -m "Upsize the token fields"

Which generates your migration file… And when you open it, it is empty… Wait! What?

1c78e2f11814_upsize_the_token_fields.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


# ... omitted for brevity

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###

That is certainly not what we wanted, what could possibly be wrong? Turns out that by default, the types are not compared in alembic, which makes sense as the type comparison has a varying accuracy depending on the backend.

The solution is as simple and as elusive as it gets.

In your alembic env.py file, modify the run_migrations_* methods context.configure segments by adding the compare_type=True (lines 15 and 37).

alembic/env.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


# ... omitted for brevity

def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True, compare_type=True)

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


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
        )

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

Delete the empty migration file (if you haven’t already) and rerun the migration auto generation the output will be what you would expect

53bcc796e829_upsize_the_token_fields.py

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


# ... omitted for brevity

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'access_token',
               existing_type=sa.VARCHAR(length=128),
               type_=sa.String(length=256),
               existing_nullable=True,
               existing_server_default=sa.text(u"''::character varying"))
    op.alter_column('users', 'refresh_token',
               existing_type=sa.VARCHAR(length=128),
               type_=sa.String(length=256),
               existing_nullable=True,
               existing_server_default=sa.text(u"''::character varying"))
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'refresh_token',
               existing_type=sa.String(length=256),
               type_=sa.VARCHAR(length=128),
               existing_nullable=True,
               existing_server_default=sa.text(u"''::character varying"))
    op.alter_column('users', 'access_token',
               existing_type=sa.String(length=256),
               type_=sa.VARCHAR(length=128),
               existing_nullable=True,
               existing_server_default=sa.text(u"''::character varying"))
    ### end Alembic commands ###

And there you go, your migrations will now be even less prone to manual editing before deployment.

The Joy of Hex

Drunken Monkey Coding Style with a hint of Carlin humor

Setting up alembic to detect the column length change