How to wait for a MYSQL query to finish before executing another using Node server?

Question

I am building an Express server to receive request (a dict with 10 items) from my React front end and then save the data to database. Below is my code.

I found that the query may crash during the insertion e.g. 2 queries got the same id by last_insert_id(). I have tried to use setTimeout() to wrap the getConnection function but the issue still exists. How to better solve the problem?

The request data:

{{.....}, {.....}, {.....}, {.....}, {.....}} #10 item

Code:

router.post('/fruit', (req, res) => {
    const dict = req.body;
    let itemCount = 0;
    var err_list = [];
    Object.keys(dict).forEach(function(r){
        let query = "call sp_insert_fruit();"
        setTimeout(function() {
            getConnection(function(err, conn){
                if (err) {
                    return res.json({ success: false, error: err })
                } else {
                    conn.query(query, function (err, result, fields) {
                        if (err) {
                            err_list.push({'errno':err.errno, 'sql_message':err.sqlMessage});
                        }
                        itemCount ++;
                        if (itemCount === Object.keys(dict).length) {
                            conn.release()
                            console.log('released', err_list)
                            if (err_list .length === 0) {
                                return res.json({ success: true});
                            } else {
                                return res.json({ success: false, error: err_list});
                            }
                        }
                    });
                }
            });
        }, 1000);
    });
});

connection.js:

const p = mysql.createPool({
  "connectionLimit" : 100,
  "host": "example.org",
  "user": "test",
  "password": "test",
  "database": "test",
  "multipleStatements": true
});

const getConnection = function(callback) {
    p.getConnection(function(err, connection) {
        callback(err, connection)
    })
};

module.exports = getConnection

Answer 1

You should replace callbacks with Promises and async/await to avoid callback hell. Using Promises, this problem should be easy to solve.

connection.js

const p = mysql.createPool({
  "connectionLimit" : 100,
  "host": "example.org",
  "user": "test",
  "password": "test",
  "database": "test",
  "multipleStatements": true
});

// wrap p.getConnection with Promise
function getConnection() {
    return new Promise((resolve, reject) => {
        p.getConnection((err, connection) => {
            if (err) reject(err);
            else resolve(connection);
        });
    });
};

module.exports = getConnection;

Router code

// wrap conn.query with Promise
function executeQuery(conn, query) {
    return new Promise((resolve, reject) => {
        conn.query(query, (err, result, fields) => {
            if (err) reject(err);
            else resolve({ result, fields });
        });
    });
}

router.post('/fruit', async (req, res) => {
    const dict = req.body;
    const errList = [];
    const query = "call sp_insert_fruit();"

    let conn = null;
    try {
        conn = await getConnection();
    } catch (err) {
        return res.json({
            success: false,
            error: err
        });
    }

    for (const r of Object.keys(dict)) {
        try {
            const { result, fields } = await executeQuery(conn, query);
        } catch (err) {
            errList.push({
                'errno': err.errno,
                'sql_message': err.sqlMessage
            });
        }
    }

    conn.release();
    console.log('released', errList);
    // I don't know what err_imnt is, so I guess it's errList?
    if (errList.length === 0) {
        return res.json({
            success: true
        });
    } else {
        return res.json({
            success: false,
            error: errList
        });
    }
});