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

score 1 · Accepted Answer · answered May 24 '23 at 23:56

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

Do you know how this can be solved with `callbacks` instead of async/awaits? — Stoobish, May 25 '23 at 14:10
@Stoobish - It can be solved without promises and without async/await, but it's not easy because you have a loop with an asynchronous operation in it so the loop has to be coded manually if you're not going to use `await`. Why would you not use promises and the easier way to write this code? — jfriend00, May 25 '23 at 21:11

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

1 Answers1