Redundant alembic migrations with foreign keys drop/create

[anujkumar93]

1. What version of Python are you using (python --version)?
   A. 3.7.5

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')?
   A. Darwin-18.7.0-x86_64-i386-64bit

3. What are the component versions in the environment (pip list)?
   Relevant ones:
   Flask 1.1.1
   Flask-Migrate 2.5.2
   Flask-SQLAlchemy 2.4.1
   snowflake-sqlalchemy 1.1.17

4. What did you do?
   The complete problem is stated in https://stackoverflow.com/questions/59449387/prevent-alembic-auto-migration-from-being-generated-when-there-are-no-changes-to . Essentially, I built a minimal flask application using sqlalchemy for my models (2 tables with 1 foreign key between them), and snowflake as my backend database. This package proved beneficial for this integration :). After I make and execute the first migration, my db is as expected. However, when I run alembic revision --autogenerate again (through flask-migrate), I get an extra/duplicate/redundant migration file which drops the previous foreign key and creates a new one. This is due to a schema mismatch when comparing metadata and connection fks on alembic side. I always get a redundant migration file no matter how often I migrate and upgrade.

5. What did you expect to see?
   No migration file generated at all, since there's no changes to the schema

6. What did you see instead?
   Redundant migration file:

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 ###

7. Can you set logging to DEBUG and collect the logs?
   The problem identified by me here is that the way alembic works with schema names is a lil involved: Redundant Foreign Key migrations (again) sqlalchemy/alembic#519 . As the author of Alembic says there, the schema name in foreign keys created through SQLAlchemy will be set to empty, because we don't pre-define the schema names when declaring a new class (table) in our models. When comparing, Alembic sees the schema for the foreign key in the metadata as None. When it gets the existing foreign key from the connection, the schema is returned through https://github.com/snowflakedb/snowflake-sqlalchemy/blob/master/snowdialect.py#L290 which sets the schema name to the db connection name (not None), as required by snowflake. Since there is a mismatch, Alembic drops the foreign key and tries to create it again every single time. I was able to fix the problem by changing that line to:

'referred_schema': None

However, I am not sure this is the correct permanent fix, since this seems specific to integration with Alembic. Please let me know if you have any questions. Thanks!

My question is: is it okay for the referred_schema to be returned as None for foreign keys? Should this be changed permanently in the repo? What other options do I have to make this work?

[anujkumar93]

cc @snowstakeda @keller00

[lgwacker]

Hey guys, any update on this issue?

[adisunw]

bump on this.

[sfc-gh-mkeller]

Hey, I'm sorry but we don't officially support Alembic.
I know I have fixed a few bugs with it previously, but I really don't see myself having bandwidth for this anytime soon. Just setting up Alembic takes me a while... 😢
If someone could fix this in a PR then I'd be happy to look at it.

[Hulow]

Hi all!
anyone can provide any update on this issue?

[mCo0L]

This is happening on all migrations. Any update on this?

[m-ar13f]

i'm facing this issue also right now. Any update guys?

[DanCardin]

I dont know if I'm yet convinced this is a foolproof solution or not, but I set schema="information_schema".

As far as I can tell, you're forced to set schema to something, which (at least for us) led to setting it to the schema one is querying against. For us, trying to manage sqlalchemy table definitions with alembic like normal with tablearg's {"schema": "value"}, that appears to be the problem and leads to them appearing to be not equal.

By using some other schema, i.e. information_schema, your tables end up being referenced by their full path, including the schema and it seems to arrive at the correct autogenerate inferences.

[sfc-gh-dszmolka]

thank you for sharing the workaround earlier here! for the full support and necessary changes, we'll consider this as a possible enhancement in the future but as mentioned earlier by the dev team, contributions are more than welcome and can likely speed up implementation by a great deal.