Are Many-to-Many link supported with fastapi?

[Trophime]

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 typing import List, Optional

from fastapi import Depends, FastAPI, HTTPException, Query
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

sqlite_file_name = "test.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

# version sans fastapi: engine = create_engine(sqlite_url, echo=True)
connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)

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

class MPartMagnetLink(SQLModel, table=True):
    """
    MPart/Magnet many to many link table
    """
    magnet_id: Optional[int] = Field(
        default=None, foreign_key="magnet.id", primary_key=True
    )
    mpart_id: Optional[int] = Field(
        default=None, foreign_key="mpart.id", primary_key=True
    )


class MagnetBase(SQLModel):
    """
    Magnet
    """
    name: str

class Magnet(MagnetBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mparts: List["MPart"] = Relationship(back_populates="magnets", link_model=MPartMagnetLink)

class MagnetRead(MagnetBase):
    id: int

class MagnetCreate(MagnetBase):
    pass

class MagnetUpdate(SQLModel):
    """
    Magnet
    """
    name: str
    mparts: List["MPart"] = [] #Relationship(back_populates="magnets", link_model=MPartMagnetLink)

class MPartBase(SQLModel):
    """
    Magnet Part
    """
    name: str

class MPart(MPartBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    magnets: List[Magnet] = Relationship(back_populates="mparts", link_model=MPartMagnetLink)

class MPartRead(MPartBase):
    id: int

class MPartCreate(MPartBase):
    pass

class MPartUpdate(SQLModel):
    """
    Magnet Part
    """
    name: str
    magnets: List[Magnet] = []

class MPartReadWithMagnet(MPartRead):
    magnets: List[MagnetRead] = []

class MagnetReadWithMParts(MagnetRead):
    mparts: List[MPartRead] = []


def get_session():
    with Session(engine) as session:
        yield session

app = FastAPI()

@app.patch("/magnets/{magnet_id}", response_model=MagnetRead)
def update_magnet(
    *, session: Session = Depends(get_session), magnet_id: int, magnet: MagnetUpdate):
    db_magnet = session.get(Magnet, magnet_id)
    if not db_magnet:
        raise HTTPException(status_code=404, detail="Magnet not found")
    magnet_data = magnet.dict(exclude_unset=True)
    for key, value in magnet_data.items():
        setattr(db_magnet, key, value)
    session.add(db_magnet)
    session.commit()
    session.refresh(db_magnet)
    return db_magnet

@app.on_event("startup")
def on_startup():
    create_db_and_tables()

Description

• Start uvicorn
• Open browser: it crashes:

2021-10-04 15:53:27,962 INFO sqlalchemy.engine.Engine COMMIT
INFO:     Application startup complete.
INFO:     127.0.0.1:54084 - "GET /docs HTTP/1.1" 200 OK
INFO:     127.0.0.1:54084 - "GET /openapi.json HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/vscode/.local/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 375, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/vscode/.local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/home/vscode/.local/lib/python3.8/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "/home/vscode/.local/lib/python3.8/site-packages/starlette/routing.py", line 52, in app
    response = await func(request)
  File "/home/vscode/.local/lib/python3.8/site-packages/fastapi/applications.py", line 161, in openapi
    return JSONResponse(self.openapi())
  File "/home/vscode/.local/lib/python3.8/site-packages/fastapi/applications.py", line 136, in openapi
    self.openapi_schema = get_openapi(
  File "/home/vscode/.local/lib/python3.8/site-packages/fastapi/openapi/utils.py", line 387, in get_openapi
    definitions = get_model_definitions(
  File "/home/vscode/.local/lib/python3.8/site-packages/fastapi/utils.py", line 24, in get_model_definitions
    m_schema, m_definitions, m_nested_models = model_process_schema(
  File "pydantic/schema.py", line 548, in pydantic.schema.model_process_schema
  File "pydantic/schema.py", line 589, in pydantic.schema.model_type_schema
  File "pydantic/schema.py", line 241, in pydantic.schema.field_schema
  File "pydantic/schema.py", line 440, in pydantic.schema.field_type_schema
  File "pydantic/schema.py", line 773, in pydantic.schema.field_singleton_schema
  File "pydantic/schema.py", line 667, in pydantic.schema.field_singleton_sub_fields_schema
  File "pydantic/schema.py", line 495, in pydantic.schema.field_type_schema
  File "pydantic/schema.py", line 839, in pydantic.schema.field_singleton_schema
  File "/usr/lib/python3.8/abc.py", line 102, in __subclasscheck__
    return _abc_subclasscheck(cls, subclass)
TypeError: issubclass() arg 1 must be a class

Operating System

Linux

Operating System Details

Ubuntu 20.04 LTS

SQLModel Version

0.0.4

Python Version

Python 3.8.10

Additional Context

I've tried to adapt the tutorial example with the many-to-many associative tables
for using it with fastapi without success. If this feature is supported it would be great
to have an example in the tutorial.

Best

[res234]

I've noticed a similar issue with delayed annotation from Pydantic. Copying the documentation exactly, the following code works. Here is my models.py file. This code runs well:

class TeamBase(SQLModel):
    name: str
    headquarters: str


class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    heroes: List["Hero"] = Relationship(back_populates="team")


class TeamCreate(TeamBase):
    pass


class TeamRead(TeamBase):
    id: int


class TeamUpdate(SQLModel):
    id: Optional[int] = None
    name: Optional[str] = None
    headquarters: Optional[str] = None


class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    team: Optional[Team] = Relationship(back_populates="heroes")


class HeroRead(HeroBase):
    id: int


class HeroCreate(HeroBase):
    pass


class HeroUpdate(SQLModel):
    name: Optional[str] = None
    secret_name: Optional[str] = None
    age: Optional[int] = None
    team_id: Optional[int] = None


class HeroReadWithTeam(HeroRead):
    team: Optional[TeamRead] = None


class TeamReadWithHeroes(TeamRead):
    heroes: List[HeroRead] = []

If you split the models into teams.py and heroes.py then the same that Trophime is getting occurs.

Here's teams.py:

class TeamBase(SQLModel):
    name: str
    headquarters: str


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

    heroes: List["Hero"] = Relationship(back_populates="team")


class TeamRead(TeamBase):
    id: int


class TeamCreate(TeamBase):
    pass


class TeamUpdate(SQLModel):
    id: Optional[int] = None
    name: Optional[str] = None
    headquarters: Optional[str] = None


class TeamReadWithHeroes(TeamRead):
    heroes: List["HeroRead"] = []

And here's heroes.py:

class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    team: Optional["Team"] = Relationship(back_populates="heroes")


class HeroRead(HeroBase):
    id: int


class HeroCreate(HeroBase):
    pass


class HeroUpdate(SQLModel):
    name: Optional[str] = None
    secret_name: Optional[str] = None
    age: Optional[int] = None
    team_id: Optional[int] = None


class HeroReadWithTeam(HeroRead):
    team: Optional["TeamRead"] = None

The issue can even be replicated in a single models.py file:

class TeamBase(SQLModel):
    name: str
    headquarters: str


class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    heroes: List["Hero"] = Relationship(back_populates="team")


class TeamCreate(TeamBase):
    pass


class TeamRead(TeamBase):
    id: int


class TeamUpdate(SQLModel):
    id: Optional[int] = None
    name: Optional[str] = None
    headquarters: Optional[str] = None

class TeamReadWithHeroes(TeamRead):
    heroes: List["HeroRead"] = []


class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    team: Optional[Team] = Relationship(back_populates="heroes")


class HeroRead(HeroBase):
    id: int


class HeroCreate(HeroBase):
    pass


class HeroUpdate(SQLModel):
    name: Optional[str] = None
    secret_name: Optional[str] = None
    age: Optional[int] = None
    team_id: Optional[int] = None


class HeroReadWithTeam(HeroRead):
    team: Optional[TeamRead] = None

All of these produce the same exception. If you start uvicorn and then open your browser and go to the docs page you get the following exception:

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 373, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/starlette/routing.py", line 52, in app
    response = await func(request)
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/fastapi/applications.py", line 161, in openapi
    return JSONResponse(self.openapi())
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/fastapi/applications.py", line 136, in openapi
    self.openapi_schema = get_openapi(
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/fastapi/openapi/utils.py", line 387, in get_openapi
    definitions = get_model_definitions(
  File "/Users/user/.pyenv/versions/3.9.7/envs/sqlmodel-try/lib/python3.9/site-packages/fastapi/utils.py", line 24, in get_model_definitions
    m_schema, m_definitions, m_nested_models = model_process_schema(
  File "pydantic/schema.py", line 548, in pydantic.schema.model_process_schema
  File "pydantic/schema.py", line 589, in pydantic.schema.model_type_schema
  File "pydantic/schema.py", line 241, in pydantic.schema.field_schema
  File "pydantic/schema.py", line 440, in pydantic.schema.field_type_schema
  File "pydantic/schema.py", line 773, in pydantic.schema.field_singleton_schema
  File "pydantic/schema.py", line 667, in pydantic.schema.field_singleton_sub_fields_schema
  File "pydantic/schema.py", line 495, in pydantic.schema.field_type_schema
  File "pydantic/schema.py", line 839, in pydantic.schema.field_singleton_schema
  File "/Users/user/.pyenv/versions/3.9.7/lib/python3.9/abc.py", line 123, in __subclasscheck__
    return _abc_subclasscheck(cls, subclass)
TypeError: issubclass() arg 1 must be a class
INFO:     127.0.0.1:53550 - "GET /openapi.json HTTP/1.1" 500 Internal Server Error

I have tried this on Python 3.8.1 and Python 3.9.7. I think that Pydantic had an issue earlier that was fixed in a previous version: pydantic/pydantic#1298.

[mafetoro92]

I am experiencing the same exception as well.

[LucidDan]

Prefacing this by saying I'm relatively new to pydantic and sqlalchemy, so take what I suggest with a grain of salt.

For @Trophime 's sample code - If you add this at the bottom of the file, it works:

MagnetUpdate.update_forward_refs()

This should be done after all the router endpoints are defined.
It resolves this particular case, by updating the forward reference to the actual referenced model after everything has loaded.

For multiple files it seems to be a bit trickier -

You can do some non-PEP8 shenanigans like importing the forward ref models at the bottom of the file, and then update the forward ref - technically this can work, although your python linter will hate you:

class FirstModel(SQLModel, table=True):
    ref: List["ForwardRefModel"]
    # ...

from .other_module import ForwardRefModel
FirstModel.update_forward_refs()

If you don't want to engage in acts of dubious code style legality, you can also use a package file to import all the models together, and run any updates there...this is how I'm doing it for my current project. Something like:

"""
app/models/__init__.py

Import the various model modules in one place and resolve forward refs.
"""

# Make sure all modules are imported here...
from app.models.first_module import FirstModelBase, FirstModel, FirstModelRead  # (etc)
from app.models.other_module import ForwardRefModelBase, ForwardRefModel  # (etc)

FirstModel.update_forward_refs()

Note though, the above code caused me some NameErrors, unless I explicitly passed the namespace for the forward refs, like this:

FirstModel.update_forward_refs(ForwardRefModel=ForwardRefModel)

I haven't done a lot of testing on this; that issue could be specific to my code. I mostly figured this out by reading pydantic code, github issues, and applying brute force trial and error.

I'm not at all sure this is a good solution, but it is a workaround that seems to have sorted the issue for me, at least...for now. 😅

[res234]

@LucidDan thank you so much! I did tinker around using update_forward_refs() but I ran into NameErrors as well. Your solution of explicitly passing the the namespace gets around this!

I will keep on trying this with more complex models and see if I run into any issues. Thank you so much.

[Trophime]

@LucidDan thanks for your help.
Now I have a stupid question as a beginner in this field.
How can I actually proceed to update mparts of a magnet in my example?
I've naively tried something like that:

{
  "name": "string",
  "mparts": [
        {
            "name": "string",
            "id": 0
        }
   ]
}

which is obviously not working

I think the simplest way is to use a method to add mpart from an mpart_id.
Otherwise I guess I need to modify the updat_mpart method...

Thanks for your suggestions

[lobotmcj]

If you don't want to engage in acts of dubious code style legality, you can also use a package file to import all the models together, and run any updates there...this is how I'm doing it for my current project. Something like:

"""
app/models/__init__.py

Import the various model modules in one place and resolve forward refs.
"""

# Make sure all modules are imported here...
from app.models.first_module import FirstModelBase, FirstModel, FirstModelRead  # (etc)
from app.models.other_module import ForwardRefModelBase, ForwardRefModel  # (etc)

FirstModel.update_forward_refs()

Note though, the above code caused me some NameErrors, unless I explicitly passed the namespace for the forward refs, like this:

FirstModel.update_forward_refs(ForwardRefModel=ForwardRefModel)

I haven't done a lot of testing on this; that issue could be specific to my code. I mostly figured this out by reading pydantic code, github issues, and applying brute force trial and error.

I'm not at all sure this is a good solution, but it is a workaround that seems to have sorted the issue for me, at least...for now. 😅

The package file import all (e.g., __init__.py) described above by @LucidDan works for my more complex case, but only with the ForwardRefModel=ForwardRefModel and you have to do this for every forward reference...including ones in parent classes (even if you've already updated their forward refs).

For example, if the models in one of your files are like this:

class EventReadWithHosts(EventRead):
    hosts: List["HostRead"] = []


class EventReadFull(EventReadWithHosts):
    data_sources: List["DataSourceReadWithHost"] = []

...then you have to (or I had to at least) update forward refs as follows to avoid NameErrors and get the docs to actually load:

EventReadWithHosts.update_forward_refs(HostRead=HostRead)
EventReadFull.update_forward_refs(
    HostRead=HostRead, DataSourceReadWithHost=DataSourceReadWithHost
)

note that I already updated EventReadWithHosts -> HostRead, but had to do it again for the EventReadFull subclass

I'm sure I am misunderstanding/describing something incorrectly, but it didn't work for me until I did the above

[res234]

@lobotmcj Yes, I found the exact same thing. You have to update the forward refs for every forward reference. I may be doing something wrong as well though!

[LucidDan]

Yeah to be clear folks, this is what I had to do, too. I only used a single example, but in my actual project, It wasn't just one model, it was several, and several namespace entries that had to be passed in as well.

How I approached finding the ones to add was I set my server up so it would immediately raise a runtime error and crashing out if there was any models that didn't resolve properly (rather than only logging a caught error when you accessed an endpoint), and then just added each of those models causing issues to the list of updates.

I could've perhaps run an iteration over all models and done an update on all of them but that seemed a bit excessive...it's a large project, I've got many dozens of models.

It's not ideal, for sure...definitely want to see a better solution, and I'll eventually put some time into figuring it out, at least for my own project. I'll update this issue if I make any progress on that, would love to hear from anyone else that figures out better solutions, too.

[masreplay]

any updates?

[res234]

@masreplay What updates were you looking for specifically? I think @LucidDan offered a solution.

[thomas-daughters]

@LucidDan's approach worked for me too, but isn't ideal. As a minimum, the tutorial / documentation should be updated. Better still would be if this could somehow be managed seamlessly by the library itself.