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?

Use async/await if you want to write asynchronous code as if it was synchronous. — zero298
– zero298, Commented Jul 25, 2019 at 18:12
Also, you should use prepared statements instead of template literals for queries to avoid SQL injection. — zero298
– zero298, Commented Jul 25, 2019 at 18:13

GProst · Accepted Answer · 2019-07-25 18:46:52Z

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) }

Charlie · Accepted Answer · 2019-07-25 18:20:35Z

The for loop goes through all its iterations right away, making all the top level queries.

By the time you reach the end of the for loop, you have as many asynchronous DB requests working in the background as the number of iterations.

There is no guarantee that all the promises of the first iteration are resolved before the promises from subsequent iterations are resolved.

In short, the for loop doesn't wait until any of the promises are resolved.

Solution - use async/await

for (let i = 0; i < mainrow.length; i++) { await pool.query(foo); await pool.query(bar); }

This is one (and most preferred) way to solve it.

Collectives™ on Stack Overflow

Promise inside a for loop

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related