SQLAlchemy - JSON not changing on commit
Changing a key inside a SQLAlchemy JSON column and committing doesn’t save anything, and the commit doesn’t raise an error:
# product.attrs starts as {"color": "red", "size": {"width": 1}}
product.attrs["color"] = "blue"
session.commit() # the row still says "red"
The ORM only sees a change when the attribute gets a new object, like product.attrs = {**product.attrs, "color": "blue"}. That saves, but every edit in the codebase has to remember it. The JSON type’s docs point to MutableDict instead, which makes in-place edits count once you wrap the column type in it:
from sqlalchemy import JSON
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Product(Base):
__tablename__ = "product"
id: Mapped[int] = mapped_column(primary_key=True)
attrs: Mapped[dict] = mapped_column(MutableDict.as_mutable(JSON), default=dict)
On PostgreSQL, use MutableDict.as_mutable(JSONB), with JSONB imported from sqlalchemy.dialects.postgresql.
MutableDict only tracks the top level of the dict, so a change one level down is still lost. Call flag_modified after the nested change to mark the whole column as changed:
from sqlalchemy.orm.attributes import flag_modified
product.attrs["size"]["width"] = 2
flag_modified(product, "attrs")
session.commit()
flag_modified works on a plain JSON column too. Everything here behaves this way on SQLAlchemy 2.1.6.