Hybrid Properties, Column Properties and SQL Expressions

A derived value that a query filters, sorts or limits by must be a SQL expression, not a Python property: use hybrid_property with an expression half for values from the same row, column_property or query_expression() for aggregates over a relationship, and with_loader_criteria for a predicate that must apply everywhere. This topic belongs to advanced query patterns and bulk data operations.

Concept & Execution Model

Every application has values that are not columns: a customer's full name, whether a subscription is active, how many comments a post has, whether a row counts as deleted. Each one can be computed in Python after loading, or expressed as SQL the database evaluates. SQLAlchemy offers four constructs for the second, and choosing between them is the subject of this topic.

import datetime as dt

from sqlalchemy import ForeignKey, func, select
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import (DeclarativeBase, Mapped, column_property, mapped_column,
                            query_expression, relationship)


class Base(DeclarativeBase):
    pass


class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    published_at: Mapped[dt.datetime | None]
    deleted_at: Mapped[dt.datetime | None] = mapped_column(default=None, index=True)

    comments: Mapped[list["Comment"]] = relationship(back_populates="post")

    # Same row, two evaluators.
    @hybrid_property
    def is_published(self) -> bool:
        return self.published_at is not None

    @is_published.inplace.expression
    @classmethod
    def _is_published_expression(cls):
        return cls.published_at.is_not(None)

    # Filled per query, only where it is wanted.
    comment_count: Mapped[int] = query_expression()

The four constructs divide by where they are evaluated and when you pay for them. A hybrid property has a Python half and a SQL half, so the same attribute works on a loaded object and inside a WHERE clause. A column_property is a SQL expression — typically a correlated aggregate — rendered into every SELECT for the class. query_expression() is the same idea made opt-in per query. And with_loader_criteria is not a value at all: it is a WHERE fragment attached to an entity wherever it appears, which is what makes a soft-delete filter reliable.

All four exist for one reason: a value the database can compute is a value the database can also filter, sort and limit by. A value computed in Python can only be used after every candidate row has been loaded, which is the difference between reading twenty rows and reading the table.

Once these expressions exist, they can be exposed to callers — carefully — which is what building dynamic filters and sorting from API query parameters covers. This topic sits inside advanced query patterns and bulk data operations, alongside the loading strategies and window functions that the same expressions often appear in.

Query Construction & Async Execution Patterns

Because these constructs are SQL, they behave identically in synchronous and asynchronous code — and that is precisely their appeal under async, where the Python alternatives need loaded relationships and expired attributes reloaded.

# Sync — the derived value is part of the statement
from sqlalchemy import select
from sqlalchemy.orm import Session

from blog.models import Post


def published_titles(session: Session) -> list[str]:
    return list(session.scalars(
        select(Post.title).where(Post.is_published).order_by(Post.published_at.desc())
    ))

# Async — the same statement, awaited
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from blog.models import Post


async def published_titles(session: AsyncSession) -> list[str]:
    return list(await session.scalars(
        select(Post.title).where(Post.is_published).order_by(Post.published_at.desc())
    ))

Neither version loads a Post object, and neither touches a relationship, so there is nothing that could lazy-load and nothing to await beyond the execution itself.

Three patterns cover most usage.

Filtering and sorting by a hybrid. where(Post.is_published) and order_by(Post.is_published.desc()) work because the expression half renders SQL. A hybrid without an expression half is the most common cause of a filter that silently matches nothing.

Aggregates attached per query. with_expression() fills a query_expression() placeholder with any scalar expression, so a list endpoint can carry a count while a detail endpoint does not pay for it:

from sqlalchemy import func, select
from sqlalchemy.orm import with_expression

from blog.models import Comment, Post

count_comments = (
    select(func.count(Comment.id))
    .where(Comment.post_id == Post.id)
    .correlate_except(Comment)
    .scalar_subquery()
)

stmt = (
    select(Post)
    .options(with_expression(Post.comment_count, count_comments))
    .order_by(count_comments.desc())
    .limit(20)
)

Ordering by the subquery rather than the placeholder is deliberate: the placeholder only has a value once the option is applied, while the expression always does.

Criteria applied to an entity. with_loader_criteria(Post, Post.deleted_at.is_(None), include_aliases=True) adds its predicate wherever Post appears — including inside selectinload() — and propagates to lazy loads from the returned objects. That reach is what distinguishes it from a .where() clause, and why it is the right tool for a rule that must hold everywhere.

