Implementing soft deletes with Prisma and middleware

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.

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

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.

UPDATE categories
SET is_deleted = true
WHERE 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.

SELECT * 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.

SELECT * FROM categories
WHERE 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.

userSchema.prisma#

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  password  String
  address   Address? @relation(fields: [addressId], references: [id])
  addressId Int?     @unique
  posts     Post[]
}

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#

model Category {
  id        Int       @id @default(autoincrement())
  name      String
  posts     Post[]
  deletedAt DateTime? @db.Timestamptz
}

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.

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

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

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

migrations/20230423200453_category_add_deleted_at_column/migration.sql#

ALTER 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.

prisma.service.ts#

import { Injectable, OnModuleInit, OnModuleDestroy } from "@nestjs/common"
import { PrismaClient, Prisma } from "@prisma/client"
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect()
    this.$use(this.loggingMiddleware)
  }
  loggingMiddleware: Prisma.Middleware = async (params, next) => {
    console.log(`${params.action} ${params.model} ${JSON.stringify(params.args)}`)
    const result = await next(params)
    console.log(result)
    return result
  }
  async onModuleDestroy() {
    await this.$disconnect()
  }
}

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#

import { Injectable, OnModuleInit, OnModuleDestroy } from "@nestjs/common"
import { PrismaClient, Prisma } from "@prisma/client"
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect()
    this.$use(this.categorySoftDeleteMiddleware)
  }
  categorySoftDeleteMiddleware: Prisma.Middleware = async (params, next) => {
    if (params.model !== "Category") {
      return next(params)
    }
    if (params.action === "delete") {
      return next({
        ...params,
        action: "update",
        args: {
          ...params.args,
          data: {
            deletedAt: new Date(),
          },
        },
      })
    }
    return next(params)
  }
  async onModuleDestroy() {
    await this.$disconnect()
  }
}

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#

import { Injectable, OnModuleInit, OnModuleDestroy } from "@nestjs/common"
import { PrismaClient, Prisma } from "@prisma/client"
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect()
    this.$use(this.categorySoftDeleteMiddleware)
    this.$use(this.categoryFindMiddleware)
  }
  categoryFindMiddleware: Prisma.Middleware = async (params, next) => {
    if (params.model !== "Category") {
      return next(params)
    }
    if (params.action === "findUnique" || params.action === "findFirst") {
      return next({
        ...params,
        action: "findFirst",
        args: {
          ...params.args,
          where: {
            ...params.args?.where,
            deletedAt: null,
          },
        },
      })
    }
    if (params.action === "findMany") {
      return next({
        ...params,
        args: {
          ...params.args,
          where: {
            ...params.args?.where,
            deletedAt: null,
          },
        },
      })
    }
    return next(params)
  } // ...
}

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.