Serialize model to JSON/JSONB column

[j4k0bk]

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

def json_serializer(value: Any, _: SerializerFunctionWrapHandler) -> str:
    # Serialize the value to JSON using Pydantic's encoder
    return json.dumps(value, default=pydantic_encoder)


class Filters(SQLModel, table=True):
    text: Optional[str] = None
    tags: Optional[List[str]] = None


class Job(SQLModel, table=True):
    """
    A job.
    """
    filters: Filters = \
        Field(default=None, sa_column=sa.Column(JSONB(), default=None))

    filters2: Annotated[Filters, WrapSerializer(json_serializer)] = \
        Field(sa_column=sa.Column(JSONB()))

Description

Is there a way I can use an instance of another model as attribute and have it JSON dumped to/loaded from a Postgres JSONB column?

Operating System

Other

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.11.4

Additional Context

No response

[connesy]

What worked for me was to override the global json.dumps-function used by psycopg when converting a field to JSON(B), using psycopg.types.json.set_json_dumps():

import json
from psycopg.types.json import set_json_dumps
from sqlalchemy.dialects.postgresql import ARRAY, JSONB
from sqlmodel import Column, Field, SQLModel

def sqlmodel_json_serializer(obj) -> str:
    """Custom JSON serializer for SQLModel."""
    return json.dumps(obj, default=lambda o: o.model_dump())

set_json_dumps(sqlmodel_json_serializer)

class Ingredient(SQLModel):
    name: str
    quantity: float

class Recipe(SQLModel, table=True):
    ingredients: list[Ingredient] = Field(sa_column=Column(ARRAY(JSONB)))

However, when reading the data back out again, it's not validated against the Recipe model (and thus the underlying Ingredients model), so the ingredients attribute will just be a dictionary. If you want it back as a validated model, you need a separate data-model to validate against (or at least I haven't figured out a way to do data validation against a table model).