Composing Mapped Hierarchies with Mixins

A common need when mapping classes using the Declarative style is to share common functionality, such as particular columns, table or mapper options, naming schemes, or other mapped properties, across many classes. When using declarative mappings, this idiom is supported via the use of mixin classes, as well as via augmenting the declarative base class itself.

Tip

In addition to mixin classes, common column options may also be shared among many classes using PEP 593 Annotated types; see Mapping Multiple Type Configurations to Python Types 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:

  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.     pass
  13. class CommonMixin:
  14.     """define a series of common elements that may be applied to mapped
  15.     classes using this class as a mixin class."""
  17.     @declared_attr.directive
  18.     def __tablename__(cls) -> str:
  19.         return cls.__name__.lower()
  21.     __table_args__ = {"mysql_engine": "InnoDB"}
  22.     __mapper_args__ = {"eager_defaults": True}
  24.     id: Mapped[int] = mapped_column(primary_key=True)
  27. class HasLogRecord:
  28.     """mark classes that have a many-to-one relationship to the
  29.     ``LogRecord`` class."""
  31.     log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))
  33.     @declared_attr
  34.     def log_record(self) -> Mapped["LogRecord"]:
  35.         return relationship("LogRecord")
  38. class LogRecord(CommonMixin, Base):
  39.     log_info: Mapped[str]
  42. class MyModel(CommonMixin, HasLogRecord, Base):
  43.     name: Mapped[str]

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_column(), Mapped or Column 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 declared_attr.directive 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 relationship() 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 relationship() 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 declared_attr will attempt to illustrate the correct PEP 484 annotations for each method example. The use of annotations with declared_attr 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 PEP 484 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 declared_attr.directive to declared_attr to distinguish between Mapped 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 Mapped annotation class, mixin classes also work perfectly well with non-annotated and legacy Declarative forms, such as when using Column directly instead of mapped_column().

Changed in version 2.0: For users coming from the 1.4 series of SQLAlchemy who may have been using the mypy plugin, the declarative_mixin() class decorator is no longer needed to mark declarative mixins, assuming the mypy plugin is no longer in use.

Augmenting the Base

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 declarative_base() or registry.generate_base(), the declarative_base.cls 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 Declarative table 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 Table that the Declarative process generates. All three of the mapped_column(), Mapped, 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 Column.onupdate 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 mapped_column() 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:

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

  • Column - legacy Declarative form:

    1. class TimestampMixin:
    2.     created_at = Column(DateTime, default=func.now())
    3.     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 Column objects as well as mapped_column() constructs of any form when using mixins without the need to use declared_attr(). Previous limitations which prevented columns with ForeignKey elements from being used directly in mixins have been removed.

Mixing in Relationships

Relationships created by relationship() are provided with declarative mixin classes exclusively using the declared_attr 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 MapperProperty classes

Like relationship(), other MapperProperty 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 declared_attr. Within the function, other ordinary mapped columns that were declared with mapped_column(), Mapped, 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

The declared_attr decorator causes the decorated callable to behave exactly as a classmethod. However, typing tools like Pylance may not be able to recognize this, which can sometimes cause it to complain about access to the cls variable inside the body of the function. To resolve this issue when it occurs, the @classmethod decorator may be combined directly with declared_attr as:

  1. class SomethingMixin:
  2.     x: Mapped[int]
  3.     y: Mapped[int]
  5.     @declared_attr
  6.     @classmethod
  7.     def x_plus_y(cls) -> Mapped[int]:
  8.         return column_property(cls.x + cls.y)

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

Using Mixins and Base Classes with Mapped Inheritance Patterns

When dealing with mapper inheritance patterns as documented at Mapping Class Inheritance Hierarchies, some additional capabilities are present when using declared_attr 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 mapped_column() 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.

Using declared_attr() with inheriting Table and Mapper arguments

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]:
  37.         """override __tablename__ so that Manager is single-inheritance to Person"""
  39.         return None
  41.     __mapper_args__ = {"polymorphic_identity": "manager"}

In the above example, both the Person 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 Table 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 Table 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 single table inheritance.

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’ Table.

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 Table. 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 declared_attr() 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 declared_attr 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 declared_attr.cascading 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 MapperProperty 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)

Creating Indexes with Mixins

To define a named, potentially multicolumn Index that applies to all tables derived from a mixin, use the “inline” form of Index 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"
  12.     c = mapped_column(Integer, primary_key=True)