SQLAlchemy Cheatsheet 18 — Hybrid Properties, Computed Columns, and Aggregates

Cheatsheet for derived attributes. Pick the right primitive based on where the computation happens.

Decision matrix

Tool	Where it runs	When
Python `@property`	Python only	Read-only, in-memory derivation
`hybrid_property`	Python AND SQL	Need to filter/order by the derived value
`column_property`	SQL only (fetched at query)	Computed at SELECT, not stored
`Computed` (generated col)	DB-side (stored or virtual)	Persisted; indexable

Plain @property

class User(Base):
    first: Mapped[str]
    last: Mapped[str]
    
    @property
    def full_name(self) -> str:
        return f"{self.first} {self.last}"

Use in Python. Can’t use in select(...).where(...).

hybrid_property

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    first: Mapped[str]
    last: Mapped[str]
    
    @hybrid_property
    def full_name(self) -> str:
        return f"{self.first} {self.last}"
    
    @full_name.expression
    @classmethod
    def full_name(cls):
        return func.concat(cls.first, " ", cls.last)

# Python
user.full_name              # "Alice X"

# SQL
stmt = select(User).where(User.full_name == "Alice X")

Define both: Python implementation + SQL expression.

hybrid_property setter

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

@full_name.setter
def full_name(self, value: str):
    self.first, self.last = value.split(" ", 1)

user.full_name = "Alice X"   # splits

column_property

from sqlalchemy.orm import column_property

class Order(Base):
    quantity: Mapped[int]
    unit_price: Mapped[Decimal]
    
    total: Mapped[Decimal] = column_property(
        quantity * unit_price,
        deferred=False,
    )

Adds a computed column to every SELECT: SELECT ..., quantity*unit_price AS total.

Note: column_property defaults to non-deferred (always loaded).

Computed (DB-side)

from sqlalchemy import Computed

class Product(Base):
    name: Mapped[str]
    name_lower: Mapped[str] = mapped_column(Computed("lower(name)", persisted=True))

DB-side generated column. persisted=True stores (indexable). persisted=False virtual.

Aggregates as column_property

class User(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    post_count: Mapped[int] = column_property(
        select(func.count(Post.id))
        .where(Post.author_id == id)
        .correlate_except(Post)
        .scalar_subquery(),
        deferred=True,
    )

Subquery as a column. deferred=True only loads when accessed.

hybrid_method

For methods that take arguments:

from sqlalchemy.ext.hybrid import hybrid_method

class User(Base):
    age: Mapped[int]
    
    @hybrid_method
    def older_than(self, years: int) -> bool:
        return self.age > years
    
    @older_than.expression
    @classmethod
    def older_than(cls, years: int):
        return cls.age > years

# Both work
user.older_than(18)
stmt = select(User).where(User.older_than(18))

Filtering on hybrid_property

# Define expression
@full_name.expression
@classmethod
def full_name(cls):
    return func.concat(cls.first, " ", cls.last)

# Filter
stmt = select(User).where(User.full_name.ilike("%alice%"))
# Note: not indexed unless you add an index on (first, last) and write the expression to match

For indexable searches: store + Computed column + GIN trigram index (Postgres).

Ordering

stmt = select(User).order_by(User.full_name)

Same expression used.

Combining with relationships

class Author(Base):
    posts: Mapped[list[Post]] = relationship(back_populates="author")
    
    post_count: Mapped[int] = column_property(
        select(func.count(Post.id)).where(Post.author_id == id).scalar_subquery(),
        deferred=True,
    )

deferred=True: doesn’t load with default SELECT. Trigger via undefer(...).

Common mistakes

Using @property then trying to filter on it → AttributeError from SQL side.
hybrid_property without .expression → SQL error when used in where().
column_property with non-deferred subquery → every SELECT runs the subquery (slow).
Heavy Computed expressions stored — write amplification.
Mismatch between Python and SQL expression (e.g., different string concat) — bugs.

FastAPI integration

class UserOut(BaseModel):
    id: int
    first: str
    last: str
    full_name: str          # populated from hybrid_property
    post_count: int         # from column_property
    
    model_config = {"from_attributes": True}

For column_property to load: include explicitly:

from sqlalchemy.orm import undefer

stmt = select(User).options(undefer(User.post_count))

Decision matrix#

Plain @property#

hybrid_property#

hybrid_property setter#

column_property#

Computed (DB-side)#

Aggregates as column_property#

hybrid_method#

Filtering on hybrid_property#

Ordering#

Combining with relationships#

Common mistakes#

FastAPI integration#

Read this next#