Alembic drop and create foreignkey on every migrate without any changes

[marcszy91]

We have a larger project with flask, sqlalchemy and flask-migrate (alembic).
We have completely recreated and migrated the database. If I perform another migrate immediately afterwards, it wants to delete all foreignkeys and create them again.

Before we used schema, it worked. But now we have to use schema and have the problem.

Database: MSSQL
alembic==1.11.1
Flask-Migrate==4.0.5
Flask-SQLAlchemy==2.5.1

It has multibe db (init --multidb)
and references to remote database on the same server in some relationship

Example:

Model

class CustomerAddress(db.Model):
    """Model for table customer_address"""

    __bind_key__ = "Customer"
    __tablename__ = "customer_address"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_id = db.Column(db.ForeignKey("dbo.customer._id"))
    street = db.Column(db.String(255))
    house_number = db.Column(db.String(10))
    department = db.Column(db.String(50))
    zip_code = db.Column(db.String(10))
    city = db.Column(db.String(255))
    country = db.Column(db.String(255))

    customer = db.relationship("Customer", foreign_keys=[customer_id])

class Customer(db.Model):
    """customer table in Customer database"""

    __bind_key__ = "Customer"
    __tablename__ = "customer"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    description = db.Column(db.String(450), unique=True)
    company = db.Column(db.String(450))
    supplier_number = db.Column(db.String(450))
    sap_customer_number = db.Column(db.String(450))

    customer_address = db.relationship(
        "CustomerAddress",
        cascade="save-update, merge, delete, delete-orphan",
        overlaps="customer",
    )

Initial Migrate

def upgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('customer',
    sa.Column('_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('description', sa.String(length=450), nullable=True),
    sa.Column('company', sa.String(length=450), nullable=True),
    sa.Column('supplier_number', sa.String(length=450), nullable=True),
    sa.Column('sap_customer_number', sa.String(length=450), nullable=True),
    sa.PrimaryKeyConstraint('_id', name=op.f('pk_customer')),
    sa.UniqueConstraint('description'),
    schema='dbo'
    )
    op.create_table('customer_address',
    sa.Column('_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('customer_id', sa.Integer(), nullable=True),
    sa.Column('street', sa.String(length=255), nullable=True),
    sa.Column('house_number', sa.String(length=10), nullable=True),
    sa.Column('department', sa.String(length=50), nullable=True),
    sa.Column('zip_code', sa.String(length=10), nullable=True),
    sa.Column('city', sa.String(length=255), nullable=True),
    sa.Column('country', sa.String(length=255), nullable=True),
    sa.ForeignKeyConstraint(['customer_id'], ['dbo.customer._id'], name=op.f('fk_customer_address_customer_id_customer')),
    sa.PrimaryKeyConstraint('_id', name=op.f('pk_customer_address')),
    schema='dbo'
    )
    # ### end Alembic commands ###


def downgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('customer_address', schema='dbo')
    op.drop_table('customer', schema='dbo')
    # ### end Alembic commands ###

second migrate

INFO  [alembic.env] Migrating database Customer
INFO  [alembic.runtime.migration] Context impl MSSQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed foreign key (customer_id)(_id) on table customer_address
INFO  [alembic.autogenerate.compare] Detected added foreign key (customer_id)(_id) on table dbo.customer_address

def upgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('customer_address', schema=None) as batch_op:
        batch_op.drop_constraint('fk_customer_address_customer_id_customer', type_='foreignkey')
        batch_op.create_foreign_key(batch_op.f('fk_customer_address_customer_id_customer'), 'customer', ['customer_id'], ['_id'], referent_schema='dbo')

    # ### end Alembic commands ###


def downgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('customer_address', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_customer_address_customer_id_customer'), type_='foreignkey')
        batch_op.create_foreign_key('fk_customer_address_customer_id_customer', 'customer', ['customer_id'], ['_id'])

Can anyone help?

Kind regards

[CaselIT]

Hi,

I believe the issue is that you are using dbo in the foreign keys, and also in the table.
If you remove if from the foreign keys does it work?

[marcszy91]

Hey,

if i remove the dbo from the foreignkeys i get an exception

from app.model.customer.customer import Customer
from .. import db


class CustomerAddress(db.Model):
    """Model for table customer_address"""

    __bind_key__ = "Customer"
    __tablename__ = "customer_address"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_id = db.Column(db.ForeignKey("customer._id"))

Exception has occurred: NoReferencedTableError
Foreign key associated with column 'customer_address.customer_id' could not find table 'customer' with which to generate a foreign key to target column '_id'
  File "D:\src\artur-api\migrations\env.py", line 171, in run_migrations_online
    context.run_migrations(engine_name=name)
  File "D:\src\artur-api\migrations\env.py", line 191, in <module>
    run_migrations_online()
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'customer_address.customer_id' could not find table 'customer' with which to generate a foreign key to target column '_id'

if i use the table object, i get the same problem with drop and create every time

from app.model.customer.customer import Customer
from .. import db


class CustomerAddress(db.Model):
    """Model for table customer_address"""

    __bind_key__ = "Customer"
    __tablename__ = "customer_address"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_id = db.Column(db.ForeignKey(Customer._id))

Another idea?

Kind Regards

[CaselIT]

ok, looked better at the error and the the impl, not too familiar with mssql.

sqlalchemy default schema for mssql is dbo and that's interfering with the reflection of the columns.
the solution is to not set the schema in the tables if it's dbo. Only set the schema if a table is in another one

[marcszy91]

I have several databases on an MSSQL server (all in schema dbo), between which I have relationships. If I do not set the schema, many sql queries are executed for a relationship or a foreign key instead of a join, which significantly reduces performance. Unfortunately, this is not a solution in this case

Edit: i create a new Schema "art" for our own tables. But now alembic detect the tables as new on every migrate

class Customer(db.Model):
    """customer table in Customer database"""

    __bind_key__ = "Customer"
    __tablename__ = "customer"
    __table_args__ = {"schema": "art"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    description = db.Column(db.String(450), unique=True)
    company = db.Column(db.String(450))
    supplier_number = db.Column(db.String(450))
    sap_customer_number = db.Column(db.String(450))

INFO  [alembic.autogenerate.compare] Detected added table 'art.bill_template'
...

Exception has occurred: ProgrammingError
(pyodbc.ProgrammingError) ('42S01', '[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An object named "bill_template" already exists in the database. (2714) (SQLExecDirectW)')
[SQL: 
CREATE TABLE art.bill_template (
	_id INTEGER NOT NULL IDENTITY, 
	description VARCHAR(450) NOT NULL, 
	file_name VARCHAR(450) NOT NULL, 
	file_stream VARBINARY(max) NULL, 
	file_mimetype VARCHAR(450) NOT NULL, 
	PRIMARY KEY (_id), 
	CONSTRAINT bill_template_1 UNIQUE (description), 
	CONSTRAINT bill_template_2 UNIQUE (file_name)
)

def upgrade_():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('bill_template',
    sa.Column('_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('description', sa.String(length=450), nullable=False),
    sa.Column('file_name', sa.String(length=450), nullable=False),
    sa.Column('file_stream', sa.LargeBinary(), nullable=True),
    sa.Column('file_mimetype', sa.String(length=450), nullable=False),
    sa.PrimaryKeyConstraint('_id'),
    sa.UniqueConstraint('description', name='bill_template_1'),
    sa.UniqueConstraint('file_name', name='bill_template_2'),
    schema='art'
    )

I have the feeling that Alembic and MSSQL don't properly recognize what already exists when a schema is used. can this be?

[zzzeek]

if you log into your SQL Server database the way your env.py is doing, and you say "SELECT * FROM bill_template", without using the schema name "art", does it find the table? if so, remove schema='art' from your metadata definition, turn off include_schemas. just work within the single, default schema that you have.

database connections all have a "default schema". you can't state that schema explicitly in the "schema" argument for a Table as alembic will see the same table in two different contexts.

[zzzeek]

see https://docs.sqlalchemy.org/en/20/core/reflection.html#metadata-reflection-schemas

[zzzeek]

https://docs.sqlalchemy.org/en/20/core/reflection.html#interaction-of-schema-qualified-reflection-with-the-default-schema