NodeJS, Express, MSSQL - How do I wait for MSSQL pool to connect and return query result before printing to console

Question

Node Version -                 16.1.0
Microsoft SQL Server 2008 -    10.50.2550.0
SQL Server Management Studio - 15.0.18358.0
Windows 10 Pro Edition -       2004
OS Build ^ -                   19041.572

Project Architecture:
root
|- <node_modules>
|- index.js
|- databaseStuff.js
|- test.js
|- package.json

Important Note, I have to run my app with the following command: node --tls-min-v1.0 index.js

I'm making an API to take information from a GitLab webhook and write to a table using NodeJS, Express, and MSSQL. I will perform various read/write operations from Stored Procedures but the first step is executing a simple query. I would like to send the result of a query as the HTTP response.

The part I'm having trouble with is timing the query around DB connection.

The errors I'm receiving contain ConnectionError code 'ENOTOPEN' (see error_console_output)

// error_console_output

Server is running..
Promise { <pending> }
Connected to DB
TypeError: Cannot read property 'on' of undefined
    at \root\node_modules\mssql\lib\tedious\request.js:428:
20
    at \root\node_modules\mssql\lib\base\connection-pool.js
:289:41
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at runNextTicks (node:internal/process/task_queues:65:3)
    at processImmediate (node:internal/timers:437:9)
\root\node_modules\mssql\lib\base\connection-pool.js:298
      return shared.Promise.reject(new ConnectionError('Connection not yet open.
', 'ENOTOPEN'))
                                   ^

ConnectionError: Connection not yet open.
    at ConnectionPool._acquire (\root\node_modules\mssql\li
b\base\connection-pool.js:298:36)
    at ConnectionPool.acquire (\root\node_modules\mssql\lib
\base\connection-pool.js:284:56)
    at Immediate.<anonymous> (\root\node_modules\mssql\lib\
tedious\request.js:409:19)
    at processImmediate (node:internal/timers:464:21) {
  code: 'ENOTOPEN'
}

Part of me is wondering if my SQL Server version isn't compatible with MSSQL or if I'm just not writing the connection properly.

// index.js

var db = require('./databaseStuff')

var express = require('express');
var app = express();

app.get('/', function(req, res) {
  var result = db.execute();
  console.log(result);
})

var server = app.listen(8080, function () {
    console.log('Server is running..');
});

// databaseStuff.js

var sql = require("mssql");

var config = {
    user: 'user',
    password: 'password',
    server: 'server',
    database: 'DBname',
    options: {
      encrypt: true,
      trustServerCertificate: true
    }
};

var sqlQuery = "select * from Games";

async function connectDB()
{
  const pool = new sql.ConnectionPool(config);

  try
  {
    await pool.connect();
    console.log('Connected to DB');
    return pool;
  }
  catch (err)
  {
    console.log('Failed to connect to DB', err);

    return err;
  }
}

async function getGames() {
  const DB = await connectDB();

  try {
    const result = await DB.request().query(sqlQuery, function (err, res) {
      if (err) console.log(err);
      else console.log(res);
    });

    return result.recordset;
  }
  catch (err)
  {
    console.log('Error querying DB', err);

    return err;
  }

  finally
  {
    DB.close();
  }
}

async function execute()
{
  let result = await getGames();

  return result;
}

module.exports = {
  execute
};

// package.json

{
  "name": "root",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "msnodesqlv8": "^2.1.0",
    "mssql": "^7.1.0",
    "tedious": "^6.2.0"
  }
}

I had something working before I tried to deconstruct it. test.js will wait for the MSSQL query to return a result. I would use this but I would like to keep my code segmented from the main class. Note that test.js will send the SQL query result as the response body, I would like to keep this behavior. (see expected_results).

// test.js

var express = require('express');
var app = express();
var server = app.listen(8080, function () {
    console.log('Server is running..');
    var sql = require("mssql");
    // config for your database
    var config = {
        user: 'user',
        password: 'password',
        server: 'server',
        database: 'DBname',
        options: {
          encrypt: true,
          trustServerCertificate: true
        }
    };
    // connect to your database
    sql.connect(config, function (err) {
        if(err)
        {
            console.log("CONNECTION: " + err);
            return;
        }
        console.log("Connected!!");
        // create Request object
        var request = new sql.Request();
        // query to the database and get the records
        request.query('select * from Games', function (err, recordset) {
            if (err)
            {
                 console.log("QUERY: " + err);
            }
            // send records as a response
            console.log(recordset);
            //res.send(recordset);
        });
    });
});

// expected_results

{
  recordsets: [ [ [Object] ] ],
  recordset: [ { ID: 1, Name: 'GameName' } ],
  output: {},
  rowsAffected: [ 1 ]
}

EDIT(s):

app.get('/', async function(req, res) {
  try
  {
    var result = await db.execute();
    console.log(result);
  }
  catch (error)
  {
    console.log(error);
  }
})

The above change prevents 'console.log(result)' from firing before a result is returned. However, I am still experiencing the same ConnectionError from error_console_output.

Answer 1

I figured it out in case anyone runs into this problem. For further reference, please view this

My problem was due to not properly creating a connection to my database. I followed the Connection Pools section of the mssql documentation (linked earlier).

This method makes sure a connection has been created and secured before moving forward with a request

// index.js

var db = require('./databaseStuff')

var express = require('express');

var app = express();

var server = app.listen(8080, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
});

app.get('/', async function(req, res) {
  let result = await db.execute();
  res.send(result);
  res.status(200).end();
  console.log(result);
})

// databaseStuff.js

var sql = require("mssql");

// config for your database
var config = {
    user: 'user',
    password: 'password',
    server: 'server',
    database: 'DB',
    options: {
      encrypt: true,
      trustServerCertificate: true
    }
};

// async/await style
const pool1 = new sql.ConnectionPool(config);
const pool1Connect = pool1.connect();

pool1.on('error', err => {
  console.log(err);
})

var sqlQuery = "select * from Games";

async function getGames() {
  // ensure pool has been created
  await pool1Connect;

  try
  {
    const request = pool1.request();
    const result = await request.query(sqlQuery);

    return result.recordset;
  }
  catch (err)
  {
    console.error('SQL Error', err);
  }
}

async function execute()
{
  return await getGames();
}

module.exports = {
  execute
};

// output

App now running on port 8080
[ { ID: 1, Name: 'GameName' } ]