Proposal: onDelete and onUpdate relation properties

[dpetrick]

Prisma on delete and on update behavior

A fundamental feature of Prisma is expressing relations between models. A relation can express many different concepts and can have many different meanings depending on the application domain: inclusion, association, dependence, state, etc. They are an essential tool of relational data model design.

As such, there is no single right answer to the question of what should happen when one side of a relation is changed or deleted.
This issue is a concrete proposal to enable that functionality and the result of multiple internal design discussions. Prisma Schema Language (PSL) syntax and client/migration/introspection behavior to consider will be outlined.

Please note that this proposal is only valid for SQL connectors for now. The behavior for other connectors will be considered separately in the future.

We are looking forward to any feedback on this proposal.

PSL Syntax Proposal

Without further ado, the following is an example of the syntax the internal working group settled on:

model User {
	id    String @id
	posts Post[]
}

model Post {
	id        String @id
	author_id String
	author    User   @relation(fields: [author_id], onDelete: Cascade, onUpdate: Cascade)
}

A note on terminology:

• "Relation scalar fields": The fields that store the relation between two records, ie. the fields defined in fields of @relation. For 1-m and 1-1 relations, this is the fields in the fields property of @relation. In the example above, this would be author_id. M-n relations do not have relation scalar fields in the same sense as other relations, they only have their IDs (on both sides) as "referenced scalar fields" due to the underlying join table (see below).
• "Referenced scalar fields": The fields that the relation scalar fields reference, ie. the field defined in references of @relation. For m-n relations, it's currently always the IDs of both models (which are stored as tuples in an implicit join table).

The semantics of onDelete and onUpdate are almost exactly how SQL expresses on update and on delete. For the example above: If the related author (User) of a Post is deleted (onDelete), delete all Post rows that are related to the deleted User (Cascade). If the id field of the related User is updated, also update author_id of all Posts that references that User.

Possible keywords for onDelete and onUpdate are:

• Cascade: Deletes record if dependent record is deleted. Updates relation scalar fields if referenced scalar fields of the dependent record are updated.
• Restrict: Prevents operation (both updates and deletes) from succeeding if any records are connected. This behavior will always result in a runtime error for required relations.
• NoAction: Behavior is database specific. Either defers throwing an integrity check error until the end of the transaction or errors immediately. If deferred, this makes it possible to temporarily violate integrity in a transaction while making sure that subsequent operations in the transaction restore integrity.
• SetNull: Sets relation scalar fields to null if the relation is deleted or updated. This will always result in a runtime error if one or more of the relation scalar fields are required.
• SetDefault: Sets relation scalar fields to their default values on update or delete of relation. Will always result in a runtime error if no defaults are provided for any relation scalar fields.

Note that the availability of these keywords and the exact behavior depends on the provider. For example, SQL Server does not support Restrict, but the semantics of NoAction are identical to Restrict.

Limitation: Implicit Many-to-many

Prisma offers an implicit many-to-many relation syntax, for example:

model User {
	id    String @id
	posts Post[]
}

model Post {
	id      String @id
	authors User[]
}

Internally, this maps to a join table with two foreign keys. Providing onDelete/onUpdate behavior for m-n relations would be inconsistent with 1-1 or 1-m relations:

• The definition would be inverted compared to other relation types.
• Allowing onDelete/onUpdate on m-n misleads people into thinking that related records are deleted, when in fact they are only disconnected.

Elaborating on the first point, this is an example of an m-n relation written out with an explicit join table and onDelete:

model User {
	id    String @id
	posts JoinTable[]
}

model JoinTable {
	post_id String
	user_id String
	
	post Post @relation(fields: [post_id], references: [id], onDelete: Cascade)
	user User @relation(fields: [user_id], references: [id], onDelete: Cascade)

	@@id([post_id, user_id])
}

model Post {
	id      String @id
	authors JoinTable[]
}

To be consistent with other relations, the definition of delete or update behavior must happen on the join table as this is the place where the relations are actually defined and follow the logic of "if x happens with the related record, then something happens with this record". This leads into the second point, which is that having the syntax on implicit m-n would suggest different behavior to what it does. Let's write out a hypothetical onDelete example for implicit m-n:

model User {
	id    String @id
	posts Post[] @relation(onDelete: Cascade)
}

