Post Migration Hook

migration-using.ts

import { Connection } from 'typeorm';
import { PostHookBase, PostHookOptions } from '@c2fo/nest-db/src/lib/post-hook';

/**
 * PostHook runs after a migration if so desired
 */
export class PostHook extends PostHookBase {
  constructor(conn: Connection) {
    super(conn, 'public');
  }

  run(): Promise<void> {
    this.logger.log('running created timestamp trigger');
    return this.createCreatedTimestampTrigger()
      .then(() => {
        this.logger.log('running updated timestamp trigger');
        return this.createUpdatedTimestampTrigger();
      })
      .catch(err => {
        this.logger.error(err);
        throw err;
      })
      .finally(() => {
        this.logger.log('completed running post hooks');
      });
  }

  private async createCreatedTimestampTrigger(): Promise<void> {
    const triggerOpts: PostHookOptions = {
      tableColumnName: 'created',
      dbFunctionName: 'auto_set_created_column',
      tableTriggerName: 'auto_set_created_column_trigger',
      actionFragment: 'BEFORE INSERT',
      dropTriggerName: '',
    };
    return this.execFromOpts(triggerOpts);
  }

  private async createUpdatedTimestampTrigger(): Promise<void> {
    const triggerOpts: PostHookOptions = {
      tableColumnName: 'updated',
      dbFunctionName: 'auto_set_updated_column',
      tableTriggerName: 'auto_set_updated_column_trigger',
      actionFragment: 'BEFORE UPDATE',
      dropTriggerName: '',
    };
    return this.execFromOpts(triggerOpts);
  }
}

post-hook.spec.ts

import { NotImplementedException } from '@nestjs/common';
import { DBConnection, PostHookBase, PostHookOptions } from './post-hook';
import {
  getCreateTriggerSql,
  getDbFunctionNameExistsSql,
  getDropTriggerSql,
  getTablesContainingColumnNameSql,
  getTablesWithTriggerNameSql,
} from './sql';

class TestPostHookImpl extends PostHookBase {
  constructor(conn: DBConnection) {
    super(conn, 'public');
  }

  run(): Promise<void> {
    return Promise.resolve(undefined);
  }
}

class TestPostHookImplWithRun extends TestPostHookImpl {
  run(): Promise<void> {
    const pho: PostHookOptions = {
      tableColumnName: 'created',
      dbFunctionName: 'my_pg_function_name',
      tableTriggerName: 'custom_trigger_name_trigger',
      actionFragment: 'BEFORE INSERT',
      dropTriggerName: '',
    };
    return this.execFromOpts(pho);
  }
}

