API with NestJS #81. Soft deletes with raw SQL queries

JavaScript NestJS SQL

This entry is part 81 of 85 in the API with NestJS

Removing entities is a very common feature in a lot of web applications. The most straightforward way of achieving it is permanently deleting rows from the database. In this article, we implement soft deletes that only mark records as deleted.

You can find the code from this article in this repository.

The idea behind soft deletes

The simplest way of implementing soft deletes is with a boolean flag.

Above, we use the DEFAULT keyword. Thanks to that, every time we insert an entity into our database, the flag equals .

When we want to perform a soft delete on the above record, we don’t use the keyword. Instead, we update the value in the column.

The crucial thing is that implementing soft deletes affects various queries. For example, we need to consider it when getting the list of all entities.

Advantages of soft deletes

The most apparent advantage of soft deletes is that we can quickly restore the entities we’ve deleted. While that’s also possible with backups, soft deletes allow for a better user experience. A good example is an undo button that changes the flag back to .

The convenient thing is that we can fetch the deleted records from the database even though we’ve marked them as removed. This can be useful when we want to generate a report that includes all our records, for example.

If you want to know how to use SQL to generate a report, check out API with NestJS #78. Generating statistics using aggregate functions in raw SQL

Soft deletes might also help us deal with relationships. For example, in this series, we’ve created the table.

If you want to know more about the above table, check out API with NestJS #74. Designing many-to-one relationships using raw SQL queries

Performing a hard delete on a category that’s referenced in the table causes the foreign constraint violation. The above does not happen with soft deletes because we don’t remove the records from the database.

Disadvantages of soft deletes

A significant drawback of implementing soft deletes is that we always need to consider them in various queries. When we implement an endpoint that fetches the data, and we forget to filter by the column, the client might access data that shouldn’t be accessible. Having to implement additional filtering might also have an impact on the performance.

Besides the above, we must also watch out for the unique constraint. Let’s create an example by modifying our table by adding the column.

In the above case, we require every email to be unique. With hard deletes, removing users makes their email accessible to others. However, we don’t remove records from the database when we use soft deletes. Because of that, removing users with soft deletes does not make their emails available to use.

If you want to know more about constraints, check out Defining constraints with PostgreSQL and TypeORM

Implementing soft deletes in our NestJS project

A common approach to implementing soft deletes is storing the deletion date instead of using a simple boolean column. Let’s create a migration that adds a new table that uses soft deletes.

20221029170345_add_comments_table.ts

If you want to know more about dates in PostgreSQL, check out Managing date and time with PostgreSQL and TypeORM

Deleting entities

The crucial part of implementing soft deletes is handling the DELETE method correctly.

comments.controller.ts

The SQL query in our repository should set the value for the column correctly. One way to do that is to use the function that returns the current date and time with the timezone.

comments.repository.ts

Above, we include check if the equals NULL to disallow removing the entity if it is already marked as deleted.

Fetching entities

It is crucial to account for the column in the rest of our queries. A good example is implementing the GET method.

comments.controller.ts

When fetching the entities, we need to make sure to filter out the records that have the .

comments.repository.ts

Thanks to the above approach, trying to fetch a record marked as deleted results in the 404 Not Found error.

Updating entities

Using soft deletes can also affect the implementation of our PUT method.

comments.controller.ts

We want our API to respond with a 404 Not Found error when the user tries to update a record marked as deleted.

comments.repository.ts

Restoring deleted entities

We might want to restore a deleted entity. Fortunately, this is very easy to achieve by setting the value in the column to null.

comments.repository.ts

Summary

In this article, we’ve gone through the idea of soft deletes and discussed their pros and cons. Soft deletes can help us achieve a good user experience associated with deleting entities and restoring them. But unfortunately, they come with the cost of the increased complexity of all of our SQL queries. Even though that’s the case, soft deletes have their use cases and might come in handy.

Series Navigation<< API with NestJS #80. Updating entities with PUT and PATCH using raw SQL queriesAPI with NestJS #82. Introduction to indexes with raw SQL queries >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments