How to use JSON columns with SQLModel

Question

I'm trying to define a JSON column via SQLModel:

from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, JSON


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    meta: JSON

Code is from SQLModel, but extended by "meta" attribute.

Using the above code with the rest of the example code (setting up sqlite, adding data), I get the following error:

RuntimeError: no validator found for <class 'sqlalchemy.sql.sqltypes.JSON'>, see `arbitrary_types_allowed` in Config

I tried to extend the code by

class Hero(SQLModel, table=True):
    [...]
    meta: JSON

    @validator('meta')
    def validate_json(v):
        return v

    class Config:
        arbitrary_types_allowed = True 

But this leads to another error:

sqlalchemy.exc.CompileError: (in table 'hero', column 'meta'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?

I tried it with using SQLAlchemy only and it has worked.

So any ideas how can I get the "connection" done between SQLModel and SQLAlchemy for the JSON field?

Update: I also have tested to set it optional and give it a default value. No success (2. error again):

class Hero(SQLModel, table=True):
    [...]
    meta: Optional[JSON] = {}

    class Config:
        arbitrary_types_allowed = True 

Small hint: Even if JSON is imported from SQLModel, it gets finally imported from SQLAlchemy.sqltypes without any changes.

Jone

Perhaps my implementation here can assist: github.com/tiangolo/sqlmodel/issues/235#issuecomment-1162063590 – Wrangle

Answer 1

I believe the connection you are looking for might be provided by the sa_column argument of Field, for example:

class Hero(SQLModel, table=True):
    [...]

    meta: Dict = Field(default_factory=dict, sa_column=Column(JSON))

    # Needed for Column(JSON)
    class Config:
        arbitrary_types_allowed = True

Rue

arbitrary_types_allowed doesn't seem to be mandatory, it works without it under sqlmodel 0.0.8 – Epirogeny

Answer 2

With pydantic BaseModel you can do it like this...

# schema.py
from pydantic import BaseModel, Field

class YourSchema(BaseModel):
    """normal BaseModel schema for your data"""
    username: str = Field(example="geminixiang")
    data: dict = Field(default={}, example={"foo": "bardata"})

# model.py
from schema import YourSchema
from typing import Optional
from sqlalchemy import JSON, Column
from sqlmodel import Field, SQLModel


class ForDBModel(SQLModel, YourSchema, table=True):
    """when you use it for db orm"""
    id: Optional[int] = Field(default_factory=dict, primary_key=True)
    # overwrite `dict` field
    data: dict = Field(sa_column=Column(JSON), default={})

Merrilee

Note the data Field definition should use default_factory=dict rather than default={} because each instance should get its own dictionary object. – Moussorgsky

Answer 3

If you are using PostgreSQL you can use:

from typing import Optional
from sqlalchemy.dialects.postgresql import JSONB

Then in your model:

meta: Optional[dict] = Field(nullable=True, sa_type=JSONB)

Adminicle