Add DELETE CASCADE feature in Hydra

[zeeshanakram3]

In PostgreSQL, a cascade means that delete or update of records in a parent table will automatically delete or update matching records in a child/referencing table where a foreign key relationship is in place.

---

Using VIdeo <-> Comment example, currently Graphql & SQL schemas are defined as

Graphql Schema	SQL Schema(generated by hydra)
type Comment @entity { id: ID! video: Video! ... } type Video @entity { id: ID! ... }	CREATE TABLE comment ( id character varying PRIMARY KEY, video_id character varying NOT NULL REFERENCES video(id), ... ); CREATE TABLE video ( id character varying PRIMARY KEY, ... );

Now, whenever a video gets deleted we want to delete all the comments which are referencing the id of deleted video as the foreign key. Currently, the only way to achieve this is to, first, delete all the comments where the id of deleted video is being referenced and then, remove the video in content_VideoDeleted mapping.

This might not be a quite optimized way to achieve the purpose, given that PostgreSQL specifically has "cascade delete" feature for this use case. The way it works is, that whenever a referencing field is extended with ON DELETE CASCADE in SQL schema definition, the deletion of referenced/parent record will trigger the removal of all the child records too.

e.g. the new SQL schema would look as:

CREATE TABLE comment (
    id character varying PRIMARY KEY,
    video_id character varying NOT NULL REFERENCES video(id) ON DELETE CASCADE,
    ...
);

CREATE TABLE video (
    id character varying PRIMARY KEY,
    ...
);

However, currently there is not a way to mark a field with "cascade delete" option in graphql schema in QN and then translate it to typeORM/SQL schema

Solution

Create a new directive, maybe cascadeDelete in hydra, so that a field can be extended with the said directive and then it can be mapped in SQL schema, so the new Graphql schema would look as

type Comment @entity {
  id: ID!
  video: Video! @cascadeDelete
  ...
}

type Video @entity {
  id: ID!
  ...
}

[bedeho]

This looks very interesting indeed.

I do think its quite rare we have this cascading deletions though, as mostly the object trees we delete are not so deep. Given the complexity of the change I think we should give this a low priority compared to our existing backlog.