Inheritance Patterns#
Advanced Alchemy’s declarative base classes and repositories support all three SQLAlchemy inheritance strategies:
Single Table: Subclasses share one table distinguished by a discriminator column (
polymorphic_on). Suitable when subclasses add only a few nullable columns.Joined Table: Shared columns reside in a parent table and subtype-specific columns reside in child tables joined by a foreign key.
Concrete Table: An abstract Python base class (
__abstract__ = True) shares column definitions while each subclass maps to an independent table.
Comparing the Layouts#
from typing import Optional
from sqlalchemy.orm import Mapped
from advanced_alchemy.base import UUIDAuditBase
class Employee(UUIDAuditBase):
__tablename__ = "employee"
name: Mapped[str]
type: Mapped[str]
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "employee",
}
class Manager(Employee):
__mapper_args__ = {"polymorphic_identity": "manager"}
department: Mapped[Optional[str]]
class Engineer(Employee):
__mapper_args__ = {"polymorphic_identity": "engineer"}
primary_language: Mapped[Optional[str]]
from sqlalchemy.orm import Mapped
from advanced_alchemy.base import UUIDAuditBase
class Employee(UUIDAuditBase):
__tablename__ = "employee"
name: Mapped[str]
type: Mapped[str]
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "employee",
}
class Manager(Employee):
__mapper_args__ = {"polymorphic_identity": "manager"}
department: Mapped[str | None]
class Engineer(Employee):
__mapper_args__ = {"polymorphic_identity": "engineer"}
primary_language: Mapped[str | None]
from uuid import UUID
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import UUIDAuditBase
class Person(UUIDAuditBase):
__tablename__ = "person"
name: Mapped[str]
type: Mapped[str]
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "person",
}
class Staff(Person):
__tablename__ = "staff"
id: Mapped[UUID] = mapped_column(ForeignKey("person.id"), primary_key=True)
badge_number: Mapped[str]
__mapper_args__ = {"polymorphic_identity": "staff"}
from sqlalchemy.orm import Mapped
from advanced_alchemy.base import UUIDAuditBase
class Vehicle(UUIDAuditBase):
__abstract__ = True
name: Mapped[str]
class Car(Vehicle):
__tablename__ = "car"
engine_type: Mapped[str]
class Bicycle(Vehicle):
__tablename__ = "bicycle"
has_basket: Mapped[bool]
Querying Inherited Models#
Bind a repository to the parent model to query across all polymorphic subtypes, or bind a repository to a specific subclass to query only that subtype:
from sqlalchemy.ext.asyncio import AsyncSession
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
class EmployeeRepository(SQLAlchemyAsyncRepository[Employee]):
model_type = Employee
class ManagerRepository(SQLAlchemyAsyncRepository[Manager]):
model_type = Manager
async def list_staff(db_session: AsyncSession) -> tuple[list[Employee], list[Manager]]:
all_employees = await EmployeeRepository(session=db_session).get_many()
only_managers = await ManagerRepository(session=db_session).get_many()
return all_employees, only_managers