Why do async functions not work within a controller's get() handler?

Question

I am using Node and Express with the the mssql npm package to connect to an SQL Server database. I do this in my app.js file which sets up a global variable to create a connectionPool to the database like so (I omitted some boilerplate stuff for brevity):

const mssql = require('mssql/msnodesqlv8'); // needs to use msnodesqlv8 driver for Windows Authentication to work
const express = require('express');
const app = express();

const DB_MYDB_Config = {
    server: "localhost",
    database: "MYDB",
    options: {
        trustedConnection: true // Windows auth enabled hence no username/password required
    }
};

global.MSSQL_MYDB = new mssql.ConnectionPool(DB_MYDB_Config); //connectionPool available to whole app

I have a Model file called offer.js which just does this:

async function getOffersAll() {
    await MSSQL_MYDB.connect(); // connects to the database
    try {
        var result = await MSSQL_MYDB.request(MSSQL_MYDB).query('SELECT Top(1) * FROM [dbo].[Offer]');
        return result; // returns the recordset of data
    } catch (error) {
        console.log(error);
    } finally {
        if (MSSQL_MYDB){
            try {
            await MSSQL_MYDB.close(); // closes the DB connection
            }
            catch (error) {
                console.log('Error closing connection');
            }
        }

    }

}

exports.getOffersAll = getOffersAll;

So far so good. I then have a Controller file index.js which doesn't really work (explained with comments):

    var router = require('express').Router();
    const Offer = require('../models/offer'); // the `offer.js` file

    /* the function below works perfectly */
    (async function () {
      var rsOffersAll = await Offer.getOffersAll();
      console.dir(rsOffersAll); // works great! recordset rsOffersAll is printed to console
    })();

    /* this is where it goes wrong even after commenting out the test function above */
    router.get('/',  async function(req, res) {
     var rsOffersAll = await Offer.getOffersAll(); // this just hangs and eventually I get a login failed error for SQL Server. 
      console.dir(rsOffersAll);
      res.render('index', { pagetitle: 'Homepage'}); // never renders
    });

module.exports = router;

My question is why does the first async function() that awaits a result from Offer.getOffersAll() not fail, but the same async function placed within the router.get('/') handler fails with a login error? If I remove the var rsOffersAll = await Offer.getOffersAll(); from the router.get('/') handler then the page renders, but of course I have no data to pass to it.

The exact same thing happens even if I store the test function's value in a variable and try to put it in the router.get() handler like this:

async function getOffersTest() {
          return await Offer.getOffersAll();
        }

router.get('/',  async function(req, res) {
         var rsOffersAll = await getOffersTest(); // still breaks
          console.dir(rsOffersAll);
          res.render('index', { pagetitle: 'Homepage'}); // still never renders
        });

My ultimate question how do I fix this so it just works the way it should in that when the homepage is visited, the router waits for the data to be returned from the database and then I can pass it to my view or just print to the console if I want?

Is it possible that calling `await MSSQL_MYDB.connect();` is causing the error, because it has already connected? — TKoL, Nov 18 '19 at 13:46
@TKoL I never call two of the `Offer.getOffersAll()` functions in the controller. I only call it once... so when I'm trying it out in my `router.get('/')` part I will comment out the test function. Therefore there shouldn't be two `await MSSQL_MYDB_connect();` running. — volume one, Nov 18 '19 at 13:54

score 0 · Answer 1 · answered Nov 18 '19 at 14:01

because of this line global.MSSQL_MYDB = new mssql.ConnectionPool(DB_MYDB_Config);,

when you execute this code outside of router,

(async function () {
     var rsOffersAll = await Offer.getOffersAll();
     console.dir(rsOffersAll); // works great! recordset rsOffersAll is printed to console
  })();

getOffersAll has access to global variable, and you can successfully connect with db in line await MSSQL_MYDB.connect(); //

but as for router, global scope is the current module.exports object, not the global object.

Solution

you can set MSSQL_MYDB in app like this,

app.set('MSSQL_MYDB')

then you can get this same variable in following function like this

router.get('/',  async function(req, res) {
     const MSSQL_MYDB = req.app.get('MSSQL_MYDB')
     var rsOffersAll = await getOffersTest(MSSQL_MYDB ); 
      console.dir(rsOffersAll);
      res.render('index', { pagetitle: 'Homepage'}); // still never renders
    });

I tried to move this: `const DB_MYDB_Config = { server: "localhost", database: "MYDB", options: { trustedConnection: true } }; const MSSQL_MYDB = new mssql.ConnectionPool(DB_MYDB_Config);` into my `offer.js` file and it still doesn't work. Its no longer in global scope so it should work right? — volume one, Nov 18 '19 at 14:22
yes - i tried it both inside the `getOffersAll` function and outside of it. get the same error that login failed. — volume one, Nov 18 '19 at 14:29

score 0 · Answer 2 · answered Nov 19 '19 at 01:07

This whole problem was just solved and it is a bug or something in node mssql package. It only works if you provide a username and password. If you use options: {trustedConnection: true } to enable windows authentication, then the router can never log in. I don't know why this is the case, but if you just supply a username and password (I used sa for testing) then it works fine.

Why do async functions not work within a controller's get() handler?

2 Answers2