SQLModel many-many relationships not working

Question

I've been struggling with this for weeks now. Here's the relevant code:

Base = declarative_base()

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Genre", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Band", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    band_links: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)

    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='bands',link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

Session = sessionmaker(bind=engine)

session = Session()
statement = select(Band)
results = session.execute(statement)

for r in results.all():
    for g in r.genres:
        print(g)

The goal is to get all the genres when retrieving the band (via FastAPI/Pydantic).

But I keep getting an error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Genre.bands - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression

I've tried what feels like every possible combination of SQLAlchemy relationships kwargs in the relationship. Defining only one side of the many-many and a ton of other options, but I can't seem to resolve this. Has anyone else found a solution to this?

Answer 1

You need to update the foreign_key in the link table Band_Genre to point to the actual ID's of the respective models that is Genre and the Band model.

Also update the back_populates attribute in the band to point band_links in the Genre model

You can refer to this SQLModel tutorial

Base = declarative_base()

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="genre.genre_id", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="band.band_id", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    band_links: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)
    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='band_links',link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

Session = sessionmaker(bind=engine)

session = Session()
statement = select(Band)
results = session.execute(statement)

for r in results.all():
    for g in r.genres:
        print(g)