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.

Perhaps my implementation here can assist: https://github.com/tiangolo/sqlmodel/issues/235#issuecomment-1162063590 — Zaffer, Jun 21 '22 at 17:49

Getafix · Answer 1 · 2022-01-10T22:31:30.620

12

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={}, sa_column=Column(JSON))

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

edited Jan 10 '22 at 22:31

answered Jan 10 '22 at 22:22

Getafix

121
4

arbitrary_types_allowed doesn't seem to be mandatory, it works without it under sqlmodel 0.0.8 – rcepre Jan 24 '23 at 06:54

score 0 · Answer 2 · answered Apr 26 '23 at 02:14

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=None, primary_key=True)
    # overwrite `dict` field
    data: dict = Field(sa_column=Column(JSON), default={})

Note the `data` Field definition should use `default_factory=dict` rather than `default={}` because each instance should get its own dictionary object. — Stephen Simmons, May 15 '23 at 23:02

How to use JSON columns with SQLModel

2 Answers2

Linked