Soft deletes (e.g. `deleted_at`)

[matthewmueller]

It could be nice to add this kind of feature to core, so you get filtered views without cluttering up your application queries

Open questions:

• Would it be slow to add this filter to all queries?
• Can we do it only when we need it?
• Would this be better handled in Photon?

[schickling]

I'm not sure whether this should be a core feature or rather be a pattern that's easy to be implemented in "user land" e.g. by using scoped queries.

[nelsonpecora]

I have a slight preference for this being a userland thing, as I've definitely mixed "hard delete" and "soft delete" models in the same system depending on product requirements (e.g. soft delete Article records, but hard delete Tag records)

[duro]

Some kind of mechinism to support this would be a big help.

[leohxj]

something like GORM, if model have deleteAt field, it auto have soft_delete ability.

http://gorm.io/docs/delete.html

[Hebilicious]

Maybe we could have the ability to tag a model with something like @@softDelete or a field with @deleteAt in the prisma schema?

[LinboLen]

It shoud be a core feature

[carlosfiori]

It would be great

[pantharshit00]

This can be possibly be created using a middleware for now(thanks @mhwelander for the example)

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient({});
async function mainAsync() {
  prisma.$use(async (params, next) => {
    // Check incoming query type
    if (params.model == "Post" && params.action == "delete") {
      // Change action to an update
      params.action = "update";
      // Set field value
      params.args["data"] = { deleted: true };
    }
    return next(params);
  });
  const createPost = await prisma.post.create({
    data: { title: "How to use Prisma Client" },
  });
  const deletePost = await prisma.post.delete({
    where: {
      id: createPost.id,
    },
  });
  const getPost = await prisma.post.findOne({
    where: {
      id: deletePost.id,
    },
  });
  console.log(getPost); // Will return post with deleted:true
}
mainAsync();

[scorsi]

Is there any news about that functionnality ? :)

[euberdeveloper]

This can be possibly be created using a middleware for now(thanks @mhwelander for the example)

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient({});
async function mainAsync() {
  prisma.$use(async (params, next) => {
    // Check incoming query type
    if (params.model == "Post" && params.action == "delete") {
      // Change action to an update
      params.action = "update";
      // Set field value
      params.args["data"] = { deleted: true };
    }
    return next(params);
  });
  const createPost = await prisma.post.create({
    data: { title: "How to use Prisma Client" },
  });
  const deletePost = await prisma.post.delete({
    where: {
      id: createPost.id,
    },
  });
  const getPost = await prisma.post.findOne({
    where: {
      id: deletePost.id,
    },
  });
  console.log(getPost); // Will return post with deleted:true
}
mainAsync();

It is a good solution, but in most cases it is not enough.

I have a relation "building" and another relation "room". The rooms are in the building. Hence, if I use "findUnique" in a building, the query should return only the rooms that are not soft-deleted. Also, if I delete a building, all its rooms should also be soft-deleted. There are millions of soft cases such as this one. This is really painful to implement and a functionality of prisma that handles this would save the life of many people.

[alexolivier]

Definitely a requirement for something I am working on - I'm not sure about the middleware solution especially if it breaks the types

[euberdeveloper]

For now, I implemented something similar to the proposed middleware (I just added things such as count and made the relation that applies to it an array parameter).

