sqlmodel_update not handling nested model components

[LordPhwa]

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

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


class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None


class GoodHeroComponent(SQLModel, table=True):
    hero_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="hero.id")
    hero: "Hero" = Relationship(back_populates="good_hero_component")
    good_deeds_count: int


class GoodHeroComponentUpdate(SQLModel):
    good_deeds_count: int | None = Field(default=None)


class BadHeroComponent(SQLModel, table=True):
    hero_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="hero.id")
    hero: "Hero" = Relationship(back_populates="bad_hero_component")
    bad_deeds_count: int


class BadHeroComponentUpdate(SQLModel):
    bad_deeds_count: int | None = Field(default=None)


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

    good_hero_component: Optional[GoodHeroComponent] = Relationship(
        back_populates="hero",
        sa_relationship_kwargs={"cascade": "all, delete-orphan"},
    )
    bad_hero_component: Optional[BadHeroComponent] = Relationship(
        back_populates="hero",
        sa_relationship_kwargs={"cascade": "all, delete-orphan"},
    )


class HeroUpdate(SQLModel):
    name: str | None = Field(default=None)
    secret_name: str | None = Field(default=None)
    age: int | None = Field(default=None)

    good_hero_component: GoodHeroComponentUpdate | None = Field(default=None)
    bad_hero_component: BadHeroComponentUpdate | None = Field(default=None)


class GoodHero(HeroBase):
    id: int
    good_hero_component: GoodHeroComponent


class BadHero(HeroBase):
    id: int
    bad_hero_component: BadHeroComponent


def variant_1(engine, hero_id):
    new_bad_deeds_count = 49

    hero_update = HeroUpdate(
        bad_hero_component=BadHeroComponentUpdate(bad_deeds_count=new_bad_deeds_count),
    )

    with Session(engine) as session:
        hero_ = session.get(Hero, hero_id)
        hero_update_data = hero_update.model_dump(exclude_unset=True)
        hero_ = hero_.sqlmodel_update(hero_update_data)
        session.commit()
        session.refresh(hero_)
        assert hero_.bad_hero_component.bad_deeds_count == new_bad_deeds_count


def variant_2(engine, hero_id):
    new_bad_deeds_count = 49

    hero_update = HeroUpdate(
        bad_hero_component=BadHeroComponentUpdate(bad_deeds_count=new_bad_deeds_count),
    )

    def update_recursive(obj, data) -> None:
        for key, value in data.items():
            if isinstance(value, SQLModel):
                update_recursive(getattr(obj, key), value)
            if isinstance(value, dict):
                update_recursive(getattr(obj, key), value)
            else:
                setattr(obj, key, value)

    with Session(engine) as session:
        hero_ = session.get(Hero, hero_id)
        hero_update_data = hero_update.model_dump(exclude_unset=True)
        update_recursive(hero_, hero_update_data)
        session.commit()
        session.refresh(hero_)
        assert hero_.bad_hero_component.bad_deeds_count == new_bad_deeds_count


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

    engine = create_engine(sqlite_url, echo=True)

    SQLModel.metadata.create_all(engine)

    hero = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero.bad_hero_component = BadHeroComponent(bad_deeds_count=48)

    with Session(engine) as session:
        session.add(hero)
        session.commit()
        session.refresh(hero)

    hero_id = hero.id

    # Does not work
    variant_1(engine, hero_id)

    # Does work
    variant_2(engine, hero_id)

Description

Thanks a lot for your awesome work with sqlmodel. I really respect the work and effort that went into such a huge undertaking.

In my Project, i use a composition-based approach in my models. There are several differently structured but related models in a single table. The approach uses additional table models, where the primary key is a foreign_key of the base table model, like a component of the base model as an optional relation. This way, i can enforce a structure for a concrete type (like a GoodHero has a GoodHero component), but can have one table with one relation to another table (like a Hero can still be a member of a Team).

I considered several alternatives to this approach:

• A GoodHero Table and a BadHero Table -> Would exponentially increase the complexity in relations to other tables
• A Hero Table, with nullable Fields for the not-shared attributes -> Open to inconsistencies

I really like the approach used in my example, because i can easily define which attributes belong to a concrete type, and even define update models for them to control what should be changable. Also adding new types is easy, because i can put them into a new component, without much additional changes to the model. Relations to other Entities are handled in the base table, so that other entities dont need knowledge about specific (sub-)types.

My problem lies in updating this design: When trying to use my defined update model "HeroUpdate" with sqlmodel_update, the attributes of the component are not updated, even though they are present in the dictionary which is passed to sqlmodel_update. This is the example in the function "variant_1".

I built a workaround for this in the example in function "variant_2", but this feels like a hack, and i am not sure if this could lead to other problems.

Is there a way to handle this properly within sqlmodel? Maybe there could be a flag like "include_nested_models" or something like that. Also, if you can see a design issue with this, im happy to receive feedback for that.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

Python 3.11.7

Additional Context

No response

[sparklog]

I found this problem as well. In this scenario, I update the data manually.