Filtering soft-deleted rows with with_loader_criteria

Apply the filter once with with_loader_criteria(Post, Post.deleted_at.is_(None), include_aliases=True) — it reaches the entity wherever it appears, including inside selectinload() — and register it for every query from a do_orm_execute listener with an include_deleted execution option as the escape hatch. This guide belongs to hybrid properties, column properties and SQL expressions.

Quick Answer

A soft delete is only as good as its least careful query. Repeating the predicate by hand fails in two ways: someone forgets it, and eager loads never had it.

Before — the predicate repeated, and missing from the eager load:

from sqlalchemy import select
from sqlalchemy.orm import selectinload

from blog.models import Comment, Post

# Filters the posts, but loads deleted comments with them.
stmt = (
    select(Post)
    .where(Post.deleted_at.is_(None))
    .options(selectinload(Post.comments))
)

After — one option, applied wherever the entity appears:

from sqlalchemy import select
from sqlalchemy.orm import selectinload, with_loader_criteria

from blog.models import Comment, Post

stmt = (
    select(Post)
    .options(
        selectinload(Post.comments),
        with_loader_criteria(Post, Post.deleted_at.is_(None), include_aliases=True),
        with_loader_criteria(Comment, Comment.deleted_at.is_(None), include_aliases=True),
    )
)
# SELECT ... FROM posts WHERE posts.deleted_at IS NULL
# SELECT ... FROM comments WHERE comments.post_id IN (...) AND comments.deleted_at IS NULL

include_aliases=True makes the criteria apply to aliased(Post) too, which matters as soon as a query joins the same entity twice or uses a subquery alias.

For a model-wide rule, register it once for a mixin instead of per query — the next section does that, and keeps an escape hatch for the queries that need deleted rows.

Execution Context & Async Workflow Integration

with_loader_criteria() is a loader option that carries a WHERE fragment and an entity to apply it to. Unlike a .where() clause, which applies to one place in one statement, it is applied wherever that entity appears while the statement is compiled — the top-level FROM, the IN subquery selectinload() builds, the LEFT OUTER JOIN joinedload() builds — and, because propagate_to_loaders defaults to True, it is remembered for lazy loads issued later from the objects the statement returned.

That propagation is what makes it the right tool for soft deletion, and it is also the part worth testing, because the criteria reaches places that are easy to overlook.

Applying it globally is a documented recipe: a do_orm_execute listener that appends the option to every ORM SELECT.

import datetime as dt

from sqlalchemy import event, select
from sqlalchemy.orm import Session, declarative_mixin, with_loader_criteria
from sqlalchemy.orm import Mapped, mapped_column


@declarative_mixin
class SoftDeleteMixin:
    deleted_at: Mapped[dt.datetime | None] = mapped_column(default=None, index=True)


@event.listens_for(Session, "do_orm_execute")
def _apply_soft_delete_filter(execute_state) -> None:
    if (
        execute_state.is_select
        and not execute_state.is_column_load
        and not execute_state.is_relationship_load
        and not execute_state.execution_options.get("include_deleted", False)
    ):
        execute_state.statement = execute_state.statement.options(
            with_loader_criteria(
                SoftDeleteMixin,
                lambda cls: cls.deleted_at.is_(None),
                include_aliases=True,
            )
        )

Four details in that condition matter. is_select restricts it to reads. is_column_load excludes attribute refreshes, where adding criteria would break a load for a row that is already known. is_relationship_load excludes lazy loads, which already inherit the criteria from the statement that loaded their parent — applying it twice is harmless but wasteful. And the include_deleted execution option is the escape hatch, without which an admin screen or a restore flow could not see the rows at all.

Passing the criteria as a lambda taking cls is what lets one registration cover every class using the mixin: SQLAlchemy calls it per entity, so cls is the actual mapped class each time.

The listener is registered on the synchronous Session class, which every AsyncSession wraps, so it applies to async code unchanged. Because the filter is added at execution time, it is also part of the cache key, so statements with and without include_deleted are compiled and cached separately.

One gap to know about: session.get() for an object already in the identity map returns it without issuing a query, so no criteria can apply. A soft-deleted row fetched earlier in the same session is still reachable by get(). Where that matters — an endpoint loading by id, then acting on the object — check the flag explicitly, or use select() with .where().

Resolving Warnings, Errors & Common Mistakes

The partial unique index is the schema change teams most often discover late, usually when a user deletes their account and cannot sign up again with the same address:

from sqlalchemy import Index, text
from sqlalchemy.orm import Mapped, mapped_column


class User(SoftDeleteMixin, Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]

    __table_args__ = (
        Index(
            "uq_users_email_live",
            "email",
            unique=True,
            postgresql_where=text("deleted_at IS NULL"),
        ),
    )

Only live rows participate, so one address can be reused after a delete while remaining unique among active users. Build it with CREATE UNIQUE INDEX CONCURRENTLY on an existing table, as in creating indexes concurrently in Alembic migrations, and drop the old constraint only after the new index is valid.

The other gap worth planning for is that soft deletion does not cascade. session.delete(post) is not involved at all, so no relationship cascade runs: soft-deleting a post leaves its comments live. Decide per relationship whether children should be soft-deleted with the parent, and if so do it in one statement:

from sqlalchemy import func, update

