Add optional soft delete support

[Sakura0122]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

from datetime import datetime

from sqlmodel import Field, Session, SQLModel, create_engine, select


class SoftDeleteMixin:
    deleted_at: datetime | None = Field(default=None)


class Hero(SQLModel, SoftDeleteMixin, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    hero = Hero(name="Deadpond")
    session.add(hero)
    session.commit()

    session.delete(hero)
    session.commit()

    heroes = session.exec(select(Hero)).all()
    print(heroes)

Description

I would like to ask whether SQLModel maintainers would consider adding optional soft delete support.

There was a previous PR for this feature: #1890

That PR was closed due to inactivity. It added delete interception, but it did not include query filtering and used a fixed deleted_at field.

The API I would like to discuss could include:

• SoftDeleteMixin, using deleted_at by default
• SoftDeleteSession.delete(), which marks soft-deletable models as deleted instead of physically deleting them
• default filtering for SoftDeleteSession.exec(select(...)), so soft-deleted rows are hidden by default
• an option such as include_deleted=True to include soft-deleted rows
• support for custom soft delete field names, for example with __soft_delete_field__

Example usage:

class Hero(SQLModel, SoftDeleteMixin, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

with SoftDeleteSession(engine) as session:
    session.delete(hero)
    session.commit()

    heroes = session.exec(select(Hero)).all()

By default, heroes would not include soft-deleted rows.

For custom field names:

class Hero(SQLModel, table=True):
    __soft_delete_field__ = "removed_at"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    removed_at: datetime | None = Field(default=None)

Would this kind of optional soft delete API be welcome in SQLModel?

Operating System

macOS

Operating System Details

No response

Project Version

0.0.39

Python Version

No response

Additional Context

No response

[VectorPeak]

This is a reasonable feature to consider, but it should probably be scoped pretty carefully. Soft delete looks small at the model level, but once it becomes part of Session behavior it touches a lot of ORM semantics: selects, aliases, joins, relationship loading, cascades, bulk deletes, and escape hatches for admin/maintenance code.

The delete() interception part is the simpler piece. Something like SoftDeleteMixin plus a SoftDeleteSession.delete() override is understandable and matches what many users expect:

class SoftDeleteMixin:
 deleted_at: datetime | None = Field(default=None)

class Hero(SQLModel, SoftDeleteMixin, table=True):
 id: int | None = Field(default=None, primary_key=True)
 name: str

For the session API, I would avoid making soft delete implicit in the normal Session class. A separate SoftDeleteSession seems safer because it keeps existing behavior unchanged and makes the feature opt-in:

with SoftDeleteSession(engine) as session:
 session.delete(hero) # sets deleted_at
 session.commit()

The harder and more important part is query filtering. If session.delete(hero) only sets deleted_at, but session.exec(select(Hero)) still returns that row, the feature is incomplete and users will have to remember to add Hero.deleted_at == None everywhere. That is the kind of behavior that tends to create subtle bugs.

For the “include deleted” escape hatch, I’d lean toward a SQLAlchemy-style execution option rather than adding a special keyword to every exec() call:

heroes = session.exec(select(Hero)).all()

all_heroes = session.exec(
 select(Hero).execution_options(include_deleted=True)
).all()

That keeps the query object carrying the behavior and avoids widening the Session.exec() API too much. Internally, the filtering would likely need to be based on SQLAlchemy ORM execution hooks, probably using with_loader_criteria() for mapped classes that opt into soft delete. That would also make relationship loads more consistent than only modifying top-level select(Hero) statements.

Custom field names are useful, but they also make this more than just a mixin. If __soft_delete_field__ = "removed_at" is supported, the implementation should validate that the field exists on the mapped model and that it is nullable / comparable to None. Otherwise failures will be confusing and will happen late, during query execution.

class Hero(SQLModel, table=True):
 __soft_delete_field__ = "removed_at"

 id: int | None = Field(default=None, primary_key=True)
 name: str
 removed_at: datetime | None = Field(default=None)

The main cases I would want covered before merging something like this are:

• session.delete(obj) marks the configured field instead of issuing DELETE
• a hard-delete bypass exists and is explicit
• select(Model) hides soft-deleted rows by default
• execution_options(include_deleted=True) includes them again
• joined/relationship-loaded soft-deletable models behave consistently
• models without the mixin or configured field are unaffected
• custom soft delete field names work and fail clearly when misconfigured

One detail worth deciding early is whether soft delete should cascade. SQLAlchemy’s normal delete cascade semantics do not map perfectly to “set deleted_at on each related object”, and doing that automatically could surprise users. It may be better for an initial version to only soft-delete the instance passed to delete() and leave relationship/cascade behavior out unless there is a very explicit design for it.

---

If my answer solved your problem, you can click answered the question. I'm really here to help, and along the way I'm also collecting Galaxy Brain badges haha 😆