skip to content

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%

answer

  1. one table, one type column
  2. only the base names a table
  3. subtype columns must allow NULL
  4. subclass select adds type IN
  5. base select skips subtype columns

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.

solid answer

~40 s

Only `Payment` declares `__tablename__`; it has a `type` column and `__mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "payment"}`. `CardPayment`, `BankTransfer` and `WalletPayment` omit `__tablename__` and set their own `polymorphic_identity`; any `mapped_column()` they declare is added to the shared `payment` table, so it must be nullable (`Mapped[str | None]` or `nullable=True`) or inserting a sibling fails the NOT NULL constraint. SQLAlchemy sets `type` to the identity when the object is constructed, so you never assign it. `select(Payment)` reads the one table and builds whichever subclass each row's `type` names; `select(CardPayment)` adds `WHERE payment.type IN (...)` bound to `'card'`. The catch: a base-class select fetches only base columns, so `card_last4` costs a second SELECT on first access unless the subclass sets `polymorphic_load="inline"` or the query uses `with_polymorphic()`.

code

python · 48 lines
python
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Payment(Base):
    __tablename__ = "payment"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]
    type: Mapped[str] = mapped_column(String(20))
    __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "payment"}


class CardPayment(Payment):
    card_last4: Mapped[str | None] = mapped_column(String(4))
    __mapper_args__ = {"polymorphic_identity": "card"}


class BankTransfer(Payment):
    iban: Mapped[str | None] = mapped_column(String(34))
    __mapper_args__ = {"polymorphic_identity": "bank_transfer"}


class WalletPayment(Payment):
    wallet_ref: Mapped[str | None] = mapped_column(String(64))
    __mapper_args__ = {"polymorphic_identity": "wallet", "polymorphic_load": "inline"}


engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    session.add_all(
        [
            CardPayment(amount_cents=1999, card_last4="4242"),
            BankTransfer(amount_cents=50000, iban="DE89370400440532013000"),
            WalletPayment(amount_cents=750, wallet_ref="w-81"),
        ]
    )

with Session(engine) as session:
    payments = session.scalars(select(Payment).order_by(Payment.id)).all()
    print([type(p).__name__ for p in payments])
    print(payments[0].card_last4)  # second SELECT: not in the base query
    cards = session.scalars(select(CardPayment)).all()  # WHERE payment.type IN (...)

go deeper

for a junior

Recall the shape: one tablename on the base, polymorphic_on naming the type column, a polymorphic_identity per subclass, and the ORM filling the type column for you.

for a middle

Explain where subclass columns go, why they must be nullable, what WHERE criterion a subclass select adds, and why a base-class select still issues a second SELECT for subtype columns.

for a senior

Show you have caught the hidden per-row SELECTs in a listing and fixed them with polymorphic_load inline or with_polymorphic, and that you enforce subtype-required columns another way.

for a principal

Frame single-table as a trade: simple one-table queries against columns the database cannot require, and set team defaults for inline loading and identity naming.