describe('PostHookBase', () => {
  it('should throw an error if Connection is not connected', async () => {
    const connection = {
      isConnected: false,
      query: jest.fn(
        (): Promise<any> => {
          return Promise.resolve([]);
        },
      ),
    };

    try {
      const postHook = new TestPostHookImpl(connection);
      await postHook.run();
    } catch (err) {
      expect(err).toBeInstanceOf(NotImplementedException);
      expect(err.message.error).toBe('Not Implemented');
      expect(err.message.message).toBe('provided connection is not connected to the database');
      expect(connection.query).toBeCalledTimes(0);
    }
  });

  it('should query tables from the database and setup trigger', async () => {
    const connection = {
      isConnected: true,
      query: jest
        .fn()
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesContainingColumnNameSql('public', 'created'));
            return Promise.resolve([{ tablename: 'table_name_one' }]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getDbFunctionNameExistsSql('my_pg_function_name'));
            return Promise.resolve([{ count: '1' }]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
            return Promise.resolve([{ relname: 'tale_name_one' }]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(
              getCreateTriggerSql(
                'custom_trigger_name_trigger',
                'BEFORE INSERT',
                'table_name_one',
                'my_pg_function_name',
              ),
            );
            return Promise.resolve([]);
          },
        ),
    };

    try {
      const postHook = new TestPostHookImplWithRun(connection);
      await postHook.run();
      expect(connection.query).toBeCalledWith(getTablesContainingColumnNameSql('public', 'created'));
      expect(connection.query).toBeCalledWith(getDbFunctionNameExistsSql('my_pg_function_name'));
      expect(connection.query).toBeCalledWith(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
      expect(connection.query).toBeCalledWith(
        getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'table_name_one', 'my_pg_function_name'),
      );
      expect(connection.query).toBeCalledTimes(4);
    } catch (err) {
      expect(err).toBeUndefined();
    }
  });

  it('should error if the database function does not exist', async () => {
    const connection = {
      isConnected: true,
      query: jest
        .fn()
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesContainingColumnNameSql('public', 'created'));
            return Promise.resolve(['custom_table_name_1']);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getDbFunctionNameExistsSql('my_pg_function_name'));
            return Promise.resolve([]);
          },
        ),
    };

    try {
      const postHook = new TestPostHookImplWithRun(connection);
      await postHook.run();
      expect(connection.query).toBeCalledWith(getTablesContainingColumnNameSql('public', 'created'));
      expect(connection.query).toBeCalledWith(getDbFunctionNameExistsSql('my_pg_function_name'));
      expect(connection.query).toBeCalledTimes(2);
    } catch (err) {
      expect(err.message).toBe('Cannot find database function [dbFunctionName=my_pg_function_name]');
    }
  });

  it('should use targetTables list if provided and setup triggers', async () => {
    class LocalTestPostHookWithTargets extends TestPostHookImpl {
      run(): Promise<void> {
        const pho: PostHookOptions = {
          tableColumnName: 'created',
          targetTables: ['custom_table_1', 'custom_table_2'],
          dbFunctionName: 'my_pg_function_name',
          tableTriggerName: 'custom_trigger_name_trigger',
          actionFragment: 'BEFORE INSERT',
          dropTriggerName: '',
        };
        return this.execFromOpts(pho);
      }
    }

    const connection = {
      isConnected: true,
      query: jest
        .fn()
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getDbFunctionNameExistsSql('my_pg_function_name'));
            return Promise.resolve([{ count: '1' }]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
            return Promise.resolve([]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(
              [
                getCreateTriggerSql(
                  'custom_trigger_name_trigger',
                  'BEFORE INSERT',
                  'custom_table_1',
                  'my_pg_function_name',
                ),
                getCreateTriggerSql(
                  'custom_trigger_name_trigger',
                  'BEFORE INSERT',
                  'custom_table_2',
                  'my_pg_function_name',
                ),
              ].join(''),
            );
            return Promise.resolve([]);
          },
        ),
    };

    try {
      const postHook = new LocalTestPostHookWithTargets(connection);
      await postHook.run();
      expect(connection.query).toBeCalledWith(getDbFunctionNameExistsSql('my_pg_function_name'));
      expect(connection.query).toBeCalledWith(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
      expect(connection.query).toBeCalledWith(
        [
          getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'custom_table_1', 'my_pg_function_name'),
          getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'custom_table_2', 'my_pg_function_name'),
        ].join(''),
      );
      expect(connection.query).toBeCalledTimes(3);
    } catch (err) {
      expect(err).toBeUndefined();
    }
  });

  it('should execute drop trigger sql', async () => {
    class LocalTestPostHookWithDrop extends TestPostHookImpl {
      run(): Promise<void> {
        const pho: PostHookOptions = {
          tableColumnName: 'created',
          dbFunctionName: 'my_pg_function_name',
          tableTriggerName: 'custom_trigger_name_trigger',
          actionFragment: 'BEFORE INSERT',
          dropTriggerName: 'custom_drop_trigger',
        };
        return this.execFromOpts(pho);
      }
    }

    const connection = {
      isConnected: true,
      query: jest
        .fn()
        // get tables containing column name since no targetTables were passed
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesContainingColumnNameSql('public', 'created'));
            return Promise.resolve([{ tablename: 'table_1' }, { tablename: 'table_2' }]);
          },
        )
        // look up of the dbFunctionName exists
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getDbFunctionNameExistsSql('my_pg_function_name'));
            return Promise.resolve([{ count: '1' }]);
          },
        )
        // find tables to create triggers on, whatever is returned will get diffed from our previous list
        // to only create triggers on tables that need them
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
            // return Promise.resolve([{ relname: 'table_1' }, { relname: 'table_2' }]);
            return Promise.resolve([]);
          },
        )
        .mockImplementationOnce(
          (sql: string): Promise<any> => {
            expect(sql).toBe(
              [
                getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'table_1', 'my_pg_function_name'),
                getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'table_2', 'my_pg_function_name'),
                getDropTriggerSql('custom_drop_trigger', 'table_1'),
                getDropTriggerSql('custom_drop_trigger', 'table_2'),
              ].join(''),
            );
            return Promise.resolve([]);
          },
        ),
    };

    try {
      const postHook = new LocalTestPostHookWithDrop(connection);
      await postHook.run();
      expect(connection.query).toBeCalledWith(getTablesContainingColumnNameSql('public', 'created'));
      expect(connection.query).toBeCalledWith(getDbFunctionNameExistsSql('my_pg_function_name'));
      expect(connection.query).toBeCalledWith(getTablesWithTriggerNameSql('custom_trigger_name_trigger'));
      expect(connection.query).toBeCalledWith(
        [
          // getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'custom_table_1', 'my_pg_function_name'),
          // getCreateTriggerSql('custom_trigger_name_trigger', 'BEFORE INSERT', 'custom_table_2', 'my_pg_function_name'),
          getDropTriggerSql('custom_drop_trigger', 'table_1'),
          getDropTriggerSql('custom_drop_trigger', 'table_2'),
        ].join(''),
      );
      expect(connection.query).toBeCalledTimes(4);
    } catch (err) {
      expect(err).toBeUndefined();
    }
  });

  // it('should support a schema other than public');
});

