Mastering Soft Delete: Advanced SQLAlchemy Techniques

The Hidden Challenge in CRUD Operations: Deleting Data

So you are making CRUD APIs using SQLAlchemy and some python web framework like FastAPI or Flask. What is the most terrible part in CRUD? DELETE may be? Once an entry is deleted from the database, restoring it can be a painful process. That’s why it’s generally recommended to use soft deletes instead of hard or permanent deletes.

Why Soft Deletes Matter

In a soft delete, instead of removing the data entirely, we add a “delete flag” column to the table. When a user wants to delete a record, we simply set this flag to True. Then, when querying the database, we filter out rows where the delete flag is set. However, this can become cumbersome when dealing with many tables or data models with delete flags. As you add more APIs, you end up repetitively applying the same filter, making your code WET💧 (Write Everything Twice).

A Better Approach with with_loader_criteria

So here comes the with_load_criteria ORM option to rescue us, especially when used in conjunction with the do_orm_execute ORM event.

from sqlalchemy import create_engine, event
from sqlalchemy.orm import ORMExecuteState, Session, sessionmaker, with_loader_criteria

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

class VisibilityMixin:
    is_deleted = Column(Boolean, server_default="0")

class Human(DeclarativeBase, VisibilityMixin):
    __tablename__ = "humans"

class Alien(DeclarativeBase, VisibilityMixin):
    __tablename__ = "aliens"

@event.listens_for(SessionLocal, "do_orm_execute")
def _add_filtering_criteria(execute_state: ORMExecuteState):
    if execute_state.is_select:
        execute_state.statement = execute_state.statement.options(
            with_loader_criteria(
                VisibilityMixin,
                lambda cls: cls.is_deleted.is_(False),
                include_aliases=True,
            )
        )

In this example, the do_orm_execute event listener automatically adds a filter to every SELECT statement, ensuring that rows marked as deleted are excluded from the results.

Handling Special Cases: Skipping the Filter

While applying a global soft delete filter across all queries is usually beneficial, there are situations where you might need to bypass this filter — for example, when you need to retrieve all records, including those marked as deleted. Fortunately, SQLAlchemy provides a flexible way to achieve this by using execution options.

Here’s how you can implement this:

from sqlalchemy import create_engine, Column, Boolean, event
from sqlalchemy.orm import sessionmaker, with_loader_criteria, Session
from sqlalchemy.ext.declarative import declarative_base
from fastapi import FastAPI, Depends

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)
DeclarativeBase = declarative_base()
app = FastAPI()

class VisibilityMixin:
    is_deleted = Column(Boolean, server_default="0")

class Human(DeclarativeBase, VisibilityMixin):
    __tablename__ = "humans"

@event.listens_for(SessionLocal, "do_orm_execute")
def _add_filtering_criteria(execute_state):
    skip_filter = execute_state.execution_options.get("skip_filter", False)
    if execute_state.is_select and not skip_filter:
        execute_state.statement = execute_state.statement.options(
            with_loader_criteria(
                VisibilityMixin,
                lambda cls: cls.is_deleted.is_(False),
                include_aliases=True,
            )
        )

@app.get("/everything")
async def everything(db: Session = Depends(get_db)):
    return db.query(Human).execution_options(skip_visibility_filter=True).all()

Conclusion

In this article, we’ve explored how to simplify soft deletes in SQLAlchemy by leveraging with_loader_criteria and the do_orm_execute event. This method keeps your code DRY and ensures that your CRUD operations remain consistent and maintainable as your application scales.

Additionally, we discussed how to handle scenarios where you might need to bypass the global filter using execution options. By setting skip_visibility_filter to True, you can selectively disable the soft delete filter for specific queries, providing you with greater flexibility and control.

By integrating these advanced SQLAlchemy features, you can streamline your workflow, avoid repetitive filtering logic, and maintain cleaner, more efficient code.