API with NestJS #53. Implementing soft deletes with PostgreSQL and TypeORM

JavaScript NestJS SQL TypeScript

This entry is part 53 of 180 in the API with NestJS

In this series, we’ve implemented deleting functionalities for various entities. So far, it has always meant removing records permanently from our database. Instead, we can perform soft deletes. When we delete a record using a soft delete, we only mark it as deleted.

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

Soft deletes with PostgreSQL

To implement soft deletes straightforwardly, we can use a boolean flag. Let’s implement a straightforward example with the table.

For starters, let’s insert a user into our database. Thanks to using the DEFAULT keyword, all our users are not deleted by default.

If we want to perform a soft delete on the above record, we no longer use the statement. Instead, we perform an .

Now we need to take the column into account when performing various other queries. A good example is a query to get the list of all the users.

Advantages and disadvantages of soft deletes

The obvious advantage of soft deletes is that we can always easily restore the data we’ve previously deleted. We might think we could also achieve that with frequent backups, but soft deletes could help us achieve a better user experience. For example, we could create an undo button in our application that changes the flag to .

Still, we definitely should create backups and make sure they work.

A significant disadvantage of soft deletes is that we always need to keep them in mind when performing various queries. If we use Object-Relational Mapping such as TypeORM, it makes our work a bit easier. However, we still need to be aware of the additional effort PostgreSQL needs to make to filter out records where equals .

Even if we expect to be in most of our queries, this might not always be the case. A significant advantage of the soft deletes is the fact that we can still fetch the deleted records. This might come in handy when we want to generate some report, for example. Thanks to soft deletes, we can include all of the records.

There is also an important catch in our example with the table above. When we mark the column as unique, it works in a predictable way when we decide to use the regular statements. This is not that straightforward with soft deletes. PostgreSQL takes the records with set to into account when checking if the unique constraint is violated.

Soft deletes can come in handy when dealing with relationships. For example, in this series, we associated posts with users through a many-to-one relationship. Because of that, the table has a column called used to save the author of that post. Trying to perform a hard delete on a user that is an author of a post leads to a foreign constraint violation. This does not happen with soft deletes.

We could deal with the above issue using cascade deletes, but this would delete the posts also.

Soft deletes with TypeORM

The TypeORM community anticipated the soft delete feature very much. To start using this feature, we need to use the decorator.

category.entity.ts

The above is because TypeORM stores the deletion date instead of a simple boolean flag. Thanks to that, we have an additional piece of information stored that might come in handy.

To perform the soft delete, we need to use the method instead of .

categories.service.ts

When we do the above, TypeORM sets the column to the current date.

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

Performing queries

Thanks to doing the above, we can check if a record has been soft-deleted when performing other queries.

Thankfully, TypeORM does that for us out of the box when we use the method. Under the hood, it checks if the column has the value.

Still, we might want to allow the users to view a deleted category if they know the id, for example. Our endpoint by default will respond with 404 Not Found for soft-deleted records, though.

Thankfully, we can explicitly tell TypeORM not to filter out soft-deleted records using the argument.

When we do the above, we can notice that the property is now set.

Restoring deleted records

With soft deletes, we can very easily restore the deleted records. TypeORM allows us to do that with the method.

When we do the above, TypeORM sets the value of back to null.

Summary

In this article, we’ve learned the concept of soft delete. While doing so, we wrote some SQL code. Understanding how the soft delete functions under the hood is essential to grasping its advantages and disadvantages better. Even though TypeORM can help us a lot in implementing soft deletes, it comes with some drawbacks. After going through them in this article, it might appear that we need a pretty good reason to use soft deletes. This is because it is bound to make our database requires more space and take a toll on the performance. Even so, it has its uses and might come in handy in some cases.

Series Navigation<< API with NestJS #52. Generating documentation with Compodoc and JSDocAPI with NestJS #54. Storing files inside a PostgreSQL database >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mahdi
3 years ago

Very handy article, thank you!