State Management & Session Boundaries

These constructs interact with the session in ways that are easy to get wrong precisely because they look like ordinary attributes.

A hybrid's Python half reads loaded columns. customer.full_name needs first_name and last_name in memory. If either is deferred, expired by a commit, or was never loaded because the query selected specific columns, reading the hybrid triggers a load — and under async that raises MissingGreenlet. Keeping hybrid getters dependent only on columns the object normally carries, and setting expire_on_commit=False, avoids nearly all of it.

A column_property is loaded with the row and never recomputed. Its value is a snapshot from the moment the row was fetched. Insert a comment in the same session and post.comment_count still holds the old number until the object is refreshed or reloaded. That is correct — it is what the database said when asked — but it surprises code that expects an attribute to track the collection.

A query_expression() is None unless filled. No option, no value. Code that reads it unconditionally needs either a default or a guarantee that every query applies the option, and the second is hard to maintain across a large codebase. Returning the value explicitly from the query — as a second column rather than an attribute — is often clearer.

Loader criteria follow the objects. With propagate_to_loaders=True (the default), the criteria that filtered a parent query also filter lazy loads issued from the objects it returned. This is usually what you want for soft deletion, and worth knowing when debugging why a collection looks shorter than the table suggests.

session.get() bypasses criteria for identity-map hits. If the object is already in the session, get() returns it without a query, so no WHERE fragment can apply. A soft-deleted row fetched earlier in the same session is still reachable that way. Where the distinction matters, load with select() and check the flag explicitly.

The common thread is that all of these are query-time mechanisms. They describe what SQL to write, not what an object should mean after it has been loaded. Rules that must hold regardless of how an object arrived — validation, invariants — belong in the mapped class or in the schema, not in an expression.

Advanced Expression Patterns

Beyond the four constructs, a handful of SQL expressions come up often enough to be worth naming.

case() for bucketing and priority. A derived category — a shipping band, a risk tier, a sort priority — is a CASE expression, and it can be mapped as a hybrid or built per query:

from sqlalchemy import case, select

from shop.models import Order

priority = case(
    (Order.status == "paid", 1),
    (Order.status == "pending", 2),
    else_=3,
).label("priority")

stmt = select(Order).order_by(priority, Order.placed_at)

Sorting by a CASE is how "paid first, then pending, then everything else, each by date" is expressed without three queries or a Python sort.

func.coalesce() for defaults in SQL. A derived value over a nullable column needs a decision about NULL, and making it in SQL keeps Python and SQL agreeing: func.coalesce(Product.discount_cents, 0).

Boolean expressions as first-class values. select(Post.id, Post.is_published) returns the boolean per row; select(func.count().filter(Post.is_published)) counts only the matching rows in a single pass. FILTER is far cheaper than several subqueries when a report needs "total, published, draft".

Generated columns when the expression is hot. Computed("...", persisted=True) moves the work to write time and makes the value an ordinary, indexable column. The hybrid's expression half then becomes a plain column reference, and nothing else changes for callers. This is the same trade-off made for full-text search vectors.

Expression indexes to make any of it fast. A filter on lower(email), coalesce(discount, 0) or a CASE cannot use an ordinary index. The index has to match the expression exactly, which is why an expression and its index usually belong in the same change — see creating partial and expression indexes from SQLAlchemy models.

The one expression pattern to avoid is a derived value that duplicates a rule already enforced elsewhere. A hybrid that reimplements a check constraint, or a column_property that recomputes a stored counter, is a second source of truth that will eventually disagree with the first.

Hybrid Architectures & Migration Strategies

Models arriving from 1.x, or from a synchronous codebase, usually compute derived values in Python. Converting them is incremental, and the order that works is driven by what queries need rather than by tidiness.

Start with the values that appear in queries. A @property nobody filters on is fine as it is. The ones to convert are those a WHERE, ORDER BY or report already references — or those a developer worked around by loading rows and sorting in Python. Add the expression half, keep the Python half, and check the two agree with the test described in writing hybrid properties that work in Python and SQL.

Replace len(obj.children) with a count expression. Every one of these is a loaded collection that exists only to be measured. column_property is the smallest change; query_expression() is the better end state for a class read on many paths.

