skip to content

Declarative Mapping

Declarative classes map Python attributes to columns and relationships from type annotations. Interviewers ask how Mapped[] decides nullability and how a relationship finds its foreign key.

on this pageshow

explore

questions

21

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 2.1, how do you map an Author with many Posts using relationship() and back_populates, and what does back_populates do?

level: juniorimportance: must knowfreq 70%

basics

~10 s

Put ForeignKey("authors.id") on Post.author_id, declare Author.posts: Mapped[list["Post"]] and Post.author: Mapped["Author"], each with relationship(back_populates=...) naming the other attribute. back_populates keeps both Python sides in step before any flush.

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

With SQLAlchemy's default relationship() cascade, what happens to a Post's Comments when the post is deleted, and what does "all, delete-orphan" change?

level: middleimportance: must knowfreq 48%

basics

~20 s

The default cascade is "save-update, merge", so deleting a Post sets each Comment's post_id to NULL, failing on a NOT NULL key. "all, delete-orphan" deletes the comments with the post and deletes any comment removed from post.comments.

open as a page

In SQLAlchemy, what does a Mapped[Status] column store for a Python enum.Enum member, and how do you store its value instead?

level: juniorimportance: should knowfreq 32%

basics

~20 s

A Mapped[Status] annotation maps to SQLAlchemy's Enum type, which writes each member's name (PAST_DUE), not its value, and returns Status members on load. To store the values, pass Enum(Status, values_callable=lambda cls: [m.value for m in cls]).

open as a page

In SQLAlchemy, why does a hybrid_property sometimes need a separate expression form, and how do you add one?

level: middleimportance: should knowfreq 30%

basics

~20 s

A hybrid_property runs one getter on instances, in Python, and on the class, to build SQL. Python-only logic such as an if, an f-string or abs() then builds wrong SQL or fails, so @prop.inplace.expression supplies a SQL form such as case().

open as a page

In SQLAlchemy, how do you write a TypeDecorator that stores a Decimal price as integer cents, and what does its cache_ok flag control?

level: middleimportance: should knowfreq 30%

basics

~20 s

Subclass TypeDecorator with impl = Integer, convert Decimal to cents in process_bind_param and back in process_result_value, passing None through. Set cache_ok = True so statements using it are cached; left at None, SQLAlchemy warns and skips caching.

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

In SQLAlchemy 2.1 joined-table inheritance, what does select(Payment) load, and how do selectin_polymorphic() and with_polymorphic() change the SQL?

level: middleimportance: should knowfreq 28%

basics

~20 s

select(Payment) reads only the payment table; subtype columns load per object on first access. selectin_polymorphic() adds one SELECT per subclass table keyed by IN on primary keys; with_polymorphic() LEFT OUTER JOINs the subtables into one statement.

open as a page

In SQLAlchemy 2.1, how do you map Payment subtypes to one table with polymorphic_on and polymorphic_identity, and what does selecting each class emit?

level: middleimportance: should knowfreq 32%

basics

~10 s

Give the base class the table, a discriminator column and polymorphic_on; give each subclass a polymorphic_identity and no tablename. select(Payment) returns subclass instances from one table, and select(CardPayment) adds a WHERE type IN filter.

open as a page

In SQLAlchemy, why does a Post with author_id and editor_id both referencing authors raise AmbiguousForeignKeysError, and how do you fix it?

level: middleimportance: should knowfreq 35%

basics

~20 s

relationship() infers its join from the foreign keys between two tables, and two keys to authors give two candidate paths. Pass foreign_keys= on every relationship involved, Post.author and Post.editor and their reverse collections, naming its own column.

open as a page

In SQLAlchemy, when do you map Post-to-Tag through relationship(secondary=post_tags), and when through an association-object class instead?

level: middleimportance: should knowfreq 42%

basics

~20 s

Use secondary= when post_tags holds only the two foreign keys: SQLAlchemy inserts and deletes link rows as tags are added or removed. Once the link row carries data, map post_tags to its own class and reach tags through it.

open as a page

In SQLAlchemy, a Subscription.total_cents column_property sums its charges with a correlated scalar_subquery(); why does it still show the old total after a new charge is flushed?

level: seniorimportance: should knowfreq 22%

basics

~20 s

A column_property is computed inside the SELECT that loads Subscription and then held like any column value. Flushing a Charge only INSERTs the charge, so total_cents stays stale until it is expired and reloaded, for example by Session.refresh(sub, ['total_cents']).

open as a page

In SQLAlchemy, a JSON column mapped with MutableDict.as_mutable(JSON) saves top-level key edits but drops a nested edit; why, and how do you fix it?

level: seniorimportance: should knowfreq 25%

basics

~20 s

The ORM notices a JSON change only through attribute events. MutableDict emits them for its own keys, but nested dicts stay plain, so sub.billing['renewal']['auto'] = False marks nothing dirty. Assign a new dict, call flag_modified(sub, 'billing'), or coerce nested values.

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

Why does a SQLAlchemy 2.1 query that joins Customer.payments and filters on CardPayment.card_last4 warn of a cartesian product under joined-table inheritance, and how do you fix it?

level: seniorimportance: should knowfreq 18%

basics

~10 s

Customer.payments targets Payment, so the join reaches only the payment table and card_payment enters the FROM clause unjoined. Join Customer.payments.of_type(CardPayment) instead; it renders payment JOIN card_payment inside the join.

open as a page

Deleting a SQLAlchemy Post with 200,000 comments through Session.delete() takes minutes and huge memory; why, and how do passive_deletes and WriteOnlyMapped fix it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

The ORM SELECTs every unloaded comment so it can delete or null each one. Add ondelete="CASCADE" to the foreign key and passive_deletes=True to the relationship, and declare it WriteOnlyMapped so the collection is never loaded.

open as a page

For a growing SQLAlchemy 2.1 Payment hierarchy, how do you choose between single-table and joined-table inheritance, and which loading rules do you set?

level: principalimportance: should knowfreq 20%

basics

~20 s

Single-table keeps base queries on one table and lets base-class filters reach subtype columns, but every subtype column must be nullable. Joined keeps NOT NULL per subtype at the cost of joins and later loads; set polymorphic_load either way.

open as a page

In SQLAlchemy 2.1, how do you map a Money dataclass onto price_cents and price_currency columns with composite(), and how does it behave in queries?

level: middleimportance: nice to knowfreq 18%

basics

~20 s

composite() maps one attribute onto several columns and builds the dataclass from them. Comparisons expand to one condition per column. In SQLAlchemy 2.1, all-NULL columns give Money(None, None) unless return_none_on is set, which Mapped[Money | None] does.

open as a page

In SQLAlchemy 2.1 concrete-table inheritance, what do ConcreteBase and AbstractConcreteBase add, and what does a query against the base class cost?

level: middleimportance: nice to knowfreq 12%

basics

~20 s

With concrete=True alone, select(Payment) reads only Payment's own table. ConcreteBase and AbstractConcreteBase map the base onto a polymorphic_union, so every base query wraps a UNION ALL of all tables, padded with NULLs and a literal type column.

open as a page