Soft Deletes as the responsibility of the Database

Using triggers, key generators and a second table for delete & restore functionality

Table Of Contents

Today I Explained

You’ve likely heard of the concept of “soft delete” within tables. These is where a flag exists on a row called deleted (or similarly named), that can be toggled to indicate whether an entry has been “deleted”. This doesn’t remove the row from the table, and keeps it around allowing a user to restore it (set the flag to false) at a future date. Should it not be toggled back, after some time it will be removed from the database, based on a deleted-at and expiration policy.

A frustration that comes with this approach, is that it now becomes necessary to take into consideration the deleted flag when doing queries like SELECT * FROM table;, as any table will contain both active & “deleted” entries. This results in various approaches that leak the implementation details around the deleted field across the database in views, stored functions or the dynamic SQL queries.

An alternative approach that I’ve adopted is pushing the responsibility of this delete/restore behaviour onto the semantics of the database code generators. This means that a model that represents a table within the database is annotated with [DeleteRestore] or [DeleteBehaviour(Capabilities.DELETE_RESTORE)]. This informs the code generator that it should generate the necessary database mechanisms to support deleting & restoring from a table. This is accomplished by having 2 tables, one for the active records, and another containing any deleted records. Using triggers, specifically a delete trigger, it is possible to automatically push this row into the deleted table (${TABLE}_deleted) with a populated deleted-at field on insert. Restoring it from this table then becomes the responsibility of a stored procedure or function.

exec restore_${TABLE}(id);

This no longer means that view, stored functions or dynamic SQL queries need to consider deleted records that might exist in the active table. Foreign keys can still present challenges, as restoring records which have foreign keys to rows that no longer exist causes this approach to quickly break down. Depending on the application or row domain, this may be viable.

A note on complex deletes & restores

This is a tricky one. In some cases it makes sense to treat the deletion behaviour of a table in isolation, with restoring being prevented if any of the foreign key rows are deleted. In other cases, having any foreign key relationship require the preservation of the related rows at time of delete is sufficient. Thus creating an entirely new row if necessary when restoring.

Both of these can be suboptimal, as they don’t really capture the intent behind a restore operation by the user.