Writing hybrid properties that work in Python and SQL

Define the Python half with @hybrid_property and the SQL half with @<name>.inplace.expression, so the same attribute computes on a loaded object and renders into WHERE, ORDER BY and GROUP BY — then test that both halves return the same answer for the same row. This guide belongs to hybrid properties, column properties and SQL expressions.

Quick Answer

A plain @property cannot be used in a query: accessing it on the class runs the getter against InstrumentedAttribute objects rather than values, which either raises or builds a clause that means nothing.

Before — a property that only works on instances:

from sqlalchemy import select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str]
    last_name: Mapped[str]

    @property
    def full_name(self) -> str:
        return f"{self.first_name} {self.last_name}"


select(Customer).where(Customer.full_name == "Ada Lovelace")
# The f-string interpolates two InstrumentedAttribute objects, so the comparison is
# between a meaningless string and a literal: the filter matches nothing.

After — a hybrid with both halves:

from sqlalchemy import func, select
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str]
    last_name: Mapped[str]

    @hybrid_property
    def full_name(self) -> str:
        return f"{self.first_name} {self.last_name}"

    @full_name.inplace.expression
    @classmethod
    def _full_name_expression(cls):
        return func.concat_ws(" ", cls.first_name, cls.last_name)


# Instance access: Python.
customer.full_name                       # "Ada Lovelace"

# Class access: SQL.
stmt = select(Customer).where(Customer.full_name == "Ada Lovelace")
# SELECT ... WHERE concat_ws(' ', customers.first_name, customers.last_name) = $1

inplace is the 2.0 spelling that keeps the name bound to one attribute, so type checkers see a single full_name rather than a redefinition. The _full_name_expression name is never used directly — it exists only to carry the decorator.

Execution Context & Async Workflow Integration

A hybrid is a descriptor with two implementations and a rule for choosing between them: accessed through an instance, it runs the Python function against loaded values; accessed through the class, it runs the expression function and returns a SQL construct.

That construct is then compiled into whatever statement it appears in, so the database evaluates it, once per row. This is what makes hybrids worth defining: func.concat_ws(...) in a WHERE clause filters a million rows in the database, while a Python property would require loading a million objects to filter them in the application.

When no expression is supplied, SQLAlchemy falls back to evaluating the Python getter at class level. Sometimes that works by accident — an expression built from arithmetic on columns produces a valid SQL construct, because cls.price * cls.quantity is a SQL expression — and sometimes it silently produces nonsense, as the f-string above does. Relying on the accident is the most common hybrid bug; if the attribute is used in a query, write the expression.

Under async, the thing to watch is what the Python half depends on. customer.full_name reads first_name and last_name, and if either is deferred, expired after a commit, or was never loaded because the query selected specific columns, reading it triggers a lazy load — which raises MissingGreenlet. Two practical consequences: keep hybrid getters dependent only on columns the object normally carries, and set expire_on_commit=False on the session factory so post-commit access still works, as using expire_on_commit=False in FastAPI dependencies describes.

A hybrid can also cross a relationship, and there the async caveat is sharper: a getter that reads self.orders needs the collection loaded, so the attribute is only safe on objects fetched with the right loader option. When a value is derived from a related collection — a count, a sum — a column_property or a query-time expression is usually the better tool, because it is computed in SQL and needs nothing loaded.

Indexability is the other half of the performance story. WHERE lower(email) = $1 can use an index only if that index exists on lower(email). A hybrid does not create an index, and a hybrid over a function is exactly the case where an expression index is needed — declared in the model as Index("ix_customers_email_lower", func.lower(Customer.email)).

Resolving Warnings, Errors & Common Mistakes

The Boolean value of this clause is not defined error is worth internalising, because it comes up in every non-trivial expression. Python's and, or and not need a truth value, and a SQL expression has none until a row is evaluated. The expression half must therefore use SQL constructs:

import datetime as dt

from sqlalchemy import and_, func, or_
from sqlalchemy.ext.hybrid import hybrid_property


class Subscription(Base):
    __tablename__ = "subscriptions"
    id: Mapped[int] = mapped_column(primary_key=True)
    starts_on: Mapped[dt.date]
    ends_on: Mapped[dt.date | None]
    cancelled_at: Mapped[dt.datetime | None]

    @hybrid_property
    def is_active(self) -> bool:
        today = dt.date.today()
        return (
            self.cancelled_at is None
            and self.starts_on <= today
            and (self.ends_on is None or self.ends_on >= today)
        )

    @is_active.inplace.expression
    @classmethod
    def _is_active_expression(cls):
        today = func.current_date()
        return and_(
            cls.cancelled_at.is_(None),
            cls.starts_on <= today,
            or_(cls.ends_on.is_(None), cls.ends_on >= today),
        )

Note what is symmetric there: is None in Python becomes is_(None) in SQL, and the three-way condition uses and_/or_ rather than Python operators. Note also what is not symmetric: dt.date.today() is the application's clock and func.current_date() is the database's. On a UTC server with date granularity that is harmless; with timestamps and time zones it is a real source of disagreement, and the next section fixes it properly.

Advanced: hybrid_method, Comparators and Matching Clocks

When a derived value depends on something the caller knows — a reference date, a radius, a currency — a hybrid_method takes it as an argument, which also removes the clock mismatch by making the clock an input:

import datetime as dt

from sqlalchemy import and_, or_, select
from sqlalchemy.ext.hybrid import hybrid_method



