Issue using from_orm stripping Relationship data from models

[res234]

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 sqlmodel import SQLModel, Relationship, Field, create_engine, Session
from typing import List, Optional, Literal

engine = create_engine("sqlite://", connect_args={"check_same_thread": False})


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


class LocationBase(SQLModel):
    street: str
    city: str
    country: str


class Location(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    employee_id: Optional[int] = Field(default=None, foreign_key="employee.id")
    employee: "Employee" = Relationship(back_populates='locations')


class EmployeeBase(SQLModel):
    name: str
    job: str


class Employee(EmployeeBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    locations: List[Location] = Relationship(back_populates="employee")


class EmployeeCreate(EmployeeBase):
    locations: List[Location]


def create_employees():
    locations = [Location(street='State St.', city='New York', country='USA')]
    e = EmployeeCreate(name='Test Employee', job='Test Job', locations=locations)

    employee = Employee.from_orm(e) # this strips the locations list from the Employee object
    print(employee)

    # You have to manually readd the locations before sending data to the DB.
    # If you skip this line then no location data will be added to the DB.
    e.locations = locations

    # Now you can commit.


def main():
    create_db_and_tables()
    create_employees()


if __name__ == "__main__":
    main()

Description

Apologies if this seems trivial! When using SQLModel's from_orm method the relationship data is stripped from the outputted model. I think that there may be an issue with how the from_orm method works in SQLModel but I can't diagnose where the data is actually stripped.

I found a similar issue here: pydantic/pydantic#1334. The difference is that typically (before SQLModel) most models refer to other Pydantic models instead of other relationships. This could be an example of a typical model relationship in Pydantic:

from pydantic import BaseModel


class Employee(BaseModel):
    id: int
    name: str
    type: str


class Address(BaseModel):
    id: int
    city: str
    employee: Employee

    class Config:
        orm_mode = True

e = Employee(id=1, name='Test', type='pydantic')
a= Address(id=1, city='New York', employee=e)

print(a.from_orm(a))

(This correctly outputs id=1 city='New York' employee=Employee(id=1, name='Test', type='pydantic'))

It's not a big deal to readd the models but it becomes tedious if you have many models to manage. I was wondering if anyone else had this issue and I couldn't find it referenced anywhere else. Thank you so much!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8.1

Additional Context

No response

[mafetoro92]

I am having the same issue too

[Butch78]

I was having the same issue, I'm confused why you are able to add Objects with children here in the Docs:

But in the Fastapi + SQLmodel example there is no example of how to solve this solution only if you want to read from the Database:

I'm assuming the Advanced guide might have a solution for how you create models with children. Unless I'm misunderstanding how you should create objects? but I assume you would want an endpoint to behave like @res234's create employee function:

def create_employees():
    locations = [Location(street='State St.', city='New York', country='USA')]
    e = EmployeeCreate(name='Test Employee', job='Test Job', locations=locations)

    employee = Employee.from_orm(e)
    

E.g:

class TeamBase(SQLModel):
 name: str = Field(index=True)
 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):
 heros: Optional[List[Hero]] = Field(default=None)

@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

[res234]

@Butch78 I think we are having different issues.

In your first code example, you can add objects with children because you are creating the Team object directly. My example is a little different because I am creating an EmployeeCreate object and then using the from_orm method in Pydantic. The from_orm method strips out the child data when making the Employee SQLModel object. So I can use from_orm to serialize part of the Employee object but I have to add the locations of the employee manually using employee.locations and then commit.

The docs do give an example of how to read from the database with children: https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/#models-with-relationships

My issues is strictly with the from_orm method only. Apologies if I'm misunderstanding your questions.

[Butch78]

Sorry my answer wasn't very clear @res234, I would like to do the same as you and I agree the 'from_orm' Doesn't quite work right. I would like to Convert Json or TeamCreate objects into a SQLModel using 'from_orm' that can then be added to the database instead of having to manually add it.

[res234]

@Butch78 No problem!

[dansmachina]

Facing the same challenge here... :-)

[Phifo]

same issue here 😢

[ckudera]

I'm facing the same issue. Any updates?

[JeffTax]

I'm here for the same reason, was debugging and found out nested objects were being stripped. This is not intuitive at all, and I came across https://github.com/Wouterkoorn/sqlalchemy-pydantic-orm, but this should be a core feature of SQLModel IMO.

[whugarra]

same issue here 😢

[prurph]

Yeah this bit me as well.