2

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 classs. 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",
        source_schema="",
    )
    # ### end Alembic commands ###

Here the column order in the migration matches the class

I then added some more columns to 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.

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

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
sea
  • 199
  • 2
  • 9
  • Why do you need the order in the migration scripts to match the order in the class? – snakecharmerb Jan 13 '21 at 18:05
  • The class is used to reorder data written to csv a csv file to be loaded into redshift. Redshift expects the the column orders to be a match. This is automated accross 500+ tables. – sea Jan 13 '21 at 19:55
  • @sea which database are you using? And are new columns only added after existing columns? – rfkortekaas Jan 13 '21 at 20:52
  • 1
    Using a Postgres db. The columns will be added in the order specified in the alembic migration file. Which seems to inconsistent with the order in the class. – sea Jan 14 '21 at 13:49

0 Answers0