Node-mssql: How to handle Multiple Connections dynamically?

[jtlindsey]

Hey,
I was looking at the link that describes how to handle multiple connections. In the example, there are 2 hard coded connections. I'm building an app that has many users and I'm trying to figure out how to allow them to not get errors if they query the database at the exact same time or before another query is finished.

The following is what I'm using in a nodejs app running on a Linux machine. It works as long as nobody makes a request before another request is finished. Otherwise with the following code I still get Error: Global connection already exists. Call sql.close() first.

const settings  = require('./settings');
const sql = require('mssql');

exports.execSql = async function(sqlquery) {
  try {
    let pool = await new sql.connect(settings.config);
    let result = await pool.request().query(sqlquery);
    return {success: result};
  } catch (err) { 
    return {err: err};
  } finally {
    sql.close(); //closing connection after request is finished.
  }
};

sql.on('error', err => {
  // ... error handler 
  console.log('sql errors', err);
});

The hardcoded example from the docs assumes there are only 2 connections. How do I modify the above to handle a dynamic number of connections?

[patriksimek]

Try this:

const settings  = require('./settings');
const sql = require('mssql');

exports.execSql = async function(sqlquery) {
  const pool = new sql.ConnectionPool(settings.config);
  pool.on('error', err => {
    // ... error handler 
    console.log('sql errors', err);
  });

  try {
    await pool.connect();
    let result = await pool.request().query(sqlquery);
    return {success: result};
  } catch (err) { 
    return {err: err};
  } finally {
    pool.close(); //closing connection after request is finished.
  }
};

[jtlindsey]

@patriksimek Running that code returns some odd errors in the try catch block.

an error TypeError: Cannot read property 'port' of undefined
    at ConnectionPool (/nodeSQL-RESTapi-Alpha/node_modules/mssql/lib/base.js:139:35)
    at ConnectionPool (/nodeSQL-RESTapi-Alpha/node_modules/mssql/lib/tedious.js:175:1)
    at Object.connect (/nodeSQL-RESTapi-Alpha/node_modules/mssql/lib/base.js:1592:22)
    at Object.exports.execSql (/nodeSQL-RESTapi-Alpha/main/db.js:50:22)
    at Object.exports.getAll (/nodeSQL-RESTapi-Alpha/controllers/productReturn.js:21:6)
    at Server.http.createServer (/nodeSQL-RESTapi-Alpha/server.js:14:23)
    at emitTwo (events.js:106:13)
    at Server.emit (events.js:194:7)
    at parserOnIncoming (_http_server.js:565:12)
    at HTTPParser.parserOnHeadersComplete (_http_common.js:99:23)

I commented it out and ran my original code to make sure I didn't have any config issues and making a single request works. Any ideas?

[patriksimek]

Sorry, I had a typo on a first line in try block. It should be await pool.connect();. I have updated my previous post.

[jtlindsey]

@patriksimek Success, that did the trick! Simultaneous request working now. Thanks so much!

[betweenbrain]

@patriksimek does this approach that you suggest create a new pool each time execSql is called? I would think it would as new sql.ConnectionPool is called within that function.

[patriksimek]

@betweenbrain yes, this code creates a new pool each time execSql is called. But I'm not suggesting to do this, I was only correcting the code posted by @jtlindsey. I have no idea how the code is used so I can't even tell whether it is a good approach or not.

[jtlindsey]

@betweenbrain, @patriksimek Here is how I'm using the code and with some modifications for making sure it's using parameters to pass sanitized values to your queries.

DB file

//db.js
const settings  = require('./settings');
const sql = require('mssql');

exports.execSql = async function(sqlquery, userInput={}) {
  const pool = new sql.ConnectionPool(settings.config);
  pool.on('error', err => {
    // ... error handler 
    console.log('sql pool error db.js', err);
  });

  try {
    await pool.connect();
    let result = await pool.request();
    for (let key in userInput) {
      if ( Array.isArray(userInput[key]) ) {  
        // input(field_name, dataType, value)
        result = await result.input(key, userInput[key][1], userInput[key][0]);
      } else { 
        // input(field_name, value)
        result = await result.input(key, userInput[key]);
      };
    };
    result = await result.query(sqlquery);

    return {success: result};
  } catch (err) { 
    // stringify err to easily grab just the message
    let e = JSON.stringify(err, ["message", "arguments", "type", "name"]);     
    return {error: JSON.parse(e).message};
  } finally {
    pool.close(); //closing connection after request is finished.
  }
};

Controller

//dogs_controller.js example
const mssql = require('mssql');
const db = require('./db');

exports.getAllDogs = (req, res) => {
  let sql = `SELECT * FROM dogTable ORDER BY age DESC `;
  return db.execSql(sql);
};

//example that uses input
exports.getDogByID = (req, res, ID) => {
  let sql = `SELECT * FROM dogTable WHERE dog_id=@dog_id`;
  let userInput = {'dog_id': ID};
  return db.execSql(sql, userInput); 
};

exports.updateDog = (req, res, data) => {

  let userInput = {
    'age': data.age,
    // example where specify what dataType this input should be
    'dog_value': [data.dog_value, mssql.Money],
    'dog_id': data.dog_id
  };

  let sql = `BEGIN TRY`;
  sql += ` BEGIN TRANSACTION`;
  sql += ` UPDATE dogTable SET `;
  sql += `age=@age, `;
  sql += `dog_value=@dog_value, `;
  sql += `WHERE dog_id=@dog_id`;

  sql += ` COMMIT TRAN`;
  sql += ` END TRY`;
  sql += ` BEGIN CATCH`;
  sql += ` IF @@TRANCOUNT > 0`;
  sql += ` ROLLBACK TRAN`;
  sql += ` DECLARE @ErrorMessage NVARCHAR(4000)`;
  sql += ` DECLARE @ErrorSeverity INT`;
  sql += ` DECLARE @ErrorState INT`;
  sql += ` SET @ErrorMessage = ERROR_MESSAGE()`;
  sql += ` SET @ErrorSeverity = ERROR_SEVERITY()`;
  sql += ` SET @ErrorState = ERROR_STATE()`;
  sql += ` RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)`;
  sql += ` END CATCH`;

  console.log('see sql query', sql, 'end ****');

  return db.execSql(sql, userInput);
};

[betweenbrain]

@patriksimek Thanks for the quick response!

To create one connection pool, and reuse it for each sqlquery, would the change to the above be as simple as:
````
const settings = require('./settings');
const sql = require('mssql');
const pool = new sql.ConnectionPool(settings.config);
pool.on('error', err => {
if (err) {
console.log('sql errors', err);
}
if (!err) {
pool.connect();
}
});

exports.execSql = async function (sqlquery) {
try {
let result = await pool.request().query(sqlquery);
return {success: result};
} catch (err) {
return {err: err};
} finally {
pool.close(); //closing connection after request is finished.
}
};
````

But, if pool.close(); "Close all active connections in the pool." couldn't that close other connections before they complete?

[patriksimek]

@betweenbrain Calling pool.close() in execSql is not a good idea - once you close a pool, you can no longer acquire a connection from that pool. That means that all subsequent requests will fail.

The good idea is to export a close method and close the pool when you're absolutely sure there will be no more requests to make (e.g. when application closes).

[betweenbrain]

@patriksimek Thanks, that makes perfect sense.

[fernandoguedes]

Great thread, thanks for the resolutions! :1st_place_medal: