skip to content

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%

answer

  1. annotation versus explicit argument
  2. None inside the Mapped type
  3. a Mapped[int] primary key
  4. nullable= checked first

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.

solid answer

~40 s

SQLAlchemy checks in order. If `mapped_column()` gets `nullable=True` or `nullable=False`, that wins. Otherwise the annotation decides: `Mapped[str | None]` or `Mapped[Optional[str]]` produces `NULL`, and `Mapped[str]` produces `NOT NULL`, which is why a primary key is annotated `Mapped[int]`. A `mapped_column()` with no `Mapped[]` annotation, or a legacy `Column()`, falls back to Core's rule: nullable unless it is a primary key. The annotation and the schema may disagree on purpose: `Mapped[str | None] = mapped_column(nullable=False)` lets the attribute be `None` while an object is built but still creates a `NOT NULL` column. SQLAlchemy does not check this in Python; a `None` reaches the database and comes back as `IntegrityError` at flush. The trap is rewriting old `Column(String(50))` attributes as `Mapped[str]`: they silently become `NOT NULL`.

code

python · 23 lines
python
from typing import Optional

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


class Base(DeclarativeBase):
    pass


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]
    nickname: Mapped[Optional[str]]
    display_name: Mapped[str | None] = mapped_column(String(100), nullable=False)
    legacy_code = mapped_column(String(20))


for column in User.__table__.columns:
    print(f"{column.name:<13} nullable={column.nullable}")

go deeper

for a junior

Remember the everyday rule: Mapped[str] is NOT NULL, Mapped[str | None] is nullable, and a primary key is annotated without None, as Mapped[int].

for a middle

Lay out the full precedence: nullable= first, then None in the annotation, then Core's rule when there is no Mapped[] at all, and explain why annotation and column may disagree on purpose.

for a senior

Point out that nothing is validated in Python, so a None surfaces as IntegrityError at flush, and that converting Column() attributes to Mapped[str] silently tightens columns a migration then tries to alter.

for a principal

Treat nullability as a schema contract: require reviewers to compare generated DDL when models are modernised, and decide per column whether NULL carries meaning before anyone tightens it.

