Issue with Foreign Key Relationship Across Schemas in SQLAlchemy and Alembic Migrations

[vedgautam717]

I’m trying to create a foreign key relationship between two models that are in different schemas in PostgreSQL using SQLAlchemy and Alembic. However, I’m encountering an error when trying to generate the Alembic migration for the foreign key, and I could use some guidance on what I’m doing wrong.

Problem Description

I have two schemas:

schema1 with a table model1
schema2 with a table model2 that should reference model1.id from schema1
When trying to add a foreign key from model2 to model1, I’m getting the following error:
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'model1_id' could not find table 'schema1.model1' with which to generate a foreign key to target column 'id'

Models Setup
I have two separate files for each schema, where:

model1.py defines Model1 in schema1.
model2.py defines Model2 in schema2, with a foreign key reference to Model1.
Here’s the structure:

model1.py (Schema1)

from sqlalchemy import Column, Integer, String, MetaData
from sqlalchemy.ext.declarative import declarative_base

metadata_schema1 = MetaData(schema='schema1')
Base1 = declarative_base(metadata=metadata_schema1)

class Model1(Base1):
    __tablename__ = 'model1'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

model2.py (Schema2)

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from model1 import Base1  # Import Base1 for schema1 models

metadata_schema2 = MetaData(schema='schema2')
Base2 = declarative_base(metadata=metadata_schema2)

class Model2(Base2):
    __tablename__ = 'model2'
    id = Column(Integer, primary_key=True)
    model1_id = Column(Integer, ForeignKey('schema1.model1.id'), nullable=False)
    model1 = relationship('Model1', backref='model2s')

Alembic Configuration

In env.py, I’ve set up the target_metadata to include both Base1.metadata and Base2.metadata, like so:

from alembic import context
from model1 import Base1
from model2 import Base2

target_metadata = [Base1.metadata, Base2.metadata]

# Alembic migration configuration...


context.configure(
    url=url,
    target_metadata=target_metadata,
    include_schemas=True,  # Ensures schemas are included in the migration
    include_name=include_name,  # Additional config setting to include name
    literal_binds=True,
    dialect_opts={"paramstyle": "named"},
)

Error
When I run the alembic revision --autogenerate -m "Add foreign key from model2 to model1" command, I get the error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'model1_id' could not find table 'schema1.model1' with which to generate a foreign key to target column 'id'

[zzzeek]

hi -

you are using two separate MetaData collections so there is no string lookup between those two. To retain the use of two separate MetaData collections you need to apply the actual remote Column object to ForeignKey:

from model1 import Model1

class Model2(Base2):
    # ...

    model1_id = Column(Integer, ForeignKey(Model1.id), nullable=False)