model Post {
	id      String @id
	authors User[] @relation(onDelete: Cascade)
}

To have consistent semantics with the other relations types, it would be expected that if a query deletes either a User or a Post record, dependent other records would be deleted. However, under the hood, because of the hidden join table, they would be disconnected, as the foreign keys are actually defined on the join table and only the join table records would be deleted. Even more, the construct has unclear implications, even if it would behave as expected - a Post may have many authors, deleting a post that triggers a user delete would in turn trigger deletes of more users, triggering more deletes of posts, ...

In conclusion, we feel that consistency of the schema is more important than expanding the Prisma-specific construct of implicit m-n relations. With this proposal, we will require users that want to customize their join table behavior to use explicit join tables. We understand that this will cause inconveniences, but we have m-n relation changes planned in the future that will greatly improve the query ergonomics of explicit join tables.

Emulation

For connectors without referential action support (e.g. MongoDB), we want to provide an emulated subset of actions for onDelete and onUpdate. The emulated versions try to be as close as possible to the semantics of those provided by the other connectors:

• Cascade: Unchanged
• Restrict: Unchanged
• SetNull: Unchanged.
• SetDefault: Unchanged.
• NoAction: Signals that nothing will be done by Prisma. Will result in inconsistent data if not taken care of by the application.

We will gradually introduce these, the initial versions for MongoDB and co. may only support a few.

Defaults

This section discusses the current and desired default for onDelete/onUpdate for Prisma tools if the behavior is not specified explicitly in the Prisma schema. Note that the Prisma client emulates database behavior on the application layer, due to the fact that it can't know what the database behavior is without an annotation in the schema, so the current implementation chose to emulate sensible defaults.

Required relations:

When	Migrate	Client
onUpdate	Cascade	Cascade (assumed)
onDelete	Cascade	Restrict (emulated)

Optional relations:

When	Migrate	Client
onUpdate	Cascade	Cascade (assumed)
onDelete	SetNull	SetNull (emulated)

Note for Migrate and many-to-many relations: M-n is set to Cascade for both deletes and updates on the join tables, except for many-to-many self-relations on SQL Server, where we will use NoAction to avoid cycles.

Database Management Systems (DBMS):
All SQL DBMS Prisma supports have different defaults (if one creates schemas manually in SQL) than the ones Prisma currently uses:

• onUpdate: NoAction (Restrict for MySQL)
• onDelete: NoAction (Restrict for MySQL)

The Prisma schema follows the principle of optional complexity, which means that we strive to provide sensible defaults if a property is not defined in the schema. The above raises the question which defaults we want to follow when introducing onDelete and onUpdate.

We settled on the current Prisma defaults for referential actions (connectors without direct support emulate these):

When	Optional Relation	Required Relation
onUpdate	Cascade	Cascade
onDelete	SetNull	Restrict

We believe this is the best compromise to offer a smooth path forward for the following reasons:

• It's more defensive about deleting data (ie. no delete cascade by default), reducing potential data loss errors.
• It unifies migrate and client behavior. Users of migrate will see drift in their schema after update, which is easy to fix either via reintrospecting their database to keep the current behavior or accepting the change.
• We keep the default query behavior as close as possible to the current state for developers already accustomed to how Prisma handles relations.

Concrete Feedback Questions

• If you're not accustomed to how SQL handles onX behavior: Do you feel that you can easily understand the meaning of @relation(onDelete: ..., onUpdate: ...)? If not, would you prefer a naming scheme like @relation(onDeleteOther: ...) or @relation(onDeleteUser: ...)?
• Do you feel that the suggested default behavior aligns with your expectations?

[tomhoule]

For required relations, my preference would be to align the default Migrate behaviour with the default at the database level (RESTRICT). One big downside is that it would be a breaking change.

[dpetrick]

Note: Amended the defaults section to now use agreed upon schema defaults.

[Sytten]

A couple of points:

1. I agree with implicit M2M tables being limited, but then prioritization needs to happen on Allow tables to be used in M2M API #6135 and Ability to jump "through" one model to another on M:N relationships #2186.
2. It is unclear if prisma will continue to emulate the database behaviour after this change. Personally I gave up on using the .delete keyword since it creates way too many select and behaves inconsistently. I would really prefer if prisma could leave that to the database.
3. It is a bit annoying to change the default behaviour for migrate since this means everybody will get a change out of nowhere and people might apply it without thinking of the implications. If this is still the way forward, it needs to be treated like a breaking change in the release notes.
4. We need some helpers to catch those errors on the application side and react to them, this means prioritize this Help developers better handle SQL errors #5040

