How to Implement Soft Delete with Prisma using Partial Indexes

Most APIs and applications that allow you to manage content have some form of deletion functionality. Sometimes you may want to make something recoverable after it’s been deleted, or perform the actual deletion later, and soft deletion is a way to accomplish this.

Entities in your database may also have unique identifiers such as slugs associated with them as well, and when you delete things you typically expect these identifiers to become available for use again. In the case of soft deletes though your deleted entities will still be in the database! If you’re using Postgres or SQLite you can get the best of both worlds by using partial unique indexes, which will in the case of soft deletes would only add slugs on undeleted records to the index; however if you’re connecting a partial index compatible database with Prisma then everything isn’t rainbows and sunshine when it comes to partial indexes.

A Simple Soft Delete Implementation

Before getting into the issue with partial unique indexes, let’s implement soft delete in Prisma using middleware. The following example is adapted from the Prisma documentation regarding soft deletes, though with some slight alterations for convenience.

import { Prisma, PrismaClient } from "@prisma/client";

const MODELS_SUPPORTING_SOFT_DELETE = ["Post"] as const;

const prisma = new PrismaClient({
  log: [{ emit: "event", level: "query" }],
});

prisma.$use(async (params, next) => {
  const supportedModels = MODELS_SUPPORTING_SOFT_DELETE as readonly string[];

  if (params.model && supportedModels.includes(params.model)) {
    // Change 'findUnique' actions to 'findFirst' as you cannot filter by
    // anything except ID / unique with findUnique, which adding the 'deletedAt'
    // check breaks.
    if (params.action === "findUnique" || params.action === "findFirst") {
      params.action = "findFirst";
      params.args.where.deletedAt = null;
    }

    // Handle similar actions as above but with 'OrThrow' as Prisma uses a
    // separate action for that.
    if (
      params.action === "findUniqueOrThrow" ||
      params.action === "findFirstOrThrow"
    ) {
      params.action = "findFirstOrThrow";
      params.args.where.deletedAt = null;
    }

    // Exclude deleted records from 'findMany' only if they have not been
    // explicitly requested. Default to non-deleted records if filters are
    // left unspecified.
    if (params.action === "findMany") {
      if (params.args.where) {
        if (params.args.where.deletedAt === undefined) {
          params.args.where.deletedAt = null;
        }
      } else {
        params.args.where = { deletedAt: null };
      }
    }
  }

  return next(params as Prisma.MiddlewareParams);
});

The notable change here is this version could be extended to work with multiple models fairly easily by modifying the MODELS_SUPPORTING_SOFT_DELETE constant, and we also handle the throwable variants of the find actions as well. The latter change requires we create a custom type named ExpandedPrismaAction with additional actions as the imported Prisma types don’t have these actions for some reason even though they are encountered in actual usage.

Anything specified in the model's constant is assumed to have a field named deletedAt that is nullable. In this example specifically it’s a timestamp field since it’s nice to know when something has been deleted, but it could also be a boolean if you so choose.

With that done, soft deletion is transparently implemented, at least insofar as select queries go. If you plan on blindly running update and delete queries as well, then you will need to add middleware for those actions as well.

The Problem

So as stated before we plan on using unique slugs with our model, in this case slugs for posts, and to free slugs for usage later we need to exclude them from the unique index without deleting the post records outright. Unfortunately, at the time of writing this article Prisma doesn’t allow you to define partial indexes in your schema when defining unique indexes using @@unique inside of your schema. Thankfully, there is a way to workaround this issue.

The Solution

To workaround this issue you can manage your unique indexes outside of the Prisma schema, though it does come with its own pitfalls that I will cover later. This can be done by creating partial indexes in a migration file instead of defining them in the schema file. It should be mentioned that if you do this then you should ensure a conflicting index is not defined in the schema, or else Prisma will try to overwrite the other index.

First let’s create a basic schema with the following contents defining posts:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite" // or "postgresql", etc
  url      = env("DATABASE_URL")
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]

  @@map("users")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String
  content     String
  author      User      @relation(fields: [authorId], references: [id])
  authorId    Int       @map("author_id")
  publishedAt DateTime? @map("published_at")
  deletedAt   DateTime? @map("deleted_at")

  // FIXME: https://github.com/prisma/prisma/issues/6974
  // @@unique([slug], where: { deletedAt: null })

  @@map("posts")
}

In the above schema I’ve left a comment that mentions where a partial unique index definition would go if it was supported. The problem with keeping the definition solely in the migration is that it makes it difficult to discover, and also doesn’t explain the reason why it’s there.

Now that we have a Post model with a deletedAt column, create an empty migration and do the following:

prisma migrate dev --create-only --name 'add_soft_delete'

Then the contents of the newly created migration file will be as follows:

CREATE UNIQUE INDEX "posts_slug_key"
    ON "posts" ("slug")
    WHERE "deleted_at" IS NULL;

With that all initialized and the middleware setup, and router handler defined, everything should work! Check out the full example on GitHub as I don’t show the full implementation of the example API in this article. The route handlers don’t look any different than you would expect as the middleware handles all of the heavy lifting for us.

It is worth noting that DELETE handlers do look different with soft-delete as we’re not intercepting delete calls to Prisma. This is how the implementation of the deletion looks for this example:

router.delete("/:slug", async (req, res) => {
  const { slug } = req.params;

  const existingPost = await prisma.post.findFirst({ where: { slug } });
  if (!existingPost) {
    return res.sendStatus(404);
  }

  await prisma.post.update({
    where: { id: existingPost.id },
    data: { deletedAt: new Date() },
  });
  res.sendStatus(204);
});

Caveats

So there are some caveats to implementing partial delete in this way. The first being that the schema doesn’t reflect the state of the database 1:1. Thankfully, Prisma doesn’t care about untracked indexes existing, but knowing the index exists requires looking in the migration files instead of the schema. This is worked around by leaving a comment, but it still isn’t great.

Another downside is that upsert calls do not work with the Prisma client. Making upserts with Prisma requires a unique key, but since Prisma doesn’t track this unique key the typings needed to call upsert do not exist. This can be worked around by making raw queries without the query builder.

Summary

I hope this article has helped you ease the integration of soft deletes into your application. You can find a full example on → GitHub ← that includes all of the above code snippets, a buildable project, and instructions on how to get it all working.