Promise inside a for loop

Question

I have trying to do a bunch of database insertion and selection using a promisfied mysql library.

The problem is that I have to use a for loop.

My code looks like this:

var partNumber = [],
        stageName = [],
        quantity = [],
        locationName = []
    var orderType, receiveDate, customerName

    pool.query(`select * from orderDetail where orderID = ${req.body.orderID} order by orderDetailID`)
        .then(mainrow => {
            
            for (let i = 0; i < mainrow.length; i++) {
                
                    quantity.push(mainrow[i].QtyShipped)
                     pool.query(`Select PartNumber from parts where partID = ${mainrow[i].PartID}`)

                    .then(rows => {
                        console.log(`here is 1`)
                        partNumber.push(rows[0].PartNumber)
                    
                        return pool.query(`Select StageName from stages where StageID = ${mainrow[i].StageID}`)
                    })
                    .then(rows => {
                        console.log(`here is 2`)
                        
                        return pool.query(`Select LocationName from locations where locationID =${mainrow[i].LocationID}`)
                    })
                    .then(rows=>{
                        console.log(` here is 3`)
                        locationName.push(rows[0].LocationName)
                        
                        stageName.push(rows[0].LocationName)
                        orderType=mainrow[i].OrderType
                        receiveDate = mainrow[0].receiveDate
                        req.flash('success', `You returned back to the OrderID ${req.body.orderID}`)
                        customerName = mainrow[0].CustomerName
                        if(i==mainrow.length-1){
                            var query = queryString.stringify({
                                "orderID": req.body.orderID,
                                "orderType": orderType,
                                "date": receiveDate,
                                "customerName": customerName,
                                "success": req.flash('success'),
                                "partNumber": partNumber,
                                "quantity": quantity,
                                "locationName": locationName,
                                "stageName": stageName
                
                            })
                            
                            res.redirect('/orderDetails?' + query)
                            
                        }
                        
                    })
                    .catch(err => {
                        console.log(err)
                    })

                    
            }
            
        })
        

        .catch(err => {
            console.log(err)
        })

The parent query is where I am retrieving the orderID and I am looping through various orderID's to retrieve orderDetails and populate array. However, I am having a really hard time keeping this code synchronous inside the for loop.

I want the console.log to be:

here is 1
here is 2
here is 3

here is 1
here is 2 
here is 3

here is 1
here is 2 
here is 3 

Instead I am getting:

here is 1
here is 2
here is 1
here is 1
 here is 3
here is 2
here is 2
 here is 3
 here is 3

Each async code is executing the correct number of time in this case 3 but it is not synchronous at all because of the for loop. Is there a way to use async code inside for loop and have it be synchronous?

Answer 1

This is your code rewritten with async/await. Just keep in mind that with such approach you will execute each request one after another instead of doing it in parallel as you did with promises. So this solution is slower. Also as zero298 pointed out you should use prepared statements to avoid SQL injection.

const partNumber = []
const stageName = []
const quantity = []
const locationName = []
let orderType, receiveDate, customerName
try {
  const mainrow = await pool.query(`select * from orderDetail where orderID = ${req.body.orderID} order by orderDetailID`)
  for (let i = 0; i < mainrow.length; i++) {
    quantity.push(mainrow[i].QtyShipped)
    let rows = await pool.query(`Select PartNumber from parts where partID = ${mainrow[i].PartID}`)
    console.log(`here is 1`)
    partNumber.push(rows[0].PartNumber)
    rows = await pool.query(`Select StageName from stages where StageID = ${mainrow[i].StageID}`)
    console.log(`here is 2`)
    rows = await pool.query(`Select LocationName from locations where locationID =${mainrow[i].LocationID}`)
    console.log(` here is 3`)
    locationName.push(rows[0].LocationName)
    stageName.push(rows[0].LocationName)
    orderType = mainrow[i].OrderType
    receiveDate = mainrow[0].receiveDate
    req.flash('success', `You returned back to the OrderID ${req.body.orderID}`)
    customerName = mainrow[0].CustomerName
  }
  const query = queryString.stringify({
    "orderID": req.body.orderID,
    "orderType": orderType,
    "date": receiveDate,
    "customerName": customerName,
    "success": req.flash('success'),
    "partNumber": partNumber,
    "quantity": quantity,
    "locationName": locationName,
    "stageName": stageName

  })
  res.redirect('/orderDetails?' + query)
} catch(err) {
  console.log(err)
}