[dpetrick]

I agree with implicit M2M tables being limited, but then prioritization needs to happen on #6135 and #2186.

We agree, but can't give anything more concrete at this point.

It is unclear if Prisma will continue to emulate the database behavior after this change.

Prisma will stop emulating any referential actions and rely on the database for all SQL connectors. Connectors that do not support referential actions at all (but really need them to be ergonomic) will behave differently, but we'll get there in time. Additionally, those will be clearly marked with behavior such as onDelete: EmulateCascade or similar.

It is a bit annoying to change the default behavior for migrate since this means everybody will get a change out of nowhere and people might apply it without thinking of the implications. If this is still the way forward, it needs to be treated like a breaking change in the release notes.

It will be treated as a breaking change. To be clear, we can't circumvent a breaking change without being inconsistent in the schema <> engines behavior. We'll be careful how to communicate this.

We need some helpers to catch those errors on the application side and react to them, this means prioritize this #5040

This has technically nothing to do with this topic. You will get API errors on violation that have error codes and messages you can match against, not a generic error blob. Much like unique violations are exposed right now, for example. While I agree that this ticket improves ergonomics, it is not required to make it work on the application side.

[dpetrick]

Note: Amended error in defaults where onUpdate on optional relations was SetNull instead of Cascade.

[dpetrick]

Note: Added Emulation section and added defaults for emulated referential actions. Edit: After internal discussions, the emulation section is now finalized.

[PS1TD]

Looks good👍. Can't really think of anything that should be done differently. Besides maybe more examples in docs with more complicated associations and what exactly would happen when a model gets deleted(how it is going to cascade)

[pedrovrima]

Just giving feedback: this is exactly how I feel things should be handled.

If you're not accustomed to how SQL handles onX behavior: Do you feel that you can easily understand the meaning of @relation(onDelete: ..., onUpdate: ...)? If not, would you prefer a naming scheme like @relation(onDeleteOther: ...) or @relation(onDeleteUser: ...)?

It's easy (and logical) to understand onDelete.

Do you feel that the suggested default behavior aligns with your expectations?

Yes.

[ErickPetru]

As someone used to SQL syntax, I found it to be the perfect proposal. But I imagine that for newcomers, perhaps the direction of the relationships can be confusing.

For example, I expect a lot of questions like that to rise: "If I'm using cascade delete and I delete a User, will the Post records be deleted together?" or "How to delete a Post cascade deleting the JoinTable records but keeping User records?". I think the documentation needs to be very explicit about what's going to be happening in all cases of actions between the related tables.

[Sytten]

@ErickPetru I agree on that with you, I think the vscode plugin could help a lot on that front by providing interactive documentation based on what the user is looking at.

[pimeys]

Proposal implemented in prisma/prisma-engines#1947

Instructions on how to use this, and a place to give feedback: #7816

[kevlarr]

We settled on the current Prisma defaults for referential actions (connectors without direct support emulate these):

When	Optional Relation	Required Relation
onUpdate	Cascade	Cascade
onDelete	SetNull	Restrict

We believe this is the best compromise to offer a smooth path forward for the following reasons:

• It's more defensive about deleting data (ie. no delete cascade by default), reducing potential data loss errors.

Am I reading this right as saying that, in the future, Prisma will default to ON DELETE RESTRICT for migrations, as well, if the onDelete prop isn't specified?

[pimeys]

Am I reading this right as saying that, in the future, Prisma will default to ON DELETE RESTRICT for migrations, as well, if the onDelete prop isn't specified?

Exactly. If the relation has at least one scalar field that is NOT NULL, the default action will be conservative RESTRICT for deletions.

[kevlarr]

Thanks @pimeys that's wonderful! Really appreciate the amount of thought that's gone into Prisma and the quality of the communication and documentation

[janpio]

This has now been released as a preview feature flag. You can read about it in the release notes for 2.26.0: https://github.com/prisma/prisma/releases/tag/2.26.0 If you have any feedback, please use this issue: #7816