Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 37 ยท 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