Ensuring predictable column order in migration

[shamiradjaku]

Describe your question
I am using Alembic with SqlAlchemy to autogenerate migrations. The column order in the migration file doesn't always match the order in the SqlAlchemy class. Is there a way to ensure this?

**Example **
I started with a class like this.

class SampleData(PublicSchemaBase):
    __tablename__ = "sample_data"

    user_id = Column(Integer, primary_key=True)
    name = Column(String(765))
    age = Column(Integer)
    country = Column(String(765))

Alembic generated an upgrade script like

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "sample_data",
        sa.Column("user_id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=765), nullable=True),
        sa.Column("age", sa.Integer(), nullable=True),
        sa.Column("country", sa.String(length=765), nullable=True),
        sa.PrimaryKeyConstraint("user_id"),
        schema="public",
    )
    # ### end Alembic commands ###

Here the column order in the migration matches the class.

I then added some more columns to the class to look like below

class SampleData(PublicSchemaBase):
    __tablename__ = "sample_data"

    user_id = Column(Integer, primary_key=True)
    name = Column(String(765))
    age = Column(Integer)
    country = Column(String(765))
    column_1 = Column(Integer)
    column_4 = Column(Integer)
    column_17 = Column(Integer)
    column_11 = Column(BigInteger)
    column_10 = Column(Integer)
    column_21 = Column(Integer)
    column_91 = Column(Integer)
    column_100 = Column(BigInteger)
    column_111 = Column(Integer)

The generated upgrade script looks like

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("sample_data", sa.Column("column_1", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_10", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_100", sa.BigInteger(), nullable=True))
    op.add_column("sample_data", sa.Column("column_11", sa.BigInteger(), nullable=True))
    op.add_column("sample_data", sa.Column("column_111", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_17", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_21", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_4", sa.Integer(), nullable=True))
    op.add_column("sample_data", sa.Column("column_91", sa.Integer(), nullable=True))
    # ### end Alembic commands ###

Here the new column's order doesn't match the class at all but is sorted alphabetically.

Is there a way to ensure that the columns will always be in order as they are in the class?

Additional context
Working with

• SQLAlchemy = 1.3.18
• alembic = 1.4.2

[zzzeek]

Is there a way to ensure that the columns will always be in order as they are in the class?

you would need to order these manually within your migration script. it's also maybe possible to use autogenerate hooks to reorder these directives in an automated fashion but this would be a little bit complicated since you'd have to find the metadata Table in question.

Looking at the current code it seems that this ordering should be maintained, however. Are you using a modern Python version 3.8 or greater?

[zzzeek]

oh wait, nm i see where it's doing the alphabetical sort.

[zzzeek]

can you please try this patch and run on a modern Python version 3.8 or greater to see if this resolves? thanks

diff --git a/alembic/autogenerate/compare.py b/alembic/autogenerate/compare.py
index 7ae7b61..e5ad630 100644
--- a/alembic/autogenerate/compare.py
+++ b/alembic/autogenerate/compare.py
@@ -305,7 +305,7 @@ def _compare_columns(
             c.name, "column", {"table_name": tname, "schema_name": schema}
         )
     )
-    metadata_col_names = OrderedSet(sorted(metadata_cols_by_name))
+    metadata_col_names = OrderedSet(metadata_cols_by_name)
 
     for cname in metadata_col_names.difference(conn_col_names):
         if autogen_context.run_object_filters(

[shamiradjaku]

This worked correctly, thanks.

You mentioned a hook option, how will that work. Going with the patch option means maintaining this on every version change.

[zzzeek]

the purpose of the patch is to consider it as a potential fix for this behavior in an upcoming release, thanks for confirming the approach is valid.

[shamiradjaku]

Great, thank you.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Sort per-column comparisons in metadata order https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2780