post-hook.ts

import { Connection } from 'typeorm';
import { Logger, NotFoundException, NotImplementedException } from '@nestjs/common';
import {
  getCreateTriggerSql,
  getDbFunctionNameExistsSql,
  getDropTriggerSql,
  getTablesContainingColumnNameSql,
  getTablesWithTriggerNameSql,
} from './sql';

// TODO: Move the auto_set_created_column and auto_set_updated_column migration
// TODO: and class from Eligibility API into the DBModule

interface TableTriggerOptsBase {
  dbFunctionName: string; // name of the database function
  tableTriggerName: string; // the name of the trigger on the table
  actionFragment: string; // BEFORE INSERT, BEFORE UPDATE, AFTER INSERT, AFTER UPDATE
  dropTriggerName: string; // the name of the database function to drop on the table, default to emptys
}

interface TableTriggerOpts extends TableTriggerOptsBase {
  tables: string[]; // internal reference of tables we are using, stored in snake_case
}

/**
 * DBConnection to mirror Typeorm.Connection instead of having to deal with
 * mocking the Typeorm.Connection which has many drawbacks.
 */
export interface DBConnection {
  isConnected: boolean;

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  query(sql: string): Promise<any>; // matches Typeorm.Connection.prototype.query signature
}

export interface PostHookOptions extends TableTriggerOptsBase {
  tableColumnName: string; // column name we want to create the trigger on
  targetTables?: string[];
}

/**
 * PostHookBase is the base class that needs to be extended in order
 * to create a post migration hook for your specific needs.
 */
export abstract class PostHookBase {
  // a Typeorm.Connection that is connected
  protected readonly conn: DBConnection;

  // the schema name we are operating on
  protected readonly schemaName: string;

  // our internal logger that we expose to our implementers
  protected readonly logger: Logger = new Logger(PostHookBase.name);

  /**
   * @param {Connection} conn
   * @param {string} schemaName = public (default)
   */
  protected constructor(conn: DBConnection, schemaName = 'public') {
    this.conn = conn;
    this.schemaName = schemaName;

    if (!conn.isConnected) {
      // currently we do not support trying to connect. that will be the
      // responsibility of the calling entity
      this.logger.error(`PostHook does not support a non-connected Connection`);
      throw new NotImplementedException('provided connection is not connected to the database');
    }
  }

