Why is my dictionary empty when using res.send with SQL Server and express.js?

Question

I'm using mssql with express.js. I'm trying to set the recordset results into a dictionary and then returning that dictionary using res.send but dictionary is empty. What am I doing wrong?

Here's what I have tried:

app.post('/api/v1/test', async (req, res) => {
    tableNames = ["test", "test2"]

    var response = {};
    for (let index = 0; index < tableNames .length; index++) {
        const table = tableNames [index];
        let query = `SELECT * FROM ${table }`
        await queryDatabase(query , function(err, result){
            response[table ] = result.recordset;
        });
    }

    res.send(
        {
            statusCode: 200,
            data: response
        }
    );
});

function queryDatabase(query, callback) {
    sql.connect(dbConfig, function (err) {
        if (err) {
            console.log(err);
        }
        var request = new sql.Request();
        request.query(query, function (err, result) {
            if (err) throw err
            callback(null, result)
        });
    });
}

Answer 1

queryDatabase() does not return a promise and thus await does nothing useful with it so your for loop does not pause waiting for the query to complete and thus you call res.send() before your response object contains the data.

You can either switch to a sql module that directly supports promises or you can modify queryDatabase() to support promises and then modify how it's called to use the returned promise like this:

app.post('/api/v1/test', async (req, res) => {
    try {
        tableNames = ["test", "test2"]

        var response = {};
        for (let table of tableNames) {
            const query = `SELECT * FROM ${table}`
            const result = await queryDatabase(query);
            response[table] = result.recordset;
        }

        res.send({ statusCode: 200, data: response });
    } catch (e) {
        console.error(e);
        res.status(500).send({ statusCode: 500, data: { error: "database error" } })
    }
});

function queryDatabase(query, callback) {
    return new Promise((resolve, reject) => {
        sql.connect(dbConfig, function (err) {
            if (err) {
                reject(err);
                return;
            }
            var request = new sql.Request();
            request.query(query, function (err, result) {
                if (err) {
                    reject(err);
                    return;
                }
                resolve(result);
            });
        });
    });
}