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 model:

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
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 @id @default(autoincrement())
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 @id @default(autoincrement())
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:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({})
async function main() {
/***********************************/
/* SOFT DELETE MIDDLEWARE */
/***********************************/
prisma.$use(async (params, next) => {
// Check incoming query type
if (params.model == 'Post') {
if (params.action == 'delete') {
// Delete queries
// Change action to an update
params.action = 'update'
params.args['data'] = { deleted: true }
}
if (params.action == 'deleteMany') {
// Delete many queries
params.action = 'updateMany'
if (params.args.data != undefined) {
params.args.data['deleted'] = true
} else {
params.args['data'] = { deleted: true }
}
}
}
return next(params)
})
/***********************************/
/* TEST */
/***********************************/
const titles = [
{ title: 'How to create soft delete middleware' },
{ title: 'How to install Prisma' },
{ title: 'How to update a record' },
]
console.log('\u001b[1;34mSTARTING SOFT DELETE TEST \u001b[0m')
console.log('\u001b[1;34m#################################### \u001b[0m')
let i = 0
let posts = new Array()
// Create 3 new posts with a randomly assigned title each time
for (i == 0; i < 3; i++) {
const createPostOperation = prisma.post.create({
data: titles[Math.floor(Math.random() * titles.length)],
})
posts.push(createPostOperation)
}
var postsCreated = await prisma.$transaction(posts)
console.log(
'Posts created with IDs: ' +
'\u001b[1;32m' +
postsCreated.map((x) => x.id) +
'\u001b[0m'
)
// Delete the first post from the array
const deletePost = await prisma.post.delete({
where: {
id: postsCreated[0].id, // Random ID
},
})
// Delete the 2nd two posts
const deleteManyPosts = await prisma.post.deleteMany({
where: {
id: {
in: [postsCreated[1].id, postsCreated[2].id],
},
},
})
const getPosts = await prisma.post.findMany({
where: {
id: {
in: postsCreated.map((x) => x.id),
},
},
})
console.log()
console.log(
'Deleted post with ID: ' + '\u001b[1;32m' + deletePost.id + '\u001b[0m'
)
console.log(
'Deleted posts with IDs: ' +
'\u001b[1;32m' +
[postsCreated[1].id + ',' + postsCreated[2].id] +
'\u001b[0m'
)
console.log()
console.log(
'Are the posts still available?: ' +
(getPosts.length == 3
? '\u001b[1;32m' + 'Yes!' + '\u001b[0m'
: '\u001b[1;31m' + 'No!' + '\u001b[0m')
)
console.log()
console.log('\u001b[1;34m#################################### \u001b[0m')
// 4. Count ALL posts
const f = await prisma.post.findMany({})
console.log('Number of posts: ' + '\u001b[1;32m' + f.length + '\u001b[0m')
// 5. Count DELETED posts
const r = await prisma.post.findMany({
where: {
deleted: true,
},
})
console.log(
'Number of SOFT deleted posts: ' + '\u001b[1;32m' + r.length + '\u001b[0m'
)
}
main()

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!
####################################

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

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 step explores two ways to 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 user 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:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({})
async function main() {
/***********************************/
/* SOFT DELETE MIDDLEWARE */
/***********************************/
prisma.$use(async (params, next) => {
if (params.model == 'Post') {
if (params.action == 'findUnique') {
// Change to findFirst - you cannot filter
// by anything except ID / unique with findUnique
params.action = 'findFirst'
// Add 'deleted' filter
// ID filter maintained
params.args.where['deleted'] = false
}
if (params.action == 'findMany') {
// Find many queries
if (params.args.where != undefined) {
if (params.args.where.deleted == undefined) {
// Exclude deleted records if they have not been expicitly requested
params.args.where['deleted'] = false
}
} else {
params.args['where'] = { deleted: false }
}
}
}
return next(params)
})
prisma.$use(async (params, next) => {
if (params.model == 'Post') {
if (params.action == 'update') {
// Change to updateMany - you cannot filter
// by anything except ID / unique with findUnique
params.action = 'updateMany'
// Add 'deleted' filter
// ID filter maintained
params.args.where['deleted'] = false
}
if (params.action == 'updateMany') {
if (params.args.where != undefined) {
params.args.where['deleted'] = false
} else {
params.args['where'] = { deleted: false }
}
}
}
return next(params)
})
prisma.$use(async (params, next) => {
// Check incoming query type
if (params.model == 'Post') {
if (params.action == 'delete') {
// Delete queries
// Change action to an update
params.action = 'update'
params.args['data'] = { deleted: true }
}
if (params.action == 'deleteMany') {
// Delete many queries
params.action = 'updateMany'
if (params.args.data != undefined) {
params.args.data['deleted'] = true
} else {
params.args['data'] = { deleted: true }
}
}
}
return next(params)
})
/***********************************/
/* TEST */
/***********************************/
const titles = [
{ title: 'How to create soft delete middleware' },
{ title: 'How to install Prisma' },
{ title: 'How to update a record' },
]
console.log('\u001b[1;34mSTARTING SOFT DELETE TEST \u001b[0m')
console.log('\u001b[1;34m#################################### \u001b[0m')
let i = 0
let posts = new Array()
// Create 3 new posts with a randomly assigned title each time
for (i == 0; i < 3; i++) {
const createPostOperation = prisma.post.create({
data: titles[Math.floor(Math.random() * titles.length)],
})
posts.push(createPostOperation)
}
var postsCreated = await prisma.$transaction(posts)
console.log(
'Posts created with IDs: ' +
'\u001b[1;32m' +
postsCreated.map((x) => x.id) +
'\u001b[0m'
)
// Delete the first post from the array
const deletePost = await prisma.post.delete({
where: {
id: postsCreated[0].id, // Random ID
},
})
// Delete the 2nd two posts
const deleteManyPosts = await prisma.post.deleteMany({
where: {
id: {
in: [postsCreated[1].id, postsCreated[2].id],
},
},
})
const getOnePost = await prisma.post.findUnique({
where: {
id: postsCreated[0].id,
},
})
const getPosts = await prisma.post.findMany({
where: {
id: {
in: postsCreated.map((x) => x.id),
},
},
})
const getPostsAnDeletedPosts = await prisma.post.findMany({
where: {
id: {
in: postsCreated.map((x) => x.id),
},
deleted: true,
},
})
const updatePost = await prisma.post.update({
where: {
id: postsCreated[1].id,
},
data: {
title: 'This is an updated title (update)',
},
})
const updateManyDeletedPosts = await prisma.post.updateMany({
where: {
deleted: true,
id: {
in: postsCreated.map((x) => x.id),
},
},
data: {
title: 'This is an updated title (updateMany)',
},
})
console.log()
console.log(
'Deleted post (delete) with ID: ' +
'\u001b[1;32m' +
deletePost.id +
'\u001b[0m'
)
console.log(
'Deleted posts (deleteMany) with IDs: ' +
'\u001b[1;32m' +
[postsCreated[1].id + ',' + postsCreated[2].id] +
'\u001b[0m'
)
console.log()
console.log(
'findUnique: ' +
(getOnePost?.id != undefined
? '\u001b[1;32m' + 'Posts returned!' + '\u001b[0m'
: '\u001b[1;31m' +
'Post not returned!' +
'(Value is: ' +
JSON.stringify(getOnePost) +
')' +
'\u001b[0m')
)
console.log(
'findMany: ' +
(getPosts.length == 3
? '\u001b[1;32m' + 'Posts returned!' + '\u001b[0m'
: '\u001b[1;31m' + 'Posts not returned!' + '\u001b[0m')
)
console.log(
'findMany ( delete: true ): ' +
(getPostsAnDeletedPosts.length == 3
? '\u001b[1;32m' + 'Posts returned!' + '\u001b[0m'
: '\u001b[1;31m' + 'Posts not returned!' + '\u001b[0m')
)
console.log()
console.log(
'update: ' +
(updatePost.id != undefined
? '\u001b[1;32m' + 'Post updated!' + '\u001b[0m'
: '\u001b[1;31m' +
'Post not updated!' +
'(Value is: ' +
JSON.stringify(updatePost) +
')' +
'\u001b[0m')
)
console.log(
'updateMany ( delete: true ): ' +
(updateManyDeletedPosts.count == 3
? '\u001b[1;32m' + 'Posts updated!' + '\u001b[0m'
: '\u001b[1;31m' + 'Posts not updated!' + '\u001b[0m')
)
console.log()
console.log('\u001b[1;34m#################################### \u001b[0m')
// 4. Count ALL posts
const f = await prisma.post.findMany({})
console.log(
'Number of active posts: ' + '\u001b[1;32m' + f.length + '\u001b[0m'
)
// 5. Count DELETED posts
const r = await prisma.post.findMany({
where: {
deleted: true,
},
})
console.log(
'Number of SOFT deleted posts: ' + '\u001b[1;32m' + r.length + '\u001b[0m'
)
}
main()

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
findUnique: 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:

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