How can I get soft deleted entity from typeorm in postgreSQL?

When handling soft-deleted entities in a PostgreSQL database, a common practice is to set up a flag column in the table, such as is_deleted or deleted_at. This way, when an entity is "deleted," it is not actually removed from the database; instead, the flag field is updated. Next, I will explain in detail how to retrieve soft-deleted entities from such a setup and provide relevant SQL query examples.

1. Using the is_deleted Flag

Assume we have a table named employees that includes a boolean column named is_deleted. When an employee is soft-deleted, is_deleted is set to true.

To retrieve all soft-deleted employees, we can use the following SQL query:

sql
SELECT * FROM employees WHERE is_deleted = true;

This query retrieves all records where the is_deleted field is true, i.e., all soft-deleted employees.

2. Using the deleted_at Timestamp

Another common practice is to use a deleted_at column in the table, which is a timestamp type. When a record is soft-deleted, this column is set to the specific time of the soft deletion; for records that are not soft-deleted, the column remains NULL.

To retrieve all soft-deleted entities, we can use the following SQL query:

sql
SELECT * FROM employees WHERE deleted_at IS NOT NULL;

This query selects all records where the deleted_at field is not NULL.

Example

Assume we have an employees table that includes the fields id, name, is_deleted, and deleted_at.

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

The soft-deletion of an employee can be performed as follows:

sql
-- Soft-delete using the `is_deleted` flag
UPDATE employees SET is_deleted = true WHERE id = 1;

-- Soft-delete using the `deleted_at` timestamp
UPDATE employees SET deleted_at = NOW() WHERE id = 2;

Then, use the queries mentioned earlier to retrieve all soft-deleted employees:

sql
-- Retrieve all employees with `is_deleted` set to true
SELECT * FROM employees WHERE is_deleted = true;

-- Retrieve all employees with `deleted_at` not NULL
SELECT * FROM employees WHERE deleted_at IS NOT NULL;

These methods can effectively help us manage and query soft-deleted entities, maintaining database integrity and tracking historical records without fully deleting data.