skip to content

In SQLAlchemy 2.1 concrete-table inheritance, what do ConcreteBase and AbstractConcreteBase add, and what does a query against the base class cost?

level: middleimportance: nice to knowfreq 12%

answer

  1. every table complete, columns repeated
  2. plain concrete is not polymorphic
  3. UNION ALL aliased pjoin
  4. literal type, NULL padding
  5. abstract base has no table

basics

~20 s

With concrete=True alone, select(Payment) reads only Payment's own table. ConcreteBase and AbstractConcreteBase map the base onto a polymorphic_union, so every base query wraps a UNION ALL of all tables, padded with NULLs and a literal type column.

solid answer

~40 s

Each concrete subclass has a complete table and repeats every column. Without help, `select(Payment)` is not polymorphic: it reads only the `payment` table. `ConcreteBase` builds a `polymorphic_union()` at mapper configuration time: a `UNION ALL` aliased `pjoin`, one branch per table, with `CAST(NULL AS ...)` for missing columns and a literal `'card' AS type` discriminator, so base queries return the right subclasses. `AbstractConcreteBase` does the same when the base has no table: its mapping waits for `registry.configure()`, it cannot be instantiated, and `strict_attrs = True` keeps subclass-only attributes off the base. The cost: every base query and filter runs over that union subquery, `polymorphic_load` is rejected, query-time `with_polymorphic()` cannot replace it, and relationships need a foreign key and a separate `relationship()` on every subclass.

code

python · 49 lines
python
from sqlalchemy import String, create_engine, select
from sqlalchemy.ext.declarative import AbstractConcreteBase
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Payment(AbstractConcreteBase, Base):
    strict_attrs = True
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]


class CardPayment(Payment):
    __tablename__ = "card_payment"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]
    card_last4: Mapped[str] = mapped_column(String(4))
    __mapper_args__ = {"polymorphic_identity": "card", "concrete": True}


class BankTransfer(Payment):
    __tablename__ = "bank_transfer"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]
    iban: Mapped[str] = mapped_column(String(34))
    __mapper_args__ = {"polymorphic_identity": "bank_transfer", "concrete": True}


Base.registry.configure()  # maps Payment to the UNION now that subclasses exist

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
print(sorted(Base.metadata.tables))  # no payment table

with Session(engine) as session, session.begin():
    session.add_all(
        [
            CardPayment(id=1, amount_cents=1999, card_last4="4242"),
            BankTransfer(id=1, amount_cents=50000, iban="DE89370400440532013000"),
        ]
    )

stmt = select(Payment).where(Payment.amount_cents > 1000)
print(stmt)
with Session(engine) as session:
    print([type(p).__name__ for p in session.scalars(stmt)])

go deeper

for a junior

Recall that concrete inheritance gives each class a full table and that base-class queries are only polymorphic with ConcreteBase or AbstractConcreteBase.

for a middle

Explain the pjoin UNION ALL, its NULL padding and literal type column, the difference between the two helpers, and why the abstract base must wait for registry configuration.

for a senior

Show you know the limits: no polymorphic_load, no query-time override of the union, per-subclass foreign keys and relationships, and every base filter running over a subquery.

for a principal

Argue when independent subtype tables justify the UNION cost and when the same requirement is better met by joined inheritance or by unrelated mappings.

