How to create a TRIGGER in SEQUELIZE (nodeJS)?

Question

I'm trying to create a trigger using sequelize.. the main idea is to create an instance of CONFIG after creating a USER.

// USER MODEL
module.exports = function(sequelize, DataTypes) {    
    var User = sequelize.define('User', {
        name        : DataTypes.STRING(255),
        email       : DataTypes.STRING(255),
        username    : DataTypes.STRING(45),
        password    : DataTypes.STRING(100),
    }, {
        classMethods : {
            associate : function(models) {
                User.hasOne(models.Config)
            }
        }
    });    
    return User;
};

// CONFIG MODEL
module.exports = function(sequelize, DataTypes) {
    var Config = sequelize.define('Config', {
        notifications   : DataTypes.INTEGER
    }, {
        classMethods : {
            associate : function(models) {
                Config.belongsTo(models.User)
            }
        }
    });

    return Config;
};

As you can see, a "user" has one "config" and a "config" belongs to a "user", so after a user is created I want to create his config row automatically.

The goal is to do:

DELIMITER //
CREATE TRIGGER create_config AFTER INSERT ON user
  FOR EACH ROW
BEGIN
    insert into config    (user_id)     values(new.user_id);
END; //
DELIMITER ;

Now, what I do to simulate that is the following:

.then(function(user){
   return dao.Config.create(req.body, user, t);
})

Once a User is created I create his configuration like that... it works but is not what I'm searching.

How would I do it?

score 52 · Accepted Answer · edited Dec 17 '18 at 01:56

You can do this in one of two ways. As you noted, you could create a trigger in the database itself. You could run a raw sequelize query to accomplish this:

sequelize.query('CREATE TRIGGER create_config AFTER INSERT ON users' +
  ' FOR EACH ROW' +
  ' BEGIN' +
  ' insert into configs (UserId) values(new.id);' +
  'END;')

Or, you could create a hook on the user model that performs an action on an afterCreate event:

module.exports = function(sequelize, DataTypes) {    
  var User = sequelize.define('User', {
    name        : DataTypes.STRING(255),
    email       : DataTypes.STRING(255),
    username    : DataTypes.STRING(45),
    password    : DataTypes.STRING(100),
  }, {
    classMethods : {
      associate : function(models) {
        User.hasOne(models.Config)
      }
    },
    hooks: {
      afterCreate: function(user, options) {
        models.Config.create({
          UserId: user.id
        })
      }
    }
  });
  return User;
};

Excellent, that works with mysqljs too. You can just do `CREATE TRIGGER ...` without trying to change the delimiters as most often shown in examples. — Alexis Wilke, Dec 17 '18 at 03:45

Ciro Santilli OurBigBook.com · Answer 2 · 2023-06-02T07:28:29.517

SQL TRIGGER helper working on both PostgreSQL and SQLite

Usage: place this after .sync:

await sequelize.sync()
await createTrigger(sequelize, Post, 'insert', `UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`)
await createTrigger(sequelize, Post, 'delete', `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId"`)
await createTrigger(
  sequelize,
  Post,
  'update',
  `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`,
  {
    when: 'OLD."UserId" <> NEW."UserId"',
  }
)

