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?

Answer 1

A small warning about the answer of Evan: a trigger and a sequelize hook are not the same thing. The trigger lives entirely inside the database, while the sequelize hook lives entirely in JS. Therefore the (after) hooks can only work on the output of the MySQL command, while triggers can use all rows you are inserting, updating or deleting.

This is especially important if you want to update in bulk. Since an UPDATE statement does not return the ids of updated rows, sequelize hooks can only respond to a single update:

// This way the afterUpdate hook is triggered
const instance = UserModel.findOne(...);
instance.update(...);

// This way the afterBulkUpdate hook is triggered instead,
// where affected model are not directly available
UserModel.update({where: {id: 1} });

A trigger defined in the database would respond to both statements. Check the docs to see if you are getting all affected items in the callback.

Answer 2

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.

Answer 3

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;')

Answer 4

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;
};