from __future__ import annotation and many to many example

[5cat]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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 __future__ import annotations

from typing import List, Optional

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


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


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str

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


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

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


sqlite_url = f"sqlite://"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")

        hero_deadpond = Hero(
            name="Deadpond",
            secret_name="Dive Wilson",
            teams=[team_z_force, team_preventers],
        )
        hero_rusty_man = Hero(
            name="Rusty-Man",
            secret_name="Tommy Sharp",
            age=48,
            teams=[team_preventers],
        )
        hero_spider_boy = Hero(
            name="Spider-Boy", secret_name="Pedro Parqueador", teams=[team_preventers]
        )
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Deadpond:", hero_deadpond)
        print("Deadpond teams:", hero_deadpond.teams)
        print("Rusty-Man:", hero_rusty_man)
        print("Rusty-Man Teams:", hero_rusty_man.teams)
        print("Spider-Boy:", hero_spider_boy)
        print("Spider-Boy Teams:", hero_spider_boy.teams)


def main():
    create_db_and_tables()
    create_heroes()


if __name__ == "__main__":
    main()

Description

all what i have done is use the example from the docs about many to many relationships and tried to fit them with from __future__ import annotation by adding the import in the first line and changing List["Hero"] to List[Hero]. and i get this error

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Team->team, expression 'List[Hero]' failed to locate a name ('List[Hero]'). If this is a class name, consider adding this relationship() to the <class '__main__.Team'> class after both dependent classes have been defined.

also update_forward_refs() did not help for both classes.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

Python 3.9.9

Additional Context

No response

[Batalex]

Hi,
I have the same issue with sqlmodel 0.0.6 and python 3.9.9.

I have created a similar virtual env with python 3.8.10 and this piece of code works as expected.

[l1b3r]

I had a similar issue with 1-M relationship, where the models are also separated into their own files.

sqlmodel == 0.0.6
python == 3.9

Not sure if that helps, but I was able to workaround this issue by removing

from __future__ import annotations

from the file of the "parent" model and returning back to quoting the type hints:

# other imports omitted

from typing import TYPE_CHECKING:
    from .child import ChildModel


class ParentModel(SQLModel, table=True):
    #    children: List[ChildModel] = Relationship(back_populates="parent")  # does not work
    children: List["ChildModel"] = Relationship(back_populates="parent")  # OK

    #  other column definitions...

[xavipolo]

I had a similar issue with 1-M relationship, where the models are also separated into their own files.

sqlmodel == 0.0.6
python == 3.9

@l1b3r
Do you updated to lastest versions?

I am having some problems with the models separated in different files.
The example model of Heroes and Teams, works fine with separate files until I add the model that allows to get a Team with its Heroes.
https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/#update-the-path-operations

Are you using this option, does it work for you?

Thanks

[agronholm]

It looks like the forward reference is not evaluated at all, but passed to SQLAlchemy as-is.

[NunchakusLei]

I experienced the same issue on version sqlmodel == 0.0.8

[JamesHutchison]

This is a pretty confusing problem to have given the selling point of this library.

[s-weigand]

Postponed annotations are still an issue with 0.0.14, hence the ruff setting.
I guess it has to do with all the very dark black magic that SQLAlchemy does 😅

I also hit this issue (again) with a new project, But removing

from __future__ import annotations

fixed it (again).
Just in case someone else hits this issue

[aholten]

Thank you @s-weigand, I removed the annotations import and SQLAlchemy stopped complaining that classes weren't mapped!

Would have saved me time if I was told that future annotations cannot be used, and that forward type references in SQLModel classes should be enclosed in double quotes to play nice with SA. I guess I should have gleaned this from the docs, on the page about type annotation strings? Considering this issue thread though, I think it's worth addressing explicitly somewhere. I'll look into adding this caveat to the docs if contributions are accepted.

[pawrequest]

yeah i lost loads of time to this, definitely think 'don't import annotations from future' should be stated somewhere in the docs. unless i missed it?

[DirkReiners]

While documentation is important, is there a plan or a route to fix this? Strings don't work for nice things like Optional[A | B | C]...

[NunchakusLei]

While documentation is important, is there a plan or a route to fix this? Strings don't work for nice things like Optional[A | B | C]...

I think you can do something like this Optional["A" | "B" | "C"]

[aholten]

@DirkReiners Forward referenced type annotations are a planned future feature for Python, so I wouldn't expect this library to implement a fix.

I explain the situation and state of the feature in a bit more detail in my PR

[JamesHutchison]

Just to double check - this isn't something that would be fixed by adding typing.get_type_hints(...) somewhere, would it?

[agronholm]

Seems reasonable to me that it would fix the problem.

[jbaudisch]

Having the same issue.

I had a similar issue with 1-M relationship, where the models are also separated into their own files.

sqlmodel == 0.0.6
python == 3.9

