skip to content

Mapped Classes & Annotations

A DeclarativeBase subclass declares its table through Mapped[] annotations and mapped_column(). Interviewers ask how Optional sets nullable and what MappedAsDataclass adds.

on this pageshow

explore

questions

5

In SQLAlchemy's 2.0-style ORM, how do you declare a mapped class with DeclarativeBase, Mapped[] and mapped_column()?

level: juniorimportance: must knowfreq 80%

answer

  1. one base class per model set
  2. __tablename__ names the table
  3. Python type inside Mapped[]
  4. primary_key=True on one column

basics

~10 s

Subclass DeclarativeBase once as Base, then give each model a tablename and Mapped[] annotated attributes, using mapped_column() for keys, lengths and options. SQLAlchemy builds a Table in Base.metadata and maps the class to it.

solid answer

~40 s

Define `class Base(DeclarativeBase): pass` once; it owns a `registry` and a `MetaData`. Each model subclasses it, sets `__tablename__`, and declares columns as annotated attributes: `id: Mapped[int] = mapped_column(primary_key=True)`, `email: Mapped[str] = mapped_column(String(255))`, `bio: Mapped[str | None]`. The type inside `Mapped[]` picks the SQL type and nullability; `mapped_column()` adds what the annotation can't say, such as the primary key, a length, `unique=True` or a `ForeignKey`, and a bare annotation gets an implicit `mapped_column()`. Multi-column constraints and table keywords go in `__table_args__`. When the class body finishes, SQLAlchemy has built `User.__table__` in `Base.metadata` and a mapper, so the class works with `select()` and the `Session`. `declarative_base()` and `Column()` are the 1.x forms this replaces.

code

python · 28 lines
python
import datetime

from sqlalchemy import String, Text, UniqueConstraint, func
from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.schema import CreateTable


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"
    __table_args__ = (
        UniqueConstraint("email", name="uq_user_account_email"),
        {"comment": "login accounts"},
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255))
    display_name: Mapped[str] = mapped_column(String(100))
    bio: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime.datetime] = mapped_column(server_default=func.now())


print(CreateTable(User.__table__).compile(dialect=postgresql.dialect()))
print(User.__table__ is Base.metadata.tables["user_account"])

go deeper

for a junior

Write the base class and one model from memory: DeclarativeBase, tablename, Mapped[int] with primary_key=True, and a String length passed to mapped_column().

for a middle

Explain what the class statement builds, a Table in Base.metadata, a mapper, instrumented attributes and a keyword-only constructor, and how table_args carries constraints and table keywords.

for a senior

Show which mistakes fail at import time, a missing primary key, an annotation outside Mapped[], a table_args without its trailing comma, and how to move 1.x Column() classes over attribute by attribute.

for a principal

Argue for one DeclarativeBase per model set so a single MetaData drives schema tooling, and for putting the shared type map and naming convention on that base rather than in each team's models.

