Where clause correct syntax and type checking

[EvanTheB]

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

import sqlmodel

class Hero(sqlmodel.SQLModel, table=True):
    name: str = sqlmodel.Field(primary_key=True)
    dead: None | bool = sqlmodel.Field(nullable=True)

# in memory database for testing
engine = sqlmodel.create_engine("sqlite:///:memory:", echo=False)
sqlmodel.SQLModel.metadata.create_all(engine)

with sqlmodel.Session(engine) as session:
    session.add(Hero(name="Batman", dead=True))
    session.add(Hero(name="Spiderman", dead=False))
    session.add(Hero(name="Superman", dead=None))
    session.commit()

with sqlmodel.Session(engine) as session:
    print(session.exec(sqlmodel.select(Hero).where(Hero.dead)).all())
    print(session.exec(sqlmodel.select(Hero).where(Hero.dead == None)).all())

    # works but has lint errors:
    print(session.exec(sqlmodel.select(Hero).where(Hero.dead == False)).all())
    print(session.exec(sqlmodel.select(Hero).where(Hero.dead.is_(False))).all())

    # does not work and there are no lint errors:
    print(session.exec(sqlmodel.select(Hero).where(Hero.dead is False)).all())
    print(session.exec(sqlmodel.select(Hero).where(not Hero.dead)).all())

Description

I am wondering what the correct syntax to use for where clauses on booleans is. I found two syntaxes that work, but both have lint errors. And two other syntaxes that seem correct but do not give the desired result.

.where(Hero.dead == False) Avoid equality comparisons to False; use not Hero.dead: for false checks"

where(Hero.dead.is_(False)) Cannot access attribute "is_" for class "bool"  Attribute "is_" is unknown.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

Python 3.13.2

Additional Context

No response

[erkebulanmyrzabek]

Use col() to tell the type checker that you're working with a SQL expression:

from sqlmodel import col, select

statement = select(Hero).where(col(Hero.dead).is_(False))
heroes = session.exec(statement).all()

With your sample data, this returns Spiderman. For the other cases:

col(Hero.dead).is_(True)   # Batman
col(Hero.dead).is_(None)   # Superman

is False and not Hero.dead are evaluated by Python before SQLAlchemy builds the query. They cannot be overloaded to produce the SQL expression you need.

Also, IS FALSE excludes NULL rows. If NULL should count as “not dead”, you need to include that case explicitly.

The SQLModel docs explain col() here:
https://sqlmodel.tiangolo.com/tutorial/where/#type-annotations-and-errors