I am trying to use the autogenerate feature from alembic to update the schema of a MSSQL database.
class Table1(Base):
__tablename__ = "table1"
__table_args__ = (
PrimaryKeyConstraint("ID", name="PK_Table1"),
)
ID = Column(Integer, nullable=False)
class Table2(Base):
__tablename__ = "table2"
__table_args__ = (
ForeignKeyConstraint(['Table1ID'], ['Table1.ID'], name='fk_Table2_Table1')
{'schema': 'foo'}
)
Table1ID = Column(Integer, nullable=False)
Table1_ = relationship('Table1', back_populates='Table2')
After executing the command alembic revision --autogenerate, this is the upgrade() function I get:
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('fk_Table2_Table1', 'Table2', schema='foo', type_='foreignkey')
op.create_foreign_key('fk_Table2_Table1', 'Table2', 'Table1', ['Table1ID'], ['ID'], source_schema='foo')
# ### end Alembic commands ###
After digging into the code, I found that when alembic compares the foreign keys between the schema and the model in the code, sqlalchemy forces a dbo schema to the Table1.ID referenced column:
Database foreign key: ('foo', 'Table2', ('Table1ID',), 'dbo', 'Table1', ('ID',), None, None, 'not deferrable')
Model foreign key: ('foo', 'Table2', ('Table1ID',), None, 'Table1', ('ID',), None, None, 'not deferrable')
This difference leads to the drop and create commands in the upgrade() function later on. If I remove the {'schema': 'foo'} in __table_args__ the issue disappears so my guess is that the table schema (different from the default one) forces the schema on the foreign key reference.
I would like to know if there is any way to overcome this problem.
