Prevent alembic auto-migration from being generated when there are no changes to the schema

Question

I am working on a flask application and using Flask-Migrate with a snowflake database through the snowflake-sqlalchemy connector.

I have the following models in my app/models.py:

class User(db.Model):
    username = db.Column(db.String(100), primary_key=True)


class UserActions(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user = db.Column(db.String(100), db.ForeignKey('user.username'))

As you can see, it's a simple 2 table example with one table having a foreign key constraint.

The first time I run flask db migrate, I get the following autogenerated migration:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('user',
    sa.Column('username', sa.String(length=100), nullable=False),
    sa.PrimaryKeyConstraint('username', name=op.f('pk_user'))
    )
    op.create_table('user_actions',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user', sa.String(length=100), nullable=True),
    sa.ForeignKeyConstraint(['user'], ['user.username'], name=op.f('fk_user_actions_user_user')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_user_actions'))
    )
    # ### end Alembic commands ###

which looks great, and works as expected when executing flask db upgrade.

But when I run flask db migrate a second time, even though there's no changes to schema, it produces another migration of

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_user_actions_user_user', 'user_actions', type_='foreignkey')
    op.create_foreign_key(op.f('fk_user_actions_user_user'), 'user_actions', 'user', ['user'], ['username'])
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('fk_user_actions_user_user'), 'user_actions', type_='foreignkey')
    op.create_foreign_key('fk_user_actions_user_user', 'user_actions', 'user', ['user'], ['username'], referent_schema='{my-schema}')
    # ### end Alembic commands ###

I have tried various configuration settings to the metadata bound to the flask db object, but I always get this redundant migration when using alembic's autogenerate feature. Even if I perform an upgrade using this redundant migration, I get another duplicate when running flask db migrate again. My connection string to the db does have schema in it, because snowflake complains otherwise:

SQLALCHEMY_DATABASE_URI='snowflake://<my_login_name>:<password>@<account_name>/<database_name>/<schema_name>?warehouse=<warehouse_name>?role=<role_name>'

What is going on here? Why is there a redundant migration being produced every single time even though there's no changes to the schema? I have read through this link, but that didn't help me. I don't specify schema in any of the models or the migration scripts, only in the connection url.

It looks like this foreign key is created using a name that is different from what SQLAlchemy/Alembic expect, so you always get a migration trying to fix that name back to the expected one. See https://alembic.sqlalchemy.org/en/latest/naming.html. — Miguel Grinberg, Dec 23 '19 at 08:07

score 0 · Accepted Answer · answered Dec 23 '19 at 16:12

I'll answer my question for people chancing on this in the future:

Seems like the problem is in how snowflake-sqlalchemy sets up the schema name for the foreign keys. It assigns the schema name being used in the db connection string, and so the existing foreign keys are associated with that schema. However, the metadata foreign keys being compared against don't have an explicit schema (since model classes don't define one), and alembic notices a difference in schema names when comparing the two. I explained a possible fix to the problem here, but unsure if there's a cleaner way: https://github.com/snowflakedb/snowflake-sqlalchemy/issues/145

Prevent alembic auto-migration from being generated when there are no changes to the schema

1 Answers1