multiquery.js

var async = require('async');
var util = require("util")
// execute multi query
module.exports = function(connection, queries, options, cb){
  if(typeof options === "function") cb = options, options = {};
  if(!options) options = {};
  if(typeof queries == "string"){
    queries = [queries];
  }


  if(queries.length === 0){
    cb(new Error("Queries is empty"))
    return;
  }

  var tasks;
  if(util.isArray(queries)){
    tasks = []
  }else{
    tasks = {}
  }
  var keys = Object.keys(queries);
  var defaultQueryComplete = function(err, rows, fields, next){
    
    var result = {
      rows : rows,
      fields : fields,
      error : err
    }
    next(null, result)
  }
  keys.forEach(function(key){
    var query = queries[key];
    if(typeof query == "string"){
      query = {
        sql : query
      }
    }
    if(!query.name){
      query.name = key;
    }
    if(typeof query.complete != "function"){
      if(typeof options.queryComplete == "function"){
        query.complete = options.queryComplete;
      }else{
        query.complete = defaultQueryComplete;
      }
    }
    var _tasks = function(next){
      connection.query(query.sql, query.values, function(err, rows, fields){
        query.complete(err, rows, fields, next);
      });
    };
    if(util.isArray(tasks)){
      tasks.push(_tasks);
    }else{
      tasks[key] = _tasks;
    }
    
  })
  async.series(tasks, function(err, results) {
    if(cb){
      cb(err,results)
    }
  });
}

test_mocha.js

var mysql = require("mysql")
var fs = require("fs")
var assert = require("assert")
var util = require("util")
var schema = "multiquery_mocha_test";

describe("multiquery", function(){
  var connection;
  var multiquery = require("../multiquery")
  var cleanupSchema = require("../cleanup_schema")
  beforeEach(function (done) {
    var conf = JSON.parse(fs.readFileSync("config.json"))
    connection = mysql.createConnection({
      host     : conf.host,
      user     : conf.user,
      password : conf.pass
    });
    cleanupSchema(connection, "multiquery_mocha_test",function(err,result){
      var queries = [
      "CREATE TABLE IF NOT EXISTS users ( "+
      "  id int, "+
      "  name varchar(20) "+
      ");",
      "TRUNCATE TABLE users;",
      "INSERT INTO users VALUES (1,'foo');",
      "INSERT INTO users VALUES (2,'bar');",
      "INSERT INTO users VALUES (3,'baz');"
      ]
      multiquery(connection, queries, function(){
        done();
      })
    })
  });
  it("empty array", function(done){
    multiquery(connection, [],function(err, result){
      assert.equal(err.message,"Queries is empty")
      done();
    });
  })
  it("execute with only one", function(done){
    var queries = [
      "SELECT 1"
    ]
    multiquery(connection, queries,function(err, result){
      assert.deepEqual(result[0].rows,[{'1' : 1}])
      done();
    })
  })
  it("execute all", function(done){
    var queries = [
      "SELECT 1",
      "SELECT 2"
    ]
    multiquery(connection, queries,function(err, result){
      assert.equal(util.isArray(result), true);
      assert.deepEqual(result[0].rows,[{'1' : 1}])
      assert.deepEqual(result[1].rows,[{'2' : 2}])
      done();
    })
  })
  it("execute object all", function(done){
    var queries = {
      query1 : "SELECT 1",
      query2 : "SELECT 2"
    }
    multiquery(connection, queries,function(err, result){
      assert.equal(util.isArray(result), false);
      assert.deepEqual(result.query1.rows,[{'1' : 1}])
      assert.deepEqual(result.query2.rows,[{'2' : 2}])
      done();
    })
  })
  it("execute on object", function(done){
    var queries = [
      {
        sql : "SELECT * FROM users WHERE id = ?",
        values : [1]
      }
    ]
    multiquery(connection, queries,function(err, result){
      assert.deepEqual(result[0].rows,[{'id' : 1, 'name' : 'foo'}])
      done();
    })
  })

  it(" execute without handring error and ignore error", function(done){
    var queries = [
      "SELECT * FROM not_exist_table WHERE id = 1",
      "SELECT * FROM not_exist_table2 WHERE id = 2"
    ]
    multiquery(connection, queries, function(err, result){
      assert.equal(result[0].error.code, "ER_NO_SUCH_TABLE");
      assert.equal(result[1].error.code, "ER_NO_SUCH_TABLE");
      done();
    })
  })
  it("execute with handring error", function(done){
    var queries = [
      "SELECT * FROM not_exist_table WHERE id = 1",
      "SELECT * FROM not_exist_table2 WHERE id = 2"
    ]
    var option = {
      queryComplete : function(err, query, fields, next){
        assert.equal(err.code, "ER_NO_SUCH_TABLE");
        next()
      }
    }
    multiquery(connection, queries, option,  function(err, result){
      done();
    })
  })
  it("execute with handring error per query", function(done){
     var queries = [
      {
        sql : "SELECT * FROM not_exist_table WHERE id = 1",
        complete : function(err, query, fields, next){
          assert.equal(err.code, "ER_NO_SUCH_TABLE");
          next()
        }
      },
      "SELECT 1"
    ]
    multiquery(connection, queries, function(err, result){
      done();
    })
  })
})