## The base class In SQLAlchemy 2.0 and later, declarative mapping starts with **one base class** per set of models: ```python from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass ``` Subclassing `DeclarativeBase` creates a **registry** (the object that keeps track of mapped classes) and a **`MetaData`** collection (the catalogue of `Table` objects), reachable as `Base.registry` and `Base.metadata`. Every class that inherits from `Base` is mapped into that registry, and its table lands in that `MetaData`. Class-level attributes on the base customise both: - `metadata = MetaData(...)` supplies your own collection, for example one carrying a naming convention; - `type_annotation_map = {...}` changes which SQL type a Python annotation resolves to; - `registry = registry(...)` hands over a registry you built yourself. The older `declarative_base()` function still works, but 2.0 superseded it with the `DeclarativeBase` class, because a subclass is something type checkers can follow. ## Declaring columns A mapped class needs a table name and at least one primary key column: ```python class User(Base): __tablename__ = "user_account" id: Mapped[int] = mapped_column(primary_key=True) email: Mapped[str] = mapped_column(String(255)) bio: Mapped[str | None] ``` - **`__tablename__`** names the table the declarative process generates. - Each attribute annotated with **`Mapped[...]`** becomes a column. The Python type inside `Mapped[]` picks the SQL type (`int` gives `Integer`, `str` gives `String`), and whether `None` is part of it decides `NULL` versus `NOT NULL`. - **`mapped_column()`** adds what the annotation cannot say: `primary_key=True`, a length such as `String(255)`, `unique=True`, `index=True`, a `ForeignKey(...)`, a `server_default=`. It accepts every argument the Core `Column` accepts, plus ORM-only ones such as `deferred=True`. - An annotation with nothing assigned, like `bio: Mapped[str | None]`, gets an **implicit** `mapped_column()`; you write the call only when you have arguments for it. - The column takes the attribute's name unless you pass another name as the first positional argument: `mapped_column("e_mail", String(255))`. The attribute keeps its Python name either way, so queries still say `User.email`. ## Table-level options with `__table_args__` Arguments for the Core `Table` constructor that belong to no single column go in `__table_args__`: | Form | Example | Use | |---|---|---| | tuple | `(UniqueConstraint("email", name="uq_user_account_email"),)` | constraints and indexes over columns | | dict | `{"schema": "accounts"}` | keyword arguments such as `schema` or `comment` | | tuple ending in a dict | `(UniqueConstraint("email"), {"comment": "login accounts"})` | both at once | A single constraint needs its **trailing comma**. `(UniqueConstraint("email"))` is just a parenthesised object, and SQLAlchemy rejects it with `ArgumentError: __table_args__ value must be a tuple, dict, or None`. ## What the class statement produces When the class body finishes, SQLAlchemy has: 1. built a Core `Table` named `user_account` in `Base.metadata`, reachable as `User.__table__`; 2. created a `Mapper` linking `User` to that table, so `select(User)` and `Session.add(User(...))` work; 3. replaced the column attributes with instrumented descriptors, so `User.email` is a SQL expression on the class and a plain value on an instance; 4. given the class a default `__init__()` that accepts only keyword arguments named after mapped attributes. `Base.metadata.create_all(engine)` then emits `CREATE TABLE` for every mapped class. That suits tests and scripts; production schemas are usually managed by a migration tool reading the same `MetaData`. ## Errors at class creation Most mistakes surface when the module is imported, not at the first query: - **No `__tablename__`** (and no `__table__` or table-mapped parent): `InvalidRequestError`, saying the class does not have a `__table__` or `__tablename__` specified. - **No primary key column**: `ArgumentError: Mapper Mapper[...] could not assemble any primary key columns for mapped table ...`. The ORM needs a key to track identity, so it never invents one. - **An annotation outside `Mapped[]`**, such as `name: str = mapped_column(...)`: `MappedAnnotationError`. Use `Mapped[]` for mapped attributes and `ClassVar[]` for class variables; `__allow_unmapped__ = True` exists only to let legacy annotations through. - **A misspelled constructor keyword**, such as `User(mail="...")`: `TypeError: 'mail' is an invalid keyword argument for User`. ## Legacy forms Code written for 1.x declares `id = Column(Integer, primary_key=True)` on a class built from `declarative_base()`. Both still run in 2.1 and may be mixed attribute by attribute, but they carry no typing information. New code uses `DeclarativeBase`, `Mapped[]` and `mapped_column()`. Rewriting an old `Column(String(50))` as `Mapped[str]` also changes its nullability, because `Column()` defaults to nullable and a plain `Mapped[str]` means `NOT NULL`, so check each column before the switch.

  • What happens if a declarative class annotates an attribute as plain `str` instead of `Mapped[str]`?
    Class creation raises `MappedAnnotationError`: declarative expects mapped attributes inside `Mapped[]` and will not guess. Use `Mapped[str]` for a column and `ClassVar[str]` for a class variable that should stay unmapped. `__allow_unmapped__ = True` on the class or base lets legacy non-`Mapped` annotations pass while old code is migrated.
  • Why must a `__table_args__` holding one constraint end with a trailing comma?
    Without the comma, `(UniqueConstraint("email"))` is just a parenthesised constraint, not a tuple. Declarative accepts only a tuple, a dict, or a tuple whose last element is a dict, so it raises `ArgumentError: __table_args__ value must be a tuple, dict, or None` when the class is defined.
  • What does the default constructor of a plain declarative class accept?
    Only keyword arguments named after attributes the class defines, normally its mapped columns: `User(email="[email protected]")` sets the attribute. A positional argument raises `TypeError` from `__init__()`, and an unknown keyword raises `TypeError: 'mail' is an invalid keyword argument for User`. Column defaults are not applied by this constructor; they run at INSERT time.

  • Every column needs an explicit mapped_column() call even when the annotation says everything
  • declarative_base() is still the recommended way to create the base class
  • A plain Mapped[str] column gets a VARCHAR(255) length automatically
  • A mapped class can omit a primary key if it is only ever read
  • __table_args__ is where the column definitions themselves are listed

Assumes SQLAlchemy 2.0 or later (2.1 is current); DeclarativeBase, Mapped[] and mapped_column() arrived in 2.0 and supersede declarative_base() with Column().

open as a page

In SQLAlchemy declarative mapping, how does a Mapped[] annotation decide whether a column is NULL or NOT NULL, and what overrides it?

level: middleimportance: must knowfreq 65%

basics

~10 s

An explicit mapped_column(nullable=...) decides first. Otherwise Mapped[str | None] or Optional[str] gives NULL and plain Mapped[str] gives NOT NULL, as for a Mapped[int] primary key. With no Mapped[] annotation, a non-key column is nullable.

open as a page

In SQLAlchemy 2.1's mapped_column(), how do default, insert_default and server_default differ for columns like created_at and status?

level: middleimportance: should knowfreq 45%

basics

~20 s

default (or insert_default) is a Python-side value SQLAlchemy adds to the INSERT when the attribute is unset; server_default writes a DEFAULT clause into CREATE TABLE so the database fills it for every writer. In 2.1, default and insert_default cannot be combined.

open as a page

In SQLAlchemy declarative mapping, how do type_annotation_map and Annotated aliases let many mapped classes share column types and settings?

level: middleimportance: should knowfreq 30%

basics

~20 s

type_annotation_map on the DeclarativeBase subclass sets which SQL type each Python annotation resolves to, such as str to String(100) or dict to JSON. An Annotated alias either names its own map entry or carries a mapped_column() template copied into each attribute.

open as a page

After a team moves its SQLAlchemy 2.1 models to MappedAsDataclass, what changes about constructors, defaults and the instance __dict__, and what breaks first?

level: seniorimportance: should knowfreq 30%

basics

~20 s

MappedAsDataclass makes each mapped class a real dataclass with generated init(), repr() and eq(). Generated keys need init=False, defaults move into mapped_column(default=...), and in 2.1 those defaults reach the attribute through its descriptor rather than dict.

open as a page