python 57 lines · 7 steps

Soft deletes with a custom SQLAlchemy Query in Flask

A mixin plus a custom query class hides soft-deleted rows by default while keeping an escape hatch to include them.

Explained by highlit
Flask Advanced soft-delete mixin query-subclass orm default-scope
1from datetime import datetime, timezone
2 
3from flask import abort
4from sqlalchemy import Column, DateTime
5from sqlalchemy.orm import Query, declarative_mixin
6 
7from app.extensions import db
8 
9 
10@declarative_mixin
11class SoftDeleteMixin:
12 deleted_at = Column(DateTime(timezone=True), nullable=True, index=True)
13 
14 @property
15 def is_deleted(self) -> bool:
16 return self.deleted_at is not None
17 
18 def soft_delete(self) -> None:
19 self.deleted_at = datetime.now(timezone.utc)
20 
21 def restore(self) -> None:
22 self.deleted_at = None
23 
24 
25class SoftDeleteQuery(Query):
26 _with_deleted = False
27 
28 def with_deleted(self) -> "SoftDeleteQuery":
29 clone = self.__class__(self._only_full_mapper_zero("get"), session=self.session)
30 clone._with_deleted = True
31 return clone
32 
33 def __iter__(self):
34 return super(SoftDeleteQuery, self._apply_default_filter()).__iter__()
35 
36 def _get_impl(self, *args, **kwargs):
37 return super(SoftDeleteQuery, self._apply_default_filter())._get_impl(*args, **kwargs)
38 
39 def _apply_default_filter(self) -> "SoftDeleteQuery":
40 if self._with_deleted:
41 return self
42 mapper = self._only_full_mapper_zero("get")
43 entity = mapper.class_
44 if issubclass(entity, SoftDeleteMixin):
45 return self.filter(entity.deleted_at.is_(None))
46 return self
47 
48 
49def get_or_404(model, ident, *, with_deleted: bool = False):
50 query = model.query.with_deleted() if with_deleted else model.query
51 instance = query.get(ident)
52 if instance is None:
53 abort(404, description=f"{model.__name__} {ident!r} not found")
54 return instance
55 
56 
57db.Model.query_class = SoftDeleteQuery
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Overriding the query class lets you inject a default filter transparently across every lookup.
  2. 2A nullable timestamp column models deletion state without ever losing the row.
  3. 3Cloning the query with a flag gives callers an explicit opt-in to bypass the default filter.

Related explainers

python 
from fastapi import FastAPI, WebSocket, WebSocketDisconnect
 
app = FastAPI()
 

Building a WebSocket chat with FastAPI

websockets broadcast connection-management
Intermediate 9 steps
python 
import time
import uuid
 
from django.utils.deprecation import MiddlewareMixin

Attaching per-request context in Django

middleware request lifecycle multi-tenancy
Intermediate 7 steps
python 
import random
from typing import Iterator, List
 
 

How reservoir sampling picks k items

reservoir-sampling streaming randomness
Intermediate 5 steps
python 
import secrets
 
from django.contrib.auth import authenticate, login
from django.core.cache import cache

Two-factor login with OTP in Django

two-factor-auth one-time-passwords caching
Intermediate 9 steps
python 
import re
from functools import total_ordering
from typing import Optional
 

Parsing and comparing semantic versions

regex operator-overloading sorting
Intermediate 7 steps
python 
from typing import Any, Sequence, Mapping
 
 
def render_markdown_table(

Rendering an aligned Markdown table in Python

string formatting data transformation closures
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

Soft deletes with a custom SQLAlchemy Query in Flask — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code