Static type for "any SQLAlchemy ORM class with an integer field named 'id'"

How can I give a static type to a function that should accept "any SQLAlchemy ORM class with an integer field named 'id'"?

Below are my attempts, commented with the mypy errors I got. I had to resort to id: Any but I still hope I can do better.

I can't modify the ORM classes, so I can't to add an intermediate class BaseWithId between Base and Foo.

I'm open to solutions, workarounds, or explanations why it's impossible.

I am using Python 3.12.3 and SQLAlchemy 2.0.41.

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


class HasIntId(Protocol):
    # id: int  # Argument 1 to "where" of "Select" has incompatible type "bool" ... [arg-type]
    # id: orm.Mapped[int]  # Value of type variable "T" of "f" cannot be "Foo"  [type-var]
    # id: orm.attributes.InstrumentedAttribute[int]  # Value of type variable "T" of "f" cannot be "Foo"  [type-var]
    id: Any

T = TypeVar('T', bound=HasIntId)

def f(model: type[T]) -> sql.Select[tuple[T]]:
    return sql.select(model).where(model.id > 42).order_by(model.id.desc())


class Base(orm.DeclarativeBase):
    pass

class Foo(Base):
    __tablename__ = 'foos'
    id: orm.Mapped[int] = orm.mapped_column(primary_key=True)


print(f(Foo))

Solution

You're so close! Use ClassVar:

from typing import Any, ClassVar, Protocol, TypeVar

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

orm.Mapped[int] was always the right type for your protocol. int won’t work, because SQLAlchemy query fields need to support operations that plain integers don’t:

sql.select(model).where(model.id > 42).order_by(model.id.desc())

model.id > 42 produces a SQLAlchemy expression, not a literal bool
model.id.desc() calls a method that doesn’t exist on int

By default, Protocol attributes describe instance attributes (Foo().id or model().id), not class attributes (Foo.id or model.id). In most situations the types may be the same, but SQLAlchemy fields behave differently depending on whether you access them on the class or on an instance:

reveal_type(Foo.id)  # Revealed type is "sqlalchemy.sql.elements.ColumnElement[builtins.bool]"

reveal_type(Foo().id)  # Revealed type is "builtins.int"

Wrapping a type annotation with ClassVar tells the type checker that orm.Mapped[int] specifies the type of the class attribute, not the instance attribute. SQLAlchemy’s typing machinery will take care of the rest:

On the class: the attribute is treated as orm.Mapped[int]
On instances: it resolves automatically to int

In other words, by using ClassVar you're effectively mirroring SQLAlchemy's behaviour so your code should type check as expected.