I am creating web backend with FastAPI and using Alembic to make my migrations. I've created my models inhereting them from DeclarativeBase of SQLAlchemy ORM. But when I try to create migrations with Alembic I get this error:
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'airport.country_id' could not find table 'country' with which to generate a foreign key to target column 'id'
I've searched the internet on what might be the cause of this. I've seen advices on adding the name of the schema to the table name in the parent column, which didn't work. In the same question they advice on adding meta.reflect() to upgrade() function. But my migrations file does not even get created, so nowhere to put this. Maybe, there is something wrong with declaration of my models, here they are:
airport.py
class Base(DeclarativeBase):
pass
class Airport(Base):
__tablename__ = 'airport'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
iata_short: Mapped[str] = mapped_column(String(5))
icao_short: Mapped[str] = mapped_column(String(5))
timezone: Mapped[str] = mapped_column(String(5))
country_id: Mapped[int] = mapped_column(ForeignKey('country.id'))
country: Mapped['Country'] = relationship(back_populates='airports')
departure_reservations: Mapped[List["Reservation"]] = relationship(back_populates='departure_airport')
arrival_reservations: Mapped[List["Reservation"]] = relationship(back_populates='arrival_airport')
country.py
class Base(DeclarativeBase):
pass
class Country(Base):
__tablename__ = 'country'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(20))
continent: Mapped[str] = mapped_column(String(20))
currencty: Mapped[str] = mapped_column(String(3))
airports: Mapped[List['Airport']] = relationship(back_populates='country')
This is how I imported them to my env.py:
from models import (
aircraft_type,
airline,
airport,
country,
reservation,
tariff,
user
)
target_metadata = [
aircraft_type.Base.metadata,
airline.Base.metadata,
country.Base.metadata,
airport.Base.metadata,
reservation.Base.metadata,
tariff.Base.metadata,
user.Base.metadata
]
I also found out that while creating migrations Alembic connects to my PostgreSQL server. Isn't it supposed to connect only while applying migrations?
Please tell what can cause this behaviour and what I did wrong. Feel free to ask for any additional data!
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'airport.country_id' could not find table 'country' with which to generate a foreign key to target column 'id'
You get that error because you are creating different Base for different models. Typically, one Base per app is what is used. You can create something like the following and use this throughout your apps.
common.py
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
pass
airport.py
from common import Base
class Airport(Base):
__tablename__ = 'airport'
country.py
from common import Base
class Country(Base):
__tablename__ = 'country'
As for the next error in you mention in the comments.
Duplicate table keys across multiple MetaData objects
In env.py make the following changes.
# import the same common base you used across your app
from common import Base
# import all the models, they will not be used anywhere in this file, but they have to be imported
# for alembic (sqlalchemy) to know they exist.
# This step may not be needed depending on your project structure,
# but importing them here is a sure way to make sure your models are picked up by alembic (sqlalchemy).
from models import (
aircraft_type,
airline,
airport,
country,
reservation,
tariff,
user
)
# you were using a list here
target_metadata = Base.metadata
Regarding your other question.
I also found out that while creating migrations Alembic connects to my PostgreSQL server. Isn't it supposed to connect only while applying migrations?
I think Offline Mode might be what you want.