## Two things the annotation decides On a declarative class, `mapped_column()` reads the **`Mapped[]` annotation** on the left of the assignment and derives two settings from it: - **the datatype**: the Python type inside `Mapped[]` (looking through `Optional`) is looked up in a type map, so `str` becomes `String()`, `int` becomes `Integer()`, `datetime.datetime` becomes `DateTime()`. A type passed explicitly, as in `mapped_column(String(255))`, wins over the lookup. - **the nullability**: whether the column is created as `NULL` or `NOT NULL`. **Nullability** means whether the database accepts a missing value (`NULL`) in that column. It matters twice: in the `CREATE TABLE` that `create_all()` or a migration emits, and in what the database will refuse at flush time. ## The precedence order SQLAlchemy settles nullability by checking, in this order: 1. **`nullable=` on `mapped_column()`**. If you pass `True` or `False`, that is the answer, whatever the annotation says. 2. **The annotation**. `Mapped[str | None]` or `Mapped[Optional[str]]` means `NULL`; `Mapped[str]` without `None` means `NOT NULL`. The two spellings of optional are equivalent. 3. **No annotation at all**. A `mapped_column(String(20))` with no `Mapped[]` on the left, or a legacy `Column(String(20))`, follows Core's rule: nullable, unless it is a primary key. A **primary key** is `NOT NULL` when its annotation has no `None`, as in `id: Mapped[int] = mapped_column(primary_key=True)`. Keep `None` out of a primary key's annotation: `Mapped[int | None]` with `primary_key=True` leaves the generated `Column` flagged `nullable=True`. | Declaration | Column | |---|---| | `email: Mapped[str] = mapped_column(String(255))` | `VARCHAR(255) NOT NULL` | | `bio: Mapped[str \| None]` | `VARCHAR`, nullable | | `display_name: Mapped[str \| None] = mapped_column(String(100), nullable=False)` | `VARCHAR(100) NOT NULL` | | `flag: Mapped[str] = mapped_column(nullable=True)` | `VARCHAR`, nullable | | `legacy_code = mapped_column(String(20))` | `VARCHAR(20)`, nullable | ## Deliberate disagreement The annotation describes the Python attribute; `nullable=` describes the column. They may differ on purpose: - `Mapped[str | None] = mapped_column(nullable=False)` lets the attribute be `None` while an object is being assembled, for example before a service fills it in, but the database still requires a value by the time the row is written. - `Mapped[str] = mapped_column(nullable=True)` keeps type checkers treating the attribute as always set while the schema tolerates `NULL`, which can be useful while a column is being backfilled. Both are documented, supported forms, not errors. ## Nothing is checked in Python SQLAlchemy does **not** validate nullability when you assign an attribute or call `Session.add()`. `user.email = None` succeeds, and so does adding the object. The check happens in the database: when the flush sends the `INSERT`, a `NOT NULL` column receiving `NULL` makes the database refuse the row, and SQLAlchemy raises **`IntegrityError`** wrapping the driver's error (on SQLite: `NOT NULL constraint failed: user_account.email`). If you need an earlier, friendlier failure, that is application validation, not something the annotation provides. ## The migration trap The rules bite hardest when old models are modernised: - Legacy `bio = Column(String(50))` is **nullable**, because `Column()` defaults to `nullable=True`. - The natural rewrite, `bio: Mapped[str] = mapped_column(String(50))`, is **`NOT NULL`**, because `Mapped[str]` has no `None` in it. - Nothing warns you. A fresh `create_all()` builds the stricter column, and a migration tool comparing metadata with the live database proposes `ALTER ... SET NOT NULL`, which fails, or worse succeeds on an empty staging database and fails in production where rows hold `NULL`. Before converting, decide per column: if `NULL` is meaningful, write `Mapped[str | None]`; if it is not, backfill the rows first and then tighten the column on purpose. ## Checking what you declared `User.__table__.columns` exposes every generated `Column`, and each has a `.nullable` flag, so a short loop (as in the example) or `print(CreateTable(User.__table__))` shows exactly what the metadata will create. That check costs seconds and catches the silent flip before a migration does. ## Review checklist When reading a model in review, a few patterns deserve a second look: - **`Mapped[str] = mapped_column(nullable=True)`** tells type checkers the attribute is always a string, yet a row holding `NULL` loads as `None`; code that calls string methods on it will fail at runtime, not in the type check. - **A `NOT NULL` column with a `server_default=`** is fine: the database fills the value, so `Mapped[datetime.datetime] = mapped_column(server_default=func.now())` needs no `| None`. - **An optional reference column**, such as an owner that may be absent, needs `| None` in its annotation; leaving it out makes every row require one. - **A column added to a populated table as `NOT NULL`** needs a server default or a backfill, whatever the annotation says, or the schema change itself fails.

  • What breaks when a legacy `bio = Column(String(50))` is rewritten as `bio: Mapped[str] = mapped_column(String(50))`?
    The column flips from nullable to `NOT NULL` in the metadata, with no warning. A fresh `create_all()` builds the stricter column, and a migration tool comparing metadata with the live database proposes making it `NOT NULL`, which fails wherever rows hold `NULL`. Write `Mapped[str | None]` if `NULL` is meaningful, or backfill first and tighten deliberately.
  • Why would you annotate `Mapped[str | None]` but pass `nullable=False`?
    The annotation describes the Python attribute and `nullable=` the column, and `nullable=` wins. This lets an object exist with the attribute still `None` while it is being assembled, so type checkers accept that state, while the database still demands a value when the row is written. A forgotten value then surfaces as `IntegrityError` at flush.
  • Does `mapped_column(String(255))` with an explicit type still read the annotation?
    For the datatype, no: an explicit type passed to `mapped_column()` wins and the Python type inside `Mapped[]` is ignored for it. Nullability is still derived from the annotation unless `nullable=` or `primary_key=True` is given, so `email: Mapped[str] = mapped_column(String(255))` is `VARCHAR(255) NOT NULL`.

  • Mapped[str] only affects type checking; the column itself stays nullable
  • Optional[str] and str | None inside Mapped[] create different columns
  • nullable=False is ignored when the annotation includes None
  • SQLAlchemy raises as soon as None is assigned to a NOT NULL attribute
  • A mapped_column() without a Mapped[] annotation is NOT NULL by default