from blog.models import Comment, Post


async def soft_delete_post(session, post_id: int) -> None:
    now = func.now()
    await session.execute(
        update(Post).where(Post.id == post_id).values(deleted_at=now)
    )
    await session.execute(
        update(Comment)
        .where(Comment.post_id == post_id, Comment.deleted_at.is_(None))
        .values(deleted_at=now)
    )
    await session.commit()

Advanced: Restores, Retention and Intercepting Deletes

Three operational requirements follow a soft delete into production, and each is easier if it is designed alongside the filter rather than after it.

Restore is an update, and it has to reverse exactly what the delete did — including children:

from sqlalchemy import select, update

from blog.models import Comment, Post


async def restore_post(session, post_id: int) -> None:
    post = await session.scalar(
        select(Post).where(Post.id == post_id).execution_options(include_deleted=True)
    )
    if post is None or post.deleted_at is None:
        raise ValueError("post is not deleted")
    deleted_at = post.deleted_at
    await session.execute(update(Post).where(Post.id == post_id).values(deleted_at=None))
    # Only the comments deleted *with* the post, not ones deleted earlier.
    await session.execute(
        update(Comment)
        .where(Comment.post_id == post_id, Comment.deleted_at == deleted_at)
        .values(deleted_at=None)
    )
    await session.commit()

Matching on the exact timestamp is what makes the restore honest: a comment the author deleted last week stays deleted. That only works because the cascade wrote one timestamp for the whole operation, which is why func.now() was evaluated once above.

Retention turns soft deletes into hard ones eventually, both to keep tables small and because a deletion request under data-protection rules is not satisfied by a flag. A scheduled job deletes rows whose deleted_at is older than the retention window, in batches, with the escape hatch set:

import datetime as dt

from sqlalchemy import delete

from blog.models import Post


async def purge_deleted(session, older_than: dt.timedelta) -> int:
    cutoff = dt.datetime.now(dt.UTC) - older_than
    result = await session.execute(
        delete(Post)
        .where(Post.deleted_at.is_not(None), Post.deleted_at < cutoff)
        .execution_options(include_deleted=True)
    )
    await session.commit()
    return result.rowcount

Intercepting real deletes closes the loophole where code calls session.delete() and bypasses the whole scheme. A before_delete mapper event can refuse it outright, which is blunt and effective during a migration to soft deletes:

from sqlalchemy import event
from sqlalchemy.orm import Mapper


@event.listens_for(SoftDeleteMixin, "before_delete", propagate=True)
def _block_hard_delete(mapper: Mapper, connection, target) -> None:
    if not getattr(target, "_allow_hard_delete", False):
        raise RuntimeError(
            f"{type(target).__name__} uses soft deletion; set deleted_at instead"
        )

It catches only ORM deletes — a set-based delete() never loads the object — so pair it with code review on bulk statements, or with database privileges that deny DELETE to the application role and grant it to the purge job alone.

Choosing Soft Deletion in the First Place

Soft deletion is often adopted as an obvious default and then regretted, because its cost is not in the column — it is in the fact that every future query must know about it. Two questions decide whether it is the right shape.

Who restores the data, and how often? If users undelete their own items — a trashcan, a draft, an archived conversation — soft deletion is exactly right: the restore is instant, the row never moves, and references to it stay valid. If restores happen once a year, by an engineer, from a support ticket, then an archive table is simpler: the live tables stay small, no global filter is needed, and nothing else in the system has to be aware of the scheme.

How large does the table get? Dead rows still occupy the table and every index on it. A table where most rows are deleted makes every index scan read entries it will discard, and the fix — partial indexes on WHERE deleted_at IS NULL — has to be applied to each index individually. At that point moving rows out is usually cheaper than filtering them out, and the move is one statement:

from sqlalchemy import delete, insert, select

from blog.models import Post, PostArchive

COLUMNS = ["id", "title", "body", "author_id", "published_at", "deleted_at"]

moved = (
    delete(Post)
    .where(Post.deleted_at.is_not(None))
    .returning(*(Post.__table__.c[name] for name in COLUMNS))
    .cte("moved")
)
archive = insert(PostArchive).from_select(
    COLUMNS, select(*(moved.c[name] for name in COLUMNS))
)

That is the data-modifying CTE pattern from using CTEs with INSERT, UPDATE and DELETE ... RETURNING, and it is atomic: a row cannot be archived without being deleted.

A middle path suits many systems: soft-delete for a short, user-visible window — thirty days of trash — and archive or purge after it. The live table then holds only recent deletions, the global filter stays cheap, and long-term storage is a table nobody queries on the hot path.

Whichever path you take, write down which one it is. The failure mode of soft deletion is not a bug in the mechanism; it is a codebase where half the queries assume the filter is automatic and half assume it is manual, and the rows that leak are the ones nobody was looking at.

Frequently Asked Questions

Related

• Hybrid Properties, Column Properties and SQL Expressions — The parent guide: derived values and criteria applied in SQL.
• Building dynamic filters and sorting from API query parameters — Combining caller-supplied filters with global criteria.
• Configuring cascade delete and delete-orphan correctly — What real deletes do, and why soft deletes do not cascade.
• Using CTEs with INSERT, UPDATE and DELETE ... RETURNING — Archiving rows instead of flagging them.