In SQLAlchemy declarative mapping, how do type_annotation_map and Annotated aliases let many mapped classes share column types and settings?
answer
- a dictionary on the base
- Python type to SQL type
- Annotated as a distinct key
- a template copied per attribute
basics
~20 stype_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.
solid answer
~40 sDeclarative resolves `Mapped[str]` through a type map; the built-in one covers scalar and enum types but no containers, so `str` gets a length-less `String()` and `dict[str, Any]` raises `MappedAnnotationError`. Setting `type_annotation_map = {str: String(100), dict[str, Any]: JSON}` on the base fixes both for every model, though an explicit type in `mapped_column()` still wins. An `Annotated` alias such as `str255 = Annotated[str, 255]` is a separate map key, matched by identity, so `Mapped[str255]` can be `String(255)` while plain `str` stays `String(100)`. An alias can also hold a whole column: `intpk = Annotated[int, mapped_column(primary_key=True)]` is copied into each attribute, with inline `mapped_column()` arguments taking precedence. Mixins add shared attributes by copying their columns, `ForeignKey` ones included, into each class; `declared_attr` is left for relationships and per-class directives.
code
python · 38 linesimport datetime
from typing import Annotated, Any
from sqlalchemy import JSON, String, func
from sqlalchemy.dialects import mysql
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.schema import CreateTable
intpk = Annotated[int, mapped_column(primary_key=True)]
str255 = Annotated[str, 255]
created_ts = Annotated[
datetime.datetime, mapped_column(server_default=func.now())
]
class Base(DeclarativeBase):
type_annotation_map = {
str: String(100),
str255: String(255),
dict[str, Any]: JSON,
}
class TimestampMixin:
created_at: Mapped[created_ts]
class User(TimestampMixin, Base):
__tablename__ = "user_account"
id: Mapped[intpk]
email: Mapped[str255] = mapped_column(unique=True)
display_name: Mapped[str]
bio: Mapped[str255 | None]
preferences: Mapped[dict[str, Any]]
print(CreateTable(User.__table__).compile(dialect=mysql.dialect()))go deeper
Know that str maps to String() without a length and that a type map on the base can change that for every model at once.
Explain the lookup: an explicit type first, then the base's type_annotation_map, then the built-in map, with Annotated aliases acting as separate keys or as mapped_column() templates.
Recognise the failures in a real model set: MappedAnnotationError for dict columns, the MySQL VARCHAR length error, an Annotated alias silently falling back to str, dataclass arguments ignored inside a template.
Decide which column conventions a shared base should own, key style, string lengths, JSON and timestamp types, so dozens of classes cannot drift apart, and who may change them.
## The default type map When a declarative attribute has a `Mapped[]` annotation and `mapped_column()` names no type, SQLAlchemy looks the Python type up in a **type map**, a dictionary from Python types to SQL types. The built-in one covers the common scalars: - `str` to `String()`, `int` to `Integer()`, `bool` to `Boolean()`, `bytes` to `LargeBinary()`; - `datetime.datetime`, `datetime.date`, `datetime.time` and `datetime.timedelta` to `DateTime()`, `Date()`, `Time()` and `Interval()`; - `decimal.Decimal` to `Numeric()` and `uuid.UUID` to `Uuid()`; - `enum.Enum` subclasses and `Literal` types to the `Enum` type. Three consequences matter in practice: - A container type such as `dict[str, Any]` is **not** in the map, so `preferences: Mapped[dict[str, Any]]` fails at class creation with `MappedAnnotationError: Could not locate SQLAlchemy Core type ...`. - `str` maps to `String()` with **no length**, which PostgreSQL and SQLite accept as `VARCHAR` but MySQL refuses when the DDL is compiled: `CompileError: VARCHAR requires a length on dialect mysql`. - `datetime.datetime` maps to a `DateTime()` without time zone; if every timestamp should be zone-aware, that too has to be configured, or each column must say so. ## `type_annotation_map` on the base The **`type_annotation_map`** class attribute on your `DeclarativeBase` subclass is passed to the registry and consulted before the built-in map: ```python class Base(DeclarativeBase): type_annotation_map = { str: String(100), dict[str, Any]: JSON, } ``` Now every bare `Mapped[str]` in every model on this base becomes `VARCHAR(100)`, and `Mapped[dict[str, Any]]` becomes `JSON`. Entries may use `with_variant()` to pick a different type per dialect. The map only fills a gap: a type passed explicitly, as in `mapped_column(String(255))`, always wins over it. ## `Annotated` as a map key Python's `typing.Annotated` attaches extra metadata to a type. SQLAlchemy uses an `Annotated` object as a **distinct dictionary key**, matched by identity: ```python str255 = Annotated[str, 255] # in the map: str255: String(255) email: Mapped[str255] ``` The `255` inside the alias is never read; it only makes the alias a different key from `str`. Without a matching map entry, `Mapped[str255]` simply resolves as `str`, so the length silently falls back to whatever `str` maps to. Type checkers still see the attribute as `str`. ## `Annotated` carrying a whole `mapped_column()` An `Annotated` alias may instead hold a **template** `mapped_column()`: ```python intpk = Annotated[int, mapped_column(primary_key=True)] created_ts = Annotated[datetime.datetime, mapped_column(server_default=func.now())] ``` Declarative copies the template into each attribute that uses `Mapped[intpk]` or `Mapped[created_ts]`, so key style, timestamps and constraints are written once. Rules worth knowing: 1. A `mapped_column()` assigned inline is merged with the template, and **its arguments take precedence**: `id: Mapped[intpk] = mapped_column(ForeignKey("user_account.id"))` keeps the primary key and adds the foreign key. 2. Wrapping the alias as `Mapped[created_ts | None]` changes the Python type and, unless the template sets `nullable=` itself, the column's nullability too. 3. Dataclass field arguments such as `init`, `default` and `repr` are not honoured inside an `Annotated` template; SQLAlchemy emits a deprecation warning and ignores them. 4. The template form works for `mapped_column()` only; `relationship()` and similar constructs are not supported inside `Annotated`. ## Mixins A **mixin** is a plain class listed among a model's bases. Columns declared on it, with `Mapped[]`, `mapped_column()` or a legacy `Column`, are **copied** into every mapped class that inherits it, each class getting its own `Column` in its own table. Since 2.0 that includes columns carrying a `ForeignKey`, with no `declared_attr` needed. (On a base that also uses `MappedAsDataclass`, the mixin itself must subclass `MappedAsDataclass` as well.) `declared_attr` is still the tool for things that must be computed per class: a `relationship()` on a mixin, or a directive such as `__tablename__` derived from `cls.__name__` (`declared_attr.directive` marks the latter for type checkers). ## Which tool for which job | Tool | Shares | Typical use | |---|---|---| | `type_annotation_map` | the SQL type for a Python type | `str` lengths, `JSON` for dicts, timezone-aware timestamps | | `Annotated` key in the map | a named variant of a type | `str255`, `Numeric(12, 2)` money columns | | `Annotated` with `mapped_column()` | a whole column definition | primary keys, `created_at` with a server default | | mixin | one or more attributes | timestamp pairs, tenant or owner columns | Combined, they let a model set of dozens of classes declare most columns as one annotated line. Two habits keep that safe: - Put the map and the shared aliases in one module next to the base, so every model imports the same objects; an alias redefined elsewhere is a different key. - Print `CreateTable(Model.__table__)` for each target dialect in a test, so a change to the shared map shows up as a DDL difference rather than as a surprise in a migration.
- What does SQLAlchemy raise when a `Mapped[dict[str, Any]]` attribute has no type map entry?Class creation fails with `MappedAnnotationError`: "Could not locate SQLAlchemy Core type when resolving for Python type indicated by 'dict[str, typing.Any]' inside the Mapped[] annotation ...". Either pass the type inline, `mapped_column(JSON)`, or add `dict[str, Any]: JSON` to the base's `type_annotation_map` so every model resolves it the same way.
- If an `Annotated` alias carries `mapped_column(server_default=func.now())` and the attribute also assigns `mapped_column(server_default=func.current_timestamp())`, which applies?The inline one. Declarative copies the template from the alias and merges the explicitly assigned `mapped_column()` into it, with the explicit arguments taking precedence. Arguments the inline call does not mention are kept from the template.
- When does a column on a mixin still need `declared_attr`?For plain columns it no longer does: since 2.0, `Mapped[]`, `mapped_column()` and `Column` attributes on a mixin are copied into each class, `ForeignKey` ones included. `declared_attr` is still needed when the attribute must be built per class, such as a `relationship()` on a mixin, or a directive like `__tablename__` computed from `cls.__name__`.
- Mapped[dict] resolves to a JSON column without any configuration
- Annotated[str, 255] sets a VARCHAR length even with no type map entry
- type_annotation_map overrides a type passed explicitly to mapped_column()
- A mixin column with a ForeignKey must be wrapped in declared_attr
- A plain Mapped[str] column compiles on every dialect without a length