KAHIBARO
Discord Login Register ☰
☰

12.8. Deleting Records

Table of Contents

Understanding Deleting Records with an ORM

When you work with an ORM, deleting data is not just about removing rows from a table. You need to think about object state, transactions, constraints, relationships, and safety. In this chapter, you will see how deleting records works conceptually with an ORM, with many examples inspired by SQLAlchemy, but the ideas apply to most ORMs.

Basic Ways to Delete a Record

There are two common ways to delete data with an ORM:

  1. Delete a loaded object instance.
  2. Issue a bulk delete using a query.

Deleting a single loaded instance

This is the most explicit and usually the safest way. You first load an object from the database, then ask the ORM to delete it:

python 
user = session.get(User, 1)  # load user with primary key 1
if user is not None:
    session.delete(user)
    session.commit()

What happens here:

  1. session.get loads the User row with id = 1.
  2. session.delete(user) marks this instance as "deleted" in the session.
  3. session.commit() actually sends a DELETE FROM users WHERE id = 1 to the database.

Until you call commit, no row is actually removed in the database.

You can also roll back:

python 
user = session.get(User, 1)
session.delete(user)
session.rollback()  # delete is cancelled

After rollback, the user row still exists in the database.

Bulk delete via query

Bulk deletes act directly in the database. You do not need to load each record as an object.

python 
from sqlalchemy import delete
stmt = delete(User).where(User.is_inactive == True)
result = session.execute(stmt)
session.commit()
print(result.rowcount)  # number of deleted rows

In many ORMs the syntax differs, but the pattern is similar:

Bulk deletes usually do not trigger normal ORM object lifecycle hooks or relationship behavior in the same way as deleting individual objects. Use them carefully.

Object States During Deletion

ORMs track the "state" of objects:

StateMeaning
TransientNot in session, not in database
PendingIn session, not yet in database (will be inserted)
PersistentIn session and present in database
DeletedIn session, marked for deletion, not yet committed
DetachedNo longer associated with a session

Deleting works only on persistent instances.

Life cycle example

python 
user = User(name="Alice")   # transient
session.add(user)           # pending
session.commit()            # persistent (row exists in DB)
session.delete(user)        # deleted (marked)
session.commit()            # row removed from DB, user becomes detached

After the final commit, attempts to use user.id are fine, but reloading data from the DB for that user will fail because the row is gone.

Deleting by Primary Key

A very common pattern is delete-by-id.

Load then delete

python 
def delete_user_by_id(session, user_id: int) -> bool:
    user = session.get(User, user_id)
    if user is None:
        return False
    session.delete(user)
    session.commit()
    return True

You can return a boolean, raise an exception, or silently ignore missing rows, depending on your API design.

Delete directly with a filter

python 
from sqlalchemy import delete
def delete_user_by_id(session, user_id: int) -> int:
    stmt = delete(User).where(User.id == user_id)
    result = session.execute(stmt)
    session.commit()
    return result.rowcount

Conditional Deletes

You often need to delete only some rows that match a condition.

Delete inactive users older than a date

python 
from datetime import datetime, timedelta
from sqlalchemy import delete
cutoff = datetime.utcnow() - timedelta(days=365)
stmt = delete(User).where(
    User.is_active == False,
    User.last_login < cutoff,
)
result = session.execute(stmt)
session.commit()
print(f"Deleted {result.rowcount} users")

Delete all tasks of a user

python 
from sqlalchemy import delete
def delete_tasks_for_user(session, user_id: int) -> int:
    stmt = delete(Task).where(Task.user_id == user_id)
    result = session.execute(stmt)
    session.commit()
    return result.rowcount

Use conditions to avoid deleting too much. For safety, many teams avoid DELETE without a WHERE clause.

Cascading Deletes and Relationships

When you delete a record that has related rows, what happens to those rows?

You must actively decide this behavior in your schema and ORM configuration.

Types of cascading behavior

On the database side, typical options are:

BehaviorDescription
RESTRICT / nonePrevent delete if children exist
CASCADEDelete children automatically
SET NULLSet foreign key to NULL in child rows

In an ORM, you often see similar options as relationship parameters, for example in SQLAlchemy:

Example: User and Posts

Imagine:

python 
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    posts = relationship(
        "Post",
        back_populates="author",
        cascade="all, delete-orphan",
    )
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)
    author = relationship("User", back_populates="posts")
Deleting a user
python 
user = session.get(User, 1)
session.delete(user)
session.commit()

Because of cascade="all, delete-orphan":

If you remove the cascade option, the database may prevent deleting the user if there are related posts, depending on the foreign key constraint.

Always understand your delete cascade rules. A single session.delete(user) can remove a long chain of related records if cascades are configured.

ORM vs Database Cascades

You can configure cascade at two levels:

  1. In the ORM relationship.
  2. In the database foreign key.

ORM-level cascade

The ORM loads and deletes child objects in memory according to the relationship settings. This works even if the database foreign key does not have ON DELETE CASCADE.

Example behavior:

Database-level cascade

The database itself handles cascade deletes using ON DELETE CASCADE in the foreign key definition.

