Implementing Soft Deletes with ORM: Handling Deleted Records

In many applications, it is common to implement a soft delete functionality to handle deleted records. Soft deletes allow for the retention of data, while still giving the appearance that the records have been deleted. This can be useful for data auditing purposes, as well as for recovering accidentally deleted records.

In this blog post, we will explore how to implement soft deletes using an Object-Relational Mapping (ORM) library. We will discuss the steps involved in configuring the ORM to handle soft deletes, as well as the implications of using this feature.

What are Soft Deletes?

Soft deletes involve marking records as deleted instead of physically removing them from the database. This is typically done by adding a deleted_at column to the table, which stores the timestamp of when the record was marked as deleted. When querying the data, the ORM can filter out deleted records by excluding those with a non-null deleted_at value.

Configuring the ORM for Soft Deletes

To implement soft deletes using an ORM, we need to make a few changes to our model and database configurations.

1. Model Changes:

   • Add a deleted_at column to the model as a nullable timestamp.
   • Override the model's delete() method to update the deleted_at column instead of physically deleting the record.

2. Database Schema:

   • Add a deleted_at column to the database table as a nullable timestamp. Ensure indexes and foreign key constraints are correctly maintained.

3. ORM Configuration:

   • Configure the ORM to recognize the deleted_at column as the soft delete marker.
   • Update the ORM's query methods to automatically exclude soft deleted records when querying the data.

Example using Python and SQLAlchemy

Let's walk through an example using the Python ORM library SQLAlchemy.

1. Model Changes:

from sqlalchemy import Column, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import functions

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    # ... other column definitions ...
    deleted_at = Column(DateTime, nullable=True)

    def delete(self):
        self.deleted_at = functions.now()  # Set the deleted_at column to the current timestamp

2. Database Schema:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    -- ... other column definitions ...
    deleted_at TIMESTAMP
);

ALTER TABLE users ADD INDEX idx_deleted_at (deleted_at);

3. ORM Configuration:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql://username:password@localhost/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()

def exclude_deleted(query):
    return query.filter(User.deleted_at.is_(None))

# Example usage
users = session.query(User).filter(User.id == 1)
users = exclude_deleted(users).all()

Conclusion

Soft deletes provide a way to handle deleted records in a more controlled manner, allowing for data retention and recovery options. By configuring your ORM to handle soft deletes, you can easily implement this functionality in your applications. Using SQLAlchemy as an example, we have seen the necessary steps involved in implementing soft deletes and querying non-deleted records.

本文来自极简博客，作者：墨色流年，转载请注明原文链接：Implementing Soft Deletes with ORM: Handling Deleted Records