📎 Webclip
Soft delete with Ecto 3 and Postgres
The text presents a way to implement soft deletes in applications using Ecto 3 with Postgres. Records get a deleted_at timestamp instead of being removed, with normal queries seeing only non-deleted rows while special queries and a hard delete path can access or permanently remove all data. The approach relies on Postgres schemas, triggers, views, and minimal changes to Ecto schemas and the Repo.
Reading notes#
- Soft delete is defined as keeping records in the database while hiding them from the application rather than removing them outright.
- The chosen approach adds a
deleted_atcolumn to each table; null means active, a timestamp means soft deleted. - The application is configured so default operations see only non-deleted records, but it remains possible to query all records or perform hard deletes that fully remove rows.
- The term “schema” refers both to Postgres schemas (namespaces like
publicor a customfiltered) and to Ecto schemas (modules usingEcto.Schema). - Postgres schemas act as namespaces for relations, letting code refer to tables like
public.usersorfiltered.users. - Ecto schemas are modules that
use Ecto.Schema, and the text distinguishes them from Postgres schemas to avoid confusion. - Tests exercise a
SoftDelete.Usersmodule whose public API resembles standard CRUD, plus soft-delete-specific functions such aslist_deleted_users,get_deleted_user,undelete_user, andhard_delete_user. - The
delete_userfunction differs slightly from a typical implementation because, on success, it must look up and return another record rather than the one directly deleted. - Functions that work with soft deleted users use the
prefixoption inEcto.Query.from/2, which selects the Postgres schema where the query runs. - Using
prefixallowslist_deleted_usersto retrieve data frompublic.users, where all users (including soft deleted ones) live. - The Ecto schema for
Userdefines fields and changesets like a normal app, but instead ofuse Ecto.Schemait usesuse SoftDelete.Schema. SoftDelete.Schemasets@schema_prefixto"filtered", so Ecto operates againstfiltered.usersby default rather thanpublic.users.- With
@schema_prefixset,filtered.usersholds users that are not soft deleted, whilepublic.userscontains every user, including soft deleted ones. - The migration that creates the
userstable looks like a normalcreate table(:users)call, but also executes custom SQL viaprepare_table_for_soft_delete('users')onupandreverse_table_soft_delete('users')ondown. - An earlier migration sets up support for soft delete, including creation of the
filteredPostgres schema and trigger functions. - The migration uses
CREATE SCHEMA filteredto create a separate Postgres schema where filtered views will live. - A trigger is defined that, after a row is deleted, reinserts it into the original table with
deleted_atset to the current timestamp so deletes become soft deletes. - The trigger is configured as an
AFTER DELETEtrigger for specific technical reasons, though those details are not elaborated in the text. - The
prepare_table_for_soft_deletefunction takes a table name and adds adeleted_atcolumn to that table. - It also creates an index on
deleted_atbecause it will be heavily used in queries filtering by soft delete status. - The function activates the previously defined trigger on the target table so that delete operations become soft deletes.
- It creates a Postgres view in the
filteredschema with the same name as the table that only exposes rows wheredeleted_atis null. - Once
prepare_table_for_soft_deletehas been run, a table is ready for use with soft delete in the application. - A companion function,
reverse_table_soft_delete, is provided to undo the changes made byprepare_table_for_soft_delete, reversing those four steps. - Hard deletes are implemented via a
hard_deletefunction added to the Repo module. - The Repo remains mostly standard, with
hard_deletebeing the main addition and following typical patterns for Ecto code. hard_delete/1usesEcto.Multi, first disabling the soft delete trigger in the current transaction so that a real delete occurs.- After disabling the trigger, it deletes the record normally, then re-enables the trigger so future deletes revert to soft delete behavior.
- Trigger disabling happens only within the transaction scope, which avoids global state issues and race conditions that might accidentally hard delete rows.
- The author characterizes this soft delete approach as minimally invasive to normal Ecto behavior and visible only in a few focused places.
- The method is said to rely on a small amount of custom SQL, but not extensive or hacky changes.
- The article notes there are performance tradeoffs with soft delete, though the implementation should add only a millisecond or two to most operations.
- If performance becomes problematic, the author suggests that the approach can be refined and optimized in various ways without detailing them.
