Soft delete middleware

The following example uses middleware to perform a soft delete, which means that a record is marked as deleted by changing a field like deleted to true rather than actually being removed from the database. Reasons to use a soft delete include:

  • Regulatory requirements that mean you have to keep data for a certain amount of time
  • 'Trash' / 'bin' functionality that allows users to restore content that was deleted

This example uses the following schema - note the deleted field on the Post property:

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @default(autoincrement()) @id
name String?
email String @unique
posts Post[]
followers User[] @relation("UserToUser")
User User? @relation("UserToUser", fields: [userId], references: [id])
userId Int?
}
model Post {
id Int @default(autoincrement()) @id
title String
content String?
User User? @relation(fields: [userId], references: [id])
userId Int?
tags Tag[]
views Int @default(0)
deleted Boolean @default(false)
}
model Category {
id Int @default(autoincrement()) @id
parentCategory Category? @relation("CategoryToCategory", fields: [categoryId], references: [id])
Category Category[] @relation("CategoryToCategory")
categoryId Int?
}
model Tag {
tagName String @id // Must be unique
posts Post[]
}

Step 1: Store status of record

Add a field named deleted to the Post model. You can choose between two field types depending on your requirements:

  • Boolean with a default value of false:

    model Post {
    id Int @id @default(autoincrement())
    ...
    deleted Boolean @default(false)
    }
  • Create a nullable DateTime field so that you know exactly when a record was marked as deleted - NULL indicates that a record has not been deleted. In some cases, storing when a record was removed may be a regulatory requirement:

    model Post {
    id Int @id @default(autoincrement())
    ...
    deleted DateTime?
    }

Note: Using two separate fields (isDeleted and deletedDate) may result in these two fields becoming out of sync - for example, a record may be marked as deleted but have no associated date.)

This example uses a Boolean field type for simplicity.

Step 2: Soft delete middleware

Add a middleware that performs the following tasks:

  • Intercepts delete and deleteMany queries for the Post model
  • Changes the params.action to update and updateMany respectively
  • Introduces a data argument and sets { deleted: true }, preserving other filter arguments if they exist

Run the following example to test the soft delete middleware:

script.ts
1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient({});
4
5async function mainAsync() {
6 /***********************************/
7 /* SOFT DELETE MIDDLEWARE */
8 /***********************************/
9
10 prisma.$use(async (params, next) => {
11 // Check incoming query type
12 if (params.model == "Post") {
13 if (params.action == "delete") {
14 // Delete queries
15 // Change action to an update
16 params.action = "update";
17 params.args["data"] = { deleted: true };
18 }
19 if (params.action == "deleteMany") {
20 // Delete many queries
21 params.action = "updateMany";
22 if (params.args.data != undefined) {
23 params.args.data["deleted"] = true;
24 } else {
25 params.args["data"] = { deleted: true };
26 }
27 }
28 }
29 return next(params);
30 });
31
32 /***********************************/
33 /* TEST */
34 /***********************************/
35
36 const titles = [
37 { title: "How to create soft delete middleware" },
38 { title: "How to install Prisma" },
39 { title: "How to update a record" },
40 ];
41
42 console.log("\u001b[1;34mSTARTING SOFT DELETE TEST \u001b[0m");
43 console.log("\u001b[1;34m#################################### \u001b[0m");
44
45 let i = 0;
46 let posts = new Array();
47
48 // Create 3 new posts with a randomly assigned title each time
49 for (i == 0; i < 3; i++) {
50 const createPostOperation = prisma.post.create({
51 data: titles[Math.floor(Math.random() * titles.length)],
52 });
53 posts.push(createPostOperation);
54 }
55
56 var postsCreated = await prisma.$transaction(posts);
57
58 console.log(
59 "Posts created with IDs: " +
60 "\u001b[1;32m" +
61 postsCreated.map((x) => x.id) +
62 "\u001b[0m"
63 );
64
65 // Delete the first post from the array
66 const deletePost = await prisma.post.delete({
67 where: {
68 id: postsCreated[0].id, // Random ID
69 },
70 });
71
72 // Delete the 2nd two posts
73 const deleteManyPosts = await prisma.post.deleteMany({
74 where: {
75 id: {
76 in: [postsCreated[1].id, postsCreated[2].id],
77 },
78 },
79 });
80
81 const getPosts = await prisma.post.findMany({
82 where: {
83 id: {
84 in: postsCreated.map((x) => x.id),
85 },
86 },
87 });
88
89 console.log();
90
91 console.log(
92 "Deleted post with ID: " + "\u001b[1;32m" + deletePost.id + "\u001b[0m"
93 );
94 console.log(
95 "Deleted posts with IDs: " +
96 "\u001b[1;32m" +
97 [postsCreated[1].id + "," + postsCreated[2].id] +
98 "\u001b[0m"
99 );
100 console.log();
101 console.log(
102 "Are the posts still available?: " +
103 (getPosts.length == 3
104 ? "\u001b[1;32m" + "Yes!" + "\u001b[0m"
105 : "\u001b[1;31m" + "No!" + "\u001b[0m")
106 );
107 console.log();
108 console.log("\u001b[1;34m#################################### \u001b[0m");
109 // 4. Count ALL posts
110 const f = await prisma.post.findMany({});
111 console.log("Number of posts: " + "\u001b[1;32m" + f.length + "\u001b[0m");
112
113 // 5. Count DELETED posts
114 const r = await prisma.post.findMany({
115 where: {
116 deleted: true,
117 },
118 });
119 console.log(
120 "Number of SOFT deleted posts: " + "\u001b[1;32m" + r.length + "\u001b[0m"
121 );
122}
123
124mainAsync();

