CODEWITHSUNDEEP
node.jspostgresqltriggerssequelize.js
Byron Mh
Byron Mh

Reputation: 161

Define a trigger in sequelize - NodeJS

i'm currently working with sequelize v5 on a backend project, we're using postgresql as a database.

I know that sequelize can define hooks as a mean to implement triggers, however, I require the implementation (or definition) of database triggers directly on the database.

I defined hooks as it was detailed in the docs, but noticed that it failed to formally define a trigger on the database.

hooks: {
  beforeUpdate: (instance, options) => {
    sequelize.models.themodelIwantToUse.create({
      actionType: 'UPDATE',
      key: instance.dataValues.key,
      newValue: instance.dataValues.value,
      oldValue: instance._previousDataValues.value,
      createdAt: instance.dataValues.createdAt,
      createdBy: instance.dataValues.createdBy
    })
  }
}

The project manager insists on declaring triggers, so I did the following:

sequelize.query("" +
"CREATE OR REPLACE FUNCTION the_model_i_want_to_use_log_func() " +
  "    RETURNS TRIGGER " +
  "AS $$ " +
  "BEGIN " +
  "    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN " +
  "        INSERT INTO a_schema.the_model_i_want_to_use(key, action_type, old_value, new_value, created_at, created_by) VALUES (NEW.key, TG_OP, OLD.value, NEW.value,  NEW.created_at, NEW.created_by); " +
  "        RETURN NEW; " +
  "    END IF; " +
  "" +
  "    RETURN NEW; " +
  "END; " +
  "$$ " +
  "    LANGUAGE plpgsql; " +
  "DROP TRIGGER IF EXISTS general_config_log ON a_schema.the_model_i_want_to_use; " +
  "CREATE TRIGGER general_config_log " +
  "AFTER INSERT OR UPDATE OR DELETE ON a_schema.the_model_i_want_to_use" +
  "FOR EACH ROW EXECUTE PROCEDURE the_model_i_want_to_use_log_func(); "
);

the problem (as you can tell) with the snippet above, is that it will rewrite the triggers and functions every time squelize is instanced.

I know this will break if we change database (to MySql for example), but that's not an issue i'm worried with at the moment.

Is there a different or correct aproach to declaring triggers with sequelize?

Upvotes: 4

Views: 4041

Answers (1)

Ronald Coarite
Ronald Coarite

Reputation: 4736

The correct way to handle changes in the database by handling the history is by using sequence migrations.

This requires that sequelize-cli be installed and within the src / migrations directory (optional). See the documentation at.

https://sequelize.org/master/manual/migrations.html

Example:

create migration file in src / migration / create_table_person.js

module.exports = {
up: función (queryInterface, DataTypes) {
    return queryInterface.createTable ('par_ec', {
        id_par_ec: {
            tipo: DataTypes.INTEGER,
            primaryKey: verdadero,
            allowNull: falso,
            autoIncrement: verdadero
        },
        nombre: {
            tipo: DataTypes.TEXT,
            allowNull: falso
        }
    });
}
};

Connection setup to a src / config / data_base.json database

{
"username": "postgres",
"contraseña": "admin",
"base de datos": "ppte",
"host": "localhost",
"dialecto": "postgres",
"zona horaria": "América / La_Paz",
"MigrationStorage": "secuelizar",
"MigrationStorageTableName": "sequelize_migration",
"registro": falso,
"grupo": {
    "max": 15,
    "min": 0,
    "inactivo": 10000,
    "adquirir": 10000,
    "desalojar": 10000,
    "handleDisconnects": verdadero,
    "autoreconnect": verdadero
}
}

Execute all pending migrations

sequelize db: migrate --migrations-path src / migrations --config src / config / data_base.json

Upvotes: 1

Related Questions