#10

Adding Soft-Delete in PostgreSQL Without Changing Code

I recently needed to add a soft-delete feature to my application. However, I didn’t want to modify the existing program code. Instead, I leveraged PostgreSQL’s rule system to achieve this. Here’s how I did it:

I created a migration that automatically logs deleted rows into a separate table whenever a DELETE statement is executed. This ensures the original data is preserved.

CREATE TABLE deleted_data (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    row_data JSONB NOT NULL,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE RULE log_course_deletion AS
    ON DELETE TO course
    DO ALSO INSERT INTO deleted_data (table_name, row_data)
    VALUES ('course', row_to_json(OLD));

With this setup, every time a row is deleted from the course table, it is automatically inserted into the deleted_data table. This way, I can retrieve the deleted data whenever I need it by running a simple query:

SELECT row_data
FROM deleted_data
WHERE table_name = 'your_table_name'
AND row_data->>'id' = 'your_id';

This solution allowed me to implement soft-delete functionality without altering my existing program code. Simple and effective!