search

tiangolo / sqlmodel

SQL databases in Python, designed for simplicity, compatibility, and robustness.
https://sqlmodel.tiangolo.com/
MIT License
13.7k stars 616 forks source link

Are Many-to-Many link supported with fastapi? #121

Open Trophime opened 2 years ago

Trophime commented 2 years ago

First Check

Commit to Help

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

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 commented 2 years ago

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: https://github.com/samuelcolvin/pydantic/issues/1298.

mafetoro92 commented 2 years ago

I am experiencing the same exception as well.

LucidDan commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

@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 commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

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 commented 2 years ago

any updates?

res234 commented 2 years ago

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

thomas-daughters commented 2 years ago

@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.

invokermain commented 2 years ago

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.

plocher commented 2 years ago

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:

    from team import Team, select_teams
    from hero import Hero, create_heroes, select_heroes, update_heroes

    main.py:  create_db_and_tables(engine=engine)

    hero.py:   create_heroes(engine=engine)
    hero.py:   select_heroes(engine=engine)
    hero.py:   update_heroes(engine=engine)

    team.py:  select_teams(engine=engine)

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)

Sancho66 commented 1 year ago

Any updates on these issue ? it's really frustrating to not find a solution...

Zaffer commented 1 year ago

I am experiencing the same complexity, would love a more elegant solution.

Eryx5502 commented 1 year ago

Is there any on-going work on this? As @invokermain suggested, it should be possible to modify the SQLModelMetaclass so that every time a model is defined its ForwardRefs are updated if possible and every other model depending on the newly defined class is also checked. Not sure if this would be the neatest approach though, but other similar approaches based on SQLModelMetaclass should be doable.

For now, as a workaround I'm doing what @LucidDan suggests and using the __init__.py file of my models module for updating the forward refs providing the namespace. But since doing it manually it's such a pain, I am generating a dict with all the models (everything that inherits from SQLModel) and doing .update_forward_refs on every of them using that dict to provide a namespace with every needed model. Of course this is not optimal since I'm not iterating only over those models with ForwardRefs, but its easier this way. It should be possible to inspect the models and generate a list of the ones with forward refs to iterate on those only.

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)
udiNur commented 1 year ago

@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 if TYPE_CHECKING:, otherwise (when non of them actually using the import

dkubatko commented 11 months ago

Is there any on-going work on this? As @invokermain suggested, it should be possible to modify the SQLModelMetaclass so that every time a model is defined its ForwardRefs are updated if possible and every other model depending on the newly defined class is also checked. Not sure if this would be the neatest approach though, but other similar approaches based on SQLModelMetaclass should be doable.

For now, as a workaround I'm doing what @LucidDan suggests and using the __init__.py file of my models module for updating the forward refs providing the namespace. But since doing it manually it's such a pain, I am generating a dict with all the models (everything that inherits from SQLModel) and doing .update_forward_refs on every of them using that dict to provide a namespace with every needed model. Of course this is not optimal since I'm not iterating only over those models with ForwardRefs, but its easier this way. It should be possible to inspect the models and generate a list of the ones with forward refs to iterate on those only.

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)

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.

DamianLiu237 commented 3 weeks ago

Not sure when it was added but with the addition of .model_rebuild(), this solution resolved the annotation issue for me and seems the most "elegant".

# 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()