The example outputs the following:

STARTING SOFT DELETE TEST
####################################
Posts created with IDs: 587,588,589
Deleted post with ID: 587
Deleted posts with IDs: 588,589
Are the posts still available?: Yes!
####################################

Tip: Comment out the middleware to see the message change.

✔ Pros of this approach to soft delete include:

  • Soft delete happens at data access level, which means that you cannot delete records unless you use raw SQL

✘ Cons of this approach to soft delete include:

  • Content can still be read and updated unless you explicitly you filter by where: { deleted: false } - in a large project with a lot of queries, there is a risk that soft deleted content will still be displayed

  • You can still use raw SQL to delete records

    Tip: You can create rules or triggers (MySQL and PostgreSQL) at a database level to prevent records from being deleted.

Step 3: Optionally prevent read/update of soft deleted records

In step 2, we implemented middleware that prevents Post records from being deleted. However, you can still read and update deleted records. This steps explores two ways that you can prevent the reading and updating of deleted records.

Note: These options are just ideas with pros and cons, you may choose to do something entirely different.

Option 1: Implement filters in your own application code

In this option:

  • Prisma middleware is responsible for preventing records from being deleted
  • Your own application code (which could be a GraphQL API, a REST API, a module) is responsible for filtering out deleted posts where necessary ({ where: { deleted: false }}) when reading and updating data - for example, the getPost GraphQL resolver never returns a deleted post

✔ Pros of this approach to soft delete include:

  • No change to Prisma's create/update queries - you can easily request deleted records if you need them
  • Modifying queries in middleware can have some unintended consequences, such as changing query return types (see option 2)

✘ Cons of this approach to soft delete include:

  • Logic relating to soft delete maintained in two different places
  • If your API surface is very large and maintained by multiple contributors, it may be difficult to enforce certain business rules (for example, never allow deleted records to be updated)

Option 2: Use middleware to determine the behavior of read/update queries for deleted records

Option two uses Prisma middleware to prevent soft deleted records from being returned. The following table describes how the middleware affects each query:

QueryMiddleware logicChanges to return type
findUnique🔧 Change query to findFirst (because you cannot apply deleted: false filters to findUnique)
🔧 Add where: { deleted: false } filter to exclude soft deleted posts
No change
findMany🔧 Add where: { deleted: false } filter to exclude soft deleted posts by default
🔧 Allow developers to explicitly request soft deleted posts by specifying deleted: true
No change
update🔧 Change query to updateMany (because you cannot apply deleted: false filters to update)
🔧 Add where: { deleted: false } filter to exclude soft deleted posts
{ count: n } instead of Post
updateMany🔧 Add where: { deleted: false } filter to exclude soft deleted postsNo change
  • Why are you making it possible to use findMany with a { where: { deleted: true } } filter, but not updateMany?
    This particular example was written to support the scenario where a user can restore their deleted blog post (which requires a list of soft deleted posts) - but the should not be able to edit a deleted post.
  • Can I still connect or connectOrCreate a deleted post?
    In this example - yes. The middleware does not prevent you from connecting an existing, soft deleted post to a user.

