SQLModel inheritance breaks sqlalchemy mutation tracking

[jtpavlock]

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

#!/usr/bin/env python3 from typing import Optional, Any import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.mutable import MutableDict, MutableSet from sqlalchemy.orm import sessionmaker from sqlalchemy import JSON, Column from sqlmodel import SQLModel, Field, create_engine Session = sessionmaker() Base = declarative_base() class BadTrackBase(SQLModel): custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) class BadTrack(BadTrackBase, table=True): id: Optional[int] = Field(default=None, primary_key=True) class Track(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) engine = create_engine("sqlite:///:memory:") Session.configure(bind=engine) SQLModel.metadata.create_all(engine) session = Session() good_track = Track(id=1, custom={"test": "good"}) bad_track = BadTrack(id=1, custom={"test": "bad"}) session.add(good_track) session.add(bad_track) session.commit() good_track.custom["test"] = "changed" bad_track.custom["test"] = "changed" assert good_track in session.dirty assert bad_track in session.dirty

Description

When setting a field to one of the fields supported by sqlalchemy mutation tracking, it seems the mutation tracking isn't working as intended. I attached the above code sample to illustrate what I mean. The assertions on the bottom of the script should pass under normal circumstance.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.08

Python Version

3.9.13

Additional Context

No response

[blerrgh]

I have the same issue in SQLModel 0.08 and it's kind of a big deal. Is there a workaround for this besides re-assigning the entire dict i.e. good_track.custom = {"test": changed"} ?

[jyotirmay123]

Any update here, A big blocker before I can migrate from sqlalchemy+pydantic to sqlmodel.

[alexpotv]

Also a blocker here, commenting to stay in the loop!

[pwinged]

yep, same here, blocker.

[cch264]

Blocker here as well. Thanks for your post, saved me from burning any more time on figuring out what was going on with mutation tracking.

[tonjo]

I have the same issue in SQLModel 0.08 and it's kind of a big deal. Is there a workaround for this besides re-assigning the entire dict i.e. good_track.custom = {"test": changed"} ?

Problem still present with sqlmodel 0.0.22.
Note you have to re-assign the entire dict and commit in order to make this work.

[YuriiMotov]

I tested it and that's what I found:

session.dirty behaves a bit strange, but it seems that it's not related to inheritance.
If you try mutating only one of good_track or bad_track it will work well (object will be in session.dirty as expected).
But after you mutate second object, first one is not in session.dirty anymore... But second object is IN session.dirty.

good_track.custom["test"] = "changed first time" assert good_track in session.dirty # good_track is in dirty bad_track.custom["test"] = "changed first time" assert bad_track in session.dirty # bad_track is in dirty assert good_track not in session.dirty # good_track is NOT in dirty anymore !!!

At the same time, mutation is handled correctly - if you commit, the object will be updated.

session.commit() session.flush() good_track_db = session.get(Track, 1) bad_track_db = session.get(BadTrack, 1) assert good_track_db.custom["test"] == "changed first time" # Mutation worked out assert bad_track_db.custom["test"] == "changed first time" # Mutation worked out

Also, it only happens if you add object and commit, then mutate it.
If you fetch objects with session.get(), everything works as expected.

good_track_db = session.get(Track, 1) bad_track_db = session.get(BadTrack, 1) good_track_db.custom["test"] = "changed" bad_track_db.custom["test"] = "changed" assert good_track_db in session.dirty assert bad_track_db in session.dirty

Full code in details:

Details

from typing import Any, Optional from sqlalchemy import JSON, Column from sqlalchemy.ext.mutable import MutableDict from sqlalchemy.orm import sessionmaker from sqlmodel import Field, SQLModel, create_engine Session = sessionmaker() class BadTrackBase(SQLModel): custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) class BadTrack(BadTrackBase, table=True): id: Optional[int] = Field(default=None, primary_key=True) class Track(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) engine = create_engine("sqlite:///:memory:") Session.configure(bind=engine) SQLModel.metadata.create_all(engine) session = Session() # ==================================================================================== good_track = Track(id=1, custom={"test": "good"}) bad_track = BadTrack(id=1, custom={"test": "bad"}) session.add(good_track) session.add(bad_track) session.commit() good_track.custom["test"] = "changed first time" assert good_track in session.dirty # good_track is in dirty bad_track.custom["test"] = "changed first time" assert bad_track in session.dirty # bad_track is in dirty assert good_track not in session.dirty # good_track is NOT in dirty anymore !!! session.commit() session.flush() good_track_db = session.get(Track, 1) bad_track_db = session.get(BadTrack, 1) assert good_track_db.custom["test"] == "changed first time" # Mutation worked out assert bad_track_db.custom["test"] == "changed first time" # Mutation worked out # ==================================================================================== # But now session.dirty works fine good_track_db.custom["test"] = "changed" assert good_track_db in session.dirty, session.dirty bad_track_db.custom["test"] = "changed" assert good_track_db in session.dirty, session.dirty assert bad_track_db in session.dirty