class Subscription(Base):
    __tablename__ = "subscriptions"
    # ... columns as above ...

    @hybrid_method
    def active_on(self, as_of: dt.date) -> bool:
        return (
            self.cancelled_at is None
            and self.starts_on <= as_of
            and (self.ends_on is None or self.ends_on >= as_of)
        )

    @active_on.inplace.expression
    @classmethod
    def _active_on_expression(cls, as_of: dt.date):
        return and_(
            cls.cancelled_at.is_(None),
            cls.starts_on <= as_of,
            or_(cls.ends_on.is_(None), cls.ends_on >= as_of),
        )


today = dt.date.today()
subscription.active_on(today)                                  # Python
stmt = select(Subscription).where(Subscription.active_on(today))  # SQL, same date

One reference date flows into both halves, so an object and a query can never disagree, and a test can pin the date instead of mocking a clock.

A custom Comparator is the tool for changing how operators behave, rather than what a value is. Case-insensitive equality is the canonical example: instead of asking every caller to remember func.lower(), put it in one place:

from sqlalchemy import func
from sqlalchemy.ext.hybrid import Comparator, hybrid_property


class CaseInsensitive(Comparator):
    def __eq__(self, other):
        return func.lower(self.__clause_element__()) == func.lower(other)

    def __hash__(self):
        return hash(self.__clause_element__())


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]

    @hybrid_property
    def email_ci(self) -> str:
        return self.email.lower()

    @email_ci.inplace.comparator
    @classmethod
    def _email_ci_comparator(cls) -> Comparator:
        return CaseInsensitive(cls.email)


stmt = select(Customer).where(Customer.email_ci == "Ada@Example.COM")
# ... WHERE lower(customers.email) = lower($1)

That query needs Index("ix_customers_email_lower", func.lower(Customer.email)) to be fast, and with it, case-insensitive lookup is an index scan. Without it, every lookup reads the table — which is why an expression hybrid and an expression index are usually written in the same commit. (PostgreSQL's citext type is the alternative: it moves the rule into the column instead of the query.)

When a derived value is queried constantly and is expensive to compute, the last step is to stop computing it per query and store it: a Computed generated column holds the value, an ordinary index serves it, and the hybrid's expression half becomes a plain column reference. That is the same trade-off made for search vectors in implementing full-text search with tsvector.

Testing That Both Halves Agree

A hybrid is two implementations of one idea, and nothing in SQLAlchemy checks that they match. The test that catches drift is short, and it is the single most valuable test to write for any hybrid used in a query: for each row, the Python answer and the SQL answer must be the same.

import datetime as dt

import pytest
from sqlalchemy import select

from billing.models import Subscription

CASES = [
    # (starts_on, ends_on, cancelled_at, expected_active_on_2026_09_17)
    (dt.date(2026, 1, 1), None, None, True),
    (dt.date(2026, 1, 1), dt.date(2026, 12, 31), None, True),
    (dt.date(2026, 1, 1), dt.date(2026, 9, 16), None, False),          # ended yesterday
    (dt.date(2026, 9, 17), None, None, True),                          # starts today
    (dt.date(2026, 9, 18), None, None, False),                         # starts tomorrow
    (dt.date(2026, 1, 1), None, dt.datetime(2026, 5, 1, tzinfo=dt.UTC), False),
]


@pytest.mark.asyncio
@pytest.mark.parametrize("starts_on,ends_on,cancelled_at,expected", CASES)
async def test_active_on_agrees_in_python_and_sql(
    session, starts_on, ends_on, cancelled_at, expected
):
    as_of = dt.date(2026, 9, 17)
    subscription = Subscription(
        starts_on=starts_on, ends_on=ends_on, cancelled_at=cancelled_at
    )
    session.add(subscription)
    await session.flush()

    in_python = subscription.active_on(as_of)
    in_sql = await session.scalar(
        select(Subscription.id)
        .where(Subscription.id == subscription.id, Subscription.active_on(as_of))
    )

    assert in_python is expected
    assert (in_sql is not None) is expected, "SQL half disagrees with the Python half"

Three properties make this worth the lines. It is parametrised over boundary rows — the day a subscription starts, the day after it ends — which is exactly where asymmetric < versus <= hides. It compares the two halves against each other as well as against the expectation, so a change to one half fails even if the expectation was wrong. And it runs against a real PostgreSQL, because the SQL half's behaviour with NULL and dates is the database's, not SQLite's — the fixture comes from running tests against a Postgres testcontainer.

NULL is the boundary worth naming explicitly, because SQL's three-valued logic has no Python equivalent. self.ends_on is None or self.ends_on >= as_of in Python is straightforward; in SQL, ends_on >= as_of is NULL rather than false when ends_on is NULL, so the or_(... .is_(None), ...) is not optional — without it the row is simply not returned, and the Python half says it is active. A parametrised case with None in every nullable column is how that gets caught before production.

For hybrids used in ORDER BY, add one more assertion: sort the same rows in Python and in SQL and compare the sequences. Collation differences — "Ä" before or after "B", case sensitivity, how NULL sorts — are invisible until a paginated list shows a row twice.

Frequently Asked Questions

Related

• Hybrid Properties, Column Properties and SQL Expressions — The parent guide: derived values in Python and in SQL.
• Using column_property for correlated subquery counts — Derived values that aggregate a relationship.
• Building dynamic filters and sorting from API query parameters — Exposing hybrids safely to API callers.
• Writing a TypeDecorator for encrypted columns — The other way to put behaviour on a column.