Don't understand this async await call with axios

Question

I am using node 10+, and I have this function where I do a database query and wait for the result and return it:

var test3 = (req,res,query) => {   
    var conn = new sql.ConnectionPool(dbconfig);
    var req = new sql.Request(conn);
    var result;
    return  conn.connect().then(async() => {       
            result = await req.query(query);               
            conn.close();
            return result;

           }).catch(e => {
                return e;
           }).finally(() => {
                conn.close();
           });   
}

First, I would like to know why I have to return the conn.connect() block..

return  conn.connect().then(async() => {...  

I know it has something to do with promise chaining I think, but I dont understand why, because my async db call is already resolved from the await dbcall function... and I just return the result from inside the function

Then, I have a router where I call the api function here:

router.get("/api/compareCount",  function(req,res) {
    var query = `SELECT COUNT(*) as count
      FROM [DublettenReferenzmenge].[dbo].[DocumentForElasticsearch] where lastChange < dateadd(day,-1,getdate())`;
      var query2 = `SELECT COUNT(*) as count
      FROM [DublettenReferenzmenge].[dbo].[DocumentForElasticsearch] where lastChange < dateadd(hour,-8,getdate())`;
      var query3 =`SELECT COUNT(*) as count
      FROM [DublettenReferenzmenge].[dbo].[DocumentForElasticsearch]`;
    axios.all([searchES(req,res),   test3(req,res,query),   test3(req,res,query2) , test3(req,res,query3)])
    .then(axios.spread(function (esCount, mssqlCount1, mssqlCount2, mssqlCount3) {
        totalES = esCount.hits.total;

        totalMSSQL  = mssqlCount1.recordset[0].count;
        totalMSSQL2 = mssqlCount2.recordset[0].count;
        totalMSSQL3 = mssqlCount3.recordset[0].count;totalMSSQL,  " mssqlCount2: ", totalMSSQL2, "mssqlCount3: ", totalMSSQL3);
        var msg = "ES Dokumente total: " + totalES + " MSSQL Dokumente total: " + totalMSSQL + "<br>";
        if ( totalES != totalMSSQL) {
            msg += "Critical: " + totalES != totalMSSQL + "<br>"; 
        } if ((totalES != totalMSSQL2))  {            
            msg += "Warning: " + (totalES != totalMSSQL2) + "<br>";
        } if ((totalES > totalMSSQL3))  {  
            msg += "Achtung es gibt ungelöschte Dokumente im Elasticsearch Index!";
        }      
        res.set('Content-Type', 'text/html');
        res.send(msg);
    })).catch((err) => {
        res.send(err);
    });
})

router.get("/api/test3", async function (req,res) {
    var query = `SELECT COUNT(*) as count
      FROM [DublettenReferenzmenge].[dbo].[DocumentForElasticsearch] where lastChange < dateadd(day,-1,getdate())`;
    var result = await test3(req,res,query);
    res.json(result);
})

The api/test3 route returns me the result as usual, but the api/compareCount does return me correct results as well...

Furthermore, I have to use the async function ... await test3(..) async-await syntax structure to resolve my result into a variable... But I do not have to use that same structure for my api/compareCount function above, the result is returned anyways in the .then(axios.spread(function(...))). Why is that? I am quite confused as I don't really know the inner workings of the Promise chaining and calls...

EDIT: before my test3() function, I had something like this:

async function testQuery(query) {
    try {
        let pool = await sql.connect(dbconfig);
        let result1 = await pool.request()
            //.input('input_parameter', sql.Int, value)
            .query(query);  
            sql.close();  
        return result1;      

    } catch (err) {
        console.log(err);
        sql.close();  

    } finally {
        sql.close();
    }
};

I got also results with that function, however, I got some kind of race condition where it told me that the sql- connection already exists, and do sql.close() first if I reload the page too quickly... I dont get this with the test3() function anymore...

Answer 1

To start with, test3() needs to return a promise so the caller knows when it's done and whether it had an error or not. It's probably easiest to use async/await for that:

async function test3(query) => {   
    const conn = new sql.ConnectionPool(dbconfig);
    const request = new sql.Request(conn);
    await conn.connect();
    try {
        const result = await request.query(query);
        return result;
    } finally {
        conn.close();
    }
}

Various changes:

1. Only pass arguments that are going to be used
2. Use await to simplify async logic flow
3. Use try/finally to catch any error after connected so we can always close the connection and not leak a connection
4. Switch from var to const.
5. Make function async so it returns a promise that is hooked to when the internals are done or have an error

Then, if you adjust how test3() is called to only pass the query argument (since that's all that is used), your use of that function the other places you are using it should work.

Your code was doing this:

var result = await test3(req,res,query);

But, test3() had no return value so the await had nothing useful to do. await works with a promise and you weren't returning a promise that was linked to the async operations inside of test3(). That's what my changes above do.