Nodejs - node-mysql transaction with a loop

Question

My nodejs project currently uses node-mysql.

I have a multi-level data array that I want to insert into multiple tables. Transaction is what I thought of in order to accomplish my goal. Below is the outline of my code:

const mysql = require("mysql")
const pool = mysql.createPool({ // connectionLimit=10 by default
    connectionLimit: 5, // 10 by default
    host: 'somehost', 
    user: 'userblah',
    password: 'passworblah', 
    database: 'dbblah'
})

pool.getConnection(function(err, dbConnection) {

dbConnection.beginTransaction(function(err1) {
        if (err1) {
            console.log(“Unable to begin the transaction”)
            return
        }

        arrayElements.forEach(function(element) {
            var insert1 = mysql.format(“INSERT INTO table1(…) VALUES (…)”, […])
            dbConnection.query(insert1, function(error1, result1) {
                if (error1) {
                    return dbConnection.rollback(function() {
                        console.log(“Insert failed”)
                    })
                }

                const lastID = result1.insertId

                // A: more loops coming here to insert sub-data to more tables with lastID

                // B: SHOULD I dbConnection.commit here???
            })
        })
    }
}

I think it's too soon to dbConnection.commit at B (while the forEach is going on)? What should I do to make sure everything is done, then commit?

Answer 1

You are ignoring that db calls are basically IO calls. They need time to process. So when you call insert statement inside forEach all the calls are hit and they dont wait for your query to answer.

var async = require('async')
const mysql = require("mysql")
const pool = mysql.createPool({ // connectionLimit=10 by default
    connectionLimit: 5, // 10 by default
    host: 'somehost', 
    user: 'userblah',
    password: 'passworblah', 
    database: 'dbblah'
})

pool.getConnection(function(err, dbConnection) {

dbConnection.beginTransaction(function(err1) {
        if (err1) {
            console.log(“Unable to begin the transaction”)
            return
        }

        Async.eachSeries(arrayElements,function iteratorOverElems(element,callback) {
            var insert1 = mysql.format(“INSERT INTO table1(…) VALUES (…)”, […])
            dbConnection.query(insert1, function(error1, result1) {
                if (error1) {
                    return callback(err)
                    })
                }

                const lastID = result1.insertId
            moreInsertion(arguments,
                   function (err,result){
                    if(err){
                        return callback(err)
                    }
                   // A: more loops coming here to insert sub-data to more tables with lastID       
                   //now return the iterator
                   return callback();
                   })

            })
        },function finalCb(err){
                    if(err){
                       //rollback
                    }else{
                    // commmit here when all the insertions have been successful            
                     }
    });
    }
}

This is not definitive code. you'll need to work on it. Use async library to manage asynchronous function handling for each element in forEach looping function.

I think you should commit when all the queries have been successful else rollback.

The async.eachSeries works like call iterator for each Element . if callback is called with err argument then the rest of the elements are ignored and final callback is called. For more detailed info refer to async doc https://github.com/caolan/async#each