execute raw insert or update query and get affected row count in sequelize

Sequelize is a ORM framework for nodejs. It makes that it’s very easy to work with database in nodejs. Though it provide methods to do raw query, the raw query callback doesn’t give the useful information just like affected rows and insert id when wo do a insert or update query. But the mysql driver do have this function!

So how to get the affected rows in sequelize? Two methods:

1. Use the mysql driver instead when we need affected rows information.
2. Append some method to sequelize that implemet it.

In the first solution, besides the sequelize database connections, we need to create new more mysql connections. I don’t like it. We use sequelize, we set database options to sequelize, and of course we want to do all the things of database through sequelize. No other else database connections!

Here, I will introduce how to append a “execute” method to sequelize that return affected rows in the success callback.

Look into the source code of sequelize, we will find that sequelize use a connection manager and pool to manage the database connections. So the thing that we need to do is acquire a raw database connection from the manager and then query sql.

  // sequelize.execute('your query', [, replacements], callback)
  Sequelize.prototype.execute = function(){
    var sql = arguments[0];
    var params = arguments[1];
    var callback = arguments[2];
    if(typeof params === 'function'){
      callback = params;
      params = [];
    }
    var pool = this.connectorManager.pool;  // a generic-pool instance that manage the database connections
    pool.acquire(function(err, db){
      if(err) return callback(err);
      db.query(sql, params, function(err, result){
        pool.release(db);
        return callback(err, result);
      });
    });
  };

Now you can execute insert and update sql and get more informations, just like:

  var sql = "insert into names (name) values (?) on duplicate key update id=last_insert_id(id)";
  sequelize.execute(sql, ['York.Gu'], function(error, result){
    console.log(result);
  });
  /* output:
     { fieldCount: 0,
       affectedRows: 1,
       insertId: 2,
       serverStatus: 2,
       warningCount: 0,
       message: '',
       protocol41: true,
       changedRows: 0 }
  */

ps. the table names has two fields, auto increment id and varchar(255) name