Database Soft Deletes: How and Why with Custom Prisma Client queries

Hard deletes and soft deletes are two different approaches to deleting data from a database, each with its own pros and cons. In this article, we will explore soft deletes, their advantages and disadvantages, how to implement them using Prisma ORM, and write some custom Prisma client queries to handle all the hard work for us.

What are Soft Deletes?

Soft deletes are a technique that allows you to mark a record as deleted without actually removing it from the database. Instead, a property is set on the record indicating that it has been deleted. Typically, this flag is named something like is_deleted or deleted_at. This field can then be used to filter out deleted records from queries.

Advantages and Disadvantages of Soft Deletes

One of the main advantages of soft deletes is that they provide an audit trail of deleted records. This can be useful for compliance purposes or for tracking changes to the database over time. Additional metadata information can be stored about the deletion such as the date & time it was deleted (deleted_at) as well as the user who preformed the deletion (deleted_by). You can also utilize soft deletes for trash bin functionality or quickly and easily be able to "undo" or recovery deleted records.

However, soft deletes can also have some disadvantages. One of the main disadvantages of using soft deletes is having to query for is_deleted every time you want to pull back active records. If you forget to do this, you could be showing deleted records to the user. This can add some additional complexity to all of your queries. (We will address this later in the article by utilizing the Prisma ORM and writing some custom Prisma Client Queries). Since we are not actually deleting records from the database, the database can become bloated with deleted records. This, in turn, can lead to decreased performance when querying the table.

Additionally, soft deletes can cause issues with unique constraints if the same unique fields are used for both deleted and active records. Take, for example, that you have added an is_deleted field to a users table and have a unique constraint on the email column. Now, if a user wants to delete their account, the is_deleted field would be set to true. If the user wants to re-create an account with the same email, a unique constraint error would be thrown. There are a couple of workarounds for this, such as restoring the original record, using partial indexes, or virtual columns (depending on your database). This will be out of scope for this article, but there are many great resources on the internet for this: https://halimsamy.com/sql-soft-deleting-and-unique-constraint

Comparison with Hard Deletes

Hard deletes, on the other hand, involve permanently removing a record from the database. This approach can be faster and more efficient than soft deletes, but it also means that the data is lost forever. This is the default approach for many applications.

How to Implement Soft Deletes with Custom Prisma Client Queries

Let's consider the following prisma.schema file. A user can have many blog posts.

model User {
	id Int @id @default(autoincrement())
	email String @unique
	name String?
	password String
	createdAt DateTime @default(now())
	updatedAt DateTime @updatedAt
	
	blogs Blog[]
}

  

model Blog {
	id Int @id @default(autoincrement())
	title String
	description String
	createdAt DateTime @default(now())
	updatedAt DateTime @updatedAt
	
	author User? @relation(fields: [authorId], references: [id])
	authorId Int?
	
	// Soft Delete Metadata fields
	deletedAt DateTime?
	deletedBy Int?
}

Goal

We want to allow users to archive their own blog posts via a soft delete instead of a hard delete. This way, if the user accidentally deletes the wrong blog post, it can be restored. We also want to keep an audit of what user deleted what blog. Let's start by doing this the manual way:

const user = await prisma.user.create({
	data: {
		name:"Jeff",
		email:"jeff@dharmasoftware.com",
		password:"DO_NOT_STORE_IN_PLAIN_TEXT",
		blogs: {
			create: {
				title:"My first blog post",
				description:"This is my first blog post",
			},
		},	
	},
	include: {
		blogs: true,
	},

});

console.log(user);

const deletedBlog = await prisma.blog.update({
	where: {
		id: 1,
	},
	data: {
		deletedAt: new Date(),
		deletedBy: 2
	},
});

  
const allBlogs = await prisma.blog.findMany({
	where: {
		deletedAt: null,
	},	
});

In this approach, we are doing an update instead of a delete. We updated the deletedAt property to indicate that this record should be considered as deleted, as well as save the userId of who deleted the blog. Now every time that we get all blog posts, we need to be sure that we are only returning blog posts that don't have a deletedAt time. This approach does the job but can lead to some issues. One of the main issues is that you have to manually remember to return only the blogs that don't have a deletedAt time. It is very easy to forget this and then accidentally leak some information that you don't want to. We can clean this up by writing our first custom Prisma client query.

Create custom Prisma Client Query

Listen, I'm dumb, and there is a 100% chance that at some point in the development of my application, I will forget to add where: { deletedAt: null } and leak some data that I don't want to. This is where extending the Prisma Client and writing custom queries can help us. In the past, you could use Prisma Middleware; however, that has been deprecated in favor of creating custom Prisma client queries. Writing a custom Prisma client query gives you the ability to modify an incoming query or modify its result. This is an extremely powerful feature that allows you to simplify & encapsulate a lot of your Prisma queries and is the perfect tool for our soft delete use case. Let's see how we can create a custom Prisma query for soft deletes.

const prisma = new PrismaClient().$extends({
	model: {
		blog: {
		 async softDelete(id: number, userId: number) {
				await prisma.blog.update({
					where: {
						id,
					},
					data: {
						deletedAt: new Date(),	
						deletedBy: userId,
					},
				});
			},
		},
	},
	query: {
		blog: {
			async findMany({ model, operation, args, query }) {
				args.where = { deletedAt: null, ...args.where };
				
				return query(args);
			},
			async findFirst({ model, operation, args, query }) {
				args.where = { deletedAt: null, ...args.where };
			
				return query(args);

				},
			},
		},
});

When we first initialize our Prisma client we can extend its functionality with custom code. Here we are creating a softDelete function on our blog model. You can also create global functions as well. In this softDelete function, we are doing the exact same thing that we were doing in the manual process: updating deletedAt and deletedBy instead of deleting the record. We then modify the existing findMany and findFirst Prisma functions to add the deletedAt: null argument. This means that we don't have to manually specify it every time we call findMany or findFirst.

This means we can simplify our code to the following:

const otherUserId = 2;

await prisma.blog.softDelete(1, otherUserId);

const allBlogs = await prisma.blog.findMany({});

console.log(allBlogs);

That's all folks

If you have any issues or questions feel free to reach out to me on twitter @jeff_codes. Please also consider subscribing to the newsletter to get all my future blog posts on web development right way. Thanks for reading!

The prisma full documentation can be found here: https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions/query