Defining ORM Models

Basic Model (SQLAlchemy 2.0)

from sqlalchemy import String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from datetime import datetime

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(50), unique=True)
    email: Mapped[str] = mapped_column(String(100), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow
    )

    # Relationship
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

    def __repr__(self):
        return f"User(id={self.id}, username={self.username!r})"

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    content: Mapped[str] = mapped_column(String)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

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

Common Column Types

from sqlalchemy import (
    String, Text, Integer, BigInteger, SmallInteger,
    Float, Numeric, Boolean, Date, DateTime, Time,
    LargeBinary, JSON, ARRAY, UUID
)
from sqlalchemy.dialects.postgresql import JSONB, INET, CIDR

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    description: Mapped[str | None] = mapped_column(Text)
    price: Mapped[float] = mapped_column(Numeric(10, 2))
    quantity: Mapped[int] = mapped_column(Integer, default=0)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    metadata_: Mapped[dict] = mapped_column(JSONB, default={})
    tags: Mapped[list] = mapped_column(ARRAY(String), default=[])

Constraints and Indexes

from sqlalchemy import Index, UniqueConstraint, CheckConstraint

class Order(Base):
    __tablename__ = "orders"
    __table_args__ = (
        UniqueConstraint('user_id', 'order_number', name='uq_user_order'),
        CheckConstraint('total >= 0', name='ck_positive_total'),
        Index('ix_order_date', 'order_date'),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    order_number: Mapped[str] = mapped_column(String(50))
    total: Mapped[float] = mapped_column(Numeric(10, 2))
    order_date: Mapped[datetime] = mapped_column(DateTime)

Creating Tables

# Create all tables
Base.metadata.create_all(engine)

# Drop all tables
Base.metadata.drop_all(engine)

Model Definition

Model Definition

Defining ORM Models

Basic Model (SQLAlchemy 2.0)

Common Column Types

Constraints and Indexes

Creating Tables