SQLAlchemy JSON

0.7.0 · active · verified Sat Apr 11

SQLAlchemy-JSON provides mutation-tracked JSON types to SQLAlchemy. It includes `MutableJson` for tracking top-level changes in JSON objects and `NestedMutableJson` for deep tracking within nested objects or lists. The library is actively maintained with recent releases focusing on Python 3 compatibility and enhanced functionality.

Warnings

breaking Python 2.x support was officially dropped in version `0.6.0`. Projects running on Python 2 must use an older version of `sqlalchemy-json` or upgrade to Python 3.
Fix: Upgrade to Python 3 or pin `sqlalchemy-json` to `<0.6.0`.
breaking The primary public API classes `JsonObject` and `NestedJsonObject` were renamed to `MutableJson` and `NestedMutableJson` respectively in a breaking change prior to PyPI release `0.2.0`. Older codebases might still reference the deprecated names.
Fix: Update imports to use `from sqlalchemy_json import MutableJson` and `from sqlalchemy_json import NestedMutableJson`.
gotcha `MutableJson` only tracks changes to the top-level dictionary or list (e.g., adding/removing keys, reassigning a key). It will NOT detect in-place modifications to nested dictionaries or lists within the JSON structure.
Fix: For deep mutation tracking (changes within nested JSON objects or arrays), use `NestedMutableJson` instead of `MutableJson`.
gotcha SQLAlchemy's native `JSON` type (or `sqlalchemy.dialects.postgresql.JSONB`) does not automatically track in-place mutations to Python `dict` or `list` objects. `sqlalchemy-json` is specifically designed to add this mutation tracking capability.
Fix: Always use `MutableJson` or `NestedMutableJson` (or `mutable_json_type` factory) from `sqlalchemy_json` if mutation tracking is required. Do not rely on plain `sqlalchemy.JSON` for this behavior.
gotcha Earlier versions (prior to `0.6.0` and `0.5.0`) contained bugs related to pickling support, which could lead to unexpected behavior or data corruption if models with JSON types were pickled.
Fix: Upgrade to version `0.6.0` or later to ensure proper pickling support.

Install

pip install sqlalchemy-json Install latest version

Imports

MutableJson
```
from sqlalchemy_json import MutableJson
```

NestedMutableJson

from sqlalchemy_json import NestedMutableJson

JsonObject
```
from sqlalchemy_json import MutableJson
```
The class `JsonObject` was renamed to `MutableJson` in a backwards-incompatible change prior to PyPI release `0.2.0` (which established the current class names).
NestedJsonObject
```
from sqlalchemy_json import NestedMutableJson
```
The class `NestedJsonObject` was renamed to `NestedMutableJson` in a backwards-incompatible change prior to PyPI release `0.2.0`.

Quickstart

This quickstart demonstrates defining SQLAlchemy models with `MutableJson` for top-level mutable JSON fields and `NestedMutableJson` for fields requiring deep mutation tracking. It shows how changes to the JSON data are automatically detected and persisted to the database upon session commit.

import os
from datetime import datetime
from sqlalchemy import create_engine, Column, Text, ForeignKey, Integer, DateTime
from sqlalchemy.orm import declarative_base, sessionmaker, Mapped, mapped_column
from sqlalchemy_json import MutableJson, NestedMutableJson

# Setup database (in-memory SQLite for example)
DATABASE_URL = os.environ.get('DATABASE_URL', 'sqlite:///:memory:')
engine = create_engine(DATABASE_URL, echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()

# Define models
class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(Text)
    # MutableJson for top-level changes
    handles: Mapped[dict] = mapped_column(MutableJson)

class Article(Base):
    __tablename__ = "articles"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
    content: Mapped[str] = mapped_column(Text)
    # NestedMutableJson for deep changes
    references: Mapped[dict] = mapped_column(NestedMutableJson)

# Create tables
Base.metadata.create_all(engine)

session = Session()

# Example for MutableJson
author = Author(name='John Doe', handles={'twitter': '@JohnDoe', 'facebook': 'JohnDoe'})
session.add(author)
session.commit()

# Retrieve and modify top-level JSON
retrieved_author = session.query(Author).first()
print(f"Original handles: {retrieved_author.handles}")
retrieved_author.handles['twitter'] = '@JDoe'
session.commit() # Change is detected
print(f"Updated handles (MutableJson): {retrieved_author.handles}")

# Example for NestedMutableJson
article = Article(
    author_id=retrieved_author.id,
    content='Some article content',
    references={'github.com': {'repo1': 4, 'repo2': 7}, 'example.com': {'link1': 2}}
)
session.add(article)
session.commit()

# Retrieve and modify nested JSON
retrieved_article = session.query(Article).first()
print(f"Original references: {retrieved_article.references}")
retrieved_article.references['github.com']['repo1'] += 10 # Nested change
session.commit() # Change is detected
print(f"Updated references (NestedMutableJson): {retrieved_article.references}")

session.close()

view raw JSON →