Add deletedAt system field for Soft Deletion

[Saeris]

This is a feature request to support soft-deletion.

I'm proposing the addition of a deletedAt system field, which would be a nullable DateTime scalar. A value of null would indicate that the row is active, and any date value would denote a soft-deleted row.

Might need to take into consideration what to do about @unique fields and how they conflict with soft-deleted rows. This may depend on the underlying database architecture.

People may have varying opinions on how to best implement soft-deletes. I don't consider myself an expert in the subject. If this is something the team considers Prisma shouldn't be opinionated about, perhaps an alternative to standardizing an implementation, then providing example approaches in the documentation could be useful.

Ideas and discussion are welcome.

[alapini]

@Saeris , soft-deletion is an interesting feature, but I think it is more appropriate to implement it in a custom layer on top of Prisma, for example in a self-made graphql-yoga server.

I don't know much about Prisma plans, but I see Prisma at a more canonical level, which means : when I instruct Primsa to delete a node, I expect this node to be literraly erased from the drive (by running the DELETE command of the underlying SQL database for example).

[notadamking]

@alapini I disagree. All of my data is stored in the Prisma database, it doesn't make sense to bring this one piece of data into a custom layer on top. I want my database to give me the option to soft-delete a row, so I don't need to create the custom logic for every table in my database that I want to provide soft deletions for.

[alapini]

@adamjking3 , implementing soft deletion can be basically :

• Adding a nullable deletedAt field in every model
• Adding a filter (deletedAt = null) to every request to non-soft-deleted data.

But in practice, managing @unique fields in in the underlying database when keeping soft-deleted data in the same table as active data can become challenging. Lets say you have :

type User {
  username : String! @unique
  ...
}

and for performance reason, the username field should be reasonably marked as unique in the underlying database.

When you soft-delete a User node with the username john55. This node is still in the the underlying table. Then, when you want to create a new User with username john55, there will be a conflict on the username field in the table, as it is marked as unique.

But a frequent workaround is to create distinct tables in the database to store soft-deleted records. But in this case, it can be not-so-natural to implement how to serve both soft-deleted and active data in response to the same gql request.

My opinion is that, if implementing soft-deletion :

• will not affect significantly the data structure of my active data (db migration friendly)
• will not drop the performance of the database
• and will keep the gql api "nice" and without strong opinions

It would be fine to have this feature available in Prisma.

[tsdexter]

Considering all the tag changes in #218 from a year ago, it seems the team has discussed it several times, but hasn’t come to a conclusion yet. Would be great to see it implemented (👍 to see it come to graphcool-framework too!)

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[gavinservai]

Is there any update on any potential plans for this feature?

[khaled-hossain-code]

This feature can be a deal breaker for enterprise apps where soft delete is a very common scenario.

[silverqx]

Soft Delete will be challenge, have to be supported everywhere, user have to tag entity which will support soft delete eg by new directive, in this case deletedAt property will be required, so interface in this scenario is good idea:

type User @softdelete {
  id: ID!
  ...
  deletedAt: DateTime!
}

prisma-binding and prisma-client will have to take into account soft deleted rows, soft deleted rows can not be in result set. Plus some function parameter or some other mechanism how to obtain result set with soft deleted rows, e.g.:

prisma.trashed.Users()
prisma.query.users({
  withTrashed: true,
  where: {
    email_contains: "example.com"
  }
})

This feature can design only prisma expert, who knows well whole prisma source code. Until that we have to manage this feature ourselves in resolvers.

[carstenbaumhoegger]

I also have to implement something soft deletion into one of our applications. Is there any best practice approach out now that is usable as a blueprint or do I have to implement everything myself?

[dantman]

When you soft-delete a User node with the username john55. This node is still in the the underlying table. Then, when you want to create a new User with username john55, there will be a conflict on the username field in the table, as it is marked as unique.

This is a feature of soft delete, not a bug. If allowing new users to use old names is desired removing the username (optionally storing it in an alt field for records) or an archive table can be used instead. However in practice many services do not allow new users to use a username that belongs to a user that recently deleted their account. Which is a pretty typical scenario where soft delete might be used i.e. soft delete on user request then hard delete/archive after enough time has passed to avoid user impersonation scenarios and for account hacks to be reported.

[monster010]

I don't understand the problem that you partially have.

Where is the problem when a deletedAt field is built in? It is like a createdAt and updatedAt field, if it does not exist it is not used, if it exists it is used. Sure, if you have a user table in which usernames are marked as unique and you then use softdeletes, the username cannot be used again, but where is the problem? If you are bothered by the fact that the username cannot be used again, do not use a deletedAt field ?!

Furthermore, you don't necessarily have to use it for a user table, there are scenarios where I like to use these fields.