Alembic taking ~30min to autogenerate and throwing unrelated SAWarnings (hierarchyid, geography, geometry)

[mnemyx]

I use Flask-Migrate to integrate with Alembic commands. Whenever I run flask db migrate which is equivalent to alembic revision --autogenerate, the process takes about 30min to run and throws unrelated warnings before continuing with to detect column changes

• Python Version: 3.6.8
• Alembic Version: 1.7.7
• Database:
  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
  Oct 28 2016 18:17:30
  Copyright (c) Microsoft Corporation
  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
• Log Snippet:
  

It'll detect the table changes quickly, then goes through some process for 30min where it prints out the SAWarnings, then runs through the column changes very quickly. I have no clue where the warnings are coming from or why, but it's definitely where the slowdown is coming from.

As far as I know:

• We don't have a geography/geometry enabled database
• We only have 12 tables total within the app that we use, all columns being simple types: biginteger, integer, unicode, string, boolean
• However, the actual database has a crap ton of tables so if alembic does inspect every existing table, then it makes sense

Any ideas or help would be appreciated.

[zzzeek]

not really sure. it's definitely seeing in your database datatypes that are called "geography", "hierarchyid" and "geometry". Im not familiar with these types in terms of SQL Server. Does this server have many databases / schemas and you are using include_schemas=True ?

[CaselIT]

However, the actual database has a crap ton of tables so if alembic does inspect every existing table, then it makes sense

that's definitly the cause of the slowness. You should most likely restrict the schema / tables that are of interest for autogenerate.
You can use these features to do this https://alembic.sqlalchemy.org/en/latest/autogenerate.html#controlling-what-to-be-autogenerated

[zzzeek]

if the tables are all in one database then you'd think it would generate hundreds of DROP TABLE commands also...

[mnemyx]

I just finished changes according to those docs and was able to limit the tables and speed it up.

It wasn't generating DROP TABLE commands though, so I'm not sure about that one...

Thank you guys so much!