In SQLAlchemy 2.1 concrete-table inheritance, what do ConcreteBase and AbstractConcreteBase add, and what does a query against the base class cost?
answer
- every table complete, columns repeated
- plain concrete is not polymorphic
- UNION ALL aliased pjoin
- literal type, NULL padding
- abstract base has no table
basics
~20 sWith 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 sEach 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 linesfrom 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
Recall that concrete inheritance gives each class a full table and that base-class queries are only polymorphic with ConcreteBase or AbstractConcreteBase.
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.
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.
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.