  /**
   * run is the only method that you need to implement when building from.
   * Within the body is where your code will live that will run the post migration
   * hook.
   * @abstract
   * @returns Promise<void>
   */
  abstract run(): Promise<void>;

  /**
   * execFromOpts
   * Executes the trigger based on the options given
   * @param {PostHookOptions} postHookOpts
   * @returns Promise<void>
   */
  protected async execFromOpts(postHookOpts: PostHookOptions): Promise<void> {
    const tables = await this.getTablesFromOpts(postHookOpts);
    this.logger.log(
      `building from options [postHookOpts: ${JSON.stringify(postHookOpts)}, tables=${JSON.stringify(tables)}]`,
    );

    return this.createTriggersForTables({
      dbFunctionName: postHookOpts.dbFunctionName,
      tableTriggerName: postHookOpts.tableTriggerName,
      actionFragment: postHookOpts.actionFragment,
      dropTriggerName: postHookOpts.dropTriggerName,
      tables,
    });
  }

  /**
   * getTablesFromOpts will return a list of tables to create/drop triggers on
   * which can be either a given list via targetTables or by letting the base
   * class find all tables in the schema with the given column name (ie.
   * created, updated).
   * @param {PostHookOptions} postHookOpts
   * @returns {Promise<string[]>} returns a list of target tables
   * @private
   */
  private async getTablesFromOpts(postHookOpts: PostHookOptions): Promise<string[]> {
    if (postHookOpts.targetTables?.length) {
      this.logger.log(`getTablesFromOpts [targetTables=${JSON.stringify(postHookOpts.targetTables)}`);
      return postHookOpts.targetTables;
    }

    return this.getTablesWithColumnName(postHookOpts.tableColumnName);
  }

  /**
   * createTriggersForTables will look up if the database function exists, throw
   * an error if not, then will find tables that actually need the trigger
   * created or dropped, then will execute the sql to perform the action
   * @param {TableTriggerOpts} opts
   * @returns {Promise<void>}
   * @private
   */
  private async createTriggersForTables(opts: TableTriggerOpts): Promise<void> {
    const doesDbFunctionExist: boolean = await this.doesDbFunctionExists(opts.dbFunctionName);
    if (!doesDbFunctionExist) {
      this.logger.error(`database function name does not exist! [dbFunctionName=${opts.dbFunctionName}]`);
      throw new NotFoundException(
        `provided database function name does not exist [dbFunctionName=${opts.dbFunctionName}]`,
      );
    }

    this.logger.log(`createTriggersForTables [opts=${JSON.stringify(opts)}]`);

    // tables we need to create triggers for
    const tablesToCreateTriggersOn: string[] = await this.findTablesToCreateTriggersOn(
      opts.tables,
      opts.tableTriggerName,
    );
    this.logger.log(`  -> [tablesToCreateTriggersOn=${tablesToCreateTriggersOn}]`);

    // map over our tables that need the trigger and build up the sql
    const sqls = tablesToCreateTriggersOn.map(table =>
      getCreateTriggerSql(opts.tableTriggerName, opts.actionFragment, table, opts.dbFunctionName),
    );

    // if we have a drop trigger name, build up the sql
    this.logger.log(`  -> [opts.dropTriggerName=${opts.dropTriggerName}]`);
    const dropSqls =
      opts.dropTriggerName !== ''
        ? tablesToCreateTriggersOn.map(table => getDropTriggerSql(opts.dropTriggerName, table))
        : [];

    const r = [...sqls, ...dropSqls].join('');
    this.logger.log(
      `  -> createTriggersForTables [tables=${JSON.stringify(tablesToCreateTriggersOn)}, sqls=${JSON.stringify(
        sqls,
      )}, dropSqls=${JSON.stringify(dropSqls)}, r=${JSON.stringify(r)}]`,
    );

    // concat our two sql arrays together and join them into a string and execute
    return this.conn.query(r);
  }

