mysql.createPool transaction with loop

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!

mysqljs / mysql

mysql.createPool transaction with loop #2540