Declarative Models

The “ORM” half of SQLAlchemy: Python classes that map to tables. SQLAlchemy 2.0 introduced typed Mapped[...] annotations that integrate with mypy/pyright. The old Column(...) style still works.

2.0 style — typed declarative

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

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50), index=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)
    is_active: Mapped[bool] = mapped_column(default=True)
    bio: Mapped[str | None]                                       # nullable inferred from Optional
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

Key points:

• Mapped[T] is the type annotation; mypy/pyright see user.name: str.
• mapped_column() configures the column. Often omittable for simple cases.
• Mapped[int | None] (or Optional[int]) → nullable column.
• Mapped[list["Post"]] → one-to-many relationship.
• The forward reference "Post" lets you reference a class defined later.

1.x classic style (still works)

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), index=True)
    email = Column(String(255), unique=True)
    is_active = Column(Boolean, default=True)
    posts = relationship("Post", back_populates="author")

Functionally identical. Type checkers see User.name as Column[String], not str — that’s the main loss. New code: 2.0 style.

Common column types

For explicit SQL types when annotations aren’t expressive enough:

from sqlalchemy import Numeric, JSON, DateTime
from sqlalchemy.dialects.postgresql import JSONB

price: Mapped[Decimal] = mapped_column(Numeric(10, 2))             # 10 digits, 2 decimal places
metadata: Mapped[dict] = mapped_column(JSONB)                      # Postgres JSONB
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

Column options

id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
email: Mapped[str] = mapped_column(unique=True, nullable=False, index=True)
created_at: Mapped[datetime] = mapped_column(server_default=func.now())
updated_at: Mapped[datetime] = mapped_column(
    server_default=func.now(), onupdate=func.now()
)
status: Mapped[str] = mapped_column(default="pending")

Defaults — three flavors:

Use server_default for timestamps and other DB-computed values so they’re correct even from raw SQL or other apps.

onupdate similarly for “auto-update this column on every UPDATE.”

Naming conventions

For consistent constraint/index names across migrations (Alembic):

from sqlalchemy import MetaData

metadata = MetaData(
    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

Without this, Alembic generates random names → migration churn. Configure once.

Indexes and constraints

from sqlalchemy import Index, UniqueConstraint, CheckConstraint

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]
    org_id: Mapped[int]

    __table_args__ = (
        UniqueConstraint("email", "org_id", name="uq_user_email_per_org"),
        CheckConstraint("char_length(email) > 3", name="ck_email_length"),
        Index("ix_user_org_active", "org_id", "is_active"),
    )

__table_args__ is for table-level constraints/indexes. Single-column indexes/uniques can go inline on the column.

repr for debugging

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

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r})"

Without an explicit __repr__, debugging output is <User object at 0x...>. Always add one.

Mixins — shared columns

from sqlalchemy import func
from sqlalchemy.orm import declared_attr

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

class SoftDeleteMixin:
    deleted_at: Mapped[datetime | None]

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

For dynamic __tablename__:

class TableNameMixin:
    @declared_attr.directive
    def __tablename__(cls) -> str:
        return cls.__name__.lower() + "s"

Enums

import enum
from sqlalchemy import Enum

class UserStatus(enum.Enum):
    ACTIVE = "active"
    SUSPENDED = "suspended"
    DELETED = "deleted"

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[UserStatus] = mapped_column(Enum(UserStatus), default=UserStatus.ACTIVE)

Two SQL-level options:

For evolving enums, native_enum=False saves grief. For locked-down domains, native enum is stricter.

Computed and hybrid properties

For column-like accessors that aren’t physical columns:

from sqlalchemy.orm import column_property
from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str]
    last_name: Mapped[str]

    # computed at SELECT time
    full_name: Mapped[str] = column_property(first_name + " " + last_name)

    # Python-side derived; if you want it queryable too, use @hybrid_property
    @hybrid_property
    def display_name(self) -> str:
        return f"{self.first_name} {self.last_name}".strip()

column_property adds a SELECT expression to every query for that model. Cheap if it’s a simple expression; expensive if it’s a subquery (becomes a correlated subquery on every load).

@hybrid_property is Python at the instance level and an expression at the class level (for queries). More flexible.

table_args for table-level config

class User(Base):
    __tablename__ = "users"
    __table_args__ = {
        "schema": "auth",                 # Postgres schema
        "comment": "user accounts",
    }
    id: Mapped[int] = mapped_column(primary_key=True)

For constraints + dict:

__table_args__ = (
    UniqueConstraint("email"),
    Index("ix_status_org", "status", "org_id"),
    {"schema": "auth"},          # dict at the end
)

Common pitfalls

• Forgetting String(N) length — some DBs require it; some (Postgres) accept unbounded VARCHAR. Be explicit.
• default=datetime.now (note no parens) — passed as a callable, evaluated per insert. With parens (datetime.now()) it’s evaluated once at class definition — every row gets the same timestamp.
• server_default=func.now() on a Mapped[datetime] without init=False — Python doesn’t know the value at insert time; need to refresh after commit to see it.
• Using Enum(NativeEnum) in a project where you frequently add enum values — every new value is a migration. Consider native_enum=False.
• __tablename__ mismatch with FK reference — ForeignKey("user.id") when the table is "users". The error is loud but the typo wastes time.

Common interview confusions

• “default and server_default are interchangeable.” — default runs in Python (SQLAlchemy supplies the value); server_default is a DB-side default. The DB-side version works for raw SQL too.
• “Mapped[str | None] and nullable=True are different.” — they’re equivalent in 2.0 style: the | None annotation infers nullability.
• “Column types are enforced by SQLAlchemy.” — column types are mostly enforced by the DB. Python values that don’t fit (e.g. too-long string into String(50)) raise on flush, not assignment.

Interview angle

• “How do you define a table in SQLAlchemy 2.0?” — subclass DeclarativeBase, use Mapped[type] annotations with mapped_column() for config. Nullable via Mapped[T | None]. Relationships via Mapped[list["Other"]] = relationship(...).
• “Difference between default and server_default?” — default runs in Python at INSERT time (SQLAlchemy supplies the value). server_default is set as the DB’s DEFAULT clause; the DB supplies it on rows that don’t provide one. server_default survives raw SQL inserts.
• “How do you add timestamps to multiple models cleanly?” — TimestampMixin class with created_at and updated_at columns, then class User(Base, TimestampMixin).
• “What’s a hybrid property?” — accessor that’s Python on instances and SQL expression on the class. Lets you write User.full_name == "alice smith" in a query AND user.full_name in code, both routing to the same logic.
• “Why might you set native_enum=False?” — native Postgres enums are nice but every new value is a migration. Plain VARCHAR is easier to evolve.
• “What’s __table_args__ for?” — table-level config: composite unique constraints, indexes spanning multiple columns, check constraints, schema, comments. As a tuple ending with an optional dict for table-level kwargs.