Not sure if that helps, but I was able to workaround this issue by removing

from __future__ import annotations

from the file of the "parent" model and returning back to quoting the type hints:

# other imports omitted

from typing import TYPE_CHECKING:
    from .child import ChildModel


class ParentModel(SQLModel, table=True):
    #    children: List[ChildModel] = Relationship(back_populates="parent")  # does not work
    children: List["ChildModel"] = Relationship(back_populates="parent")  # OK

    #  other column definitions...

Although this workaround works for the time being, the code will no longer work in the future (when __future__.annotations become the default), so this should be fixed.

[jonyscathe]

It is possible to do the Many to Many example from the initial post with future annotations by overwriting the relationships with sqlalchemy's sa_relatiopnships.

The following code works and the only changes from the original post are changing Optional to |, List to list, the definition of heros within Team and teams within Heros. Note: secondary needs to be set to the tablename. Ideally we would pass the table object, but we don't have access to that, but sqlalchemy allows passing of the tablename as a string instead.

Obviously it is a bit of a pain to change the SQLModel Relationship to a raw sqlalchemy relationship, but at least then it works...
I guess in theory it should be possible to update the standard SQLModel Relationship to follow this pattern and have it work fine with __future__.annotations which is obviously desirable (and will probably be come mandatory in like.... 3.14 or something, maybe... if the Python devs ever actually get around to pulling the trigger on making postponed evaluation of annotations mandatory...

Note: Obviously this only fixes the original M-M issue. I still have issues with M-M or 1-M or M-1 when models are in different files... for now if I import annotations then all my tables have to sit in one file. Which is not great...

from __future__ import annotations

from sqlalchemy.orm import Mapped, relationship
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
    headquarters: str

    heroes: Mapped[list[Hero]] = Relationship(
        sa_relationship=relationship(back_populates='teams', secondary='heroteamlink'),
    )


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

    teams: Mapped[list[Team]] = Relationship(
        sa_relationship=relationship(back_populates='heroes', secondary='heroteamlink'),
    )


sqlite_url = 'sqlite://'

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name='Preventers', headquarters='Sharp Tower')
        team_z_force = Team(name='Z-Force', headquarters='Sister Margaret’s Bar')

        hero_deadpond = Hero(
            name='Deadpond',
            secret_name='Dive Wilson',
            teams=[team_z_force, team_preventers],
        )
        hero_rusty_man = Hero(
            name='Rusty-Man',
            secret_name='Tommy Sharp',
            age=48,
            teams=[team_preventers],
        )
        hero_spider_boy = Hero(
            name='Spider-Boy',
            secret_name='Pedro Parqueador',
            teams=[team_preventers],
        )
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print('Deadpond:', hero_deadpond)
        print('Deadpond teams:', hero_deadpond.teams)
        print('Rusty-Man:', hero_rusty_man)
        print('Rusty-Man Teams:', hero_rusty_man.teams)
        print('Spider-Boy:', hero_spider_boy)
        print('Spider-Boy Teams:', hero_spider_boy.teams)


def main():
    create_db_and_tables()
    create_heroes()


if __name__ == '__main__':
    main()

[sh-at-cs]

As the comment directly above mentioned, this is becoming more of an issue in Python 3.14 (which has been released by now), where unquoted forward annotations don't even need a __future__ import anymore, so I expect newcomers from now on won't generally even know that there is such a thing as quoted forward references.

[Raymondo97]

This is actually something I’ve encounter quite a bit in my code. It hasn't broken for me yet. I'm using SQLModel v0.0.38 and Python v3.14.4. So I suppose it is possible some of the underlying code that made this a problem before has since been refactored.

When it comes to have the models in different files, @l1b3r’s solution above was actually very close. Here is what I do:

# This example assumes Python >=3.14, so no __future__ import here
from typing import TYPE_CHECKING

from sqlmodel import Relationship, SQLModel

if TYPE_CHECKING:
   from .child import ChildModel


class ParentModel(SQLModel, table=True):
   children: list[ChildModel] = Relationship(back_populates="parent")

This is how my code is structured, and it works great for me. Because the ChildModel is only being used as a type hint, it's safe to be nested under the conditional typing check. The same strategy would have to be applied for the child.py file, in order for the ChildModel to have a relationship to the ParentModel.

And as far as having them in the same file goes, I haven't had much trouble with that either. The only problem there is that if you use a model for anything other than a type hint (i.e. Relationship(link_model=HeroTeamLink)), those models would need to be defined above the model that depends on it. Which the other examples in this discussion handle well. Honestly, before I migrated to SQLModel, dealing with these relationships in pure SQLAlchemy and Pydantic was more of a hassle for me.

So it could be working for me just because I'm on newer versions of Python and SQLModel. But theoretically, even using slightly older versions of Python and using the from __future__ import, it should work.