Commited model.model_copy() does not update the database accordingly

[Pamplemousse]

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

import pytest

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


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

engine = create_engine(sqlite_url, echo=True)


class Hero(SQLModel, table=True):
    id: int = Field(default=None, nullable=False, primary_key=True)
    name: str


@pytest.fixture
def create_and_teardown_db_and_tables():
    SQLModel.metadata.create_all(engine)
    yield
    SQLModel.metadata.drop_all(engine)


def test_hero_model_can_be_mutated_then_written_then_selected(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero.name = new_name
        session.add(hero)
        session.commit()
        session.refresh(hero)

        statement = select(Hero)
        results = session.exec(statement)
        retrieved_hero_object = results.one_or_none()

        # our object is up-to-date
        assert hero.name == new_name
        # the selected data from DB is up-to-date
        assert retrieved_hero_object.name == new_name


def test_hero_model_can_be_copied_with_update_then_written_then_selected(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero_copy = hero.model_copy(update={"name": new_name})

        session.add(hero_copy)
        session.commit()
        session.refresh(hero_copy)

        statement = select(Hero)
        results = session.exec(statement)
        retrieved_hero_object = results.one_or_none()

        # our object is up-to-date
        assert hero_copy.name == new_name
        # the selected data from DB is *NOT* up-to-date :'(
        assert retrieved_hero_object.name == new_name

Description

When using pydantic's method model.model_copy() to update fields, then trying to commit the resulting object to database, the record created there still has the old value for the updated field.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

3.13.3

Additional Context

No response

[Pamplemousse]

And these two tests are also failing 🤯
Not 100% sure of what is happening under the hood, and what dark magic actually trips between pydantic and SQLAlchemy 🤔

def test_hero_model_not_added_should_NOT_be_new_in_session(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero_copy = hero.model_copy(update={"name": new_name})

        session.add(hero_copy)

        assert not hero in session.new

def test_hero_model_added_should_be_new_in_session(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero_copy = hero.model_copy(update={"name": new_name})

        session.add(hero_copy)

        assert hero_copy in session.new

[YuriiMotov]

session.add() is used to add new object (which is not present in the DB).
If you need to update existing object - use session.merge():

        hero_copy = session.merge(hero_copy)

Important thing is that not hero_copy will be linked to the session, but it's copy. This is why we need to store the result of session.merge() into variable to be able to refresh it later.

Runnable code example in the details:

import pytest

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


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

engine = create_engine(sqlite_url, echo=True)


class Hero(SQLModel, table=True):
    id: int = Field(default=None, nullable=False, primary_key=True)
    name: str


@pytest.fixture
def create_and_teardown_db_and_tables():
    SQLModel.metadata.create_all(engine)
    yield
    SQLModel.metadata.drop_all(engine)


def test_hero_model_can_be_mutated_then_written_then_selected(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero.name = new_name
        session.add(hero)
        session.commit()
        session.refresh(hero)

        statement = select(Hero)
        results = session.exec(statement)
        retrieved_hero_object = results.one_or_none()


        # our object is up-to-date
        assert hero.name == new_name
        # the selected data from DB is up-to-date
        assert retrieved_hero_object.name == new_name


def test_hero_model_can_be_copied_with_update_then_written_then_selected(create_and_teardown_db_and_tables):
    with Session(engine) as session:
        new_name = "Super-Michel"

        hero = Hero(id=1, name="Michel")
        hero_copy = hero.model_copy(update={"name": new_name})

        hero_copy = session.merge(hero_copy)
        session.commit()
        session.refresh(hero_copy)

        statement = select(Hero)
        results = session.exec(statement)
        retrieved_hero_object = results.one_or_none()

        # our object is up-to-date
        assert hero_copy.name == new_name
        # the selected data from DB is *NOT* up-to-date :'(
        assert retrieved_hero_object.name == new_name