SQLAlchemy and JSON(B) data types

[jcpunk]

From : https://docs.litestar.dev/latest/usage/databases/sqlalchemy/models_and_repository.html

Pydantic BaseModel and Dict classes map to an optimized JSON type that is JSONB for Postgres, VARCHAR or BYTES with JSON check constraint for Oracle, and JSON for other dialects.

Cool, that is exactly what I'd like. After several attempts I have no idea how I might use the abstraction.

Could someone extend the sample code there by adding a JSON field to the Book model? That would both help me and, if merged to the upstream doc, provide an example for folks in the future.

[provinzkraut]

There's nothing you need to "do" to use this. The functionality is provided out of the box. If you use a dict or something that inherits from pydantic.BaseModel inside a Mapped[] column definition, it will use JSONB.

[jcpunk]

I must be missing something obvious as this errors out:

from datetime import datetime
from typing import Dict

from litestar.contrib.sqlalchemy.base import UUIDBase
from sqlalchemy import DateTime, String, func
from sqlalchemy.orm import Mapped, mapped_column


class HostInfo(UUIDBase):
    client_address: Mapped[str] = mapped_column(String(45), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=func.now(), nullable=False)
    system_facts: Mapped[dict] = mapped_column(Dict, nullable=False, default={})

[provinzkraut]

You're making this more complicated than necessary :)
system_facts: Mapped[dict] = mapped_column(nullable=False, default={}) is all you need.

[jcpunk]

Thanks so much!