express-mssqlconnection

Connect/Express middleware provides a consistent API for MS SQL Server connections during request/response life cycle. it is bassed on express-myconnection by pwalczyszyn.

It uses node-mssql thunk wrappers for "co"

Strategies

• single - creates single database connection for an application instance. Connection is never closed. In case of disconnection it will try to reconnect again.
• pool - creates pool of connections on an app instance level, and serves a single connection from pool per request. The connections is auto released to the pool at the response end.
• request - creates new connection per each request, and automatically closes it at the response end.

Usage

Configuration is straightforward and you use it as any other middleware. First param it accepts is a db options hash passed to co-mssql module when connection or pool are created. The second is a string defining strategy type.

// app.js
...
var connection  = require('express-mssqlconnection');
...
app.use(
    connection({
        user: 'dbuser',
        password: 'password',
        server: 'localhost',
		database: 'mydb',
		pool: {
			max: 10,
			min: 1,
			idleTimeoutMillis: 30000
		},
		options: {
			useUTC : false,
			appName  : 'myAppName'
		}
  },'pool')    
);

...

express-mssqlconnection extends request object with getConection(callback) function, this way connection instance can be accessed anywhere in routers during request/response life cycle:

// myroute.js
...
module.exports = function(req, res, next) {
    ...
    req.getConnection(function(err, connection) {
      if (err) return next(err);

      co( function * () {
        try {
          var response = {};
          var request = new sql.Request(connection);
          var recordset = yield request.query('SELECT count(1) as numofrecords FROM dbo.mytable ');
          response.count = recordset[0].numofrecords;
          res.status(200).json(response); 
        }
        catch(err) {
          console.log('error submiting command to db', err);
          var response = { "error": err };
          res.status(500).json(response);  
        }
      });

    });
    ...
}
...