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

Does this answer your question? [Node.js: How do you handle callbacks in a loop?](https://stackoverflow.com/questions/36879029/node-js-how-do-you-handle-callbacks-in-a-loop) — Dan O, Apr 08 '20 at 18:09

Quantumass · Accepted Answer · 2020-04-08T19:08:42.100

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

error: await is only valid in async function(in the for loop) — Святослав Марюха, Apr 08 '20 at 18:20
error: You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require('mysql2/promise') instead of 'mysql2' for a promise-compatible version of the query interface. — Святослав Марюха, Apr 08 '20 at 18:38
I updated the answer, all you have to do is to add promise() after query or require('mysql2/promise') instead of require('mysql2') — Quantumass, Apr 08 '20 at 19:10

Problem with asynchronous request to the database

1 Answers1