Skip to main content

Middleware sample: soft delete

The following sample uses middleware to perform a soft delete. Soft delete 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
warning

Note: This page demonstrates a sample use of middleware. We do not intend the sample to be a fully functional soft delete feature and it does not cover all edge cases. For example, the middleware does not work with nested writes and therefore won't capture situations where you use delete or deleteMany as an option e.g. in an update query.

This sample 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 sample 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 sample 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 sample 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 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 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 Client 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 Client'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 Client 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
🔧 From version 5.0.0, you can use findUnique() to apply delete: false filters since non unique fields are exposed.
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
  • Is it not possible to utilize soft delete with findFirstOrThrow() or findUniqueOrThrow()?
    From version 5.1.0, you can apply soft delete findFirstOrThrow() or findUniqueOrThrow() by using middleware.
  • Why are you making it possible to use findMany() with a { where: { deleted: true } } filter, but not updateMany()?
    This particular sample 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 sample - 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, Prisma } 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' || params.action === 'findFirst') {
// 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 === 'findFirstOrThrow' ||
params.action === 'findUniqueOrThrow'
) {
if (params.args.where) {
if (params.args.where.deleted == undefined) {
// Exclude deleted records if they have not been explicitly requested
params.args.where['deleted'] = false
}
} else {
params.args['where'] = { deleted: false }
}
}
if (params.action === 'findMany') {
// Find many queries
if (params.args.where) {
if (params.args.where.deleted == undefined) {
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 getOneUniquePostOrThrow = async () =>
await prisma.post.findUniqueOrThrow({
where: {
id: postsCreated[0].id,
},
})

const getOneFirstPostOrThrow = async () =>
await prisma.post.findFirstOrThrow({
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')
)
try {
console.log('findUniqueOrThrow: ')
await getOneUniquePostOrThrow()
} catch (error) {
if (
error instanceof Prisma.PrismaClientKnownRequestError &&
error.code == 'P2025'
)
console.log(
'\u001b[1;31m' +
'PrismaClientKnownRequestError is catched' +
'(Error name: ' +
error.name +
')' +
'\u001b[0m'
)
}
try {
console.log('findFirstOrThrow: ')
await getOneFirstPostOrThrow()
} catch (error) {
if (
error instanceof Prisma.PrismaClientKnownRequestError &&
error.code == 'P2025'
)
console.log(
'\u001b[1;31m' +
'PrismaClientKnownRequestError is catched' +
'(Error name: ' +
error.name +
')' +
'\u001b[0m'
)
}
console.log()
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 sample 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
  • Doesn't handle complex queries with AND, OR, every, etc...
  • Doesn't handle filtering when using include from another model.

FAQ

Can I add a global includeDeleted to the Post model?

You may be tempted to 'hack' your API by adding a includeDeleted 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.