mysql.createPool transaction with loop

[how1231]

I'm trying to change mysql connection from mysql.createConnection(config) to mysql.createPool(config). So far most of the APIs required nothing much to change except for transaction.

Problem: There is a for loop going to execute query inside a pool transaction callback hell. But the loop was not waiting for the callback to finish.

• Original code which is working fine

/* Begin transaction */
  try {
    await db.beginTransaction();

    // insert sales
    var saleId = await new Promise((resolve, reject) => {
      db.query('INSERT INTO sales SET ?', saleData, (err, dbResult) => {
        if (err) {
          if (err.sqlMessage) console.log(err.sqlMessage);
          resolve(null);
        } else {
          resolve(Object.keys(dbResult).length ? dbResult.insertId : null);
        }
      });
    });

    if (!saleId) {
      throw "Error: sale id not found, fail to insert sale.";
    }

    // insert payment
    var paymentData = {
      method: "cash",
      sale_id: saleId
    }

    var paymentId = await new Promise((resolve, reject) => {
      db.query(`INSERT INTO payments SET ?`, paymentData, (err, dbResult) => {
        if (err) {
          if (err.sqlMessage) console.log(err.sqlMessage);
          resolve(null);
        } else {
          resolve(Object.keys(dbResult).length ? dbResult.insertId : null);
        }
      });
    });

    if (!paymentId) {
      throw "Error: payment id not found, fail to insert payment.";
    }

    // update stock
    // just an update query inside a loop
    for (var i = 0; i < cartList.length; i++) {
      var updateStockSql = `UPDATE stocks 
        SET ${branchId}_quantity = COALESCE(${branchId}_quantity, 0) - ${cartList[i].Total_stock} 
        WHERE product_id = ${cartList[i].Product_id}`;

      var updateStockResult = await new Promise((resolve, reject) => {
        db.query(updateStockSql, (err, dbResult) => {
          if (err) {
            if (err.sqlMessage) console.log(err.sqlMessage);
            resolve(null);
          } else {
            resolve(Object.keys(dbResult).length ? dbResult : null);
          }
        });
      });

      if (!updateStockResult) throw "Error: Fail to update stock.";
    }

    await db.commit();
  } catch (error) {
    await db.rollback();
    return { status: "error", msg: "- Transaction Fail -" };
  }
  /* End transaction */

• Present code which got problem

/* Begin transaction */
  try {
    var trxStatus = await new Promise((resolve, reject) => {
      db.getConnection((err, trxConn) => {

        trxConn.beginTransaction((err) => {

          //start
          if (err) {
            trxConn.rollback(() => trxConn.release());
            resolve({ status: "error", msg: `Transaction Fail: ${err}` });
          } else {

            // insert sales
            trxConn.query('INSERT INTO sales SET ?', saleData, (err, salesResult) => {
              if (err || !salesResult.insertId) {
                // rollback
                trxConn.rollback(() => trxConn.release());
                if (!salesResult.insertId) err = "Error: sale id not found, fail to insert sale.";
                else if (err.hasOwnProperty('sqlMessage')) err = err.sqlMessage;
                resolve({ status: "error", msg: `Transaction Fail: ${err}` });
              } else {

                // insert payments
                trxConn.query('INSERT INTO payments SET ?', paymentData, (err, paymentResult) => {
                  if (err || !paymentResult.insertId) {
                    // rollback
                    resolve({ status: "error", msg: `Transaction Fail: ${err}` });
                  } else {

                    // here is whr I'm stucked
                    // update stock
                    var updateStockStatus = true;
                    for (var i = 0; i < cartList.length; i++) {
                      var updateStockSql = `UPDATE stocks 
                      SET ${branchId}_quantity = COALESCE(${branchId}_quantity, 0) - ${cartList[i].Total_stock} 
                      WHERE product_id = ${cartList[i].Product_id}`;

                      trxConn.query(updateStockSql, (err, updateStockResult) => {
                        if (err || !Object.keys(updateStockResult).length) {
                          if (err.hasOwnProperty('sqlMessage')) console.log(err.sqlMessage);
                          updateStockStatus = false;
                        }
                      });
                      if (!updateStockStatus) break;
                    }

                    if (!updateStockStatus) {
                      // rollback
                      trxConn.rollback(() => trxConn.release());
                      resolve({ status: "error", msg: `Transaction Fail: fail to update stock` });
                    } else {
                      // commit
                      trxConn.commit((err) => {
                        if (err) trxConn.rollback(() => trxConn.release());
                        else trxConn.release();
                        resolve({ status: "ok", msg: `- Payment Successful -` });
                      });
                    }
                  }
                });
              }
            });
          }
          //end

        });
      });
    });
  } catch (error) {
    console.log(error);
    return { status: "error", msg: `Transaction Fail: please contact IT support` };
  }
  /* End transaction */

Question: Does anyone has any idea on how to solve this kind of asynchronous problem?

ps: Sorry for the long question...I'm already stuck at here for few days, any help would be appreciated, thanks in advance!

[dougwilson]

Hello, and sorry you are having trouble. Yes, in Node.js using async callbacks in a for loop doesn't work as you would like. Thankfully there is a great library that provides this functionality: https://www.npmjs.com/package/async