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:

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.