Prisma1: Cascading Deletes

[sorenbs]

UPDATE: We will adopt the terminology given in the proposal below

Cascading deletes is an essential tool to maintain referential integrity.

Definition in types.graphql

This is a typical one-many relationship where a Comment cannot exist without a Blog.
A comment can have an author, but doesn't have to.

type Blog @model {
  id: ID! @isUnique
  comments: [Comment!]! @relation(name: "Comments", cascadeDelete: true)
  owner: User! @relation(name: "BlogOwner", cascadeDelete: false)
}

type Comment @model {
  id: ID! @isUnique
  blog: Blog! @relation(name: "Comments", cascadeDelete: false)
  author: User @relation(name: "CommentAuthor", cascadeDelete: false)
}

type User @model {
  id: ID! @isUnique
  comments: [Comment!]! @relation(name: "CommentAuthor", cascadeDelete: true)
  blog: Blog @relation(name: "BlogOwner", cascadeDelete: true)
}

• When a Blog is deleted, all Comments associated through the comments field are deleted.
• When a Comment is deleted, the related Blog will not be deleted, but the Comment is removed from the comments field. Same for related User if any.
• When a User is deleted, all related Comments are deleted. If there is a related Blog it is deleted together with all related comments.

Required relations and Cascading Delete

Relation fields can be required. If a non-list relation field is required and the related node is deleted there are two cases:

• cascadeDelete: true both nodes are deleted
• cascadeDelete: false no nodes are deleted

Effect on AddTo and RemoveFrom relations

Cascading Delete only affects mutations that delete a node.

If an AddTo and RemoveFrom relation would result in a required relation without a node, it will be blocked no matter what the cascadingDelete setting is.

Cascading Update

In the future we will introduce the ability to use a custom id for nodes. We might also make it possible to change the id of a node with the Update mutation. If we do so, we will introduce a setting for cascadeUpdate that will control if the new id should be propagated to relations or the relations should be broken.

Todo

• [ ] Explore whether deleting behaviour can be provided/overwritten via GraphQL API

[kbrandwijk]

I feel that just a true/false setting is oversimplifying the cases this needs to cover. There is a reason why cascading referential constraints are not a simple binary switch.

For example, I want to prevent the delete if there are children linked to it (compared to ON DELETE NO ACTION in SQL Server). This is currently not possible in Graphcool at all if the relation is not required, but the default behavior for many databases.
Or, I want to delete the children (compared to ON DELETE CASCADE).
Or, I want to leave the 'orphan' children (compared to ON DELETE SET NULL) - The current default

Ideally, I could also set the default behavior, but explicitly specifying it is fine for now.

[sorenbs]

All of these cases are handled by the proposal:

• prevent the delete if there are children linked to it: required and cascadeDelete: false
• delete the children: required or optional and cascadeDelete: true
• leave the 'orphan' children: optional and cascadeDelete: false

[kbrandwijk]

I added some detail to my answer. I cannot prevent the delete it the relation is not required. So it's only about that first point now.

Compare it to folders and files. I can create an empty folder (files are not required), and I can delete an empty folder, but when there are files in the folder, I can't delete folder because I get an error that it's not empty.

This is actually a pattern that is very common. And making it part of the cascading configuration would be a lot more consistent than having to write a permission query for it.

[sorenbs]

That's a great point!

Let's examine the different cases. In a parent-child relationship:

• The parent can be required or optional
• The child is always optional

1. The parent is required and you want to prevent deleting a parent when there are children. cascadeDelete: false
2. The parent is required and you want to delete all children when the parent is deleted. cascadeDelete: true
3. The parent is optional and you want to prevent deleting a parent when there are children. not possible
4. The parent is optional and you want to delete all children when the parent is deleted. cascadeDelete: true

To handle all cases we need to introduce three different settings:

• no action: don't delete the current node if there are related nodes
• cascade: delete related nodes
• set null: keep related nodes and remove from relation

The most straight-forward option is to use relational terminology directly:

type Blog @model {
  id: ID! @isUnique
  comments: [Comment!]! @relation(name: "Comments", onDelete: CASCADE)
  owner: User! @relation(name: "BlogOwner", onDelete: SET_NULL)
}

type Comment @model {
  id: ID! @isUnique
  blog: Blog! @relation(name: "Comments", onDelete: NO_ACTION)
  author: User @relation(name: "CommentAuthor", onDelete: NO_ACTION)
}

type User @model {
  id: ID! @isUnique
  comments: [Comment!]! @relation(name: "CommentAuthor", onDelete: CASCADE)
  blog: Blog @relation(name: "BlogOwner", onDelete: CASCADE)
}

If anyone can come up with better terminology I'd be happy to adopt it.

We decided not to implement the NO_ACTION option until we see a real need for it.

[cameronk]

Checking in to see what the timeframe looks like for this. Any updates?

[marktani]

This feature is currently work in progress and will rolled out soon 🙂

[do4gr]

The second part of the implementation is now done and we will release this shortly.

We decided to not implement NO_ACTION for now. If we get the feeling that there is a strong need for this we will add this later. For now SET_NULL is encoding the old behavior and CASCADE will try to delete connected nodes but still guarantee integrity rules specified by required relations in the schema.

[emipc]

Will it work for nested connections like this?

type Course {
    sections: [CourseSection!]! @relation(name: "CourseSections", onDelete: CASCADE)
}

type CourseSection {
    course: Course! @relation(name: "CourseSections")
    items: [CourseSectionItem!]! @relation(name: "CourseSectionItems", onDelete: CASCADE)
}

type CourseSectionItem {
    section: CourseSection! @relation(name: "CourseSectionItems")
}

Can't wait to try this feature! 🙂

[marktani]

@emipc, you can try it out locally right now 🙂

Here's more information about running the latest changes in the unstable channel.

[emipc]

I've installed prisma/1.2.0-beta.5 and tried it with the previous schema. Unfortunately, I got this error:

Error: The change you are trying to make would violate the required relation '_CourseSections' between Course and CourseSection.

This is my resolver:

async deleteCourse(parent, { id }, ctx, info) {
    const exists = await ctx.db.exists.Course({
        id
    });

    if (!exists) {
        throw new Error('Course not found.');
    }

    return ctx.db.mutation.deleteCourse({ where: { id } }, info);
},

And this is the mutation I'm running:

mutation DeleteCourse($id: ID!) {
    deleteCourse(id: $id) {
        id
    }
}

Similar error happens if I try to delete a CourseSection, so it's not related to nested connections.

Am I doing something wrong?

[emipc]

Well, as usually, after doing a prisma local nuke and a new deploy, it works! I guess prisma local upgrade isn't doing what it should!

Nice job, this is a very cool feature 🙂

[do4gr]

Glad to hear it works, but trying to reproduce your issue already showed me another small bug. Changing the onDelete argument does not seem to be picked up at the moment when deploying changes.
So your report was not in vein ;-)

[emipc]

Then I'm glad to be a useful beta tester!

[marktani]

This was released in 1.2 and is confirmed to work stable. Note that as of now, cascading deletes don't work for deleteMany or updateMany: https://github.com/graphcool/prisma/issues/1936.