With Relationship field filled but empty foreign_key validate raise ValidationError

[mlachowski]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 Optional, List

from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship


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

    children: List["Child"] = Relationship(back_populates="hero")


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

    hero_id: int = Field(foreign_key="hero.id")
    hero: Hero = Relationship(back_populates="children")


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

engine = create_engine(sqlite_url, echo=True)


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


def create_heroes():
    hero_1 = Hero(name="Deadpond")

    with Session(engine) as session:
        session.add(hero_1)

        session.commit()


def select_hero():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        return results.first()


def main():
    create_db_and_tables()
    create_heroes()
    hero = select_hero()

    Child.model_validate({"name": "Deadpond child", "hero": hero})


if __name__ == "__main__":
    main()

Description

• Create a Hero and Child model
• Create tables, create and select one Hero
• Try to use model_validate on Child with following data {"name": "Deadpond child", "hero": hero}
• ValidationError is raised for missing hero_id

What is the best approach for this? Should I always populate the _id field instead of Relationship field with whole entity? Or maybe the SQLModel should populate the _id field internally for validation purpose?

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

Python 3.11.7

Additional Context

No response

[YuriiMotov]

You shouldn't use models with table=True for validation. Such models bypass some validation and relationships are not validated.

Instead you should create new ChildPublic model and use it for validation\serialization:

class ChildBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

class Child(ChildBase, table=True):
    hero_id: int = Field(foreign_key="hero.id")
    hero: Hero = Relationship(back_populates="children")

class ChildPublic(ChildBase):
    hero: HeroPublic

Runnable code example in the details:

from typing import Optional, List

from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship


class HeroBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


class Hero(HeroBase, table=True):
    children: List["Child"] = Relationship(back_populates="hero")


class HeroPublic(HeroBase):
    pass


class ChildBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


class Child(ChildBase, table=True):
    hero_id: int = Field(foreign_key="hero.id")
    hero: Hero = Relationship(back_populates="children")


class ChildPublic(ChildBase):
    hero: HeroPublic


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

engine = create_engine(sqlite_url, echo=True)


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


def create_heroes():
    hero_1 = Hero(name="Deadpond")

    with Session(engine) as session:
        session.add(hero_1)

        session.commit()


def select_hero():
    with Session(engine) as session:
        statement = select(Hero)
        results = session.exec(statement)
        return results.first()


def main():
    create_db_and_tables()
    create_heroes()
    hero = select_hero()

    print(ChildPublic.model_validate({"name": "Deadpond child", "hero": hero}))


if __name__ == "__main__":
    main()