Run the following sample to see how middleware affects each query:

script.ts
1import { PrismaClient } from "@prisma/client";
2
3const prisma = new PrismaClient({});
4
5async function mainAsync() {
6 /***********************************/
7 /* SOFT DELETE MIDDLEWARE */
8 /***********************************/
9
10 prisma.$use(async (params, next) => {
11 if (params.model == "Post") {
12 if (params.action == "findOne") {
13 // Change to findFirst - you cannot filter
14 // by anything except ID / unique with findOne
15 params.action = "findFirst";
16 // Add 'deleted' filter
17 // ID filter maintained
18 params.args.where["deleted"] = false;
19 }
20 if (params.action == "findMany") {
21 // Find many queries
22 if (params.args.where != undefined) {
23 if (params.args.where.deleted == undefined) {
24 // Exclude deleted records if they have not been expicitly requested
25 params.args.where["deleted"] = false;
26 }
27 } else {
28 params.args["where"] = { deleted: false };
29 }
30 }
31 }
32 return next(params);
33 });
34
35 prisma.$use(async (params, next) => {
36 if (params.model == "Post") {
37 if (params.action == "update") {
38 // Change to updateMany - you cannot filter
39 // by anything except ID / unique with findOne
40 params.action = "updateMany";
41 // Add 'deleted' filter
42 // ID filter maintained
43 params.args.where["deleted"] = false;
44 }
45 if (params.action == "updateMany") {
46 if (params.args.where != undefined) {
47 params.args.where["deleted"] = false;
48 } else {
49 params.args["where"] = { deleted: false };
50 }
51 }
52 }
53 return next(params);
54 });
55
56 prisma.$use(async (params, next) => {
57 // Check incoming query type
58 if (params.model == "Post") {
59 if (params.action == "delete") {
60 // Delete queries
61 // Change action to an update
62 params.action = "update";
63 params.args["data"] = { deleted: true };
64 }
65 if (params.action == "deleteMany") {
66 // Delete many queries
67 params.action = "updateMany";
68 if (params.args.data != undefined) {
69 params.args.data["deleted"] = true;
70 } else {
71 params.args["data"] = { deleted: true };
72 }
73 }
74 }
75 return next(params);
76 });
77
78 /***********************************/
79 /* TEST */
80 /***********************************/
81
82 const titles = [
83 { title: "How to create soft delete middleware" },
84 { title: "How to install Prisma" },
85 { title: "How to update a record" },
86 ];
87
88 console.log("\u001b[1;34mSTARTING SOFT DELETE TEST \u001b[0m");
89 console.log("\u001b[1;34m#################################### \u001b[0m");
90
91 let i = 0;
92 let posts = new Array();
93
94 // Create 3 new posts with a randomly assigned title each time
95 for (i == 0; i < 3; i++) {
96 const createPostOperation = prisma.post.create({
97 data: titles[Math.floor(Math.random() * titles.length)],
98 });
99 posts.push(createPostOperation);
100 }
101
102 var postsCreated = await prisma.$transaction(posts);
103
104 console.log(
105 "Posts created with IDs: " +
106 "\u001b[1;32m" +
107 postsCreated.map((x) => x.id) +
108 "\u001b[0m"
109 );
110
111 // Delete the first post from the array
112 const deletePost = await prisma.post.delete({
113 where: {
114 id: postsCreated[0].id, // Random ID
115 },
116 });
117
118 // Delete the 2nd two posts
119 const deleteManyPosts = await prisma.post.deleteMany({
120 where: {
121 id: {
122 in: [postsCreated[1].id, postsCreated[2].id],
123 },
124 },
125 });
126
127 const getOnePost = await prisma.post.findUnique({
128 where: {
129 id: postsCreated[0].id,
130 },
131 });
132
133 const getPosts = await prisma.post.findMany({
134 where: {
135 id: {
136 in: postsCreated.map((x) => x.id),
137 },
138 },
139 });
140
141 const getPostsAnDeletedPosts = await prisma.post.findMany({
142 where: {
143 id: {
144 in: postsCreated.map((x) => x.id),
145 },
146 deleted: true,
147 },
148 });
149
150 const updatePost = await prisma.post.update({
151 where: {
152 id: postsCreated[1].id,
153 },
154 data: {
155 title: "This is an updated title (update)",
156 },
157 });
158
159 const updateManyDeletedPosts = await prisma.post.updateMany({
160 where: {
161 deleted: true,
162 id: {
163 in: postsCreated.map((x) => x.id),
164 },
165 },
166 data: {
167 title: "This is an updated title (updateMany)",
168 },
169 });
170
171 console.log();
172
173 console.log(
174 "Deleted post (delete) with ID: " +
175 "\u001b[1;32m" +
176 deletePost.id +
177 "\u001b[0m"
178 );
179 console.log(
180 "Deleted posts (deleteMany) with IDs: " +
181 "\u001b[1;32m" +
182 [postsCreated[1].id + "," + postsCreated[2].id] +
183 "\u001b[0m"
184 );
185 console.log();
186 console.log(
187 "findOne: " +
188 (getOnePost?.id != undefined
189 ? "\u001b[1;32m" + "Posts returned!" + "\u001b[0m"
190 : "\u001b[1;31m" +
191 "Post not returned!" +
192 "(Value is: " +
193 JSON.stringify(getOnePost) +
194 ")" +
195 "\u001b[0m")
196 );
197 console.log(
198 "findMany: " +
199 (getPosts.length == 3
200 ? "\u001b[1;32m" + "Posts returned!" + "\u001b[0m"
201 : "\u001b[1;31m" + "Posts not returned!" + "\u001b[0m")
202 );
203 console.log(
204 "findMany ( delete: true ): " +
205 (getPostsAnDeletedPosts.length == 3
206 ? "\u001b[1;32m" + "Posts returned!" + "\u001b[0m"
207 : "\u001b[1;31m" + "Posts not returned!" + "\u001b[0m")
208 );
209 console.log();
210 console.log(
211 "update: " +
212 (updatePost.id != undefined
213 ? "\u001b[1;32m" + "Post updated!" + "\u001b[0m"
214 : "\u001b[1;31m" +
215 "Post not updated!" +
216 "(Value is: " +
217 JSON.stringify(updatePost) +
218 ")" +
219 "\u001b[0m")
220 );
221 console.log(
222 "updateMany ( delete: true ): " +
223 (updateManyDeletedPosts.count == 3
224 ? "\u001b[1;32m" + "Posts updated!" + "\u001b[0m"
225 : "\u001b[1;31m" + "Posts not updated!" + "\u001b[0m")
226 );
227 console.log();
228 console.log("\u001b[1;34m#################################### \u001b[0m");
229 // 4. Count ALL posts
230 const f = await prisma.post.findMany({});
231 console.log(
232 "Number of active posts: " + "\u001b[1;32m" + f.length + "\u001b[0m"
233 );
234
235 // 5. Count DELETED posts
236 const r = await prisma.post.findMany({
237 where: {
238 deleted: true,
239 },
240 });
241 console.log(
242 "Number of SOFT deleted posts: " + "\u001b[1;32m" + r.length + "\u001b[0m"
243 );
244}
245
246mainAsync();

The example outputs the following:

STARTING SOFT DELETE TEST
####################################
Posts created with IDs: 680,681,682
Deleted post (delete) with ID: 680
Deleted posts (deleteMany) with IDs: 681,682
findOne: Post not returned!(Value is: [])
findMany: Posts not returned!
findMany ( delete: true ): Posts returned!
update: Post not updated!(Value is: {"count":0})
updateMany ( delete: true ): Posts not updated!
####################################
Number of active posts: 0
Number of SOFT deleted posts: 95

✔ Pros of this approach:

  • A developer can make a conscious choice to include deleted records in findMany
  • You cannot accidentally read or update a deleted record

✖ Cons of this approach:

  • Not obvious from API that you aren't getting all records and that { where: { deleted: false } } is part of the default query
  • Return type update affected because middleware changes the query to updateMany

FAQ

Can I add a global includeDeleted to the Post model?

You may be tempted to 'hack' your API by adding a includedDeleted property to the Post model and make the following query possible:

script.ts
1prisma.post.findMany({ where: { includeDeleted: true }});

Note: You would still need to write middleware.

We ✘ do not recommend this approach as it pollutes the schema with fields that do not represent real data.

Edit this page on GitHub