Skip to content

Lesson 34 ยท Performance and Patterns

Hybrid Properties, Custom Types, Mixins and Soft Delete

Write attributes that work in Python and in SQL with hybrid_property, store custom values with TypeDecorator, reuse columns with mixins and hide soft-deleted rows.

Most ORM code starts plain: columns, relationships, queries. Over time you notice repeated ideas: a computed value that you want both on an object and inside a WHERE, a value that needs converting on the way in and out of the database, the same created_at column on twenty tables, "deleted" rows that must never show up. SQLAlchemy has a tool for each, and together they keep models small and queries honest.

hybrid_property: one attribute, two worlds

A normal Python @property works on objects but cannot be used in a query. A hybrid property works at both levels. On an instance it runs your Python code. On the class it produces a SQL expression, so you can filter and sort with it.

Python A hybrid full_name and a hybrid expression

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When the Python expression does not translate to SQL (a conditional, a method call like .lower() on a string), give the hybrid a separate SQL version with the .expression decorator:

Python
from sqlalchemy import case

@hybrid_property
def tier(self):                       # Python side
    return "senior" if self.salary >= 100_000 else "regular"

@tier.expression
def tier(cls):                        # SQL side
    return case((cls.salary >= 100_000, "senior"), else_="regular")

Use hybrids for derived values with no storage of their own. If the value is expensive, store it in a column instead, or use column_property(select(...).scalar_subquery()) to load a correlated subquery with the object.

TypeDecorator: custom column types

Sometimes the value you want in Python is not the value you store. Money as integer cents, a set of tags as comma-separated text, a timezone-aware datetime stored as UTC. A TypeDecorator wraps an existing column type and converts in both directions.

Python Money stored as cents, JSON-free tag lists

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Always set cache_ok = True (when your type has no mutable configuration) so SQLAlchemy can cache compiled statements that use it. Notice that the filter Product.price < Decimal("5") is converted by the same process_bind_param, so comparisons use cents in the database.

Note that SQLAlchemy already ships Numeric, JSON, Enum, DateTime(timezone=True) and (on PostgreSQL) ARRAY; check for a built-in type before writing your own.

Mixins: share columns across models

A mixin is a plain Python class holding columns and methods that several models reuse. With the 2.0 style, columns in a mixin are copied into every subclass automatically.

Python TimestampMixin and soft delete

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The do_orm_execute hook adds a WHERE deleted_at IS NULL to every ORM SELECT against any class using SoftDeleteMixin, including relationship loads, so "deleted" rows really disappear from the application. The option include_deleted=True is the escape hatch for admin screens.

Soft deletion has costs. Unique constraints still see deleted rows (a deleted user's email blocks a new sign-up unless you use a partial unique index), and foreign keys still point at them. Choose it when you need an undo or an audit trail, not by default.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What makes a hybrid_property different from a normal @property?

    Accessed on the class, the hybrid is evaluated against columns and returns a SQL expression.
  2. 2. When do you add a separate @name.expression to a hybrid?

    The expression version lets you write the SQL equivalent, for example with case().
  3. 3. What are the two methods you implement in a TypeDecorator?

    One converts the Python value on the way into the database, the other on the way out.
  4. 4. What does a soft-delete filter through do_orm_execute and with_loader_criteria achieve?

    The criteria is added to all matching loads, so application code does not repeat the filter.

Summary

  • hybrid_property and hybrid_method run Python on instances and build SQL on the class.
  • Add .expression when the Python version cannot be turned into SQL directly.
  • TypeDecorator converts values in both directions; set cache_ok = True.
  • Mixins share columns and methods across models.
  • with_loader_criteria plus do_orm_execute implements soft delete, at a cost in unique constraints and foreign keys.

Next: Project: A Blog Backend with SQLAlchemy