To solve the fact that a relation A owns (has a relation one to one or one to many with) a relation B and that soft deleted B tuples should not be kept, I used { include: { [B_name]: { where: { deleted: false } } }

To solve the "cascade soft delete", I just make a transaction in my APIs where alla the B tuples owned by A are soft deleted when A is soft deleted. This is quite doable but it makes the code messy and complicates things.

Another problem with the Middleware is that making a delete an update will not throw an error if the element is already deleted. I mean, in terms of api, if I delete an element by id and then execute again the call, I have a 404 error. If I update it two times, the second call will be a 200. Making a delete an update will not throw any error if the element is already soft deleted.

The most arduous part was the fact that if you have a unique constraint in a soft deletable relation A over the columns pippo and pluto, when you soft delete a tuple, you cannot make again a tuple with the same value for pippo and pluto, because the unique constraint fails on the previous soft deleted tuple. My solution was adding (manually) a unique index on pippo and pluto with a "where _deleted is false" (actually null because I use deletion dates). It would be great to have all of this implemented automatically by prisma.

In an eventual prisma implementation, I would also give a parameter to specify the deleted column name and if it is a Date or a Boolean.

[chrissisura]

I've tried to use the prisma recommended middleware and add something like this

if (params.args.where.deleted === undefined) {
            // Exclude deleted records if they have not been expicitly requested
            params.args.where['deleted'] = null
            params.args.where = iterateArgsAddSoftDeleteFilter(
              params.args.where
            )
          }

with the functions being called would be this:

export function iterateArgsAddSoftDeleteFilter(obj: any): any {
  Object.keys(obj).map((key: string) => {
    if (typeof obj[key] === 'object' && !!obj[key]) {
      const ignoreWords = ['some', 'every', 'none', 'AND', 'OR', 'NOT', 'startsWith', 'in']
      if (
        !Object.keys(obj[key]).some((key) =>
          ignoreWords.some((ignoreWord) => ignoreWord === key)
        )
      ) {
        {
          if (obj[key]?.deleted === undefined) {
            obj[key] = { ...obj[key], deleted: null }
            console.log(obj[key])
          }
        }
      }
      iterateCreateArgsPassword(obj[key])
    }
  })
}

but like @euberdeveloper said this is really messy and I can't cover all available cases as they are too many. So I really think this should be a core feature

[MayasHaddad]

I suspect soft delete can't be achieved with a simple $use middleware since we don't have the info of whether a where clause is allowed in the include subquery query or not

[nghiepdev]

Laravel implements the Soft Deleting feature is very flexible https://laravel.com/docs/8.x/eloquent#soft-deleting

[anthonyjoeseph]

I'd just like to mention that the current soft delete middleware docs recommend replacing update actions with updateMany, but fail to point out that this will cause update's return type to be incorrect (its value will be a BatchPayload but its type will not reflect this)

[chrissisura]

You also cannot update related tables in an updateMany, which produces a very hard to debug error

[limyandi]

I'd just like to mention that the current soft delete example middleware docs recommend replacing update actions with updateMany, but fail to point out that this will cause update's return type to be incorrect (its value will be a BatchPayload but its type will not reflect this)

Hi @anthonyjoeseph , what would then be the solution to this issue?

[janpio]

Can you open an issue about this @anthonyjoeseph? If the current example in the docs does not work well, we should either fix it or remove it. And types not matching seems like quite a problem.

[lalarsson87]

I'm experiencing the same problem and would very much like it to be possible to include a where clause for includes.

[dimino]

I'd just like to mention that the current soft delete example middleware docs recommend replacing update actions with updateMany, but fail to point out that this will cause update's return type to be incorrect (its value will be a BatchPayload but its type will not reflect this)

I solved this by just disallowing updates on soft deleted data (throws an error when it hits the if (params.action === "update") clause). Not that I need the data frozen when its deleted, but for my work there's no valid use case for updating "deleted" data.

[m3hari]

I just want to mention that the docs approach doesn't allow us to enforce the referential actions(cascading policies) for delete, because there is no real delete operation at the DB engine level.

Also, it doesn't cover ' filtering deleted relations'. This is doable with something like the following, but it's quite inefficient and error-prone.

As for the implementation of this, it would really nice to see a database trigger approach if possible.

[janpio]

If there are limitations with the current middleware workaround approach from the docs, it would be neat if someone could collect these and create a PR against the docs pages. We definitely do not want to mislead people - but generally this seems useful to keep around (vs. just remove it as it does not solve all problems and fulfills all expectations).

[m3hari]

At least, it would be nice to least all the caveats of the middleware approach in the docs

[gitChristian]

You also cannot update related tables in an updateMany, which produces a very hard to debug error

Ya the docs fail to point out that if you use the soft delete middleware they suggest, you can't do a nested query using update or updateMany Am I correct? @anthonyjoeseph

[laurence702]

it would be nice to have something similar to what laravels eloquent has on soft delete implementation. and like @anthonyjoeseph mentioned, the current implementation might cause a break in update query return type

[georgekrax]

Does Prisma plan to support soft-deletes in the near future?

[mmmmmrob]

Another vote for soft deletes please.

[rostamiani]

This is an implementation tor TypeOrm. Something similar is needed: https://github.com/iWinston/typeorm-plus#1-including-soft-deleted-entities

export class Entity {

    @DeleteDateColumn({ name: 'deleted_at' })
    public deletedAt: Date

}

[paololim]

Also running into the issues others have already mentioned here.

Prisma doesn't allow us to define a conditional unique index. This is what we really need from SQL to be supported in Prisma:

CREATE UNIQUE INDEX "projects_organization_id_name_key" 
ON "projects"("organization_id", "name") 
WHERE ("deleted_at" is NULL);

Right now, we can only do:

@@unique([organizationId, name])

Which will produce the SQL without the: WHERE ("deleted_at" is NULL);.

We can't put deleted_at in the unique index, since the null value is treated by the index as unique from each other:

@@unique([organizationId, name, deletedAt])

These two insert statements will go through, since multiple rows deleted_at being null are actually different from each other, this is why we just can't add the deleted_at as part of the unique index.

insert into projects (organization_id, name, deleted_at) values ('foo', 'bar', null);
Insert 1
insert into projects (organization_id, name, deleted_at) values ('foo', 'bar', null);
Insert 1

Proposal here is to be able to define something along the lines of:

@@unique([organizationId, name], where: {deletedAt: null})