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?

Solution

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

Comments

[Alexis Wilke]

Excellent, that works with mysqljs too. You can just do CREATE TRIGGER ... without trying to change the delimiters as most often shown in examples.