The triggers are redefined every time, so this will work even if they were already defined previously, i.e. no sync({ force needed: How does sequelize.sync() work, specifically the force option?

The helper:

// on: lowercase 'insert', 'delete' or 'update'
async function createTrigger(sequelize, model, on, action, { after, when, nameExtra } = {}) {
  if (after === undefined) {
    after = 'AFTER'
  }
  if (nameExtra) {
    nameExtra = `_${nameExtra})`
  } else {
    nameExtra = ''
  }
  const oldnew = on === 'delete' ? 'OLD' : 'NEW'
  const triggerName = `${model.tableName}_${on}${nameExtra}`
  if (when) {
    when = `\n  WHEN (${when})`
  } else {
    when = ''
  }
  if (sequelize.options.dialect === 'postgres') {
    const functionName = `${triggerName}_fn`
    await sequelize.query(`CREATE OR REPLACE FUNCTION "${functionName}"()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  ${action};
  RETURN ${oldnew};
END;
$$
`)
    // CREATE OR REPLACE TRIGGER was only added on postgresql 14 so let's be a bit more portable for now:
    // https://stackoverflow.com/questions/35927365/create-or-replace-trigger-postgres
    await sequelize.query(`DROP TRIGGER IF EXISTS ${triggerName} ON "${model.tableName}"`)
    await sequelize.query(`CREATE TRIGGER ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  EXECUTE PROCEDURE "${functionName}"();
`)
  } else if (sequelize.options.dialect === 'sqlite') {
    await sequelize.query(`
CREATE TRIGGER IF NOT EXISTS ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  BEGIN
    ${action};
  END;
`)
  }
}

I don't think it's possible to avoid the big if per DBMS because:

SQLite doesn't have stored procedures: Creating stored procedure in SQLite
PostgreSQL requires a procedure for triggers: is it possible to create trigger without execute procedure in postgresql?

A minimal runnable example with asserts follows. In this example, we use a trigger to keep the User.postCount value of each user up to date as the user creates or deletes some posts.

main.js

const path = require('path')
const { DataTypes } = require('sequelize')
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2])
const force = process.argv.length <= 3 || process.argv[3] !== '0'
;(async () => {

// on: lowercase 'insert', 'delete' or 'update'
async function createTrigger(sequelize, model, on, action, { after, when, nameExtra } = {}) {
  if (after === undefined) {
    after = 'AFTER'
  }
  if (nameExtra) {
    nameExtra = `_${nameExtra})`
  } else {
    nameExtra = ''
  }
  const oldnew = on === 'delete' ? 'OLD' : 'NEW'
  const triggerName = `${model.tableName}_${on}${nameExtra}`
  if (when) {
    when = `\n  WHEN (${when})`
  } else {
    when = ''
  }
  if (sequelize.options.dialect === 'postgres') {
    const functionName = `${triggerName}_fn`
    await sequelize.query(`CREATE OR REPLACE FUNCTION "${functionName}"()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  ${action};
  RETURN ${oldnew};
END;
$$
`)
    // CREATE OR REPLACE TRIGGER was only added on postgresql 14 so let's be a bit more portable for now:
    // https://stackoverflow.com/questions/35927365/create-or-replace-trigger-postgres
    await sequelize.query(`DROP TRIGGER IF EXISTS ${triggerName} ON "${model.tableName}"`)
    await sequelize.query(`CREATE TRIGGER ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  EXECUTE PROCEDURE "${functionName}"();
`)
  } else if (sequelize.options.dialect === 'sqlite') {
    await sequelize.query(`
CREATE TRIGGER IF NOT EXISTS ${triggerName}
  ${after} ${on.toUpperCase()}
  ON "${model.tableName}"
  FOR EACH ROW${when}
  BEGIN
    ${action};
  END;
`)
  }
}

const Post = sequelize.define('Post', {
  title: { type: DataTypes.STRING },
});
const User = sequelize.define('User', {
  username: { type: DataTypes.STRING },
  postCount: { type: DataTypes.INTEGER },
});
User.hasMany(Post)
Post.belongsTo(User)
await sequelize.sync({ force })
await createTrigger(sequelize, Post, 'insert', `UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`)
await createTrigger(sequelize, Post, 'delete', `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId"`)
await createTrigger(
  sequelize,
  Post,
  'update',
  `UPDATE "${User.tableName}" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "${User.tableName}" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId"`,
  {
    when: 'OLD."UserId" <> NEW."UserId"',
  }
)

async function reset() {
  const user0 = await User.create({ username: 'user0', postCount: 0 });
  const user1 = await User.create({ username: 'user1', postCount: 0 });
  await Post.create({ title: 'user0 post0', UserId: user0.id });
  await Post.create({ title: 'user0 post1', UserId: user0.id });
  await Post.create({ title: 'user1 post0', UserId: user1.id });
  return [user0, user1]
}
let rows, user0, user1
[user0, user1] = await reset()

// Check that the posts created increased postCount for users.
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 2 },
  { username: 'user1', postCount: 1 },
])