  /**
   * findTablesToCreateTriggersOn takes a list of tables and a trigger name
   * then will look up if the trigger is already set on the table, if so the
   * passed in table list will have that entry removed since we do not need
   * to run the trigger action on this table (it already exists)
   * @param {string[]} tableNames
   * @param {string} triggerName - the trigger name
   */
  private async findTablesToCreateTriggersOn(tableNames: string[], triggerName: string): Promise<string[]> {
    // internal reference to the sql lookup from above
    interface Relname {
      relname: string;
    }

    this.logger.log(
      `findTablesToCreateTriggersOn [tableNames=${JSON.stringify(tableNames)}, triggerName: ${triggerName}]`,
    );

    const triggeredTables: Relname[] = await this.conn.query(getTablesWithTriggerNameSql(triggerName));
    // map over the fetched tables to flatten to string[]
    const flattenedTriggeredTables = triggeredTables.map(({ relname }) => relname);

    // filter out our tableNames that already have this trigger on them
    const filteredTables = tableNames.filter(tableName => !flattenedTriggeredTables.includes(tableName));

    this.logger.log(
      `  -> [triggeredTablesRaw=${JSON.stringify(triggeredTables)}, triggeredTables=${JSON.stringify(
        flattenedTriggeredTables,
      )}, filteredTables=${JSON.stringify(filteredTables)}]`,
    );

    return filteredTables;
  }

  /**
   * doesDbFunctionExist looks up if the function name exist in the database
   * @param {string} functionName
   * @returns {Promise<boolean>} returns if function exists
   */
  private async doesDbFunctionExists(functionName: string): Promise<boolean> {
    interface DbCount {
      count: string;
    }

    const records: DbCount[] = await this.conn.query(getDbFunctionNameExistsSql(functionName));
    // Typeorm safely returns you [{count: "1"}]
    if (!records || !records.length) {
      throw new Error(`Cannot find database function [dbFunctionName=${functionName}]`);
    }

    const { count: dbRecordCount } = records[0];
    this.logger.log(`doesDbFunctionExists [functionName=${functionName}, dbRecordCount=${dbRecordCount}]`);
    return dbRecordCount === '1';
  }

  /**
   * getTablesWithColumnName will find all tables with a given column name
   * @param {string} columnName
   * @returns {Promise<string[]>} returns a list of table names which have the column
   */
  private async getTablesWithColumnName(columnName: string): Promise<string[]> {
    // internal interface for the above query
    interface TableName {
      tablename: string;
    }

    const tableNames: TableName[] = await this.conn.query(
      getTablesContainingColumnNameSql(this.schemaName, columnName),
    );
    const tables = tableNames.map(({ tablename }) => tablename);
    this.logger.log(`getTablesWithColumnName [columnName=${columnName}, tables=${JSON.stringify(tables)}]`);
    return tables;
  }
}

sql.ts

export function getTablesContainingColumnNameSql(schemaName: string, columnName: string): string {
  return `
      select distinct table_name as tablename
      from information_schema.columns
      where column_name='${columnName}' and
            table_schema='${schemaName}' and
            table_name in (
                SELECT
                       table_name
                FROM information_schema.tables
                WHERE table_schema = '${schemaName}' and
                      table_type='BASE TABLE'
              );
    `.trim();
}

export function getDbFunctionNameExistsSql(dbFunctionName: string): string {
  return `select count(*) from pg_proc where proname='${dbFunctionName}';`.trim();
}

export function getTablesWithTriggerNameSql(triggerName: string): string {
  return `
      select relname from pg_class
      join pg_trigger on (tgrelid = pg_class.oid)
      where tgname='${triggerName}';
    `.trim();
}

export function getCreateTriggerSql(
  tableTriggerName: string,
  actionFragment: string,
  table: string,
  dbFunctionName: string,
): string {
  return `CREATE TRIGGER ${tableTriggerName} ${actionFragment} ON ${table} FOR EACH ROW EXECUTE FUNCTION ${dbFunctionName}();`.trim();
}

export function getDropTriggerSql(dropTriggerName: string, table: string): string {
  return `DROP TRIGGER IF EXISTS ${dropTriggerName} ON ${table};`.trim();
}