Using Promise for parallel insert query on MySQL fails
<p>I wrote a code for running insert queries in parallel in Node.js and I am also using Promise.js.</p>

<p>But the code fails and raises an exception of "Duplicate Primary Key" entry.</p>

<p>The code is as follows,</p>

<pre><code> var Promise = require("promise");
 var mySql = require("mysql");
 var _ = require("underscore");

 var connection = mySql.createConnection({
   host : "localhost",
   user : "root",
   password : "rahul",
   database : "testDb" //schema
 });

 connection.connect();

function insertDept(name){
   return new Promise(fn);

   function fn(resolve,reject){
    getMaxDept().then(function(rows){
        var deptId = rows[0]["DeptId"];
        deptId = (_.isNull(deptId) === true) ? 125 : deptId;
        var sql = "insert into departmentTbl values("+deptId+",'"+name+"')";
        console.log(sql);
        connection.query(sql,function(err,rows,fields){
            if(err){
                console.log(err);
                return reject(err);
            }else{
                return resolve(rows);
            }
        });

    }).catch(function(error){
        return reject(error);
    }); 
   }//fn            
 }//insertDept

function getMaxDept(){
return new Promise(fn);

function fn(resolve,reject){
    var sql = "select max(deptId) + 1 as 'DeptId' from departmentTbl";

    connection.query(sql,function(err,rows,fields){
        if(err){
            console.log(err.stack);

            return reject(err);
        }else{
            return resolve(rows);
        }
    });
  }// fn    
} //getMaxDept 

function createDeptForAll(){
  var promiseObj = [];

  if(arguments.length &gt; 0){
    _.each(arguments,callback);
  }else{
    throw "No departments passed";
  }

  function callback(deptName){
    promiseObj.push(insertDept(deptName))       
  }

  return Promise.all(promiseObj);
}//createDeptForAll


  createDeptForAll("Archiology","Anthropology").then(function(createDepartment){
   createDepartment.then(function(rows){
    console.log("Rows inserted "+rows["affectedRows"]);
  }).catch(function(error){
    console.log(error);
  }).done(function(){
    connection.end();
  });
});
</code></pre>

<p>When I run the above code code,
the output is</p>

<pre><code>rahul@rahul:~/myPractise/NodeWebApp/NodeMySqlv1.0$ node queryUsingPromise02.js 
insert into departmentTbl values(125,'Archiology')
insert into departmentTbl values(125,'Anthropology')
{ [Error: ER_DUP_ENTRY: Duplicate entry '125' for key 'PRIMARY'] code: 'ER_DUP_ENTRY', errno: 1062, sqlState: '23000', index: 0 }
</code></pre>

<p>As Department Id is Primary key and the promises run in parallel,
the primary key for second Department's insert query fails.</p>

<p>As you can see, before any insert query, I fetch the max of departments + 1. </p>

<p>if the above query fails, I assign '125'.</p>

<p>Now, what should I change so that my above written code runs. </p>

<p>Should I use trigger of "before insert" for calculating next value of primary key of "department ID" at the database level itself or should I do something in my own Node.js code? </p>