## What single-table inheritance means in SQLAlchemy **Single-table inheritance** maps a whole Python class hierarchy onto **one database table**. Every row carries a **discriminator column** (here `type`) whose value names the class the row belongs to. SQLAlchemy reads that value when it builds objects, and writes it when it inserts them, so a query against the base class can hand back a mix of subclass instances from a single `SELECT`. The two mapper arguments that drive this are: - `polymorphic_on`, set once on the base class, names the discriminator: a string attribute name such as `"type"`, or a column expression. - `polymorphic_identity`, set on every class that can be instantiated, is the value stored in that column for its rows. It should be unique across the hierarchy. ## Declaring the Payment hierarchy Only the base class declares `__tablename__`. Subclasses leave it out, which is what tells Declarative that they share the parent's table: ```python class Payment(Base): __tablename__ = "payment" id: Mapped[int] = mapped_column(primary_key=True) amount_cents: Mapped[int] type: Mapped[str] = mapped_column(String(20)) __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "payment"} class CardPayment(Payment): card_last4: Mapped[str | None] = mapped_column(String(4)) __mapper_args__ = {"polymorphic_identity": "card"} ``` You never assign `type` yourself: constructing a `CardPayment` sets it to `'card'`, and the INSERT writes it like any other column. ## Subclass columns land on the shared table A `mapped_column()` declared on a subclass that has no table of its own is **moved up** to the base table. Two consequences follow: - **Nullability.** In SQLAlchemy 2.x a `Mapped[str]` annotation produces `NOT NULL`. On a single-table subclass that is wrong, because every *other* subtype's rows leave the column empty; inserting a `CardPayment` would then fail with an `IntegrityError` on the `iban` column of a sibling. Annotate subtype columns as `Mapped[str | None]` or pass `nullable=True`. - **Name clashes.** If `BankTransfer` and `WalletPayment` both declare `iban`, the second class fails at definition time with `ArgumentError: Column 'iban' on class WalletPayment conflicts with existing column 'payment.iban'`. Passing `use_existing_column=True` to `mapped_column()` on both makes them share the one column. ## What each select() emits | Statement | SQL shape | Objects returned | |---|---|---| | `select(Payment)` | `SELECT` of the base columns `FROM payment` | `CardPayment`, `BankTransfer`, `WalletPayment` (and `Payment`) by row | | `select(CardPayment)` | same table, plus `WHERE payment.type IN (...)` bound to `'card'` | only `CardPayment` and its subclasses | | `select(Payment).where(CardPayment.card_last4 == "4242")` | same table, the column filter plus the `type IN` criterion | only matching card rows | The `IN` list holds the identity of the queried class and of every subclass below it, so a deeper subtype is included automatically. The same criterion is added wherever the subclass appears: in a join along a relationship narrowed with `of_type(CardPayment)`, and, since 2.1, inside the `EXISTS` subquery that `any()` or `has()` renders. `with_polymorphic()` changed in 2.1 for single-table mappings. `with_polymorphic(Payment, [CardPayment])` now loads **every** row of the hierarchy below `Payment` and adds the listed subclasses' columns, matching what a joined mapping does; earlier releases turned the listed classes into a `type IN` filter. To restrict rows to card payments, select `CardPayment` itself. ## Subclass columns are not in a base-class SELECT The surprising part: `select(Payment)` lists **only the base class's columns**, even though `card_last4` sits in the same table. Reading `card_last4` on a `CardPayment` loaded that way emits a second `SELECT ... WHERE payment.id = ? AND payment.type IN (?)` for that one row. Over a page of results that is one extra statement per row touched. Two remedies: 1. Set `"polymorphic_load": "inline"` in a subclass's `__mapper_args__`, so its columns are always added to base-class SELECTs. The documentation recommends this for single-table subclasses whose columns are commonly read, because the extra columns cost almost nothing. 2. Select from `with_polymorphic(Payment, "*")`, which adds every subclass column for that one query. ## Habits that keep the mapping honest - Give intermediate classes that are never instantiated `"polymorphic_abstract": True` instead of leaving out an identity; a mapped subclass with neither gets an `SAWarning` saying it does not indicate a `polymorphic_identity`. - Keep identity values stable: they are stored data, so renaming a Python class is safe and renaming its identity is a data change. - Configure exactly one `polymorphic_on` per hierarchy; SQLAlchemy does not support a second, "cascading" discriminator.

  • What happens when two single-table subclasses declare a column with the same name?
    Declarative raises `ArgumentError` when the second class is defined: the column conflicts with the one already moved onto the base table. Pass `use_existing_column=True` to `mapped_column()` on both subclasses; each then reuses the column if it is already present and creates it otherwise, so both map the same physical column.
  • How do you add a grouping class, such as a card-like family, that is never instantiated itself?
    Map it with `__mapper_args__ = {"polymorphic_abstract": True}` and no identity. `select()` of that class filters `type IN` the identities of its instantiable descendants, and it can be a `relationship()` target, but constructing it raises `InvalidRequestError`. Without the flag, SQLAlchemy emits an `SAWarning` that the class has no `polymorphic_identity`.
  • When is polymorphic_load="inline" the wrong choice on a single-table subclass?
    When the subclass carries large columns that base-class listings rarely read, such as a big JSON or text blob. Inline adds them to every `select(Payment)`; leaving the default keeps listings lean and loads the blob only for the rows that touch it, or you can pull it per query with `with_polymorphic()`.

  • Each single-table subclass needs its own __tablename__ pointing at the shared table.
  • A subtype column typed Mapped[str] is fine because only that subtype's rows use it.
  • You must set the type column yourself when constructing a CardPayment.
  • select(Payment) always loads every subtype column because they live in the same table.
  • select(CardPayment) reads every payment row and drops the others in Python.

Assumes SQLAlchemy 2.1; since 2.1, with_polymorphic() over a single-table hierarchy loads every class below the base it names, not only the listed subclasses.