How can I use a single mssql connection pool across several routes in an Express 4 web application?

Question

I want to use node-mssql as a MSSQL database connector in a Node JS Express 4 web application. Route handler logic is handled in separate files.

How do I create a single/global connection pool and use it across several files where route logic is handled? I don't want to make a new connection pool in each route handler function/file.

Answer 1

It's been 3 years since I asked and answered the question. Since then a few things have changed. Here's the new solution based on ES6, mssql 4 and Express 4 that I would suggest today.

Two key elements are at play here.

1. Modules are cached after the first time they are loaded. This means that every call to require('./db') will return exactly the same object. The first require of db.js will run that file and create the promise and export it. The second require of db.js will return THAT same promise without running the file. And it's that promise that will resolve with the pool.
2. A promise can be thenified again. And if it resolved before, it will immediately resolve again with whatever it resolved with the first time, which is the pool.

In server.js

const express = require('express')
// require route handlers.
// they will all include the same connection pool
const set1Router = require('./routes/set1')
const set2Router = require('./routes/set2')

// generic express stuff
const app = express()

// ...
app.use('/set1', set1Router)
app.use('/set2', set2Router)

// No need to connect the pool
// Just start the web server

const server = app.listen(process.env.PORT || 3000, () => {
  const host = server.address().address
  const port = server.address().port

  console.log(`Example app listening at http://${host}:${port}`)
})

In db.js

const sql = require('mssql')
const config = {/*...*/}

const poolPromise = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL')
    return pool
  })
  .catch(err => console.log('Database Connection Failed! Bad Config: ', err))

module.exports = {
  sql, poolPromise
}

In routes/set1.js and routes/set2.js

const express = require('express')
const router = express.Router()
const { poolPromise } = require('./db')

router.get('/', async (req, res) => {
  try {
    const pool = await poolPromise
    const result = await pool.request()
        .input('input_parameter', sql.Int, req.query.input_parameter)
        .query('select * from mytable where id = @input_parameter')      

    res.json(result.recordset)
  } catch (err) {
    res.status(500)
    res.send(err.message)
  }
})

module.exports = router

To summarize

You'll always get the same promise due to module caching and that promise will, again and again, resolve with the pool it resolved with the first time. Thus each router file uses the same pool.

BTW: there are easier ways to go about the try catch in the express route that I won't cover in this answer. Read about it here: https://medium.com/@Abazhenov/using-async-await-in-express-with-node-8-b8af872c0016

The old solution

This is the solution I posted 3 years ago, because I believed I had an answer that was worth to share and I couldn't find a documented solution elsewhere. Also in a few issues (#118, #164, #165) at node-mssql this topic is discussed.

In server.js

var express = require('express');
var sql     = require('mssql');
var config  = {/*...*/};
//instantiate a connection pool
var cp      = new sql.Connection(config); //cp = connection pool
//require route handlers and use the same connection pool everywhere
var set1    = require('./routes/set1')(cp);
var set2    = require('./routes/set2')(cp);

//generic express stuff
var app = express();

//...
app.get('/path1', set1.get);
app.get('/path2', set2.get);

//connect the pool and start the web server when done
cp.connect().then(function() {
  console.log('Connection pool open for duty');

  var server = app.listen(3000, function () {

    var host = server.address().address;
    var port = server.address().port;

    console.log('Example app listening at http://%s:%s', host, port);

  });
}).catch(function(err) {
  console.error('Error creating connection pool', err);
});

In routes/set1.js

var sql     = require('mssql');

module.exports = function(cp) {
  var me = {
    get: function(req, res, next) {
      var request = new sql.Request(cp);
      request.query('select * from test', function(err, recordset) {
        if (err) {
          console.error(err);
          res.status(500).send(err.message);
          return;
        }
        res.status(200).json(recordset);
      });
    }
  };

  return me;
};

Answer 2

When you configure your app (like when you create the express server), make the DB connection. Make sure this is done BEFORE you require all your routes! (finagle the requires at the top of the file)

Just like the docs:

var sql = require('mssql'); var connection = new sql.Connection(..... //store the connection sql.globalConnection = connection;

Then in all your route files, you can do this:

var sql = require('mssql'); var sqlConn = sql.globalConnection; var request = new sql.Request(sqlConn); //...

That should do it!

All that said, go use knex to manage your MySQL query building. It has a built in connection pool, and you store the connected knex instance the same way. As well as a generous helping of awesome.