April 24, 2023

When developing our REST APIs, we often focus on implementing the four fundamental operations: creating, reading, updating, and deleting (CRUD). The most basic approach to removing a record from our database is to delete it permanently. In this article, we explore the idea of soft deletes that allow us to keep the removed entities in the database.

Explaining soft deletes

The most straightforward way of implementing the soft deletes feature is through a boolean flag.

By adding the is_deleted boolean DEFAULT false line above, we add the is_deleted column that has the false value by default.

Whenever we want to remove a record in the categories table, we don’t have to delete it permanently. Instead, we change the value in the is_deleted column to true.

The most important thing to understand about soft deletes is that they affect various queries we perform. Let’s try fetching a list of all the categories, for example.

The above query returns all categories, including the deleted ones. So, to fix this issue, we need to filter out the removed records.

Soft delete benefits

A significant benefit of soft deletes is that we can effortlessly restore the deleted record. This allows for a user experience far superior to restoring backups. For example, in our application, we can implement an undo button that changes the value in the is_deleted column back to false.

We can also take advantage of querying the deleted records from the database. They can prove to be helpful when generating various reports, for example.

Soft deletes can also be useful when dealing with relationships. For example, permanently deleting a record referenced in another table can cause a foreign constraint violation. This does not happen with soft deletes because we don’t remove the entities from the database.

Soft delete drawbacks

An important disadvantage of soft deletes is that we need to consider them in all related queries. Whenever we fetch our data and forget to filter by the is_deleted column, we might show the user the data they shouldn’t have access to. Implementing filtering can also affect our performance.

Another important consideration is related to the unique constraint. Let’s look at the users table we’ve defined in one of the previous parts of this series.

userSchema.prisma

In our model, we require every email to be unique. When we delete a record permanently, we make the email available to other users. However, removing users through soft deletes does not make their email addresses available for reuse.

Implementing soft deletes with Prisma

The first step when implementing soft deletes is to add the appropriate column to our table. A common approach to soft deletes is storing the deletion date instead of a simple boolean flag.

categorySchema.prisma

Above, we’ve added the deletedAt property, which is a timestamp with a timezone. We will assign it with value whenever we want to delete a record from our database.

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

Now, we need to generate our migration using the Prisma CLI.

Doing the above creates a new file in the migrations directory.

migrations/20230423200453_category_add_deleted_at_column/migration.sql

The official Prisma documentation suggests implementing soft deletes with middleware. Let’s explain this concept first.

Middleware in Prisma

With middleware, we can perform an action before or after a query runs. To attach a middleware, we need to use the $use method. An appropriate place to do that is in our PrismaService class.

prisma.service.ts

delete Category {"where":{"id":8}}

Result: {id: 8, name: "Category"}

Above, we pass a callback to the $use method. Its first argument we call params, contains available parameters, such as the performed action, the model, and the arguments. The second argument, called next, is a function that calls the next action in the chain.

Middleware for deleting

To implement soft deletes with middleware, we need to modify the Prisma query every time a record of a particular type is deleted. We need to change a delete action into the update action and provide the appropriate date.

prisma.service.ts

If we also use the deleteMany action, we need to handle it separately.

Thanks to the above middleware, every time we call the prismaService.category.delete method, we perform the update action under the hood instead.

Middleware for querying

Now we need to write a middleware that filters out removed categories whenever we fetch them. We need to handle fetching a single category and fetching multiple categories separately.

prisma.service.ts

Thanks to the above approach, whenever we use the findUnique, findFirst, and findMany actions, it filters our deleted categories under the hood.

Summary

An advantage of the above approach is that with correctly written middleware, we won’t forget to handle the deletedAt column properly whenever we fetch or delete our entities.

However, this can make our code quite a bit messy. Instead of interacting with the categories table through the CategoriesService, we spread our business logic into unrelated parts of our application. It might also not be instantly apparent to our teammates that whenever they call the this.prismaService.category.delete method, it runs the update action under the hood.

The middleware approach might be suitable in less structured applications that don’t use the NestJS framework. However, in our case, we put the business logic related to categories in the CategoriesService. Therefore, it might make more sense to ditch middleware and implement soft deletes directly through our service for simplicity and readability. Nevertheless, it’s a very good example to learn how the middleware works in Prisma.

Series Navigation<< API with NestJS #104. Writing transactions with Prisma