// UPDATE the author of a post and check counts again.
await Post.update({ UserId: user1.id }, { where: { title: 'user0 post1' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 1 },
  { username: 'user1', postCount: 2 },
])

// DELETE some posts.

await Post.destroy({ where: { title: 'user0 post1' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 1 },
  { username: 'user1', postCount: 1 },
])

await Post.destroy({ where: { title: 'user0 post0' } })
rows = await User.findAll({ order: [['username', 'ASC']] })
common.assertEqual(rows, [
  { username: 'user0', postCount: 0 },
  { username: 'user1', postCount: 1 },
])

})().finally(() => { return sequelize.close() })

pacakge.json

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.14.0",
    "sql-formatter": "4.0.2",
    "sqlite3": "5.0.2"
  }
}

Sample generated trigger definition queries:

SQLite:

CREATE TRIGGER IF NOT EXISTS Posts_insert
  AFTER INSERT
  ON "Posts"
  FOR EACH ROW
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  END;
CREATE TRIGGER IF NOT EXISTS Posts_delete
  AFTER DELETE
  ON "Posts"
  FOR EACH ROW
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
  END;
CREATE TRIGGER IF NOT EXISTS Posts_update
  AFTER UPDATE
  ON "Posts"
  FOR EACH ROW
  WHEN (OLD."UserId" <> NEW."UserId")
  BEGIN
    UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  END;

PostgreSQL:

CREATE OR REPLACE FUNCTION Posts_insert_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_insert ON "Posts"
CREATE TRIGGER Posts_insert
  AFTER INSERT
  ON "Posts"
  FOR EACH ROW
  EXECUTE PROCEDURE Posts_insert_fn();
CREATE OR REPLACE FUNCTION Posts_delete_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_delete ON "Posts"
CREATE TRIGGER Posts_delete
  AFTER DELETE
  ON "Posts"
  FOR EACH ROW
  EXECUTE PROCEDURE Posts_delete_fn();
CREATE OR REPLACE FUNCTION Posts_update_fn()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  UPDATE "Users" SET "postCount" = "postCount" - 1 WHERE id = OLD."UserId";
UPDATE "Users" SET "postCount" = "postCount" + 1 WHERE id = NEW."UserId";
  RETURN NEW;
END;
$$
DROP TRIGGER IF EXISTS Posts_update ON "Posts"
CREATE TRIGGER Posts_update
  AFTER UPDATE
  ON "Posts"
  FOR EACH ROW
  WHEN (OLD."UserId" <> NEW."UserId")
  EXECUTE PROCEDURE Posts_update_fn();

I should merge this back some day. Some day.

Tested on Ubuntu 22.10, PostgreSQL 13.6.

score -1 · Answer 3 · answered Jul 01 '21 at 06:53

You add a trigger in Sequelize like this. [as @Evan Siroky mentioned, I am just adding a few more steps for more details]

import { Sequelize } from "sequelize";

const sequelize = new Sequelize({  host: "localhost",
                                   port: 3306,
                                   dialect: "MySQL",
                                   username: "your user name",
                                   password: "your password",
                                   database: "dbname",
                                   logging: false,});

sequelize.query('CREATE TRIGGER create_config AFTER INSERT ON users' +
  ' FOR EACH ROW' +
  ' BEGIN' +
  ' insert into configs (UserId) values(new.id);' +
  'END;')

How to create a TRIGGER in SEQUELIZE (nodeJS)?

3 Answers3

Linked