Implementing soft deletes with Prisma and middleware

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.

1CREATE TABLE categories (
2  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3  name text NOT NULL,
4  is_deleted boolean DEFAULT false
5)

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.

1UPDATE categories
2SET is_deleted = true
3WHERE name = 'NestJS'

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.

1SELECT * FROM categories

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

1SELECT * FROM categories
2WHERE is_deleted = false

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.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9}

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.

1model Category {
2  id        Int       @id @default(autoincrement())
3  name      String
4  posts     Post[]
5  deletedAt DateTime? @db.Timestamptz
6}

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.

1npx prisma migrate dev --name category-add-deleted-at-column

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

1ALTER TABLE "Category" ADD COLUMN "deletedAt" TIMESTAMPTZ;

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.

1import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
2import { PrismaClient, Prisma } from '@prisma/client';
3 
4@Injectable()
5export class PrismaService
6  extends PrismaClient
7  implements OnModuleInit, OnModuleDestroy
8{
9  async onModuleInit() {
10    await this.$connect();
11    this.$use(this.loggingMiddleware);
12  }
13 
14  loggingMiddleware: Prisma.Middleware = async (params, next) => {
15    console.log(
16      `${params.action} ${params.model} ${JSON.stringify(params.args)}`,
17    );
18 
19    const result = await next(params);
20 
21    console.log(result);
22 
23    return result;
24  };
25 
26  async onModuleDestroy() {
27    await this.$disconnect();
28  }
29}

1 delete Category {"where":{"id":8}} 1 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.

1import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
2import { PrismaClient, Prisma } from '@prisma/client';
3 
4@Injectable()
5export class PrismaService
6  extends PrismaClient
7  implements OnModuleInit, OnModuleDestroy
8{
9  async onModuleInit() {
10    await this.$connect();
11    this.$use(this.categorySoftDeleteMiddleware);
12  }
13 
14  categorySoftDeleteMiddleware: Prisma.Middleware = async (params, next) => {
15    if (params.model !== 'Category') {
16      return next(params);
17    }
18    if (params.action === 'delete') {
19      return next({
20        ...params,
21        action: 'update',
22        args: {
23          ...params.args,
24          data: {
25            deletedAt: new Date(),
26          },
27        },
28      });
29    }
30    return next(params);
31  };
32 
33  async onModuleDestroy() {
34    await this.$disconnect();
35  }
36}

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.

1import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
2import { PrismaClient, Prisma } from '@prisma/client';
3 
4@Injectable()
5export class PrismaService
6  extends PrismaClient
7  implements OnModuleInit, OnModuleDestroy
8{
9  async onModuleInit() {
10    await this.$connect();
11    this.$use(this.categorySoftDeleteMiddleware);
12    this.$use(this.categoryFindMiddleware);
13  }
14 
15  categoryFindMiddleware: Prisma.Middleware = async (params, next) => {
16    if (params.model !== 'Category') {
17      return next(params);
18    }
19    if (params.action === 'findUnique' || params.action === 'findFirst') {
20      return next({
21        ...params,
22        action: 'findFirst',
23        args: {
24          ...params.args,
25          where: {
26            ...params.args?.where,
27            deletedAt: null,
28          },
29        },
30      });
31    }
32    if (params.action === 'findMany') {
33      return next({
34        ...params,
35        args: {
36          ...params.args,
37          where: {
38            ...params.args?.where,
39            deletedAt: null,
40          },
41        },
42      });
43    }
44    return next(params);
45  };
46 
47  // ...
48 
49}

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.