So, I think there is no issue here. The obj in session.dirt check works strange, but mutation is handled correctly (objects in DB are updated).

@jtpavlock, do you think we can close this issue now?

[mattgiles]

Why close this issue? The issue documents broken behavior. Things like session.dirty and session.is_modified are broken (not "weird"). That seems like an important thing for users to be aware of, and a good thing for someone to come along and fix. Both of which become less likely if the issue is closed.

[YuriiMotov]

I converted it to discussion so that it would be easier for people to find it.
We'd like to keep issues cleaner and use them to track problems and feature requests that need to be addressed on our side

[mattgiles]

@YuriiMotov came back to say that the "weird" behavior isn't actually broken or weird. I think it's the expected behavior when autoflush is enabled (which it is by default). With Session = sessionmaker(autoflush=False) the assertions in the reproduction code all pass.

[jaynakus]

This is my workaround that is working for now.

class WithDataModel(SQLModel): data: dict = Field(sa_type=JSONB, sa_column_kwargs={'server_default': '{}'}) def __setattr__(self, name: str, value: Any) -> None: if name == 'data': set_attribute(self, name, value) super().__setattr__(name, self.data) else: super().__setattr__(name, value) MutableDict.associate_with(JSONB)

[zoltan-axle]

I just ran across this; it does actually seem like inheritance breaks mutability tracking, as measured by "does commit update the database" (so not just sess.dirty book-keeping). Observe:

#!/usr/bin/env python3 from typing import Any from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.mutable import MutableDict, MutableSet from sqlalchemy.orm import sessionmaker from sqlalchemy import JSON, Column from sqlmodel import SQLModel, Field, create_engine Session = sessionmaker() Base = declarative_base() class BaseModel(SQLModel): custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) class ConcreteModel(BaseModel, table=True): id: int | None = Field(default=None, primary_key=True) engine = create_engine("sqlite:///:memory:") Session.configure(bind=engine) SQLModel.metadata.create_all(engine) session = Session() instance = ConcreteModel(id=1, custom={"test": "old"}) session.add(instance) session.commit() instance = session.get(ConcreteModel, 1) assert instance.custom["test"] == "old" instance.custom["test"] = "new" session.add(instance) session.commit() instance = session.get(ConcreteModel, 1) assert instance.custom["test"] == "new", f"Actual: {instance.custom['test']}"

Traceback (most recent call last): File "/tmp/test.py", line 41, in <module> assert instance.custom["test"] == "new", f"Actual: {instance.custom['test']}" ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ AssertionError: Actual: old 

Contrast to the same code, without inheritance:

#!/usr/bin/env python3 from typing import Any from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.mutable import MutableDict, MutableSet from sqlalchemy.orm import sessionmaker from sqlalchemy import JSON, Column from sqlmodel import SQLModel, Field, create_engine Session = sessionmaker() Base = declarative_base() class Model(SQLModel, table=True): id: int | None = Field(default=None, primary_key=True) custom: dict[str, Any] = Field( sa_column=Column(MutableDict.as_mutable(JSON(none_as_null=True))), default={} ) engine = create_engine("sqlite:///:memory:") Session.configure(bind=engine) SQLModel.metadata.create_all(engine) session = Session() instance = Model(id=1, custom={"test": "old"}) session.add(instance) session.commit() instance = session.get(Model, 1) assert instance.custom["test"] == "old" instance.custom["test"] = "new" session.add(instance) session.commit() instance = session.get(Model, 1) assert instance.custom["test"] == "new", f"Actual: {instance.custom['test']}"

(no output)

This is especially problematic because this inheritance pattern seems like maybe the sanest way to work around the lack of Pydantic validations in SQLModel (see option 7 in #52 (comment)).