typeorm: Add trigger functionality

Maybe it is not needed, I believe you can use listeners and subscribers

Will this be consider if I were to write it? I have some helper functions that create DB triggers in Postgres during migrations but it would be much nicer if this were automated within migrations ecosystem.

Typerom app level subscribers are NOT the same as DB triggers and functions.

@Murillo2380 this is not the same thing. Listeners and Subscribers only protects the database from the application using TypeORM making invalid modifications to the data, the database itself does not have the data integrity it needs.

How should one implement this currently?

@incompletude TypeORM already has decorators such as AfterInsert, AfterUpdate, BeforeRemove, etc which act as trigger functions. This issue — I presume — is to be able to use a single Trigger() decorator for specifying when to run a specific trigger function. So you could have a trigger function execute both after an insert and an update.

Currently I’m implementing this feature. I’m writing it in a raw SQL because there is no other solution.

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE OR REPLACE FUNCTION process_delete_project() RETURNS TRIGGER AS $tr_delete_project$
      BEGIN
        UPDATE project_logs SET project_title = OLD.title WHERE project_id = OLD.id;
        RETURN OLD;
      END;
      $tr_delete_project$ LANGUAGE plpgsql;`,
    );

    await queryRunner.query(
      `CREATE TRIGGER tr_delete_project
       BEFORE DELETE ON projects
        FOR EACH ROW EXECUTE PROCEDURE process_delete_project();`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TRIGGER tr_delete_project ON project`);
    await queryRunner.query(`DROP FUNCTION process_delete_project();`);
  }

I will appreciate if you tell me possible issues.

How should one implement this currently?

Still waiting on this.

@imnotjames I think Database Triggers need to be configured seperatly, so that they register themselves again when schema sync is active in dev. So when I have configured a trigger on the user table and change it, I’d expect the user table to be changed and the trigger to be applied afterwards again if possible (or showing an error message if not).

Is this still a thing?

I am willing to implement this. Do we have a process for designing the api for something like this @pleerock?

I am come up with a proposal if you want.

What is the reason this is not implemented? Are there technical difficulties?

@mrkmg just take a look how other decorators are implemented. Similar to this will be a @Subcriber decorator.

You’ll need to do it something like this:

1. decorator take all options (“args”) and saves them in “metadata args storage”
2. connection on “connect” calls builds all “metadata args” into “metadata” (TriggerMetadata for example)
3. trigger metadata is stored somewhere, maybe in the connection object
4. when user calls a trigger (maybe connection.executeTrigger(UserDateUpdater)) it takes a metadata object from the connection and calls it getSql method and passes sql string to the connection.query method.

@mrkmg - Can I confirm that this would be similar to: https://github.com/typeorm/typeorm/blob/master/src/decorator/listeners/EventSubscriber.ts

☝️ The meat being:

getMetadataArgsStorage().entitySubscribers.push(...)

If I were feeling brave and wanted this feature, what are some other good pointers? I’d like to understand the scope of this effort before diving in - anything would be welcome.