How to Type SQLAlchemy ORM Classes with mypy: Using ClassVar[orm.Mapped[int]] for a Class-Level id

Learn to build type-safe SQLAlchemy Select queries in mypy by annotating a class-level id as ClassVar[orm.Mapped[int]]. Avoid Any and support where/order_by.

Static typing and SQLAlchemy often meet at a tricky edge: class-level mapped attributes that behave differently on a class versus an instance. A common need is to type a function that accepts any SQLAlchemy ORM class with an integer primary key named id. The goal is to keep strict types and avoid falling back to id: Any, while supporting expressions like model.id > 42 and model.id.desc() in a select/where/order_by chain. Environment: Python 3.12.3, SQLAlchemy 2.0.41, and type checking with mypy.

Problem

The target is a function that accepts any ORM class that has an integer id and returns a typed SQLAlchemy Select. Several attempts to model the constraint using a Protocol and a TypeVar resulted in mypy errors, leading to a compromise with id: Any.

from typing import Any, Protocol, TypeVar
import sqlalchemy as sa
from sqlalchemy import orm


class WithIntPk(Protocol):
    id: Any  # weaker than desired, but type checks

U = TypeVar('U', bound=WithIntPk)

def build_query(model_cls: type[U]) -> sa.Select[tuple[U]]:
    return sa.select(model_cls).where(model_cls.id > 42).order_by(model_cls.id.desc())


class OrmBase(orm.DeclarativeBase):
    pass

class Widget(OrmBase):
    __tablename__ = 'widgets'
    id: orm.Mapped[int] = orm.mapped_column(primary_key=True)

print(build_query(Widget))

Prior attempts included id: int, id: orm.Mapped[int], and id: orm.attributes.InstrumentedAttribute[int]. mypy responded with errors like “Argument 1 to 'where' of 'Select' has incompatible type 'bool' ... [arg-type]” and “Value of type variable 'T' of 'f' cannot be 'Foo' [type-var]”.

Why this happens

The crux is that SQLAlchemy fields on the class produce SQL expressions and support operator and method calls that plain integers do not. In other words, model.id > 42 is not a boolean; it yields a SQLAlchemy expression. Likewise, model.id.desc() is a method on the SQL expression object, not on int. That’s why a bare int annotation doesn’t fit the usage in where and order_by.

There’s also a subtlety in Protocols: attributes declared in a Protocol describe instance attributes by default, whereas the function works with class attributes (model_cls.id). SQLAlchemy itself exposes different types depending on whether you access id on the class or the instance. The difference can be seen when revealing types:

reveal_type(Foo.id) — “sqlalchemy.sql.elements.ColumnElement[builtins.bool]”
reveal_type(Foo().id) — “builtins.int”

So, the Protocol needs to express a class attribute with SQLAlchemy’s mapped type, not an instance attribute.

Solution

The fix is to use orm.Mapped[int] for the attribute type and wrap it with ClassVar to make it a class-level contract. This mirrors how SQLAlchemy’s typing machinery exposes the attribute: on the class it behaves as a mapped, expression-capable attribute; on instances it resolves to the underlying Python type.

from typing import ClassVar, Protocol, TypeVar
import sqlalchemy as sa
from sqlalchemy import orm


class IntPkContract(Protocol):
    id: ClassVar[orm.Mapped[int]]

R = TypeVar('R', bound=IntPkContract)

def make_select(entity: type[R]) -> sa.Select[tuple[R]]:
    return sa.select(entity).where(entity.id > 42).order_by(entity.id.desc())


class BaseModel(orm.DeclarativeBase):
    pass

class Thing(BaseModel):
    __tablename__ = 'things'
    id: orm.Mapped[int] = orm.mapped_column(primary_key=True)

print(make_select(Thing))

This retains precise types while allowing the familiar SQLAlchemy expression usage in where and order_by.

What to keep in mind

orm.Mapped[int] is the right target type for the id attribute in the Protocol because it supports SQLAlchemy’s operator overloads and methods used in queries. ClassVar ensures you are specifying the class attribute contract, not the instance attribute. On instances, SQLAlchemy still resolves the attribute to int, aligning with expected behavior.

There is also a reported observation that using a plain id: orm.Mapped[int] in the Protocol type checks in pyright, whereas mypy requires ClassVar to pass. One view is that this may be a mypy bug; there is a linked mypy issue and a pyright discussion on the topic. If that behavior changes in the future, the necessity of ClassVar in this scenario may evolve accordingly. References: https://github.com/python/mypy/issues/19702 and https://github.com/microsoft/pyright/discussions/7942.

Why this matters

Accurate typing for SQLAlchemy models brings real benefits: it guards against silent regressions, enables safe refactoring, and keeps query-building code honest about the shape and behavior of mapped attributes. Avoiding id: Any prevents entire expression chains from degrading into untyped land, which can mask subtle bugs.

Takeaways

If you need a function that accepts any SQLAlchemy ORM class with an integer id and you are using mypy, define a Protocol with id: ClassVar[orm.Mapped[int]] and bind your TypeVar to it. This satisfies both the class-level access pattern and the expression semantics required by select, where, and order_by. Keep an eye on type checker behavior across mypy and pyright, and prefer precise annotations over Any wherever possible. For background on class-level typing, see the Python documentation for typing.ClassVar: https://docs.python.org/3/library/typing.html#typing.ClassVar.