For example, in SQL:

sql 
ALTER TABLE posts
ADD CONSTRAINT posts_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE;

Now, when a user row is deleted, the database removes posts that reference it, even if you used a bulk delete that the ORM does not fully track.

In SQLAlchemy-style ORM, you can hint this with:

python 
user_id = Column(
    Integer,
    ForeignKey("users.id", ondelete="CASCADE"),
)

Combined approaches are common. For example:

Safe Deletion Patterns

Completely removing data is risky if you make a mistake. Many applications use "soft deletes" for safety.

Hard delete vs soft delete

TypeWhat happensProsCons
Hard deleteRow is removed from the tableSaves space, simpler queriesData cannot be recovered
Soft deleteRow is marked as deleted but not removedRecoverable, good for auditsQueries must filter out deleted rows

Implementing soft delete

Add a deleted_at or is_deleted column.

python 
from datetime import datetime
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    deleted_at = Column(DateTime, nullable=True)

Instead of:

python 
session.delete(user)
session.commit()

You do:

python 
def soft_delete_user(user: User):
    user.deleted_at = datetime.utcnow()
    # ORM will issue UPDATE, not DELETE

Then, change your queries to ignore deleted users:

python 
active_users = (
    session.query(User)
    .filter(User.deleted_at.is_(None))
    .all()
)

If you implement soft delete, every query that should ignore deleted records must filter by the soft delete flag. Forgetting this leaks "deleted" data back into your application.

Some ORMs support "query filters" or "scopes" that automatically apply deleted_at IS NULL to every query. These are very helpful.

Transactions When Deleting

Deletes should almost always be part of a transaction. Transactions ensure that either:

Delete with try / except / rollback

python 
def delete_user_and_posts(session, user_id: int):
    try:
        user = session.get(User, user_id)
        if user is None:
            return False
        session.delete(user)  # cascade may delete posts
        session.commit()
        return True
    except Exception:
        session.rollback()
        raise

This pattern:

Bulk delete inside a transaction

python 
from sqlalchemy import delete
def cleanup_old_logs(session):
    try:
        stmt = delete(Log).where(Log.created_at < some_date)
        session.execute(stmt)
        session.commit()
    except Exception:
        session.rollback()
        raise

Handling Missing Records

Deleting something that is not there should be clearly defined in your API.

Options:

  1. Return False or 0 if nothing was deleted.
  2. Raise a "Not Found" error.
  3. Silently do nothing.

Example: Raise an exception if not found

python 
class NotFoundError(Exception):
    pass
def delete_task(session, task_id: int):
    task = session.get(Task, task_id)
    if task is None:
        raise NotFoundError(f"Task {task_id} not found")
    session.delete(task)
    session.commit()

In a REST API, you might translate this to an HTTP 404 response.

Deleting With a Repository Pattern

In many codebases, direct session access is hidden behind repositories. A repository isolates data access logic.

Simple repository example

python 
class UserRepository:
    def __init__(self, session):
        self.session = session
    def get(self, user_id: int) -> User | None:
        return self.session.get(User, user_id)
    def delete(self, user: User) -> None:
        self.session.delete(user)
    def delete_by_id(self, user_id: int) -> bool:
        user = self.get(user_id)
        if user is None:
            return False
        self.delete(user)
        return True

Then your service layer can manage transactions:

python 
def delete_user_service(session, user_id: int) -> bool:
    repo = UserRepository(session)
    try:
        deleted = repo.delete_by_id(user_id)
        session.commit()
        return deleted
    except Exception:
        session.rollback()
        raise

This keeps session and transaction logic in one place and makes it easier to test.

Bulk Deletes vs Per-Object Deletes

Choosing between bulk delete and per-object delete involves trade-offs.

Per-object delete

python 
users = session.query(User).filter(User.is_inactive == True).all()
for user in users:
    session.delete(user)
session.commit()

Pros:

Cons:

Bulk delete

python 
from sqlalchemy import delete
stmt = delete(User).where(User.is_inactive == True)
session.execute(stmt)
session.commit()

Pros:

Cons:

Do not use bulk deletes when you rely on ORM cascades or per-object validation logic. Use them for pure data cleanup tasks where you fully understand the consequences.

Example: Deleting a Record in a REST API

Imagine a FastAPI endpoint that deletes a task. At this point, you already have routing and response handling in other chapters, so we only focus on the deletion behavior.

python 
from fastapi import APIRouter, HTTPException, status, Depends
from sqlalchemy.orm import Session
router = APIRouter()
@router.delete(
    "/tasks/{task_id}",
    status_code=status.HTTP_204_NO_CONTENT,
)
def delete_task_endpoint(
    task_id: int,
    session: Session = Depends(get_session),
):
    task = session.get(Task, task_id)
    if task is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Task not found",
        )
    session.delete(task)
    session.commit()
    # 204 No Content, so no body is returned

Key points:

Summary

Deleting records with an ORM is more than just DELETE FROM table:

With these patterns, you can remove data safely and predictably in your backend applications.

Views: 5

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3442 / 24h
Impressum / Imprint
Datenschutz / Data Policy