FastAPI and Pydantic - Relationships Not Working · Issue #37 · fastapi/sqlmodel · GitHub

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, select


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] = []


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

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)


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


app = FastAPI()


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


@app.post("/heroes/", response_model=HeroRead)
def create_hero(*, session: Session = Depends(get_session), hero: HeroCreate):
    db_hero = Hero.from_orm(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero


@app.get("/heroes/", response_model=List[HeroRead])
def read_heroes(
    *,
    session: Session = Depends(get_session),
    offset: int = 0,
    limit: int = Query(default=100, lte=100),
):
    heroes = session.exec(select(Hero).offset(offset).limit(limit)).all()
    return heroes


@app.get("/heroes/{hero_id}", response_model=HeroReadWithTeam)
def read_hero(*, session: Session = Depends(get_session), hero_id: int):
    hero = session.get(Hero, hero_id)
    if not hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    return hero


@app.patch("/heroes/{hero_id}", response_model=HeroRead)
def update_hero(
    *, session: Session = Depends(get_session), hero_id: int, hero: HeroUpdate
):
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    hero_data = hero.dict(exclude_unset=True)
    for key, value in hero_data.items():
        setattr(db_hero, key, value)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero


@app.delete("/heroes/{hero_id}")
def delete_hero(*, session: Session = Depends(get_session), hero_id: int):

    hero = session.get(Hero, hero_id)
    if not hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    session.delete(hero)
    session.commit()
    return {"ok": True}


@app.post("/teams/", response_model=TeamRead)
def create_team(*, session: Session = Depends(get_session), team: TeamCreate):
    db_team = Team.from_orm(team)
    session.add(db_team)
    session.commit()
    session.refresh(db_team)
    return db_team


@app.get("/teams/", response_model=List[TeamRead])
def read_teams(
    *,
    session: Session = Depends(get_session),
    offset: int = 0,
    limit: int = Query(default=100, lte=100),
):
    teams = session.exec(select(Team).offset(offset).limit(limit)).all()
    return teams


@app.get("/teams/{team_id}", response_model=TeamReadWithHeroes)
def read_team(*, team_id: int, session: Session = Depends(get_session)):
    team = session.get(Team, team_id)
    if not team:
        raise HTTPException(status_code=404, detail="Team not found")
    return team


@app.patch("/teams/{team_id}", response_model=TeamRead)
def update_team(
    *,
    session: Session = Depends(get_session),
    team_id: int,
    team: TeamUpdate,
):
    db_team = session.get(Team, team_id)
    if not db_team:
        raise HTTPException(status_code=404, detail="Team not found")
    team_data = team.dict(exclude_unset=True)
    for key, value in team_data.items():
        setattr(db_team, key, value)
    session.add(db_team)
    session.commit()
    session.refresh(db_team)
    return db_team


@app.delete("/teams/{team_id}")
def delete_team(*, session: Session = Depends(get_session), team_id: int):
    team = session.get(Team, team_id)
    if not team:
        raise HTTPException(status_code=404, detail="Team not found")
    session.delete(team)
    session.commit()
    return {"ok": True}

Description

Is realationships working for anyone?
I either get null or an empty list.

OK, so, I've copied the last full file preview at the - https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/
Run it and it creates the Db and the foreign key
Then I've insert the data into the Db.

Checking the docs UI everything looks great

But when I do a request for a hero, team is null

Really not sure what going on, especially when all I have just is copied the code example with no changes?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.8.2

Additional Context

No response

@Chunkford - I tried your code above, it works fine.

I suspect maybe the way you are doing things isn't working. The sequence I used was:

Create Some Teams - Post to the API to create the team
Create Some Heros with the Team ID set to the teams that I created previously - since the Create Hero API asks for team ID - so I have to have one.
Get one of the heroes I created - Now shows the team details.

I assume the order you did things is different? - If you set the Team ID to something that doesn't exist in the database then you will see a null response, if you then add a team that has that ID then the get will start to populate.

So in your screenshots, you would want to check the teams and make sure that a team with team_id 2 actually exists in the database - my suspicion is that it doesn't.

If it was a production app, I would likely add some checks to stop setting a team_id when the id doesn't exist in the team table but that would complicate the example I think.

I too am having the exact same issue regarding backrefs not displaying data. Both with my own app, and the demo above.

I followed your example too @obassett with a failed outcome. Data was created and linked (according to dbeaver), however no data is being displayed with a API call is requested.

Tested Versions on 0.0.3 and 0.0.4
Tested DBs: Sqlite, Mariadb
Python: 3.9.6
OS: Fedora KDE

Edit: added screenshots

Do you see it trying to do the SQL lookups if you set echo=True in the create engine? Or are there any errors logged when trying to access it?

@obassett The code I posted came straight from the tutorial pages - https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/
It's the last 'full file preview' on that page with no changes made.
There's defiantly data in the DB.
I originally had the issue using Postgresql which was driving me round the bend, so I thought I would just copy the code from the tutorial to see if that works, which it doesn't.

Here's the SQL output for when I access an individual hero

2021-08-27 10:34:04,787 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-08-27 10:34:04,788 INFO sqlalchemy.engine.Engine SELECT hero.name AS hero_name, hero.secret_name AS hero_secret_name, hero.age AS hero_age, hero.team_id AS hero_team_id, hero.id AS hero_id 
FROM hero 
WHERE hero.id = ?
2021-08-27 10:34:04,788 INFO sqlalchemy.engine.Engine [cached since 838.4s ago] (1,)
INFO:     127.0.0.1:58454 - "GET /heroes/1 HTTP/1.1" 200 OK
2021-08-27 10:34:04,791 INFO sqlalchemy.engine.Engine ROLLBACK

OK, worked out what it was.
I was using fastapi==0.68.0
There's a newer version fastapi==0.68.1 that added support for read_with_orm_mode - https://github.com/tiangolo/fastapi/releases/tag/0.68.1

@alucarddelta update fastapi pip install fastapi -U and it should resolve the issue

@Chunkford I suppose, you shouldn't ever parse lazy relations in production. You would query your database (n * k + 1) times on each request, where n is amount of items in response, and k is amount of relation-fields in single model. You should either restrict relations in your DTOs (to avoid lazy loading), or specify SQLAlchemy's select options (selectinload, joinedload etc).
Using read_with_orm_mode would hide that amount of queries.

OK, worked out what it was.
I was using fastapi==0.68.0
There's a newer version fastapi==0.68.1 that added support for read_with_orm_mode - https://github.com/tiangolo/fastapi/releases/tag/0.68.1

@alucarddelta update fastapi pip install fastapi -U and it should resolve the issue

This worked @Chunkford, I have submitted a pull request #48 to change the pip requirement for the project to be 0.68.1 as it was set to 0.68.0.

🤦‍♂️ I lost so many hours on this -- thank you for the version update recommendation and MR!

Still having the same issue, here is my case:

fastapi==0.70.0
sqlmodel==0.0.4

class Professional(Base, table=True):
    permissions: ProfessionalPermissions = Field(ProfessionalPermissions.NONE, nullable=False,
                                                 sa_column_kwargs={'server_default': text('0')})
    store_id: UUID = Field(foreign_key="store.id", primary_key=True)
    store: List['Store'] = Relationship(back_populates="professionals")

class Store(Base, table=True):
    id: UUID = UUIDField(primary_key=True)
    # ORM Relationships
    professionals: List['Professional'] = Relationship(back_populates="store")

@router.get("", response_model=List[Store])
def list_stores(
        limit: int = 100,
        offset: int = 0,
        crud: CRUDController = Depends(get_crud),
        _: User = Depends(get_current_user),
):
    r = crud.store.list(limit, offset)
    print(r[0].professionals)
    return r

What I get in the request response

[
  {
    "created_at": "2021-12-10T15:45:05.382071",
    "id": "7847365b-bae3-467e-9efa-e6b6f09c5e80",
    "phone_number": "+351210000001",
    "email": "store1@scedle.com",
    "updated_at": "2021-12-10T15:45:05.382071",
    "name": "Store 1"
  }, 
  ...
]

What I get in the terminal with the print(r[0].professionals)

[Professional(permissions=65535, store_id=UUID('7847365b-bae3-467e-9efa-e6b6f09c5e80'), created_at=datetime.datetime(2021, 12, 10, 15, 45, 5, 382000), updated_at=datetime.datetime(2021, 12, 10, 15, 45, 5, 382000), user_id=UUID('b4883904-6d7b-4ce
d-93e9-c6d1a3c5cdb4'))]

I had the same problem but using an async engine. I solved it using selectinload as suggested here #74

response = await db_session.exec(select(self.model).where(self.model.id == id).options(selectinload('*')))

My issue with this was that my Pydantic models for my endpoints (response models) weren't lining up with my SQLAlchemy models. There's really good info here.

This issue exists in 0.78.0 version for me. Anyone else facing the same in the latest version? Please help.

This issue exists in 0.78.0 version for me. Anyone else facing the same in the latest version? Please help.

Have a look at: #315

Pinning SQLAlchemy = ">=1.4.17,<=1.4.35" helped me!

I had the same error using sqlmodel with SQLAlchemy 1.4.40... user: "User" = Relationship(back_populates="person") \\ person: Person = Relationship(back_populates="user")
During the creation process, if User need to reference person_id = int = Field(foreign_key="person.id") it will fail. Even if I commit the changes and refresh the instance, the callback will be always null.

How to reproduce: Use sqlmodel=0.0.6 with SQLAlchemy=1.4.40
Using SQLAlchemy 1.4.35 works flawlessly

Fix SQLalchemy error

Thanks for the discussion everyone! Yep, for the original problem, you should use the latest FastAPI, at least 0.68.1.

For the other issues, it was indeed reported in #315, it was solved in #322, and it will be available in the next version, released in the next hours, SQLModel 0.0.7. 🚀

build(api): pin sqlachemy version for compat

We are still seeing this error, very similarly to rickerp. We have a model with a one to many relationship (e.g. Team to Heroes). In our route, we can print the Team object, and it has Heroes populated. However, when we get the response from the router, it is totally missing. We are on FastAPI version 0.85.1 and SQLModel version 0.0.8.

e.g.

@router.get("/{team_id}/", response_model=Team)
async def get_team(
    team_id: uuid.UUID,
    authed_user: AuthedUser = Depends(get_authed_user),
    db: Session = Depends(get_db),
) -> sql.Project:
    team = TeamOperator(db).get_team(team_id)
    print("TEAM:", team)
    print("HEROES:", team.heroes)
    return team

The prints have all the data populated. But in receiving this response, the data for heroes is totally missing. Our team model looks like this:

class Team(SQLModel, table=True):
    __tablename__ = 'team'

    id: uuid.UUID = Field(sa_column=Column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
        nullable=False,
        unique=True,
    ))
    name: str = Field(sa_column=Column(String, nullable=False))
    heroes: List["Hero"] = Relationship(back_populates="team", sa_relationship_kwargs={
        "cascade": "all, delete-orphan"
    })

It seems that at the root of the issue is the fact that in FastAPI's encoder call, it's calling .dict() on the object, which does not include the heroes attribute. https://github.com/tiangolo/fastapi/blob/0.85.1/fastapi/encoders.py#L56

@nick-catalist, did you have a solution to this? I am also struggling with this. Even when using .options(subqueryload(...)) my relationship objects are not being returned from the FastAPI endpoint. If you return result.__dict__, however, you do seem to get the relationships populated.

There is no need to do subqueryload, if you properly define "out models" and configure response_model in your routes.
It is well explained on site.
The encoder does not know how to render a Relationship field, but it knows how to render a list.
So if you have a 1 to many Relationship, in the response_model you have to define a new model with the very same field but defined of type list.

@JBorrow @tonjo .. right, thank you tonjo for jogging my memory on this as well.

All you need to do is define a separate response model and redefine the relationship field as a list instead. E.G. you'd want a set up like this:

class TeamBase(SQLModel):
    name: str = Field(sa_column=Column(String, nullable=False))

class Team(TeamBase, table=True):
    __tablename__ = 'team'

    id: uuid.UUID = Field(sa_column=Column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
        nullable=False,
        unique=True,
    ))
    heroes: list["Hero"] = Relationship(back_populates="team", sa_relationship_kwargs={
        "cascade": "all, delete-orphan"
    })

class TeamResponse(TeamBase):
    heroes: list["Hero"]

Like tonjo said, Pydantic doesn't know how to render a relationship, but it does know how to render a plain list. Additionally, it will coerce your returned value into whatever you have defined as your response_model (you can also just make it the output of the router function via -> TeamResponse rather than explicitly passing in response_model to the router decorator). So if you return a Team object as a TeamResponse object, it will properly populate the heroes field.

See more info at https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/#models-with-relationships

Ah fantastic, thank you both for your quick reply! This makes sense, though it does feel like a bit of an antipattern (heroes is declared to SQLModel as being of type list[Hero] all along).

Also, @nick-catalist, I presume you want table=False for TeamResponse

Yes, that was a typo, fixed :)

instead of creating a new response_model, i tried messing with pydantic 's model_serializer aand came up with this

class Team(SQLModel, table=True):
	id: str = Field(default_factory=uuid, primary_key=True)
	heroes: list['Hero'] | None = Relationship(back_populates='team')

	@model_serializer
	def model_serializer(self):
		data = {
			'heroes': self.heroes,
		}
		for key in self.model_fields.keys():
			data[key] = getattr(self, key)
		return data

FastAPI and Pydantic - Relationships Not Working · Issue #37 · fastapi/sqlmodel · GitHub

Repository navigation

FastAPI and Pydantic - Relationships Not Working #37

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

FastAPI and Pydantic - Relationships Not Working · Issue #37 · fastapi/sqlmodel · GitHub

Repository navigation

FastAPI and Pydantic - Relationships Not Working #37

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

obassett commented on Aug 27, 2021

alucarddelta commented on Aug 27, 2021

obassett commented on Aug 27, 2021

Chunkford commented on Aug 27, 2021

Chunkford commented on Aug 27, 2021

vimhead commented on Aug 27, 2021

alucarddelta commented on Aug 28, 2021

lobotmcj commented on Oct 21, 2021

rickerp commented on Dec 13, 2021

vargasjona commented on Mar 2, 2022

alexfromapex commented on Mar 6, 2022

Gauth27 commented on Jun 27, 2022

pesc commented on Jul 1, 2022

fkeil commented on Aug 20, 2022

tiangolo commented on Aug 27, 2022

nick-catalist commented on Aug 17, 2023

nick-catalist commented on Aug 17, 2023

JBorrow commented on May 3, 2024

tonjo commented on May 3, 2024

nick-catalist commented on May 3, 2024

JBorrow commented on May 3, 2024

JBorrow commented on May 3, 2024

nick-catalist commented on May 3, 2024

realByg commented on Aug 22, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions