How to typecheck the select api with sqlalchemy.orm's Mapped or equivalent

[pikevsten]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Sequence
from uuid import UUID, uuid4
from sqlalchemy.orm import Mapped
from sqlmodel import Field, SQLModel, Session, select

class ExampleModel(SQLModel):
    # Alternative A: Runtime error incompatible with pydantic 1/2
    id: Mapped[UUID] = Field(default_factory=uuid4, primary_key=True)
    is_active: Mapped[bool] = Field(default=True)

    # # Alternative B: Does not typecheck (with pylance)
    # id: UUID = Field(default_factory=uuid4, primary_key=True)
    # is_active: bool = Field(default=True)

def query(session: Session) -> Sequence[ExampleModel]:
   stmt = select(ExampleModel).where(ExampleModel.is_active.is_(True))
   return session.exec(stmt).all()

Description

SQLModel doesn't support type-checking when using the select query syntax like is_:

Error (Pylance)

When not using something like Mapped, typechecking fails:

Cannot access attribute "is_" for class "bool"
  Attribute "is_" is unknown

Error (pydantic v1)

When using pydantic v1 and from sqlalchemy.orm import Mapped

TypeError: Fields of type "<class 'sqlalchemy.orm.base.Mapped'>" are not supported.

Error (pydantic v2)

When using pydantic v2 and from sqlalchemy.orm import Mapped

pydantic.errors.PydanticSchemaGenerationError: Unable to generate pydantic-core schema for sqlalchemy.orm.base.Mapped[uuid.UUID]. Set `arbitrary_types_allowed=True` in the model_config to ignore this error or implement `__get_pydantic_core_schema__` on your type to fully support it.

If you got this error by calling handler(<some type>) within `__get_pydantic_core_schema__` then you likely need to call `handler.generate_schema(<some type>)` since we do not call `__get_pydantic_core_schema__` on `<some type>` otherwise to avoid infinite recursion.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.19

Python Version

Python 3.12.3

Additional Context

No response

[pikevsten]

I'll happily contribute some docs around typing if there is a blessed way forward we can get working ☺️

[redb0]

You should use col. See docs

select(Model).where(col(Model.deleted_at).is_(None))

[mchataigner]

Thanks for this clarification, I missed that from the documentation.

However, It’s not very convenient having to use a function (such as col) to make it work. It feels broken.

On top of that, this trick does not work when calling .options(joinedload(relation_field)) to preload the relationship.

I would expect this to work out of the box without having to rely on additional function that adds noises to the code, nor adding # type: ignore at each line of my queries.

[JacobHayes]

I've been struggling with this too - instead of using Mapped[...] once (per field) during class definition, we now have to use col(...) (or a type ignore) every time we reference a field or want to use the columns with SQLAlchemy select/similar constructs directly for more advanced logic.

My hacky work around for now has been:

if TYPE_CHECKING:
    from sqlalchemy.orm import Mapped as Mapped
else:

    class Mapped:
        def __class_getitem__(cls, item):
            return item

and then including that in the annotations on models like in vanilla SQLAlchemy. That satisfies type checkers and still works at runtime because Pydantic/SQLmodel/etc just see the inner type hint. I played around with setting up a Pydantic GetPydanticSchema for Mapped, but that needed further tweaks to SQLModel's get_type_from_field, get_relationship_to, and other helpers.