SQLAlchemy Cheatsheet 16 — Type Annotations and Mapped Style

class User(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]               # NOT NULL, inferred String
    name: Mapped[str | None]         # NULL
    age: Mapped[int | None] = mapped_column(default=None)

from sqlalchemy import String, Text
from sqlalchemy.dialects.postgresql import JSONB
from datetime import datetime
from sqlalchemy.types import DateTime

class Base(DeclarativeBase):
    type_annotation_map = {
        str: String(255),                            # all str → VARCHAR(255)
        datetime: DateTime(timezone=True),           # all datetime → TIMESTAMPTZ
        dict: JSONB,                                 # all dict → JSONB
    }

bio: Mapped[str] = mapped_column(Text)

from typing import Annotated
from sqlalchemy import String

str120 = Annotated[str, mapped_column(String(120))]
intpk = Annotated[int, mapped_column(primary_key=True)]
nullable_str = Annotated[str | None, mapped_column(default=None)]

class User(Base):
    id: Mapped[intpk]
    name: Mapped[str120]
    bio: Mapped[nullable_str]

class Order(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    code: Mapped[str] = mapped_column(String(8))
    description: Mapped[str | None] = mapped_column(Text)
    amount: Mapped[Decimal] = mapped_column(Numeric(12, 2))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

from sqlalchemy.dialects.postgresql import JSONB, ARRAY

class Doc(Base):
    tags: Mapped[list[str]] = mapped_column(ARRAY(String))      # Postgres array
    metadata_: Mapped[dict] = mapped_column(JSONB)
    config: Mapped[dict | None] = mapped_column(JSONB, default=None)

class User(Base):
    posts: Mapped[list["Post"]] = relationship(back_populates="author")
    profile: Mapped["Profile | None"] = relationship(back_populates="user", uselist=False)

class Post(Base):
    author: Mapped[User] = relationship(back_populates="posts")

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(server_default=func.now(), onupdate=datetime.utcnow)

class User(TimestampMixin, Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)

from sqlalchemy.orm import column_property

class Post(Base):
    title: Mapped[str]
    body: Mapped[str]
    title_lower: Mapped[str] = column_property(func.lower(title))

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    first: Mapped[str]
    last: Mapped[str]
    
    @hybrid_property
    def full_name(self) -> str:
        return f"{self.first} {self.last}"
    
    @full_name.expression
    @classmethod
    def full_name(cls):
        return func.concat(cls.first, " ", cls.last)

# Now both work:
user.full_name              # Python
await s.execute(select(User).where(User.full_name == "Alice X"))   # SQL

from sqlalchemy.types import TypeDecorator, String
from pydantic import BaseModel

class PydanticJSONB(TypeDecorator):
    impl = JSONB
    cache_ok = True
    
    def __init__(self, model_cls, **kwargs):
        self.model_cls = model_cls
        super().__init__(**kwargs)
    
    def process_bind_param(self, value, dialect):
        if value is None: return None
        return value.model_dump() if isinstance(value, BaseModel) else value
    
    def process_result_value(self, value, dialect):
        if value is None: return None
        return self.model_cls.model_validate(value)

class Event(Base):
    payload: Mapped["EventPayload"] = mapped_column(PydanticJSONB(EventPayload))

import enum
from sqlalchemy import Enum as SAEnum

class Role(str, enum.Enum):
    USER = "user"; ADMIN = "admin"

class User(Base):
    role: Mapped[Role] = mapped_column(SAEnum(Role, name="user_role"))

[tool.mypy]
strict = true
plugins = ["sqlalchemy.ext.mypy.plugin"]