Node.js mssql multiple concurrent connections to SQL servers interfering with each other

Question

I am using mssql in my Node.js express application to make connections to many different databases across many different SQL servers.

I have constructed the following example to demonstrate the general structure of my code:

app.get('/api/example'), async (request, response) => {

    // FYI I may be using await incorrect here since I'm new to it, just using it here for code simplicity
    let results1 = await GetDataFromSqlServerA()
    let results2 = await GetDataFromSqlServerB()

    response.status(200).send([results1, results2])
});

function GetDataFromSqlServerA() {
    return new Promise(function(resolve, reject)    {

        let sql = require("mssql")

        let sqlConnectionDetails = {
            user: 'test',
            password: 'foobar',
            server: 'SQLServerA', 
            database: 'DatabaseA'
        }

        sql.connect(sqlConnectionDetails, function (error)    {

            let sqlRequest = new sql.Request()
            let queryText = 'SELECT * FROM TableA'

            sqlRequest.query(queryText, function (error, results) {
                sql.close()
                resolve(results)
            })
        })
    })
}

function GetDataFromSqlServerB() {
        return new Promise(function(resolve, reject)    {

        let sql = require("mssql")

        let sqlConnectionDetails = {
            user: 'test',
            password: 'foobar',
            server: 'SQLServerB', 
            database: 'DatabaseB'
        }

        sql.connect(sqlConnectionDetails, function (error)    {

            let sqlRequest = new sql.Request()
            let queryText = 'SELECT * FROM TableB'

            sqlRequest.query(queryText, function (error, results) {
                sql.close()
                resolve(results)
            })
        })
    })
}

I have a request being made which looks for data from two separate SQL server locations asynchronously. The first SQL call to be made executes OK, but the second fails Invalid object 'Table2'. It cannot find the table because the second call picks up the connection details for the first call for some reason. It's pointing at the wrong SQL server and database!

I would have thought this is not possible because the functions are within their own scopes. The first SQL call should know nothing about the second and vice-versa - or at least I would have thought.

I have also tried defining one instance of sql globally, but the same issues occur.

I can have a single function make a SQL connection to server A, make a request to server A, disconnect from server A, make a SQL connection to server B, and finally make a request to server B. However, when asynchronicity comes into play and SQL is being triggered by concurrent requests, I have issues.

Hoping I am just being dumb since I am new to asynchronous code, please enlighten me! Thank you!

Answer 1

Thought I would update this in case anyone else runs into similar trouble. @AlwaysLearning provided me with a link to a section of the mssql npm documentation that explains connection pooling and doing so when access to multiple databases is necessary. I would recommend reading through it.

It turns out that the .connect() function of mssql is a global. If the global pool is already connected when the .connect() function is called, it will resolve to the already connected pool. This was causing me troubles because the following would occur in my application when two requests are send in quick succession:

1. Request 1 would connect to database A
2. Request 2 would try to connect to database B
3. Since database A is already connected in the global pool, Request 2 picks up the database A connection
4. Request 2's SQL query fails because it is not valid for database A

To get around this, you need to develop some connection pool management code. This way you can ensure there are separate connection pools for each database connection that is necessary. In addition, this is an easy way to make your queries faster because you are not reconnecting to the database every time you want to make a request.

The npm documentation provides a couple example helper files, but I found them to not be ideal for me and I couldn't compile all the code in my environment. I customised the code into the following:

mssql-connection-pooling.js:

const { ConnectionPool } = require('mssql')
const pools = {}
 
// create a new connection pool
function CreatePool(config) {
    let key = JSON.stringify(config)

    if (GetPool(key))
        throw new Error('Pool already exists')

    pools[key] = (new ConnectionPool(config)).connect()
    return pools[key]
}

// get a connection pool from all pools
function GetPool(name) {
  if (pools[name])
    return pools[name]
  else
    return null
}

// if pool already exists, return it, otherwise create it
function GetCreateIfNotExistPool(config)  {
    let key = JSON.stringify(config)

    let pool = GetPool(key)
    if(pool)
        return pool
    else
        return CreatePool(config)
}

// close a single pool
function ClosePool(config) {
    let key = JSON.stringify(config)

    if (pools[key]) {
        const pool = pools[key];
        delete pools[key];
        pool.close()
        return true
    }
    return false
}

// close all the pools
function CloseAllPools() {
    pools.forEach((pool) => {
        pool.close()
    })
    pools = {}
    return true
}
 
module.exports = {
  ClosePool,
  CloseAllPools,
  CreatePool,
  GetPool,
  GetCreateIfNotExistPool
}

I created the GetCreateIfNotExistPool() function, which you supply a database connection configuration object. The function checks if there is an open connection stored in the pools for that given connection configuration. If there is, it simply returns the connection pool. If not, it creates it then returns it.

Example of usage:

const sql = require("mssql");
let mssql = require('./mssql-pool-management.js')

let exampleDBConfigA = {
    user: 'test',
    password: 'password,
    server: 'SqlServerA', 
    database: 'DatabaseA'
};
let exampleDBConfigB = {
    user: 'test',
    password: 'password,
    server: 'SqlServerB', 
    database: 'DatabaseB'
};

...

// Request 1
try {
    let sqlPool = await mssql.GetCreateIfNotExistPool(exampleDBConfigA)
    let request = new sql.Request(sqlPool)

    // query code
}
catch(error) {
    //error handling
}

...

// Request 2
try {
    let sqlPool = await mssql.GetCreateIfNotExistPool(exampleDBConfigB)
    let request = new sql.Request(sqlPool)

    // query code
}
catch(error) {
    //error handling
}

In this example, requests 1 and 2 can be called concurrently just fine without their connections interfering!