skip to content

Hybrids & Custom Types

hybrid_property, column_property and TypeDecorator add computed attributes and custom column types that work in Python and in SQL. Interviewers ask why a hybrid needs an expression form.

on this pageshow

explore

questions

6

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%

answer

  1. what the Enum type persists
  2. the annotation picks the type
  3. a callable listing persisted strings
  4. native type versus VARCHAR

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

solid answer

~40 s

Annotated 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 lines
python
import 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

for a junior

Recall that Mapped[Status] stores member names, that values_callable switches to the values, and that loads return Status members.

for a middle

Explain native_enum and the DDL each backend gets, why create_constraint defaults to False, and when LookupError fires.

for a senior

Show what breaks in production: a renamed member strands old rows, and a native PostgreSQL type needs a migration to gain a label.

for a principal

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
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, 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

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