I am facing the task of implementing soft-deletion across our entire database. As of now, the only approach I know of is to add a new column (e.g.: isDeleted
) to every table. Maintaining this is pretty unpleasant since you have to deal with checking whether a record that you want to query is deleted or not aswell as implementing your own cascading logic. Furthermore you have to deal with checking whether a referenced record is not deleted when working with foreign-keys.
All of that is, as I said, very unpleasant to maintain, hence I am looking for new ways to implement a soft-delete in PostgreSQL.
If you have any suggestions, please let me know. I would like to implement this mostly on the server-side, rather than on the client-side.
Note: I am using TypeScript without any type of ORM. Plain SQL Queries.
Googled a lot! Read many articles.
2
You can leverage ready-mage gems like acts_as_paranoid it works by adding default_scope
to your models (https://github.com/ActsAsParanoid/acts_as_paranoid/blob/master/lib/acts_as_paranoid.rb#L48).
Another way to do it would be to actually delete the records, but have another solution that keeps them in a different place. Something like papertrail
could accomplish that: it saves every change to the models in a separate table, and allows you to restore previous versions (even after deletion).
Both have tradeoffs. Keeping the records and just marking them as deleted is simpler, but you risk errors (default_scope
is additive, but someone could use uscoped
and show “soft deleted” records to your users).
The second option is safer, but in case of changing model or column names – you need to consider migrating the entries in papertrail
table to retain the functionality.
2