Using NodeJS Promises with MySQL

Question

I'm trying to reverse engineer a Node script from a library (research project) to do the following tasks sequentially:

1) Open and read a file (e.g., 'input.txt'). For simplicity, assume that the contents are properly formatted SQL queries)

2) Create a connection to a MySQL database

3) Execute the queries (constructed from (1) -- assume queries are properly defined in the file)

4) Terminate connection with the database

I want these tasks to be executed in order (i.e., 1--4). I don't have much experience in using Promises (Bluebird). Here is an excerpt of the code I have so far:

//Read the input file
function readFilePromise(){
    return new Promise(function(resolve, reject){
        var filePath = path.join(__dirname, filename);
        //asynchronous read
        fs.readFile(filePath, 'utf8', function (err, text){
            if (err)
                reject(err);
            else
                resolve(text.split('\n'));
        });
    })
}

//create connection
function createConnectionPromise(){
    return new Promise(function (resolve, reject){
        var connection = mysql.createConnection(connectionOptions);//global
        connection.connect(function(err){
            if(err){
                console.log('Error connecting to Db');
                reject(err);
            }
            else{
                console.log('Connection established');
                resolve(connection);
            }
        });
    })
}

//do transactions
function doTransactionsPromise (data){
    return new Promise(function (resolve, reject){
        var connection = data[0];
        var lines      = data[1];
        var topPromises = [];

        lines.forEach(function(sSQL){
            var p = new Promise(function(resolve,reject){
                console.log('Add: ' + sSQL);
                makeTransaction(connection, sSQL);
                return connection;
            });
            topPromises.push(p);
        });
        resolve(topPromises);
    });
}

//make transaction    
function makeTransaction(connection, sSQL){
  connection.beginTransaction(function(err){
        function treatErro(err, connection) {
            console.log('Failed to insert data in the database . Undoing!');
            connection.rollback();
        }

    function final() {
        connection.commit(function(err) {
            if(err) {
                treatErro(err, connection);
            }
            else {
                console.log('Added: ' + sSQL);
                return connection;
            }
        });
    }

    if(err) {
        treatErro(err, connection);
    }
    else {
        connection.query(sSQL, function (err, result) {
            if(err) {
                console.log(sSQL);
                treatErro(err, connection);
            }
            else {
                id = result.insertId;
            }
        });
        final();
    }
  });
}

Promise.all([createConnectionPromise(), readFilePromise()])
   .then(doTransactionsPromise)
   .then(function(promises){
       Promise.all(promises)
           .then(function(data){
                var connection = data[0];
                connection.end();
            });
   })
   .catch(function(error) {
       console.log('Error occurred!', error);
   });

The queries are executed fine but the connection to the DB does not terminate. Any help is appreciated.

PS: I'm sure the code can be improved massively.

Answer 1

I have not tested the code, but following code should do

    //Read the input file
    function readFilePromise(){
        return new Promise(function(resolve, reject){
            var filePath = path.join(__dirname, filename);
            //asynchronous read
            fs.readFile(filePath, 'utf8', function (err, text){
                if (err)
                    reject(err);
                else
                    resolve(text.split('\n'));
            });
        })
    }

    //create connection
    function createConnectionPromise(){
        return new Promise(function (resolve, reject){
            var connection = mysql.createConnection(connectionOptions);//global
            connection.connect(function(err){
                if(err){
                    console.log('Error connecting to Db');
                    reject(err);
                }
                else{
                    console.log('Connection established');
                    resolve(connection);
                }
            });
        })
    }

    //do transactions
    function doTransactionsPromise (data){



        var connection = data[0];
        var lines = data[1];
        var topPromises = [];

        topPromise = lines.map(function(sSQL){

            return makeTransaction(connection, sSQL);
        });

        return Promise.all(topPromises).then( function(){
              return connection;
        },function(){
            return connection;
        });


    }

    //make transaction    
    function makeTransaction(connection, sSQL){
      return new Promise(resolve, reject, function(){
          connection.beginTransaction(function(err){

            function treatErro(err, connection) {
                console.log('Failed to insert data in the database . Undoing!');
                connection.rollback();
                reject(connection);

            }

            function final() {
                connection.commit(function(err) {
                    if(err) {
                        treatErro(err, connection);
                    }
                    else {
                        console.log('Added: ' + sSQL);
                        resolve(connection);
                        return connection;
                    }
                });
            }

            if(err) {
                treatErro(err, connection);
            }
            else {
                connection.query(sSQL, function (err, result) {
                    if(err) {
                        console.log(sSQL);
                        treatErro(err, connection);
                    }
                    else {
                        id = result.insertId;
                    }
                });
                final();
            }
          });

      })  

    }

    Promise.all([createConnectionPromise(), readFilePromise()])
       .then(doTransactionsPromise)
       .then(function(connection){
            return connection.end();
       })
       .catch(function(error) {
           console.log('Error occurred!', error);
       });

Answer 2

The possible problem I see in your code is in function doTransaction

 function doTransactionsPromise (data){
   return new Promise(function (resolve, reject){
    var connection = data[0];
    var lines      = data[1];
    var topPromises = [];

    lines.forEach(function(sSQL){
        var p = new Promise(function(resolve,reject){
            console.log('Add: ' + sSQL);

            makeTransaction(connection, sSQL);
            return connection;
        });
        // P is never fullfilled.
        //Either transfer the responsibility to full-fill the promise to makeTransaction 
       // or makeTransaction function should return the promise which is full-filled by itself. 

        topPromises.push(p);
    });

    resolve(topPromises);
});
}