Problem with asynchronous request to the database

Question

I have a "Place your order" button that calls /orderRegistration, which in turn updates the quantity of product in stock according to the order and sends the confirmed order to the email

const orderStatus = ['Confirmed', 'Not confirmed'];
router.post('/orderRegistration', (req, res) => {
  if (req.session.successAuthentication === true &&
      req.session.isWorker === false) {
    conn.query(`SELECT  orders.id,
                        products.product_id,
                        products.product_name,
                        products.product_amount,
                        order_product.count 
                FROM orders INNER JOIN order_product
                ON orders.id = order_product.order_id INNER JOIN products
                ON order_product.product_id = products.product_id
                WHERE orders.id IN(
                    SELECT id 
                    FROM orders
                    WHERE user_id=${req.session.userId}
                    AND status = '${orderStatus[1]}')
                AND orders.status = '${orderStatus[1]}';`, (err, selProductId) => {
      if (err) {throw err;}
      if (selProductId.length > 0) {
        let dateNow = new Date(); 
        let prepDate = {
          day: (dateNow.getDate() < 10) ? `0${dateNow.getDate()}` : dateNow.getDate(),
          month: ( dateNow.getMonth() + 1 < 10) ? `0${dateNow.getMonth() + 1}` : dateNow.getMonth() + 1,
          year: dateNow.getFullYear(),
          hours: (dateNow.getHours() < 10) ? `0${dateNow.getHours()}` : dateNow.getHours(),
          minutes: (dateNow.getMinutes() < 10) ? `0${dateNow.getMinutes()}` : dateNow.getMinutes()
        };
        let orderDate =  `${prepDate.day}.${prepDate.month}.${prepDate.year} ${prepDate.hours}:${prepDate.minutes}`;
        let productsInOrderHTML = '';
        let totalAmount = 0;
        for (let i = 0; i < selProductId.length; i++) {
          conn.query(`UPDATE products
                      SET products.product_count_stock = products.product_count_stock - ${selProductId[i].count}
                      WHERE products.product_id = ${selProductId[i].product_id}`, err => {
            if (err) {throw err;}
            productsInOrderHTML += `<tr>
                                      <td>
                                        ${selProductId[i].product_name}
                                      </td>
                                      <td>
                                        ${selProductId[i].count}
                                      </td>
                                      <td>
                                        ${selProductId[i].product_amount}
                                      </td>
                                    </tr>`;
            totalAmount +=  selProductId[i].count *
                            selProductId[i].product_amount;
            if(i === selProductId.length - 1) {
              console.log('totalAmount: ' + totalAmount);
            }
          });
        }
      } else {
        res.send('error');
      }
    });        
  } else {
    res.send('error');
  }
});

But because the calls are asynchronous, sometimes the loop does not have time to update all the products and occurs

if(i === selProductId.length - 1) {
    console.log('totalAmount: ' + totalAmount);
}

that is, sometimes totalAmount may have time to update all products, and sometimes it does not, and it turns out that totalAmount will not be equal to the cost that the user ordered the product for.

How do I rewrite the query or refactor it so that this doesn't happen again

P.S. Sorry for the English, I translated this through a translator, because I speak Russian.I may also have missed something, so correct me if necessary

Answer 1

The queries to the database are asynchronous which means the eventloop of nodejs will pass them to the queue and the loop will continue so you have to await for them

try this

const orderStatus = ['Confirmed', 'Not confirmed'];
router.post('/orderRegistration', async (req, res) => {
    if (req.session.successAuthentication === true &&
        req.session.isWorker === false) {
        await conn.promise().query(`SELECT  orders.id,
                        products.product_id,
                        products.product_name,
                        products.product_amount,
                        order_product.count 
                FROM orders INNER JOIN order_product
                ON orders.id = order_product.order_id INNER JOIN products
                ON order_product.product_id = products.product_id
                WHERE orders.id IN(
                    SELECT id 
                    FROM orders
                    WHERE user_id=${req.session.userId}
                    AND status = '${orderStatus[1]}')
                AND orders.status = '${orderStatus[1]}';`, async (err, selProductId) => {
            if (err) {
                throw err;
            }
            if (selProductId.length > 0) {
                let dateNow = new Date();
                let prepDate = {
                    day: (dateNow.getDate() < 10) ? `0${dateNow.getDate()}` : dateNow.getDate(),
                    month: (dateNow.getMonth() + 1 < 10) ? `0${dateNow.getMonth() + 1}` : dateNow.getMonth() + 1,
                    year: dateNow.getFullYear(),
                    hours: (dateNow.getHours() < 10) ? `0${dateNow.getHours()}` : dateNow.getHours(),
                    minutes: (dateNow.getMinutes() < 10) ? `0${dateNow.getMinutes()}` : dateNow.getMinutes()
                };
                let orderDate = `${prepDate.day}.${prepDate.month}.${prepDate.year} ${prepDate.hours}:${prepDate.minutes}`;
                let productsInOrderHTML = '';
                let totalAmount = 0;
                for (let i = 0; i < selProductId.length; i++) {
                    await  conn.promise().query(`UPDATE products
                      SET products.product_count_stock = products.product_count_stock - ${selProductId[i].count}
                      WHERE products.product_id = ${selProductId[i].product_id}`, err => {
                        if (err) {
                            throw err;
                        }
                        productsInOrderHTML += `<tr>
                                      <td>
                                        ${selProductId[i].product_name}
                                      </td>
                                      <td>
                                        ${selProductId[i].count}
                                      </td>
                                      <td>
                                        ${selProductId[i].product_amount}
                                      </td>
                                    </tr>`;
                        totalAmount += selProductId[i].count *
                            selProductId[i].product_amount;
                        if (i === selProductId.length - 1) {
                            console.log('totalAmount: ' + totalAmount);
                        }
                    });
                }
            } else {
                res.send('error');
            }
        });
    } else {
        res.send('error');
    }
});