Using SQLAlchemy Mixins and Inheritance for Model Reusability

A SQLAlchemy mixin is a plain class that contributes mapped attributes to several models. It’s a good fit for fields that mean the same thing everywhere, such as creation and update timestamps. With SQLAlchemy’s typed declarative mapping, put the column declarations on the mixin and include it in each model’s base classes.

from datetime import datetime

from sqlalchemy import DateTime, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        nullable=False,
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class Article(TimestampMixin, Base):
    __tablename__ = "article"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]

SQLAlchemy copies mapped columns from a mixin onto each mapped class, so Article gets its own created_at and updated_at columns. Keep the mixin focused: a timestamp mixin should not quietly add a primary key, a relationship, or assumptions about a table’s name.

Be precise about what the defaults do. server_default is applied by the database when an insert omits the column. onupdate tells SQLAlchemy to include the expression when it issues an update; it does not create a database trigger and will not run for updates made outside SQLAlchemy. If every writer must maintain the timestamp, enforce that in the database.

Mixins can also supply constraints and indexes, but those objects may need a fresh instance for each mapped table. Use @declared_attr when the declaration depends on the model that consumes the mixin.

from sqlalchemy import Index
from sqlalchemy.orm import declared_attr


class SlugMixin:
    slug: Mapped[str]

    @declared_attr.directive
    def __table_args__(cls):
        return (
            Index(f"ix_{cls.__tablename__}_slug", "slug", unique=True),
        )

Relationships often need the same treatment, especially when their target or foreign-key column depends on the consuming model. A direct relationship declaration can work for simpler cases, but @declared_attr makes the per-model construction explicit.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class OwnerMixin:
    @declared_attr
    def owner_id(cls) -> Mapped[int]:
        return mapped_column(ForeignKey("user.id"), nullable=False)

    @declared_attr
    def owner(cls):
        return relationship("User")

Prefer concrete types and explicit nullability in shared fields. A mixin magnifies a mistake across every model that uses it, so keep its contract small enough that each consuming model’s resulting table remains easy to inspect.

Choosing between mixins and model inheritance

Use a mixin when models share fields or behavior but remain independent entities. An Article and a Invoice can both have timestamps without being the same kind of thing in the ORM. The mixin contributes columns; it does not create a table, a shared identity, or a relationship between those models.

Use mapped inheritance when the domain really has a base entity and specialized subtypes. Inheritance affects how SQLAlchemy constructs objects and queries them, not just which columns appear in a table. A query for the base class can return instances of its subclasses, and SQLAlchemy uses mapper configuration to determine each object’s type.

With single-table inheritance, the base and its subclasses share one table. A discriminator column identifies each row’s mapped type, while subclass-specific columns live on that same table and are generally nullable for rows belonging to other subclasses.

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    kind: Mapped[str] = mapped_column(String(30), nullable=False)
    email: Mapped[str] = mapped_column(unique=True)

    __mapper_args__ = {
        "polymorphic_on": kind,
        "polymorphic_identity": "user",
    }


class Admin(User):
    admin_level: Mapped[int | None] = mapped_column(nullable=True)

    __mapper_args__ = {
        "polymorphic_identity": "admin",
    }

Here, Admin is a kind of User, and loading a row whose discriminator is admin produces an Admin instance. The nullable annotation reflects the table design: ordinary user rows have no admin level. If the subtype’s data must be non-null or independently constrained, a separate table may be a better fit.

Joined-table inheritance puts subtype-specific columns in a separate table linked to the base row by a primary-key foreign key. It avoids a wide table full of nullable subtype columns, but loading a subtype can require a join, and the base row still represents the shared identity.

from sqlalchemy import ForeignKey


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    kind: Mapped[str] = mapped_column(String(30), nullable=False)
    email: Mapped[str] = mapped_column(unique=True)

    __mapper_args__ = {
        "polymorphic_on": kind,
        "polymorphic_identity": "user",
    }


class Admin(User):
    __tablename__ = "admin"

    id: Mapped[int] = mapped_column(
        ForeignKey("user.id"),
        primary_key=True,
    )
    admin_level: Mapped[int]

    __mapper_args__ = {
        "polymorphic_identity": "admin",
    }

Do not choose inheritance merely to avoid repeating a few column declarations. It introduces polymorphic identities and inheritance-specific schema rules; a mixin or an abstract mapped base is simpler when the goal is only to reuse columns. Conversely, if code routinely branches on subtype, or the database needs a genuine base-to-subtype relationship, inheritance may express the model more accurately than several unrelated classes sharing a mixin.

Concrete inheritance maps each subclass to its own complete table, including copies of the base fields. It can be appropriate when the tables must stand alone, but polymorphic queries and relationships across the hierarchy are more involved. Decide based on the queries and integrity constraints the application needs, not on which declaration looks shortest.

Building reusable models without hidden surprises

