Cascading deletes

[matthewmueller]

Currently, we support cascading deletes with @relation(onDelete:CASCADE). This is working today across introspection and lift.

Cascading deletes in Prisma work differently than SQL. In Prisma, the way cascading deletes work is if I get deleted, delete my children.

model Blog {
  posts Post[] @relation(onDelete:CASCADE)
}

model Post {
  blog Blog
}

In SQL, the way cascading deletes work is if my parent gets deleted, delete me.

create table Blog (
  id int primary key
)

create table Post (
  blog int references Blog (id) on delete cascade
)

Problems

1. As a user, you can stick @relation(onDelete:CASCADE) on either side of the relationship, but if you run lift up, then introspect, you'll end up with @relation(onDelete:CASCADE) only on the back-relation (e.g. Post[]).
2. Coming from an SQL background, it's confusing that the cascade is on the back-relation (e.g. Post[]) not the forward-relation (e.g. Blog).
3. (optional) Syntax is verbose.

Proposal

Cascading deletes only matter with Introspection, Lift and the Schema. Since we only care about Introspection & Photon for GA, we only need to define the syntax for cascading deletes.

For this, I propose 2 adjustments:

1. Cascading deletes are always only present on the forward relation.

These rules will make sure that when we run lift up, then prisma introspect, we'll end up with the same schema.

Rule A) We store on delete cascade where the data about the relation is stored.

This is the same as how SQL works. What's intuitive for some is unintuitive for others. We're not trying to fix SQL with PSL.

Rule B) Storing on the back-relation is a compiler error.

We can make this less painful with prisma format.

Example Changes

I've added some examples of how we should change from our current approach to this proposal's approach.

⚠️ I've added the SQL equivalent, but keep in mind this only matters for Lift, which we don't care about right now.

One-to-One:

model User {
  id Int @id
- customer Customer
+ customer Customer @relation(onDelete:CASCADE)
}

model Customer {
  id Int @id
- user User @virtual @relation(onDelete:CASCADE)
+ user User @virtual
}

Only store on the side where the relation is stored, never on the back-relation.

Resulting SQL:

create table Customer (
  id serial primary key
);

create table User (
  id serial primary key,
  customer int references Customer (id) on delete cascade
);

One-to-Many:

model User {
  id Int @id
- posts Post[] @relation(onDelete:CASCADE)
+ posts Post[]
}

model Post {
  id Int @id
- user User
+ user User @relation(onDelete:CASCADE)
}

Resulting SQL:

create table User (
  id serial primary key
);

create table Post (
  id serial primary key,
  user int references User (id) on delete cascade
);

Many-to-Many (explicit):

model User {
  id Int @id
- postUsers PostUser[] @relation(onDelete:CASCADE)
+ postUsers PostUser[]
}

join PostUser {
- post Post
+ post Post @relation(onDelete:CASCADE)
- user User
+ user User @relation(onDelete:CASCADE)
  @@id([post, user])
}

model Post {
  id Int @id
- postUsers PostUser[] @relation(onDelete:CASCADE)
+ postUsers PostUser[]
}

Resulting SQL:

create table User (
  id serial primary key
);

create table Post (
  id serial primary key
);

create table PostUser (
  post int references User (id) on delete cascade,
  user int references Post (id) on delete cascade,
  primary key(post,user)
);

Composite Keys:

model User {
  first String
  last String
- @@id([first, last]) @relation(onDelete:CASCADE)
+ @@id([first, last])
}

model Post {
  id Int @id
- user User
+ user User @relation(onDelete:CASCADE)
}

Resulting SQL:

create table User (
  first text primary key,
  last text primary key,
  primary key(first,last)
);

create table Post (
  id serial primary key,
  user int references User (first,last) on delete cascade
);

2. We change @relation(onDelete:CASCADE) to onDelete(cascade).

model User {
  id Int @id
-  posts Post[] @relation(onDelete:CASCADE)
+  posts Post[] @onDelete(cascade)
}

model Post {
  id Int @id
  user User
}

This is just a matter of preference but not a blocker or anything.

[tomhoule]

Related: prisma/migrate#214

[thebiglabasky]

Closed in favor of prisma/prisma#2810 to follow work in there