SQLAlchemy Cheatsheet 02 — Schema, Mapped Columns, and Types

from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)

from sqlalchemy.orm import Mapped, mapped_column
from datetime import datetime
from sqlalchemy import String, Text, BigInteger, Numeric, DateTime, func

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True, nullable=False, index=True)
    name: Mapped[str | None] = mapped_column(String(120))
    bio: Mapped[str | None] = mapped_column(Text)
    salary: Mapped[Decimal] = mapped_column(Numeric(12, 2))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=datetime.utcnow,
    )

email: Mapped[str]                # NOT NULL
email: Mapped[str | None]         # NULL
email: Mapped[str | None] = mapped_column(default=None)

# Python-side (runs at ORM-level insert)
created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

# DB-side (DEFAULT in DDL; preferred for consistency)
created_at: Mapped[datetime] = mapped_column(server_default=func.now())

# On update (Python-side; runs on UPDATE via ORM)
updated_at: Mapped[datetime] = mapped_column(onupdate=datetime.utcnow)

# Single
id: Mapped[int] = mapped_column(primary_key=True)

# Composite
class OrderItem(Base):
    order_id: Mapped[int] = mapped_column(primary_key=True)
    line: Mapped[int] = mapped_column(primary_key=True)

# UUID
import uuid
from sqlalchemy import Uuid
class Doc(Base):
    id: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), primary_key=True, default=uuid.uuid4)

from sqlalchemy import ForeignKey

class Post(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"),
        index=True,
    )

# Inline
email: Mapped[str] = mapped_column(unique=True, index=True)

# Composite / named
from sqlalchemy import Index

class User(Base):
    __tablename__ = "users"
    email: Mapped[str]
    active: Mapped[bool]
    
    __table_args__ = (
        Index("ix_users_active_email", "active", "email"),
    )

from sqlalchemy import CheckConstraint, UniqueConstraint

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[Decimal]
    
    __table_args__ = (
        CheckConstraint("amount > 0", name="ck_orders_amount_positive"),
        UniqueConstraint("user_id", "code", name="uq_orders_user_code"),
    )

from sqlalchemy import Computed

class Product(Base):
    name: Mapped[str]
    name_lower: Mapped[str] = mapped_column(Computed("lower(name)", persisted=True))

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))

from sqlalchemy import JSON
class Event(Base):
    payload: Mapped[dict] = mapped_column(JSON)

# Postgres-specific JSONB:
from sqlalchemy.dialects.postgresql import JSONB
class Event(Base):
    payload: Mapped[dict] = mapped_column(JSONB)

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 SoftDeleteMixin:
    deleted_at: Mapped[datetime | None]

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

class User(Base):
    __tablename__ = "users"
    email: Mapped[str]
    
    __table_args__ = (
        UniqueConstraint("email"),
        Index("ix_users_email", "email"),
        {"comment": "App users", "schema": "public"},
    )

from sqlalchemy.types import TypeDecorator, String

class LowercaseString(TypeDecorator):
    impl = String
    cache_ok = True
    
    def process_bind_param(self, value, dialect):
        return value.lower() if value else value