A reusable model should make its schema contract visible at the point where it’s used. In SQLAlchemy’s typed mappings, Mapped[str] normally implies a non-nullable column, while Mapped[str | None] implies a nullable one. Keep the annotation and the column options in agreement; a mixin reused across tables can otherwise make a small ambiguity into a schema-wide surprise.

class AuditMixin:
    created_by_id: Mapped[int] = mapped_column(
        ForeignKey("user.id"),
        nullable=False,
    )

Defaults have two different homes. A Python-side default is applied by SQLAlchemy when it constructs an insert; a server_default is part of the database schema and applies when any client omits the value. Pick the one that matches the guarantee you need, rather than adding both by habit.

from sqlalchemy import text


class StatusMixin:
    status: Mapped[str] = mapped_column(
        String(20),
        default="pending",
        server_default=text("'pending'"),
        nullable=False,
    )

For mutable values, use a callable so every instance gets its own object. A list or dictionary constructed once at class definition time can be shared accidentally by application code.

from sqlalchemy import JSON


class PreferencesMixin:
    preferences: Mapped[dict[str, str]] = mapped_column(
        JSON,
        default=dict,
        nullable=False,
    )

Be cautious when a mixin declares a relationship. Its foreign key and relationship target become part of every consuming table’s contract, even when a particular model does not naturally have that association. If the relationship varies by model, make that variation explicit with @declared_attr, or split the mixin into smaller pieces that can be composed deliberately.

Names are part of that contract too. A constraint or index name this is identical across tables can cause problems on databases that require schema-wide names. Derive names from the consuming class or use a metadata naming convention, and verify the generated names in the migration output.

from sqlalchemy import Index
from sqlalchemy.orm import declared_attr


class SearchableMixin:
    search_key: Mapped[str]

    @declared_attr.directive
    def __table_args__(cls):
        return (
            Index(f"ix_{cls.__tablename__}_search_key", "search_key"),
        )

Finally, keep mixins narrow enough that a model’s bases explain its schema. A class that combines timestamps, ownership, soft deletion, and search indexing may be reusable, but it also hides several independent decisions behind one name. Small mixins let each model opt into the fields and constraints it actually supports.

Testing the schema your models actually create

Type annotations describe intent, but the mapped table is the contract SQLAlchemy will hand to the database. Test that contract directly. A mixin can look correct while a consuming model ends up with the wrong nullability, default, index, or foreign key.

from sqlalchemy import inspect


def test_article_timestamp_columns():
    table = Article.__table__

    assert table.c.created_at.nullable is False
    assert table.c.updated_at.nullable is False
    assert table.c.created_at.server_default is not None
    assert table.c.updated_at.server_default is not None
    assert table.c.created_at is not table.c.updated_at

When several models use a mixin, check each mapped table. This catches changes to a mixin that accidentally affect only some consumers, and verifies that SQLAlchemy created separate column objects for each table.

def test_timestamp_mixin_is_applied_to_each_model():
    for model in (Article, Invoice):
        table = model.__table__

        assert "created_at" in table.c
        assert "updated_at" in table.c
        assert table.c.created_at.nullable is False
        assert table.c.updated_at.nullable is False

    assert Article.__table__.c.created_at is not Invoice.__table__.c.created_at

Metadata tests do not prove that a target database accepts the schema. Create the tables on the database used by the test, then inspect what it reports. An in-memory SQLite database is useful for fast checks, but it does not replace testing migrations against the production database dialect.

import pytest
from sqlalchemy import create_engine, inspect


@pytest.fixture
def engine():
    engine = create_engine("sqlite+pysqlite:///:memory:")
    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()


def test_created_article_schema(engine):
    inspector = inspect(engine)
    columns = {
        column["name"]: column
        for column in inspector.get_columns("article")
    }

    assert columns["created_at"]["nullable"] is False
    assert columns["updated_at"]["nullable"] is False
    assert columns["created_at"]["default"] is not None

Use the inspector for database-level details such as foreign keys and indexes. These assertions exercise the DDL emitted by SQLAlchemy, rather than only inspecting the Python declarations.

def test_owner_constraint_and_index_exist(engine):
    inspector = inspect(engine)

    foreign_keys = inspector.get_foreign_keys("article")
    assert any(
        "owner_id" in constraint["constrained_columns"]
        and constraint["referred_table"] == "user"
        for constraint in foreign_keys
    )

    indexes = inspector.get_indexes("article")
    assert any(index["name"] == "ix_article_slug" for index in indexes)

Base.metadata.create_all() creates missing tables; it does not update existing ones when a model changes. A test that starts from an empty database therefore checks fresh DDL, not whether deployed databases can reach that schema. Run the project’s Alembic migrations against a temporary database and inspect the resulting tables as well.

from alembic import command
from alembic.config import Config


def test_migrations_create_expected_schema(engine):
    config = Config("alembic.ini")
    config.attributes["connection"] = engine.connect()

    try:
        command.upgrade(config, "head")
        inspector = inspect(engine)
        table_names = set(inspector.get_table_names())

        assert "article" in table_names
        assert "user" in table_names
    finally:
        config.attributes["connection"].close()