Add DELETE CASCADE feature in Hydra

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, ... );

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!
  ...
}

Joystream / hydra

Add DELETE CASCADE feature in Hydra #490

Solution