skip to content

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