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.

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