Skip to main content

Soft Delete: Dealing With Unique Constraint in Real-World Case

Β· 3 min read

Cover Image I introduced how to achieve soft delete in ZenStack in the previous post below:

Soft delete: Implementation issues in Prisma and solution in ZenStack

The solution appears quite elegant with the help of the access policy in the schema.

model Post {
deleted Boolean @default(false) @omit
@@deny(β€˜read’, deleted)

There are users who come to ZenStack specifically for the soft delete feature after reading the post:


This was an β€œaha” moment in building ZenStack for me to be able to provide an ideal solution for a very common issue:


However, soon after, the ideal was shattered by reality.

Real-world Problem

A user once asked how to create a partial index with a where condition in ZenStack to implement soft delete:

yeah .. problem is if we implemented soft delete we need this partial index otherwise normal index will consider delete = true record as well .. And that's problem πŸ™

It immediately brought me back to three years ago when I encountered the same issue. Ironically, I even searched for the same article I had looked at three years ago πŸ˜‚ :

Dealing with MySQL nulls and unique constraint

TLDR, the correct solution should be to make the deleted field an integer having the default value of 0. When it is deleted, set the value to the timestamp of deletion:

model Post {
id String @id @default(uuid())
// name should be unique
name String
// when deleting, set it to the timestamp of deletion
deleted Int @default(0) @omit

@@unique([name, deleted])
@@deny('read', deleted != 0)

While it may not be as elegant as before, it is the solution that addresses the real-world problem.

From Ideal to Reality

In the ever-evolving software world, we often come across solutions that appear flawless on the surface, promising to solve all our problems effortlessly. These seemingly perfect solutions may dazzle us with their elegance and efficiency, giving us a glimpse of an idealized future. However, as we venture deeper into the realm of real-world challenges, we discover that these picture-perfect solutions often fall short of addressing the complexities and intricacies that arise in practical scenarios.

The software landscape is a realm where theory and practice sometimes diverge. While idealism inspires innovation, it is the pragmatic, adaptable, and context-aware solutions that ultimately triumph in overcoming the real problems we face. Hence, it is important to always keep in mind the importance of real-world cases and make appropriate trade-offs based on them.

Apart from this one, there are certainly other instances within ZenStack that may represent ideal cases. If you believe ZenStack has the potential to assist you, we would deeply appreciate your collaboration in transforming those ideals into tangible realities.🀝

Feel free to ask questions, give feedback, or report issues.

Don't Spam

You can edit/delete your comments by going directly to the discussion, clicking on the 'comments' link below