中间件示例：软删除
以下示例使用 中间件 执行软删除。软删除意味着通过将 deleted 等字段更改为 true 来将记录标记为已删除，而不是实际从数据库中删除。使用软删除的原因包括：
¥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
-
'垃圾' / 'bin' 功能允许用户恢复已删除的内容
¥'Trash' / 'bin' functionality that allows users to restore content that was deleted
注意：本页演示了中间件的示例使用。我们不打算将示例作为功能齐全的软删除功能，并且它不涵盖所有边缘情况。例如，中间件不适用于嵌套写入，因此不会捕获使用 delete 或 deleteMany 作为选项的情况，例如在 update 查询中。
¥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.
该示例使用以下架构 - 请注意 Post 型号上的 deleted 字段：
¥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[]
}
步骤 1：存储记录状态
¥Step 1: Store status of record
将名为 deleted 的字段添加到 Post 模型中。你可以根据你的要求在两种字段类型之间进行选择：
¥Add a field named deleted to the Post model. You can choose between two field types depending on your requirements:
-
Boolean，默认值为false：¥
Booleanwith a default value offalse:model Post {
id Int @id @default(autoincrement())
...
deleted Boolean @default(false)
} -
创建可为空的
DateTime字段，以便你准确了解记录被标记为已删除的时间 -NULL表示一条记录未被删除。在某些情况下，存储删除记录的时间可能是监管要求：¥Create a nullable
DateTimefield so that you know exactly when a record was marked as deleted -NULLindicates 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?
}
注意：使用两个单独的字段（
isDeleted和deletedDate）可能会导致这两个字段不同步 - 例如，一条记录可能被标记为已删除，但没有关联的日期。）¥Note: Using two separate fields (
isDeletedanddeletedDate) may result in these two fields becoming out of sync - for example, a record may be marked as deleted but have no associated date.)
为简单起见，此示例使用 Boolean 字段类型。
¥This sample uses a Boolean field type for simplicity.
第 2 步：软删除中间件
¥Step 2: Soft delete middleware
添加执行以下任务的中间件：
¥Add a middleware that performs the following tasks:
-
拦截
Post模型的delete()和deleteMany()查询¥Intercepts
delete()anddeleteMany()queries for thePostmodel -
将
params.action分别更改为update和updateMany¥Changes the
params.actiontoupdateandupdateManyrespectively -
引入
data参数并设置{ deleted: true }，保留其他过滤器参数（如果存在）¥Introduces a
dataargument 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!
####################################
注释掉中间件以查看消息变化。
¥Comment out the middleware to see the message change.
✔ 这种软删除方法的优点包括：
¥✔ Pros of this approach to soft delete include:
-
软删除发生在数据访问级别，这意味着除非使用原始 SQL，否则无法删除记录
¥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:
-
内容仍然可以阅读和更新，除非你明确按
where: { deleted: false }过滤 - 在有大量查询的大型项目中，存在软删除内容仍会显示的风险¥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 -
你仍然可以使用原始 SQL 来删除记录
¥You can still use raw SQL to delete records
你可以在数据库级别创建规则或触发器（MySQL 和 PostgreSQL）以防止记录被删除。
¥You can create rules or triggers (MySQL and PostgreSQL) at a database level to prevent records from being deleted.
步骤 3：（可选）阻止读取/更新软删除记录
¥Step 3: Optionally prevent read/update of soft deleted records
在步骤 2 中，我们实现了防止删除 Post 记录的中间件。但是，你仍然可以读取和更新已删除的记录。此步骤探讨了两种防止读取和更新已删除记录的方法。
¥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.
选项 1：在你自己的应用代码中实现过滤器
¥Option 1: Implement filters in your own application code
在此选项中：
¥In this option:
-
Prisma Client 中间件负责防止记录被删除
¥Prisma Client middleware is responsible for preventing records from being deleted
-
你自己的应用代码（可以是 GraphQL API、REST API、模块）负责在读取和更新数据时在必要时 (
{ where: { deleted: false } }) 过滤掉已删除的帖子 - 例如，getPostGraphQL 解析器永远不会返回已删除的帖子¥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, thegetPostGraphQL resolver never returns a deleted post
✔ 这种软删除方法的优点包括：
¥✔ Pros of this approach to soft delete include:
-
Prisma 客户端的创建/更新查询没有变化 - 如果需要，你可以轻松请求删除的记录
¥No change to Prisma Client's create/update queries - you can easily request deleted records if you need them
-
修改中间件中的查询可能会产生一些意想不到的后果，例如更改查询返回类型（请参阅选项 2）
¥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
-
如果你的 API 表面非常大并且由多个贡献者维护，则可能很难执行某些业务规则（例如，不允许更新已删除的记录）
¥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)
选项 2：使用中间件确定已删除记录的读取/更新查询的行为
¥Option 2: Use middleware to determine the behavior of read/update queries for deleted records
选项二使用 Prisma Client 中间件来防止软删除记录被返回。下表描述了中间件如何影响每个查询：
¥Option two uses Prisma Client middleware to prevent soft deleted records from being returned. The following table describes how the middleware affects each query:
| 查询 | 中间件逻辑 | 返回类型的更改 | |
|---|---|---|---|
findUnique() | 🔧 将查询更改为 findFirst（因为你无法将 deleted: false 过滤器应用于 findUnique()） 🔧 添加 where: { deleted: false } 过滤器以排除软删除的帖子 🔧 从版本 5.0.0 开始，你可以使用 findUnique() 从 非唯一字段被暴露 开始应用 delete: false 过滤器。 | 不用找了 | |
findMany | 🔧 添加 where: { deleted: false } 过滤器，默认排除软删除帖子 🔧 允许开发者通过指定 deleted: true 显式请求软删除帖子 | 不用找了 | |
update | 🔧 将查询更改为 updateMany（因为你无法将 deleted: false 过滤器应用于 update） 🔧 添加 where: { deleted: false } 过滤器以排除软删除的帖子 | { count: n } 代替 Post | |
updateMany | 🔧 添加 where: { deleted: false } 过滤器以排除软删除的帖子 | 不用找了 |
-
findFirstOrThrow()或findUniqueOrThrow()不能使用软删除吗？
从 5.1.0 版本开始，你可以通过中间件应用软删除findFirstOrThrow()或findUniqueOrThrow()。¥Is it not possible to utilize soft delete with
findFirstOrThrow()orfindUniqueOrThrow()?
From version 5.1.0, you can apply soft deletefindFirstOrThrow()orfindUniqueOrThrow()by using middleware. -
为什么可以将
findMany()与{ where: { deleted: true } }过滤器一起使用，而不是updateMany()？
编写此特定示例是为了支持用户可以恢复已删除博客文章的场景（这需要软删除帖子的列表） - 但用户不应该能够编辑已删除的帖子。¥Why are you making it possible to use
findMany()with a{ where: { deleted: true } }filter, but notupdateMany()?
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. -
我还可以对已删除的帖子进行
connect或connectOrCreate吗？
在此示例中 - 是的。中间件不会阻止你将现有的软删除帖子连接到用户。¥Can I still
connectorconnectOrCreatea 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:
-
开发者可以有意识地选择将已删除的记录包含在
findMany中¥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:
-
从 API 来看，你没有获取所有记录并且
{ where: { deleted: false } }是默认查询的一部分并不明显¥Not obvious from API that you aren't getting all records and that
{ where: { deleted: false } }is part of the default query -
返回类型
update受到影响，因为中间件将查询更改为updateMany¥Return type
updateaffected because middleware changes the query toupdateMany -
不处理
AND、OR、every等复杂查询...¥Doesn't handle complex queries with
AND,OR,every, etc... -
使用其他型号的
include时不处理过滤。¥Doesn't handle filtering when using
includefrom another model.
常见问题
¥FAQ
我可以在 Post 模型上添加全局 includeDeleted 吗？
¥Can I add a global includeDeleted to the Post model?
你可能会想通过向 Post 模型添加 includeDeleted 属性来对你的 API 进行 'hack' 操作，并使以下查询成为可能：
¥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.