FastAPI and Pydantic - Relationships Not Working

[Chunkford]

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

[obassett]

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

1. Create Some Teams - Post to the API to create the team
2. 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.
3. 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.

[alucarddelta]

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

[obassett]

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?

[Chunkford]

@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

[Chunkford]

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

[AlexanderFarkas]

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

[alucarddelta]

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.

[lobotmcj]

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

[rickerp]

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'))]

[jonra1993]

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('*')))

[alexfromapex]

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.