How Typeorm define stored procedures, functions and triggers to DB. With migrations if possible

Question

I am trying to create a stored function in a Postgress DB. The reason is that we might have several consumers from this DB, and we currently use the Migrations of typeorm to generate our tables from our entities.

Looking into the documentation of typeorm and the source code of the library I find that there are some implementations of "Listeners" to certain events and "Subscribers" as well.

The problem with this approach is that it is tight to the technology or ORM itself...

Is it possible to generate a raw query as a migration with a similar definition to this:

import { MigrationInterface, QueryRunner } from "typeorm";

export class ProcedureCreation123123123 implements MigrationInterface {
    name = 'ProcedureCreation123123123'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
          CREATE OR REPLACE FUNCTION validate_update() RETURNS TRIGGER
          BEGIN
            ...
          END;
          CREATE TRIGGER validate_update_value
          BEFORE UPDATE
            ON table
            FOR EACH ROW
            EXECUTE PROCEDURE validate_update();
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TRIGGER "validate_update_value"`);
    }

}

I went through the generate of a migration documentation of typeorm to see how to generate such a migration, but the only way to generate it is by having differences in the entities in relation to the DB.

Adding a listener to the entity to try to generate the trigger as part of the migration did not work either. And the generation stated there were no changes in database schema.

One more alternative would be to invoke on module init of my nestjs service to define a raw query that will do exactly this things, but it sounds wrong to run such a code everytime I start the service and may be add or not this function...

If there is any advice on how to do such a change to my DB in conjunction with the migrations of typeorm would be great.

Answer 1

The solution was trivial:

typeorm migration:create -n NameOfMigration

generated the template I was looking for... Although I was expecting that the listener of the entity would eventually generate the trigger or allow me to define this query. I realise that I was searching to use the entity in a more tightly couple way than defining a migration separated from the current definition of my entity.

Finally inside of this file that was generated by typeorm I was able to define the up and down on my own needs for the creation of the trigger and drop of them.