Simplify set up of Many to Many relationships

[12rambau]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class HeroTeamLink(SQLModel, table=True):
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)


class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: list["Hero"] = Relationship(back_populates="teams", link_model=HeroTeamLink)


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

    teams: list[Team] = Relationship(back_populates="heroes", link_model=HeroTeamLink)

Description

I come from another ORM in another language namely Doctrine for Php and I'm quite surprise how complicated setting up a Many to Many relationship remains in the Python realm. In my previous experience the ORM was ther to "stop thinking about table and instead think only about object and relations" the key feature being that Doctrine was dealing with all the table related things including the link table for many-to-many relations.

Would it be possible in SQLModel to detect Many-to-many relations at model parsing and automatically create the link table ?

several advantage that I can think about are:

• The table does not appear in the generated graphs (whatver the lib you select)
• The user cannot make a mistake setting it up
• any relation types are dealt the same way: in the classes