SQLAlchemy Cheatsheet 17 — Inheritance and Polymorphism

Cheatsheet for inheritance patterns. Often unnecessary; useful when subtypes share most fields.

Single-table inheritance (most common)

class Item(Base):
    __tablename__ = "items"
    id: Mapped[int] = mapped_column(primary_key=True)
    type: Mapped[str]
    name: Mapped[str]
    
    __mapper_args__ = {
        "polymorphic_on": "type",
        "polymorphic_identity": "item",
    }

class Book(Item):
    isbn: Mapped[str | None]
    pages: Mapped[int | None]
    
    __mapper_args__ = {"polymorphic_identity": "book"}

class CD(Item):
    artist: Mapped[str | None]
    tracks: Mapped[int | None]
    
    __mapper_args__ = {"polymorphic_identity": "cd"}

One table. type column distinguishes. Subclass columns are nullable.

Querying

# Auto-filtered by type
books = (await s.execute(select(Book))).scalars().all()
# WHERE items.type = 'book'

# All items including subclasses
items = (await s.execute(select(Item))).scalars().all()

# Manual polymorphic load
from sqlalchemy.orm import with_polymorphic
poly = with_polymorphic(Item, [Book, CD])
stmt = select(poly)

Joined-table inheritance

Each subclass in its own table; PK is also FK to parent.

class Employee(Base):
    __tablename__ = "employees"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    type: Mapped[str]
    
    __mapper_args__ = {
        "polymorphic_on": "type",
        "polymorphic_identity": "employee",
    }

class Manager(Employee):
    __tablename__ = "managers"
    id: Mapped[int] = mapped_column(ForeignKey("employees.id"), primary_key=True)
    reports: Mapped[int]
    
    __mapper_args__ = {"polymorphic_identity": "manager"}

class Engineer(Employee):
    __tablename__ = "engineers"
    id: Mapped[int] = mapped_column(ForeignKey("employees.id"), primary_key=True)
    language: Mapped[str]
    
    __mapper_args__ = {"polymorphic_identity": "engineer"}

Each row in managers has corresponding row in employees (PK joined).

Joined-table queries

# Single subclass — joins automatically
managers = (await s.execute(select(Manager))).scalars().all()

# All employees with all subclass columns loaded
poly = with_polymorphic(Employee, [Manager, Engineer])
stmt = select(poly)
# Generates LEFT JOIN on each subclass table

# Filter on subclass column
stmt = select(poly).where(poly.Engineer.language == "Python")

Concrete inheritance (rare)

Each subclass = independent table, no shared base table.

from sqlalchemy.ext.declarative import ConcreteBase

class Employee(ConcreteBase, Base):
    __tablename__ = "employees"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "employee", "concrete": True}

class Engineer(Employee):
    __tablename__ = "engineers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    language: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "engineer", "concrete": True}

Rarely worth it. UNION queries; awkward FKs.

Choosing

Pattern	Storage	Pros	Cons
Single-table	All in one table; nullable subclass cols	Fast queries; simple schema	Nulls; sparse columns
Joined-table	Parent + per-subclass tables	Normalized; no nulls	Joins on every query
Concrete	Independent tables	Subclasses fully separate	Awkward parent queries

For most apps: single-table or no inheritance at all (composition over inheritance).

When to skip inheritance

Subclasses share < 50% of fields.
Subclasses behave very differently.
You’d be happier with a discriminated union of separate models.

Use a parent column + jsonb for variant data, or just separate tables.

Polymorphic loading

poly = with_polymorphic(Employee, [Manager, Engineer], aliased=True)
stmt = select(poly).options(selectinload(poly.Manager.reports))

Loads all subclass columns. Useful when listing mixed types.

Polymorphic identity at create

# Subclass auto-sets polymorphic_identity
m = Manager(name="Alice", reports=5)
# m.type == "manager" automatically

Mapping inheritance to API responses

Pydantic discriminated union maps well:

class ItemBase(BaseModel):
    id: int
    name: str

class BookOut(ItemBase):
    kind: Literal["book"]
    isbn: str | None
    pages: int | None

class CDOut(ItemBase):
    kind: Literal["cd"]
    artist: str | None
    tracks: int | None

ItemOut = Annotated[BookOut | CDOut, Field(discriminator="kind")]

# Adapter
def to_out(item: Item) -> ItemOut:
    if isinstance(item, Book): return BookOut(kind="book", ...)
    if isinstance(item, CD): return CDOut(kind="cd", ...)

Common mistakes

Single-table with many sparse columns → bloat.
Joined-table without eager-loading subclass → 1 query per item.
Forgetting polymorphic_identity → all queries return base class.
Inheritance when composition would do.

Single-table inheritance (most common)#

Querying#

Joined-table inheritance#

Joined-table queries#

Concrete inheritance (rare)#

Choosing#

When to skip inheritance#

Polymorphic loading#

Polymorphic identity at create#

Mapping inheritance to API responses#

Common mistakes#

Read this next#