Alembic generates redundant foreign key operations for existing cross-schema references in PostgreSQL

[jhamer8]

Describe the bug
Alembic consistently generates hundreds of redundant foreign key operations (drop + recreate) on every migration when using PostgreSQL schemas with cross-schema foreign key relationships. These operations are functionally no-ops since they drop and recreate identical foreign keys that already exist correctly in the database, making migrations extremely verbose and slow.

Expected behavior
When using include_schemas=True and foreign keys already exist correctly in the database with proper cross-schema references, Alembic should recognize they match the SQLAlchemy metadata and not generate any operations for them. New migrations should only contain actual schema changes, not redundant foreign key operations.

To Reproduce
Here's a minimal example that reproduces the issue:

# SQLAlchemy models with cross-schema foreign keys
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class TableA(db.Model):
    __tablename__ = 'table_a'
    __table_args__ = {"schema": "public"}
    
    id = db.Column(db.Integer, primary_key=True)
    table_b_id = db.Column(db.Integer, db.ForeignKey('schema_b.table_b.id'))
    table_c_id = db.Column(db.Integer, db.ForeignKey('public.table_c.id'))

class TableB(db.Model):
    __tablename__ = 'table_b'  
    __table_args__ = {"schema": "schema_b"}
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

class TableC(db.Model):
    __tablename__ = 'table_c'
    __table_args__ = {"schema": "public"}
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

# migrations/env.py configuration
def run_migrations_online():
    conf_args = current_app.extensions["migrate"].configure_args
    
    # Multi-schema configuration
    conf_args["include_schemas"] = True
    conf_args["version_table_schema"] = "public"
    conf_args["compare_type"] = True
    conf_args["compare_server_default"] = True
    
    connectable = get_engine()
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=get_metadata(), **conf_args)
        with context.begin_transaction():
            context.run_migrations()

Steps to reproduce:

1. Create a multi-schema PostgreSQL database with cross-schema foreign keys
2. Define SQLAlchemy models with explicit schema definitions using __table_args__ = {"schema": "..."}
3. Configure Alembic with include_schemas=True
4. Run flask db migrate to generate a migration
5. Observe hundreds of redundant foreign key operations

Error

# Output from flask db migrate
INFO  [alembic.autogenerate.compare] Detected removed foreign key (table_b_id)(id) on table table_a
INFO  [alembic.autogenerate.compare] Detected added foreign key (table_b_id)(id) on table public.table_a
INFO  [alembic.autogenerate.compare] Detected removed foreign key (table_c_id)(id) on table table_a  
INFO  [alembic.autogenerate.compare] Detected added foreign key (table_c_id)(id) on table public.table_a
# ... hundreds more similar operations for every foreign key in the database

Generated migration contains redundant operations like:

def upgrade():
    # Drop foreign key that already exists correctly
    op.drop_constraint('table_a_table_b_id_fkey', 'table_a', schema='public', type_='foreignkey')
    
    # Recreate identical foreign key with explicit schema reference
    op.create_foreign_key('table_a_table_b_id_fkey', 'table_a', 'table_b', 
                         ['table_b_id'], ['id'], 
                         source_schema='public', referent_schema='schema_b')
    
    # Repeated for hundreds of foreign keys...

Versions.

• OS: macOS 14.5.0 (Darwin 24.5.0)
• Python: 3.x
• Alembic: (Flask-Migrate wrapper)
• SQLAlchemy: 2.x
• Database: PostgreSQL
• DBAPI: psycopg2

Additional context

Root Cause Analysis:
The issue occurs when tables are moved into PostgreSQL schemas manually (outside of Alembic) but SQLAlchemy models have explicit schema definitions. Alembic's foreign key comparison logic incorrectly perceives a mismatch between database foreign keys and SQLAlchemy metadata schema references.

Database State Verification:
The foreign keys in the database are already correctly configured:

# Database inspection confirms proper cross-schema references
inspector.get_foreign_keys('table_a', schema='public')
# Returns: [{'name': 'table_a_table_b_id_fkey', 'referred_schema': 'schema_b', 'referred_table': 'table_b', ...}]

Extensive Troubleshooting Attempted:

1. include_object Hook Filtering ❌ - Failed to prevent foreign key detection
2. process_revision_directives Post-Processing ❌ - Operations still generated despite filtering
3. Custom compare_foreign_key Function ❌ - Function not called as expected
4. Manual No-Op Migration ❌ - Subsequent migrations still generated redundant operations
5. Nuclear Option (Fresh Migration History) ❌ - Removed all migrations, dropped alembic_version, created fresh baseline, but same redundant operations still generated

Impact:

• Migration performance severely degraded (hundreds of unnecessary operations)
• Migration readability compromised (actual changes buried in noise)
• Development workflow hindered (developers must mentally filter redundant operations)
• Increased risk of migration failures due to operation volume

Current Workaround:
Accepting the redundant operations as harmless no-ops, since they don't break functionality but significantly impact migration quality and performance.

Have a nice day!

[zzzeek]

Hi -

there's a lot of text here but the first thing I see in the code is something that likely needs to change:

class TableA(db.Model):
    __tablename__ = 'table_a'
    __table_args__ = {"schema": "public"}
    
    id = db.Column(db.Integer, primary_key=True)
    table_b_id = db.Column(db.Integer, db.ForeignKey('schema_b.table_b.id'))
    table_c_id = db.Column(db.Integer, db.ForeignKey('public.table_c.id'))

PostgreSQL database defines the default schema search path as essentially "public", meaning the name "public" is known in SQLAlchemy as the default schema. It will get confused if you name this schema explicitly in your models, so you should remove the name "public" here.

See the following sections for guidance and best practices:

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

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

[jhamer8]

Thank you so much. That fixed the problem!

[bojanbg]

This should somehow be more prominent. I had essentially the following code:

APP_SCHEMA = "public"


class Base(DeclarativeBase):
    metadata = MetaData(schema=APP_SCHEMA)

...

class Login(Base):
    __tablename__ = "login"

    user_id: Mapped[int] = mapped_column(Integer, ForeignKey(f"{APP_SCHEMA}.user.id", onupdate="CASCADE", ondelete="RESTRICT"))

This would lead to Alembic constantly dropping/re-generating foreign key constraints when auto-generating migrations. (my reason for doing it like this is to future-proof the code in case we want to switch to a custom schema later)

[CaselIT]

it's documented pretty clearly in the postgresql doc page in sqlalchemy: https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path