Consolidate repeated predicates. A predicate copied into dozens of queries — deleted_at IS NULL, tenant_id = :tenant, status != 'archived' — is a with_loader_criteria waiting to be written, and the copy that was forgotten is a bug waiting to be found.

Then make the async port. With derived values in SQL and repeated predicates centralised, the remaining MissingGreenlet failures are genuine relationship loads, which is a much smaller set to work through.

Mixing remains normal afterwards. A reporting job may run entirely in Core against the same expressions — select(count_comments.label("comments")) needs no ORM at all — while the web application uses the mapped attributes. Sharing the expression, defined once in a module and referenced from both, is what keeps the two from drifting:

# blog/expressions.py — one definition, used by the ORM and by Core reports
from sqlalchemy import func, select

from blog.models import Comment, Post

comment_count = (
    select(func.count(Comment.id))
    .where(Comment.post_id == Post.id)
    .correlate_except(Comment)
    .scalar_subquery()
)

Measuring Whether an Expression Earns Its Place

Every expression in this topic is a promise that the database can compute something cheaply. That promise is worth checking, because the difference between an indexed expression and an unindexed one is the difference between a millisecond and a table scan — and nothing in the model says which you have.

Read the plan for each filter you expose. Compile the statement with its parameters and run EXPLAIN against it. What you are looking for is the access path: an Index Scan or Bitmap Index Scan naming an index you recognise, rather than a Seq Scan on the whole table. The mechanics of compiling the exact statement SQLAlchemy sends are in reading EXPLAIN output for a SQLAlchemy query.

from sqlalchemy import select, text

from blog.models import Post


async def explain(session, stmt) -> str:
    compiled = stmt.compile(session.bind, compile_kwargs={"literal_binds": True})
    rows = await session.execute(text(f"EXPLAIN ANALYZE {compiled}"))
    return "
".join(row[0] for row in rows)


print(await explain(session, select(Post).where(Post.is_published).limit(20)))

Watch the cost of a column_property you did not ask for. Because it is rendered into every SELECT for the class, a correlated subquery added for one list page is also paid by every detail view, every relationship load and every refresh. The per-query query counter from counting queries per request will not show it — the statement count is unchanged — so the signal to watch is statement duration on endpoints that never mention the attribute.

Prefer a measurement to an intuition about LIMIT. A correlated subquery is evaluated once per returned row, so it is cheap under a small LIMIT and expensive without one. The same query shape can therefore be the right choice for a paginated list and the wrong choice for an export, which is why the decision belongs per query rather than per class.

One number makes the whole topic concrete: for a list of twenty rows sorted by a derived value, computing in Python reads every candidate row, a correlated subquery reads the rows the planner considers, and a stored column reads twenty. All three produce the same page.

Production Pitfalls & Anti-Patterns

• A filter on a hybrid silently matches nothing — no expression half, so the Python getter was evaluated at class level. Add @<name>.inplace.expression.
• TypeError: Boolean value of this clause is not defined — and, or or if used on columns. Use and_(), or_() and case().
• MissingGreenlet: greenlet_spawn has not been called reading a derived attribute — a deferred column_property, or a hybrid reading an expired column. Undefer, refresh explicitly, or use query_expression().
• Counts identical across rows, or a cartesian-product warning — a correlated subquery without correlate_except().
• Every query for a class got slower — a column_property subquery now runs in every SELECT, including relationship loads. Make it opt-in.
• Deleted rows inside eager-loaded collections — the soft-delete predicate was a .where() on the outer query only. Use with_loader_criteria.
• A sequential scan on a filter that looks indexed — the expression does not match any index. Add an expression index, or store the value.

The quietest failure is a hybrid whose two halves disagree — different clocks, different NULL handling, different rounding. Nothing errors: an object reports one thing, a query reports another, and the bug is reported as "the list is missing a row that is definitely there". A parametrised test comparing the halves against each other, over boundary rows, is the only reliable defence.

Frequently Asked Questions

Related

• Writing hybrid properties that work in Python and SQL — One definition, two evaluators — and testing that they agree.
• Using column_property for correlated subquery counts — Aggregates over a relationship without loading it.
• Filtering soft-deleted rows with with_loader_criteria — A predicate that reaches eager loads and lazy loads alike.
• Building dynamic filters and sorting from API query parameters — Exposing these expressions to callers without exposing the model.
• Complex Joins and Relationship Loading Strategies — Loading the rows these expressions summarise.