Not able to read data via relationship back-populates

[syncopatedGlitch]

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

class TripInput(SQLModel):
    start: int
    end: int
    description: str

class TripOutput(TripInput):
    id: int

class Trip(TripInput, table=True):
    id: Optional[int] = Field(default=None, primary_key = True)
    car_id: int = Field(foreign_key="car.id")
    car: "Car" = Relationship(back_populates="trips")

class CarInput(SQLModel):
    size: str
    fuel: str = "electric"
    doors: int
    transmission: str = "auto"

class Car(CarInput, table=True):
    id: Optional[int] = Field(primary_key = True, default=None)
    trips: list[Trip] = Relationship(back_populates="car")


class CarOutput(CarInput):
    id: int
    trips: list[TripOutput] = []

@app.get('/api/cars/{id}', response_model=CarOutput)
def car_by_id(id: int, session: Session = Depends(get_session)) -> Car:
    car = session.get(Car, id)
    if car:
        query = select(Trip).where(Trip.car_id == id)
        triplist = session.exec(query).all()
        car.trips.append(triplist)
        return car
    else:
        raise HTTPException(status_code=400, detail=f"No car with id {id} found")

start	end	description	id	car_id
0	100	manali trip	1	1
300	600	Leh	2	1
0	20	Mall Visit	3	2

size	fuel	doors	transmission	id
m	hybrid	4	auto	1
l	hybrid	5	auto	2

Description

I've created a carsharing app, which has car and trip classes. Both have a back populates relationship on them with the other object. I am using SQLite, and database already has a Car table with 2 entries, and trip table with 3 entries as shown in the example code.
I want to return car when a GET call is made with Car_id as input.
But getting an internal server error as below
car.trips.append(triplist)
AttributeError: 'Car' object has no attribute 'trips'

read the documentation and still not able to resolve. Appreciate any help.
code.zip

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.4

Additional Context

No response

[Apollo314]

Probably has something to do with SQLAlchemy version. After SQLAlchemy version 1.4.35, relationships have been broken. #315

[cisaacstern]

I agree with @Apollo314's assessment. We are also seeing this problem in our project, and pinning sqlalchemy==1.4.35 solves the problem, as documented in #315.

[syncopatedGlitch]

Thanks a lot folks. I will try downgrading to .35 and see how that goes. M sure thats the reason, because thats the only thing that made sense so far while debugging. Appreciate the help.

[YuriiMotov]

It was fixed by #461.
It works well in current version (SQLModel 0.0.24, SQLAlchemy 2.0.42)

Test code in the details:

from contextlib import asynccontextmanager
from typing import Optional, cast

from fastapi import Depends, FastAPI, HTTPException, Request
from fastapi.testclient import TestClient
from sqlalchemy import Engine, StaticPool
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class TripInput(SQLModel):
    start: int
    end: int
    description: str


class TripOutput(TripInput):
    id: int


class Trip(TripInput, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    car_id: int = Field(foreign_key="car.id")
    car: "Car" = Relationship(back_populates="trips")


class CarInput(SQLModel):
    size: str
    fuel: str = "electric"
    doors: int
    transmission: str = "auto"


class Car(CarInput, table=True):
    id: Optional[int] = Field(primary_key=True, default=None)
    trips: list[Trip] = Relationship(back_populates="car")


class CarOutput(CarInput):
    id: int
    trips: list[TripOutput] = []


def init_db(engine: Engine):
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        session.add(Car(size="m", fuel="hybrid", doors=4, id=1))
        session.add(Car(size="l", fuel="hybrid", doors=5, id=2))
        session.add(Trip(start=0, end=100, description="manali trip", id=1, car_id=1))
        session.add(Trip(start=300, end=600, description="Leh", id=2, car_id=1))
        session.add(Trip(start=0, end=20, description="Mall Visit", id=3, car_id=2))
        session.commit()


@asynccontextmanager
async def lifespan(app: FastAPI):
    engine = create_engine(
        "sqlite:///",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    init_db(engine)
    yield {"engine": engine}


app = FastAPI(lifespan=lifespan)


async def get_session(request: Request):
    engine = cast(Engine, request.state._state["engine"])
    with Session(engine) as session:
        yield session


@app.get("/api/cars/{id}", response_model=CarOutput)
def car_by_id(id: int, session: Session = Depends(get_session)) -> Car:
    car = session.get(Car, id)
    if car:
        # query = select(Trip).where(Trip.car_id == id)
        # triplist = session.exec(query).all()
        # car.trips.append(triplist)
        return car
    else:
        raise HTTPException(status_code=400, detail=f"No car with id {id} found")


def test_():
    with TestClient(app) as client:
        resp = client.get("/api/cars/1")
        assert resp.status_code == 200
        trips = resp.json()["trips"]
        assert len(trips) == 2
        assert trips[0]["description"] in ("manali trip", "Leh")

BTW, you don't need to manually fetch trips and add them to car.trips

@app.get("/api/cars/{id}", response_model=CarOutput)
def car_by_id(id: int, session: Session = Depends(get_session)) -> Car:
    car = session.get(Car, id)
    if car:
        # query = select(Trip).where(Trip.car_id == id)
        # triplist = session.exec(query).all()
        # car.trips.append(triplist)
        return car
    else:
        raise HTTPException(status_code=400, detail=f"No car with id {id} found")