The Open Event Server enables organizers to manage events from concerts to conferences and meetups. It offers features for events with several tracks and venues. This blog post explains how the soft deletion support has been implemented in the project to allow the event admin to quickly restore the deleted events or users.

Idea

The idea is extremely simple. We can add a column deleted_at in all the soft deleted models. If that entry is None then we can be sure that this row wasn’t deleted or else we can conclude that the row was deleted by the user. In case we want to recover it later, we can simply set it back to None.

Approach

We had a lot of models that needed this support. Hence in order to obey the DRY principles, we created base model and schema classes which would be inherited by the models and schemas that need this support.

from marshmallow_jsonapi import fields
from marshmallow_jsonapi.flask import Schema


class SoftDeletionSchema(Schema):
    """
        Base Schema for soft deletion support. All the schemas that support soft deletion should extend this schema
    """
    deleted_at = fields.DateTime(allow_none=True)

from app.models import db


class SoftDeletionModel(db.Model):
    """
        Base model for soft deletion support. All the models which support soft deletion should extend it.
    """
    __abstract__ = True

    deleted_at = db.Column(db.DateTime(timezone=True))

Usage

We use the Flask-Rest-Json-API library in the project. We have modified it in order to support the soft deletion feature. By default all the models that support soft deletion are soft deleted. In order to specify hard deletion, an extra parameter permanent set to true needs to be passed in with the request.

DELETE

We check if the model supports soft deletion or not. If the model doesn’t support soft deletion then we permanently delete it otherwise we check if we have the parameter permanent and it is set to true. If it is set to true, then we permanently delete the row otherwise we update the deleted_at with the current time.

if 'deleted_at' not in self.schema._declared_fields or request.args.get('permanent') == 'true' or current_app.config['SOFT_DELETE'] is False:
            self._data_layer.delete_object(obj, kwargs)
else:
    data = {'deleted_at': str(datetime.now(pytz.utc))}
    self._data_layer.update_object(obj, data, kwargs)

GET

When we are returning one or more entry we offer the client the option to choose whether the soft deleted entries should be included or not. By default they aren’t. In order to get them a query parameter get_trashed should be passed along with the URL.

if request.args.get('get_trashed') == 'true':
    obj = self._data_layer.get_object(kwargs, get_trashed=True)
else:
    obj = self._data_layer.get_object(kwargs)

In case soft deleted entries are not required, then we filter the results for cases when deleted_at is None

try:
    if 'deleted_at' not in self.resource.schema._declared_fields or get_trashed or current_app.config['SOFT_DELETE'] is False:
         obj = self.session.query(self.model).filter(filter_field == filter_value).one()
    else:
         obj = self.session.query(self.model).filter(filter_field == filter_value).filter_by(deleted_at=None).one()

References

• Flask REST JSON API: flask-rest-jsonapi
• SQLAlchemy: https://www.sqlalchemy.org/
• Physical vs Logical soft delete of database record: https://stackoverflow.com/questions/378331/physical-vs-logical-soft-delete-of-database-record