Are Many-to-Many link supported with fastapi? #1511
-
First Check
Commit to Help
Example Codefrom 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
Operating SystemLinux Operating System DetailsUbuntu 20.04 LTS SQLModel Version0.0.4 Python VersionPython 3.8.10 Additional ContextI've tried to adapt the tutorial example with the many-to-many associative tables Best |
Beta Was this translation helpful? Give feedback.
Replies: 22 comments
-
|
I've noticed a similar issue with delayed annotation from Pydantic. Copying the documentation exactly, the following code works. Here is my If you split the models into Here's And here's The issue can even be replicated in a single 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: 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. |
Beta Was this translation helpful? Give feedback.
-
|
I am experiencing the same exception as well. |
Beta Was this translation helpful? Give feedback.
-
|
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: This should be done after all the router endpoints are defined. 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: 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: Note though, the above code caused me some NameErrors, unless I explicitly passed the namespace for the forward refs, like this: 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. 😅 |
Beta Was this translation helpful? Give feedback.
-
|
@LucidDan thank you so much! I did tinker around using I will keep on trying this with more complex models and see if I run into any issues. Thank you so much. |
Beta Was this translation helpful? Give feedback.
-
|
@LucidDan thanks for your help. which is obviously not working I think the simplest way is to use a method to add mpart from an mpart_id. Thanks for your suggestions |
Beta Was this translation helpful? Give feedback.
-
The package file import all (e.g., 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 I'm sure I am misunderstanding/describing something incorrectly, but it didn't work for me until I did the above |
Beta Was this translation helpful? Give feedback.
-
|
@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! |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
any updates? |
Beta Was this translation helpful? Give feedback.
-
|
@masreplay What updates were you looking for specifically? I think @LucidDan offered a solution. |
Beta Was this translation helpful? Give feedback.
-
|
@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. |
Beta Was this translation helpful? Give feedback.
-
|
Yeah this is pretty complicated stuff, none of the above solutions seem 'good'. Relying on 'on import' behaviour always feels funny. But you can't solve it in the ASGI lifespan startup either as its too late by that point. I wonder if SQLModel can somehow update forward refs when they are added to the metadata. Theoretically, it should be possible to iterate over the fields and go 'I can update this I know what it is'. But also quite hacky. It sucks that the recommendation is to make 4+ models for each database table (if doing a simple CRUD app etc). So a medium size app with maybe 20 tables all of a sudden has a file with 80+ classes in it because you can't really solve the circular import issue 😆 At a minimum the documentation should be updated to address that this issue exists, the use TYPE_CHECKING solution doesn't actually fix the issue. |
Beta Was this translation helpful? Give feedback.
-
|
This seems to be much worse than simply a "all models need to be in the same file" or "fix with update_forward_refs"... It also means you can't really abstract the CRUD or ROUTE code into per-table modules if there is any cross referencing, as then the importing becomes runtime circular. Using the above modular example with the code from the tutorial, try to refactor the code paths as well: The code in hero,.py needs to refer to Team, and the code in team.py needs to refer to Hero... How does one refactor these examples so that it is possible to create a Team and add a Hero to it (or create a Hero on a Team...) with per-table MODEL, CRUD and ROUTE files? Alternatively, is there a good "large project" design pattern for keeping the business logic / database logic abstraction modular in the face of these relationships? (I really like the ActiveRecord concept from Boris Lau in issue 254 that adds class methods to the SQLModel classes for the CRUD functionality, but this limitation seems to make it impractical for these cross reference cases) |
Beta Was this translation helpful? Give feedback.
-
|
Any updates on these issue ? it's really frustrating to not find a solution... |
Beta Was this translation helpful? Give feedback.
-
|
I am experiencing the same complexity, would love a more elegant solution. |
Beta Was this translation helpful? Give feedback.
-
|
Is there any on-going work on this? As @invokermain suggested, it should be possible to modify the For now, as a workaround I'm doing what @LucidDan suggests and using the Here is the code I'm using in case it helps anyone: def get_subclasses(cls):
for subclass in cls.__subclasses__():
yield from get_subclasses(subclass)
yield subclass
models_dict = {cls.__name__: cls for cls in get_subclasses(SQLModel)}
for cls in models_dict.values():
cls.update_forward_refs(**models_dict) |
Beta Was this translation helpful? Give feedback.
-
|
@Eryx5502 Thanks for providing your solution. It works well for me while using models in different files and allows me to maintain a direct import from the parent model. Here's the updated code block: models/parent.py from children import Children
class Parent(DealBase, table=True):
id: int = Field(default=None, primary_key=True, index=True)
children: List[Childern] = Relationship(
back_populates="parent",
)models/children.py if TYPE_CHECKING:
from parent import Parent
class Children(DealBase, table=True):
id: int = Field(default=None, primary_key=True, index=True)
parent_id: int = Field(default=None, foreign_key="parent.id", index=True)
parent: Parent = Relationship(
back_populates="children,
)One of them needs to actually import the other model (in that case the parent imports the children), and then the second can do something like |
Beta Was this translation helpful? Give feedback.
-
Thank you! This actually works. This should definitely be addressed in the module itself. Just curious, is there an actual downside to doing it this way? My understanding is that it doesn't matter if you update references for packages that don't need it. |
Beta Was this translation helpful? Give feedback.
-
|
Not sure when it was added but with the addition of # app/models/__init__.py
from .event import (
Event,
EventBase,
EventCreate,
EventPublic,
EventPublicWithOrganizersAndParticipants,
EventUpdate,
)
from .organization import (
Organization,
OrganizationBase,
OrganizationCreate,
OrganizationPublic,
OrganizationPublicWithEventsAndMembers,
OrganizationUpdate,
)
from .user import (
User,
UserBase,
UserCreate,
UserPublic,
UserPublicWithOrganizationsAndEvents,
UserUpdate,
)
UserPublicWithOrganizationsAndEvents.model_rebuild()
OrganizationPublicWithEventsAndMembers.model_rebuild()
EventPublicWithOrganizersAndParticipants.model_rebuild() |
Beta Was this translation helpful? Give feedback.
-
|
I also faced this problem. I'm using Python 3.9. Calling |
Beta Was this translation helpful? Give feedback.
-
|
Remember to initialize (import) the models like it is said in the tutorial and docs (that is what worked very well for me): |
Beta Was this translation helpful? Give feedback.
-
|
The initial code doesn't through the To make your code work as intended you probably need to make all fields in class MagnetUpdate(SQLModel):
name: Optional[str] = None
mparts: Optional[List["MPart"]] = NoneAnd change the code that updates fields of magnet_data = magnet.model_dump(exclude_unset=True)
db_magnet = db_magnet.sqlmodel_update(magnet_data)
if magnet.mparts:
db_magnet.mparts = [
MPart.model_validate(mpart_data) for mpart_data in magnet.mparts
]Full runnable code example in the details: from typing import List, Optional
from fastapi import Depends, FastAPI, HTTPException
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.drop_all(engine)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
m = Magnet(
id=1,
name="magnet 1",
mparts=[
MPart(name="mpart 1"),
MPart(name="mpart 2"),
],
)
session.add(m)
session.commit()
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
mparts: list["MPartRead"]
class MagnetCreate(MagnetBase):
pass
class MagnetUpdate(SQLModel):
name: Optional[str] = None
mparts: Optional[List["MPart"]] = None
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.model_dump(exclude_unset=True)
db_magnet = db_magnet.sqlmodel_update(magnet_data)
if magnet.mparts:
db_magnet.mparts = [
MPart.model_validate(mpart_data) for mpart_data in magnet.mparts
]
session.add(db_magnet)
session.commit()
session.refresh(db_magnet)
return db_magnet
@app.on_event("startup")
def on_startup():
create_db_and_tables() |
Beta Was this translation helpful? Give feedback.
The initial code doesn't through the
TypeError: issubclass() arg 1 must be a classanymore in current version.To make your code work as intended you probably need to make all fields in
MagnetUpdateoptional:And change the code that updates fields of
db_magnetto usesqlmodel_update, then manually updatempartsfield:Full runnable code exam…