## Concrete-table inheritance in SQLAlchemy In **concrete-table inheritance** each class maps to its **own complete table**: `card_payment` has `id`, `amount_cents` and `card_last4`; `bank_transfer` has `id`, `amount_cents` and `iban`. Nothing is shared at the database level, there is no foreign key to a parent row and no stored discriminator. You mark each subclass with `"concrete": True` in `__mapper_args__`, and you must **declare every column again** on each subclass: Declarative does not copy the base class's columns into the subclass tables. The SQLAlchemy documentation is blunt about this form: it is more complicated and more limited than joined or single-table inheritance, especially for relationships, eager loading and polymorphic loading, and it suits hierarchies where polymorphic loading is **not** needed. ## concrete=True on its own: no polymorphic loading With only `"concrete": True`, the classes are related in Python but not in queries. `select(Payment)` reads **only the `payment` table** and returns plain `Payment` objects; the `card_payment` and `bank_transfer` tables are not touched. Each subclass query reads its own table. That is often exactly what a team wants, and then plain unrelated mappings do the same job with less machinery. ## ConcreteBase: a base table plus a UNION To make `select(Payment)` return card payments and bank transfers, the base mapper needs a **polymorphic selectable** that can see every table. The `polymorphic_union()` function builds it: a `UNION ALL` of one `SELECT` per table, aliased `pjoin`, in which: - each branch pads the columns it lacks with `CAST(NULL AS ...)` so all branches line up; - each branch adds a **literal** discriminator such as `'card' AS type`, so the identity comes from which table the row was in, not from stored data; - the column is named `type` unless the base sets `_concrete_discriminator_name`. Because Declarative creates each mapper as its class is defined, the base cannot be handed a UNION of tables that do not exist yet. `ConcreteBase` (from `sqlalchemy.ext.declarative`) solves that by building the union at mapper configuration time, once all subclasses are known. Use it when the base class has **its own table** and real rows. ## AbstractConcreteBase: no base table at all `AbstractConcreteBase` is the variant for a base that is **not** stored. There is no `payment` table; the base is mapped directly onto the union of the subclass tables. Things to know: 1. The base's mapping is **deferred** until the registry configures: call `Base.registry.configure()` (or `configure_mappers()`) after the last subclass is defined. Selecting from it earlier raises `UnmappedClassError`, whose message says the mapping is pending until all subclasses are defined. 2. The base is mapped as **polymorphic-abstract**: `Payment(...)` raises `InvalidRequestError` ("Can't instantiate class ... mapper is marked polymorphic_abstract=True"). Only subclasses are persisted. 3. With `strict_attrs = True` (the default is `False`, kept so older mappings behave as before), the base maps only the attributes declared on it, so `CardPayment` does not grow a meaningless `iban` attribute. ## What the union costs | Concern | Concrete with a helper | |---|---| | `select(Payment)` | one `UNION ALL` subquery over every table, filters applied outside it | | Adding a subtype | adds a branch to every base-class query | | `polymorphic_load` | not supported: setting it raises `ArgumentError` | | `with_polymorphic()` at query time | cannot override the configured union | | Identity | each concrete class keys its own identities, so `CardPayment` 1 and `BankTransfer` 1 coexist | Every filter on the base class lands on the outer query, as in `WHERE pjoin.amount_cents > ...`, so the database must be able to push it into each branch of the union for indexes on the individual tables to help; how well it does that is up to the database's planner, not SQLAlchemy. Relationships are the other cost. A `Customer` collection of `Payment` objects needs a `customer_id` foreign key in **every** subclass table, and a `relationship()` back to `Customer` declared on the base does not work on subclass instances; each concrete class declares its own, linked with `back_populates`. ## When concrete is the right call - Subtypes are queried on their own almost all the time, and a cross-type listing is rare or can live in a reporting query. - Subtype tables must be fully independent, for example with different retention or storage settings. - If cross-type queries, eager loading or relationships to the base are routine, joined or single-table inheritance will be simpler and faster.

  • A CardPayment and a BankTransfer both have id 1. Does the Session confuse them?
    No. A concrete mapper keys identities by its own class, so the identity map holds `(CardPayment, (1,))` and `(BankTransfer, (1,))` side by side, and a base-class query returns both. Joined and single-table subclasses, by contrast, share the base class's identity because the base table's primary key identifies every row.
  • Why does select(Payment) raise UnmappedClassError right after the classes are defined?
    With `AbstractConcreteBase`, the base is not mapped until mapper configuration runs, because the union needs every subclass table. Call `Base.registry.configure()` or `configure_mappers()` once all subclasses are imported; the error message itself says the mapping is pending until then.

  • Setting concrete=True on subclasses is enough to make select(Payment) return every subtype.
  • Concrete subclasses inherit the base class's columns into their own tables.
  • The concrete discriminator is a stored type column in every table.
  • polymorphic_load="selectin" makes concrete base queries cheaper.
  • A relationship declared on a concrete base class works on every subclass.