Cascading Deletes - Implementation Strategy for SQL connector

[mavilein]

Goal: This issue describes how we plan to implement the feature of cascading deletes available in the Prisma Schema Language.

Idea: We would like to leverage SQLs ON DELETE CASCADE feature wherever possible. However it does not support all the usecases of Prismas cascading deletes. The idea is that the SQL feature is used as often as possible and shims are implemented where necessary in the query engine. The parts that need to be shimmed are indicate with a 🚨 below.

Problems:

• Contrary to intuition a @relation(onDelete: CASCADE) on a field implies a SQL level ON DELETE CASCADE on the column of the related field. 💥
• Prisma provides a field for both sides of a relation that can take a onDelete annotation. On the SQL level there is only one column, and therefore only the behavior for one side can be expressed on the SQL level.
• For Many to Many relations we cannot express the behavior we want (deletion traversing through the join table) on the SQL level.

Analysis of where the SQL On Delete Cascasde works and where not

One To Many: Cascade from the Parent.

This could work purely on the SQL level and could also be introspected from the DDL.

Prisma schema:

model Parent {
  children Child[] @relation(onDelete: CASCADE)
}

model Child {
  parent Parent // this references the parent id
}

corresponding SQL:

Create Table Parent ( id Text );
Create Table Child ( id Text, 
                                  parent text REFERENCES Parent(id) ON DELETE CASCADE );

Semantics:

• delete parent: children get deleted ✅
• delete child: parent remains ✅

One To Many: Cascade from the Child.

This cannot be expressed on the SQL level and would need to be handled by Prisma. We could also not introspect this case.

Prisma schema:

model Parent {
  children Child[] 
}

model Child {
  parent Parent @relation(onDelete: CASCADE) // this references the parent id
}

corresponding SQL:

Create Table Parent ( id Text );
Create Table Child ( id Text, 
                                  parent text REFERENCES Parent(id));

Semantics:

• delete parent: children remain ✅
• delete child: parent gets deleted 🚨

One To Many: Cascade from both Sides

This cannot be fully expressed on the SQL level. We would need a mix of Prisma level and SQL level handling. We could therefore also not introspect this case.

Prisma schema:

model Parent {
  children Child[] @relation(onDelete: CASCADE)
}

model Child {
  parent Parent @relation(onDelete: CASCADE) // this references the parent id
}

corresponding SQL:

Create Table Parent ( id Text );
Create Table Child ( id Text, 
                                  parent text REFERENCES Parent(id)ON DELETE CASCADE);

Semantics:

• delete parent: children remain ✅
• delete child: parent gets deleted 🚨

Many to Many

Here the SQL level ON DELETE CASCADE statements merely ensure that there are no dangling relation entries after one of the connecting nodes are deleted. They have no connection to the Prisma level semantics. The Prisma level ones cannot be expressed in the db and therefore can also not be introspected. Here for all cases (CASCADE on child, CASCADE on parent, CASCADE on both) the implementation needs to happen on the Prisma level.

Prisma schema:

model Parent {
  children Child[] @relation(onDelete: CASCADE)
}

model Child {
  parents Parent[]
}

corresponding SQL:

Create Table Parent ( id Text );
Create Table Child ( id Text );
Create Table _ParentToChild (
  parent Text REFERENCES Parent(id) ON DELETE CASCADE,
  child Text REFERENCES Child(id) ON DELETE CASCADE
);

Semantics:

• delete parent: children get deleted 🚨
• delete child: parents remain ✅

[yoshuawuyts]

Note from triage: this is still intended to be addressed. This will come up once we do more work on migrate.

[yoshuawuyts]

Triage: no change

[pimeys]

Fixed in this issue prisma/prisma#2810