Composing Mapped Hierarchies with Mixins

    Tip

    In addition to mixin classes, common column options may also be shared among many classes using PEP 593 types; see and Mapping Whole Column Declarations to Python Types for background on these SQLAlchemy 2.0 features.

    An example of some commonly mixed-in idioms is below:

    The above example illustrates a class MyModel which includes two mixins CommonMixin and HasLogRecord in its bases, as well as a supplementary class LogRecord which also includes CommonMixin, demonstrating a variety of constructs that are supported on mixins and base classes, including:

    • columns declared using , Mapped or are copied from mixins or base classes onto the target class to be mapped; above this is illustrated via the column attributes CommonMixin.id and HasLogRecord.log_record_id.

    • Declarative directives such as __table_args__ and __mapper_args__ can be assigned to a mixin or base class, where they will take effect automatically for any classes which inherit from the mixin or base. The above example illustrates this using the __table_args__ and __mapper_args__ attributes.

    • All Declarative directives, including all of __tablename__, __table__, __table_args__ and __mapper_args__, may be implemented using user-defined class methods, which are decorated with the declared_attr decorator (specifically the sub-member, more on that in a moment). Above, this is illustrated using a def __tablename__(cls) classmethod that generates a Table name dynamically; when applied to the MyModel class, the table name will be generated as "mymodel", and when applied to the LogRecord class, the table name will be generated as "logrecord".

    • Other ORM properties such as can be generated on the target class to be mapped using user-defined class methods also decorated with the declared_attr decorator. Above, this is illustrated by generating a many-to-one to a mapped object called LogRecord.

    The features above may all be demonstrated using a select() example:

    1. >>> from sqlalchemy import select
    2. >>> print(select(MyModel).join(MyModel.log_record))
    3. SELECT mymodel.name, mymodel.id, mymodel.log_record_id
    4. FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id

    Tip

    The examples of will attempt to illustrate the correct PEP 484 annotations for each method example. The use of annotations with functions are completely optional, and are not consumed by Declarative; however, these annotations are required in order to pass Mypy --strict type checking.

    Additionally, the declared_attr.directive sub-member illustrated above is optional as well, and is only significant for typing tools, as it adjusts for the expected return type when creating methods to override Declarative directives such as __tablename__, __mapper_args__ and __table_args__.

    New in version 2.0: As part of PEP 484 typing support for the SQLAlchemy ORM, added the to declared_attr to distinguish between attributes and Declarative configurational attributes

    There’s no fixed convention for the order of mixins and base classes. Normal Python method resolution rules apply, and the above example would work just as well with:

    1. class MyModel(Base, HasLogRecord, CommonMixin):
    2.     name: Mapped[str] = mapped_column()

    This works because Base here doesn’t define any of the variables that CommonMixin or HasLogRecord defines, i.e. __tablename__, __table_args__, id, etc. If the Base did define an attribute of the same name, the class placed first in the inherits list would determine which attribute is used on the newly defined class.

    Tip

    While the above example is using Annotated Declarative Table form based on the annotation class, mixin classes also work perfectly well with non-annotated and legacy Declarative forms, such as when using Column directly instead of .

    In addition to using a pure mixin, most of the techniques in this section can also be applied to the base class directly, for patterns that should apply to all classes derived from a particular base. The example below illustrates some of the the previous section’s example in terms of the Base class:

    1. from sqlalchemy import ForeignKey
    2. from sqlalchemy.orm import declared_attr
    3. from sqlalchemy.orm import DeclarativeBase
    4. from sqlalchemy.orm import Mapped
    5. from sqlalchemy.orm import mapped_column
    6. from sqlalchemy.orm import relationship
    9. class Base(DeclarativeBase):
    10.     """define a series of common elements that may be applied to mapped
    11.     classes using this class as a base class."""
    13.     @declared_attr.directive
    14.     def __tablename__(cls) -> str:
    15.         return cls.__name__.lower()
    17.     __table_args__ = {"mysql_engine": "InnoDB"}
    18.     __mapper_args__ = {"eager_defaults": True}
    20.     id: Mapped[int] = mapped_column(primary_key=True)
    23. class HasLogRecord:
    24.     """mark classes that have a many-to-one relationship to the
    25.     ``LogRecord`` class."""
    27.     log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))
    29.     @declared_attr
    30.     def log_record(self) -> Mapped["LogRecord"]:
    31.         return relationship("LogRecord")
    34. class LogRecord(Base):
    35.     log_info: Mapped[str]
    38. class MyModel(HasLogRecord, Base):
    39.     name: Mapped[str]

    Where above, MyModel as well as LogRecord, in deriving from Base, will both have their table name derived from their class name, a primary key column named id, as well as the above table and mapper arguments defined by Base.__table_args__ and Base.__mapper_args__.

    When using legacy or registry.generate_base(), the parameter may be used as follows to generate an equivalent effect, as illustrated in the non-annotated example below:

    1. # legacy declarative_base() use
    3. from sqlalchemy import Integer, String
    4. from sqlalchemy import ForeignKey
    5. from sqlalchemy.orm import declared_attr
    6. from sqlalchemy.orm import declarative_base
    7. from sqlalchemy.orm import mapped_column
    8. from sqlalchemy.orm import relationship
    11. class Base:
    12.     """define a series of common elements that may be applied to mapped
    13.     classes using this class as a base class."""
    15.     @declared_attr.directive
    16.     def __tablename__(cls):
    17.         return cls.__name__.lower()
    19.     __table_args__ = {"mysql_engine": "InnoDB"}
    20.     __mapper_args__ = {"eager_defaults": True}
    22.     id = mapped_column(Integer, primary_key=True)
    25. Base = declarative_base(cls=Base)
    28. class HasLogRecord:
    29.     """mark classes that have a many-to-one relationship to the
    30.     ``LogRecord`` class."""
    32.     log_record_id = mapped_column(ForeignKey("logrecord.id"))
    34.     @declared_attr
    35.     def log_record(self):
    36.         return relationship("LogRecord")
    39. class LogRecord(Base):
    40.     log_info = mapped_column(String)
    43. class MyModel(HasLogRecord, Base):
    44.     name = mapped_column(String)

    Mixing in Columns

    Columns can be indicated in mixins assuming the style of configuration is in use (as opposed to imperative table configuration), so that columns declared on the mixin can then be copied to be part of the that the Declarative process generates. All three of the mapped_column(), , and Column constructs may be declared inline in a declarative mixin:

    1. class TimestampMixin:
    2.     created_at: Mapped[datetime] = mapped_column(default=func.now())
    3.     updated_at: Mapped[datetime]
    6. class MyModel(TimestampMixin, Base):
    7.     __tablename__ = "test"
    9.     id: Mapped[int] = mapped_column(primary_key=True)
    10.     name: Mapped[str]

    Where above, all declarative classes that include TimestampMixin in their class bases will automatically include a column created_at that applies a timestamp to all row insertions, as well as an updated_at column, which does not include a default for the purposes of the example (if it did, we would use the parameter which is accepted by mapped_column()). These column constructs are always copied from the originating mixin or base class, so that the same mixin/base class may be applied to any number of target classes which will each have their own column constructs.

    All Declarative column forms are supported by mixins, including:

    • Annotated attributes - with or without present:

      1. class TimestampMixin:
      2.     created_at: Mapped[datetime] = mapped_column(default=func.now())
      3.     updated_at: Mapped[datetime]

    • mapped_column - with or without Mapped present:

    • Column - legacy Declarative form:

      2.     updated_at = Column(DateTime)

    In each of the above forms, Declarative handles the column-based attributes on the mixin class by creating a copy of the construct, which is then applied to the target class.

    Changed in version 2.0: The declarative API can now accommodate objects as well as mapped_column() constructs of any form when using mixins without the need to use . Previous limitations which prevented columns with ForeignKey elements from being used directly in mixins have been removed.

    Relationships created by relationship() are provided with declarative mixin classes exclusively using the approach, eliminating any ambiguity which could arise when copying a relationship and its possibly column-bound contents. Below is an example which combines a foreign key column and a relationship so that two classes Foo and Bar can both be configured to reference a common target class via many-to-one:

    1. from sqlalchemy import ForeignKey
    2. from sqlalchemy.orm import DeclarativeBase
    3. from sqlalchemy.orm import declared_attr
    4. from sqlalchemy.orm import Mapped
    5. from sqlalchemy.orm import mapped_column
    6. from sqlalchemy.orm import relationship
    9. class Base(DeclarativeBase):
    10.     pass
    13. class RefTargetMixin:
    14.     target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))
    16.     @declared_attr
    17.     def target(cls) -> Mapped["Target"]:
    18.         return relationship("Target")
    21. class Foo(RefTargetMixin, Base):
    22.     __tablename__ = "foo"
    23.     id: Mapped[int] = mapped_column(primary_key=True)
    26. class Bar(RefTargetMixin, Base):
    27.     __tablename__ = "bar"
    28.     id: Mapped[int] = mapped_column(primary_key=True)
    31. class Target(Base):
    32.     __tablename__ = "target"
    33.     id: Mapped[int] = mapped_column(primary_key=True)

    With the above mapping, each of Foo and Bar contain a relationship to Target accessed along the .target attribute:

    1. >>> from sqlalchemy import select
    2. >>> print(select(Foo).join(Foo.target))
    3. SELECT foo.id, foo.target_id
    4. FROM foo JOIN target ON target.id = foo.target_id
    5. >>> print(select(Bar).join(Bar.target))
    6. SELECT bar.id, bar.target_id
    7. FROM bar JOIN target ON target.id = bar.target_id

    Special arguments such as relationship.primaryjoin may also be used within mixed-in classmethods, which often need to refer to the class that’s being mapped. For schemes that need to refer to locally mapped columns, in ordinary cases these columns are made available by Declarative as attributes on the mapped class which is passed as the cls argument to the decorated classmethod. Using this feature, we could for example rewrite the RefTargetMixin.target method using an explicit primaryjoin which refers to pending mapped columns on both Target and cls:

    1. class Target(Base):
    2.     __tablename__ = "target"
    3.     id: Mapped[int] = mapped_column(primary_key=True)
    6. class RefTargetMixin:
    7.     target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))
    9.     @declared_attr
    10.     def target(cls) -> Mapped["Target"]:
    11.         # illustrates explicit 'primaryjoin' argument
    12.         return relationship("Target", primaryjoin=Target.id == cls.target_id)

    Mixing in column_property() and other classes

    Like relationship(), other subclasses such as column_property() also need to have class-local copies generated when used by mixins, so are also declared within functions that are decorated by . Within the function, other ordinary mapped columns that were declared with mapped_column(), , or Column will be made available from the cls argument so that they may be used to compose new attributes, as in the example below which adds two columns together:

    1. from sqlalchemy.orm import column_property
    2. from sqlalchemy.orm import DeclarativeBase
    3. from sqlalchemy.orm import declared_attr
    4. from sqlalchemy.orm import Mapped
    5. from sqlalchemy.orm import mapped_column
    8. class Base(DeclarativeBase):
    9.     pass
    12. class SomethingMixin:
    13.     x: Mapped[int]
    14.     y: Mapped[int]
    16.     @declared_attr
    17.     def x_plus_y(cls) -> Mapped[int]:
    18.         return column_property(cls.x + cls.y)
    21. class Something(SomethingMixin, Base):
    22.     __tablename__ = "something"
    24.     id: Mapped[int] = mapped_column(primary_key=True)

    Above, we may make use of Something.x_plus_y in a statement where it produces the full expression:

    1. >>> from sqlalchemy import select
    2. >>> print(select(Something.x_plus_y))
    3. SELECT something.x + something.y AS anon_1
    4. FROM something

    Tip

    New in version 2.0: - can accommodate a function decorated with @classmethod to help with PEP 484 integration where needed.

    When dealing with mapper inheritance patterns as documented at Mapping Class Inheritance Hierarchies, some additional capabilities are present when using either with mixin classes, or when augmenting both mapped and un-mapped superclasses in a class hierarchy.

    When defining functions decorated by declared_attr on mixins or base classes to be interpreted by subclasses in a mapped inheritance hierarchy, there is an important distinction made between functions that generate the special names used by Declarative such as __tablename__, __mapper_args__ vs. those that may generate ordinary mapped attributes such as and relationship(). Functions that define Declarative directives are invoked for each subclass in a hierarchy, whereas functions that generate mapped attributes are invoked only for the first mapped superclass in a hierarchy.

    The rationale for this difference in behavior is based on the fact that mapped properties are already inheritable by classes, such as a particular column on a superclass’ mapped table should not be duplicated to that of a subclass as well, whereas elements that are specific to a particular class or its mapped table are not inheritable, such as the name of the table that is locally mapped.

    The difference in behavior between these two use cases is demonstrated in the following two sections.

    A common recipe with mixins is to create a def __tablename__(cls) function that generates a name for the mapped Table dynamically.

    This recipe can be used to generate table names for an inheriting mapper hierarchy as in the example below which creates a mixin that gives every class a simple table name based on class name. The recipe is illustrated below where a table name is generated for the Person mapped class and the Engineer subclass of Person, but not for the Manager subclass of Person:

    1. from typing import Optional
    3. from sqlalchemy import ForeignKey
    4. from sqlalchemy.orm import DeclarativeBase
    5. from sqlalchemy.orm import declared_attr
    6. from sqlalchemy.orm import Mapped
    7. from sqlalchemy.orm import mapped_column
    10. class Base(DeclarativeBase):
    11.     pass
    14. class Tablename:
    15.     @declared_attr.directive
    16.     def __tablename__(cls) -> Optional[str]:
    17.         return cls.__name__.lower()
    20. class Person(Tablename, Base):
    21.     id: Mapped[int] = mapped_column(primary_key=True)
    22.     discriminator: Mapped[str]
    23.     __mapper_args__ = {"polymorphic_on": "discriminator"}
    26. class Engineer(Person):
    27.     id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)
    29.     primary_language: Mapped[str]
    31.     __mapper_args__ = {"polymorphic_identity": "engineer"}
    34. class Manager(Person):
    35.     @declared_attr.directive
    36.     def __tablename__(cls) -> Optional[str]:
    38.         return None
    40.     __mapper_args__ = {"polymorphic_identity": "manager"}

    In the above example, both the base class as well as the Engineer class, being subclasses of the Tablename mixin class which generates new table names, will have a generated __tablename__ attribute, which to Declarative indicates that each class should have its own generated to which it will be mapped. For the Engineer subclass, the style of inheritance applied is joined table inheritance, as it will be mapped to a table engineer that joins to the base person table. Any other subclasses that inherit from Person will also have this style of inheritance applied by default (and within this particular example, would need to each specify a primary key column; more on that in the next section).

    By contrast, the Manager subclass of Person overrides the __tablename__ classmethod to return None. This indicates to Declarative that this class should not have a generated, and will instead make use exclusively of the base Table to which Person is mapped. For the Manager subclass, the style of inheritance applied is .

    The example above illustrates that Declarative directives like __tablename__ are necessarily applied to each subclass individually, as each mapped class needs to state which Table it will be mapped towards, or if it will map itself to the inheriting superclass’ .

    If we instead wanted to reverse the default table scheme illustrated above, so that single table inheritance were the default and joined table inheritance could be defined only when a __tablename__ directive were supplied to override it, we can make use of Declarative helpers within the top-most __tablename__() method, in this case a helper called has_inherited_table(). This function will return True if a superclass is already mapped to a . We may use this helper within the base-most __tablename__() classmethod so that we may conditionally return None for the table name, if a table is already present, thus indicating single-table inheritance for inheriting subclasses by default:

    1. from sqlalchemy import ForeignKey
    2. from sqlalchemy.orm import DeclarativeBase
    3. from sqlalchemy.orm import declared_attr
    4. from sqlalchemy.orm import has_inherited_table
    5. from sqlalchemy.orm import Mapped
    6. from sqlalchemy.orm import mapped_column
    9. class Base(DeclarativeBase):
    10.     pass
    13. class Tablename:
    14.     @declared_attr.directive
    15.     def __tablename__(cls):
    16.         if has_inherited_table(cls):
    17.             return None
    18.         return cls.__name__.lower()
    21. class Person(Tablename, Base):
    22.     id: Mapped[int] = mapped_column(primary_key=True)
    23.     discriminator: Mapped[str]
    24.     __mapper_args__ = {"polymorphic_on": "discriminator"}
    27. class Engineer(Person):
    28.     @declared_attr.directive
    29.     def __tablename__(cls):
    30.         """override __tablename__ so that Engineer is joined-inheritance to Person"""
    32.         return cls.__name__.lower()
    34.     id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)
    36.     primary_language: Mapped[str]
    38.     __mapper_args__ = {"polymorphic_identity": "engineer"}
    41. class Manager(Person):
    43.     __mapper_args__ = {"polymorphic_identity": "manager"}

    Using to generate table-specific inheriting columns

    In contrast to how __tablename__ and other special names are handled when used with declared_attr, when we mix in columns and properties (e.g. relationships, column properties, etc.), the function is invoked for the base class only in the hierarchy, unless the directive is used in combination with the declared_attr.cascading sub-directive. Below, only the Person class will receive a column called id; the mapping will fail on Engineer, which is not given a primary key:

    1. class HasId:
    2.     id: Mapped[int] = mapped_column(primary_key=True)
    5. class Person(HasId, Base):
    6.     __tablename__ = "person"
    8.     discriminator: Mapped[str]
    9.     __mapper_args__ = {"polymorphic_on": "discriminator"}
    12. # this mapping will fail, as there's no primary key
    13. class Engineer(Person):
    14.     __tablename__ = "engineer"
    16.     primary_language: Mapped[str]
    17.     __mapper_args__ = {"polymorphic_identity": "engineer"}

    It is usually the case in joined-table inheritance that we want distinctly named columns on each subclass. However in this case, we may want to have an id column on every table, and have them refer to each other via foreign key. We can achieve this as a mixin by using the modifier, which indicates that the function should be invoked for each class in the hierarchy, in almost (see warning below) the same way as it does for __tablename__:

    1. class HasIdMixin:
    2.     @declared_attr.cascading
    3.     def id(cls) -> Mapped[int]:
    4.         if has_inherited_table(cls):
    5.             return mapped_column(ForeignKey("person.id"), primary_key=True)
    6.         else:
    7.             return mapped_column(Integer, primary_key=True)
    10. class Person(HasIdMixin, Base):
    11.     __tablename__ = "person"
    13.     discriminator: Mapped[str]
    14.     __mapper_args__ = {"polymorphic_on": "discriminator"}
    17. class Engineer(Person):
    18.     __tablename__ = "engineer"
    20.     primary_language: Mapped[str]
    21.     __mapper_args__ = {"polymorphic_identity": "engineer"}

    Warning

    The declared_attr.cascading feature currently does not allow for a subclass to override the attribute with a different function or value. This is a current limitation in the mechanics of how @declared_attr is resolved, and a warning is emitted if this condition is detected. This limitation only applies to ORM mapped columns, relationships, and other styles of attribute. It does not apply to Declarative directives such as __tablename__, __mapper_args__, etc., which resolve in a different way internally than that of declared_attr.cascading.

    Combining Table/Mapper Arguments from Multiple Mixins

    In the case of __table_args__ or __mapper_args__ specified with declarative mixins, you may want to combine some parameters from several mixins with those you wish to define on the class itself. The declared_attr decorator can be used here to create user-defined collation routines that pull from multiple collections:

    1. from sqlalchemy.orm import declarative_mixin, declared_attr
    4. class MySQLSettings:
    5.     __table_args__ = {"mysql_engine": "InnoDB"}
    8. class MyOtherMixin:
    9.     __table_args__ = {"info": "foo"}
    12. class MyModel(MySQLSettings, MyOtherMixin, Base):
    13.     __tablename__ = "my_model"
    15.     @declared_attr
    16.     def __table_args__(cls):
    17.         args = dict()
    18.         args.update(MySQLSettings.__table_args__)
    19.         args.update(MyOtherMixin.__table_args__)
    20.         return args
    22.     id = mapped_column(Integer, primary_key=True)

    To define a named, potentially multicolumn Index that applies to all tables derived from a mixin, use the “inline” form of and establish it as part of __table_args__:

    1. class MyMixin:
    2.     a = mapped_column(Integer)
    3.     b = mapped_column(Integer)
    5.     @declared_attr
    6.     def __table_args__(cls):
    7.         return (Index(f"test_idx_{cls.__tablename__}", "a", "b"),)
    10. class MyModel(MyMixin, Base):
    11.     __tablename__ = "atable"