In SQLAlchemy, what does a Mapped[Status] column store for a Python enum.Enum member, and how do you store its value instead?
answer
- what the Enum type persists
- the annotation picks the type
- a callable listing persisted strings
- native type versus VARCHAR
basics
~20 sA 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]).
solid answer
~40 sAnnotated Declarative links any `enum.Enum` subclass to `sqlalchemy.Enum`, so `status: Mapped[Status]` becomes `Enum(Status)`. That type persists the member **names** (`'PAST_DUE'`, not `'past_due'`) and turns them back into `Status` members on load. `values_callable=lambda cls: [m.value for m in cls]` switches the stored strings to the values while Python code still sees members. `native_enum` defaults to `True`: PostgreSQL gets a separate named type (named after the class in lower case by default), MySQL an inline `ENUM(...)`, and other backends a `VARCHAR` sized to the longest label. `create_constraint` defaults to `False`, so the `VARCHAR` form carries no CHECK constraint unless you ask for one. A stored string that is not a label raises `LookupError` when the row loads.
code
python · 44 linesimport enum
from sqlalchemy import Enum, create_engine, select, text
from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.schema import CreateTable
class Status(enum.Enum):
ACTIVE = "active"
PAST_DUE = "past_due"
CANCELED = "canceled"
class Base(DeclarativeBase):
pass
class Subscription(Base):
__tablename__ = "subscription"
id: Mapped[int] = mapped_column(primary_key=True)
# Enum(Status) is inferred: the member NAMES are stored
status: Mapped[Status]
# values_callable stores the member VALUES instead
billing_status: Mapped[Status] = mapped_column(
Enum(
Status,
name="billing_status",
values_callable=lambda cls: [m.value for m in cls],
)
)
print(CreateTable(Subscription.__table__).compile(dialect=postgresql.dialect()))
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
session.add(Subscription(status=Status.PAST_DUE, billing_status=Status.PAST_DUE))
with Session(engine) as session:
print(session.execute(text("SELECT status, billing_status FROM subscription")).one())
print(session.scalars(select(Subscription.billing_status)).one())go deeper
Recall that Mapped[Status] stores member names, that values_callable switches to the values, and that loads return Status members.
Explain native_enum and the DDL each backend gets, why create_constraint defaults to False, and when LookupError fires.
Show what breaks in production: a renamed member strands old rows, and a native PostgreSQL type needs a migration to gain a label.
Frame the team convention: native types enforced by the database versus VARCHAR with an optional CHECK, and names versus values as the stored contract.
## How the annotation picks the type In SQLAlchemy's **Annotated Declarative** style a column's SQL type is inferred from its `Mapped[]` annotation. Any subclass of Python's `enum.Enum` is linked to the generic `sqlalchemy.Enum` type, so this mapping: ```python class Status(enum.Enum): ACTIVE = "active" PAST_DUE = "past_due" CANCELED = "canceled" class Subscription(Base): __tablename__ = "subscription" id: Mapped[int] = mapped_column(primary_key=True) status: Mapped[Status] ``` gives `status` the type `Enum(Status)`. A `typing.Literal` made only of strings, such as `Mapped[Literal["active", "past_due"]]`, is linked to `Enum` in the same way, with the strings as labels. ## Names are stored, not values The `Enum` type persists the **name** of each member. The value (`"past_due"` here) is never sent to the database, which is why a member's value can be any Python object, persistable or not. | Mapping | Written for `Status.PAST_DUE` | Loaded back as | |---|---|---| | `Mapped[Status]` | `'PAST_DUE'` | `Status.PAST_DUE` | | `Enum(Status, values_callable=lambda cls: [m.value for m in cls])` | `'past_due'` | `Status.PAST_DUE` | `values_callable` receives the enum class and must return the strings to persist **in the same order** as iterating the class. It changes only what is stored; Python code still reads and writes `Status` members. ## Native and non-native forms The type has two shapes, chosen by `native_enum` (default `True`): | Backend | `native_enum=True` | `native_enum=False` | |---|---|---| | PostgreSQL | a named type created with `CREATE TYPE status AS ENUM (...)` | `VARCHAR(n)` | | MySQL | an inline `ENUM('ACTIVE','PAST_DUE','CANCELED')` column type | `VARCHAR(n)` | | SQLite and others | `VARCHAR(n)` | `VARCHAR(n)` | Details worth knowing: - **The type name** on PostgreSQL defaults to the enum class name in lower case (`status`); pass `name=` to choose another, and give each distinct label set its own name. - **`length`** defaults to the length of the longest stored label; passing a smaller `length` raises `ValueError`. - **`create_constraint`** defaults to `False`, so the `VARCHAR` form has no CHECK constraint. Pass `create_constraint=True` to add one, and name it so schema tools can find it later. - On the non-native form with no constraint, **nothing in the database** limits the column to the labels; only SQLAlchemy does. ## Validation on the way in and out SQLAlchemy checks values in Python as well: 1. **Reading:** every string fetched for the column is looked up among the labels. An unknown one raises `LookupError`, for example `'paused' is not among the defined enum values. Enum name: status. Possible values: ACTIVE, PAST_DUE, CANCELED`. 2. **Writing a member:** a `Status` member is converted to its stored label. 3. **Writing a plain string:** passed through unchecked by default; `validate_strings=True` makes an unknown string raise `LookupError` too. ## Choosing what to store Storing names keeps the database independent of the values, which may be integers or objects. Storing values (through `values_callable`) suits a column that other systems read, or an existing column whose contents are already the lowercase values. Whichever you choose, the stored strings become a contract: - renaming a member when names are stored, or changing a value when values are stored, leaves old rows that fail with `LookupError` on load; - adding a member to a native PostgreSQL type means altering that type in a migration, while the `VARCHAR` form needs only a Python change (and a constraint change if `create_constraint=True`). ## Checking what a mapping really does Three quick checks settle what a mapping stores, without guessing from the class: - **Compile the DDL** for the target backend: `print(CreateTable(Subscription.__table__).compile(dialect=postgresql.dialect()))` shows whether the column is a named type or a `VARCHAR`, and whether a CHECK constraint was added. - **Read the raw rows** with `text("SELECT status FROM subscription")`. Core `text()` returns the stored strings untouched, so you see `'PAST_DUE'` rather than the member. - **Load through the ORM** and compare: `session.scalars(select(Subscription.status))` returns `Status` members, which shows the conversion happens in SQLAlchemy, not in the database. A `typing.Literal` annotation behaves the same way, except that the labels are the literal strings themselves, so names and values cannot diverge. It suits a short, fixed set of states that need no methods; a Literal containing anything other than strings raises an error. ## Common mistakes - Expecting `'past_due'` in the table and finding `'PAST_DUE'`. - Assuming a non-native column is constrained by the database when no CHECK constraint was requested. - Reusing one PostgreSQL type name for two different label sets.
- Why might a team pass native_enum=False for a status column on PostgreSQL?A native PostgreSQL enum is a separate named type, so adding or renaming a label means altering that type in a migration. With `native_enum=False` the column is a plain `VARCHAR` and a new member is a Python change only. The price is that nothing in the database restricts the column to the labels unless `create_constraint=True` adds a CHECK constraint, which then has to change with the labels.
- What happens when a row holds a string that is not one of the labels?SQLAlchemy checks every string it reads and raises `LookupError`, naming the enum and the allowed values. On the way in, a `Status` member is always converted, while a plain string is checked only when the type was built with `validate_strings=True`.
- SQLAlchemy's Enum type stores each member's value, such as 'past_due', by default
- The non-native Enum always gets a CHECK constraint listing the labels
- values_callable makes queries return plain strings instead of enum members
- A row with an unknown label simply loads as the raw string
- native_enum=True gives SQLite a real database enum type