Node-mssql: Recommendation for Global Connection in Web Server

[cdeutsch]

Is it better to create one global connection for the life of a web server, for example:

sql.connect(config, function(err) {
  if (err) { console.log('Connect err: ' + err); return; }
  isConnected = true;
});

exports.getLocations = function(callback) {
  var request = new sql.Request();
  request.query('select * from Locations', function(err, recordset) {
    if (err) { callback(err, null); return; }
    callback(null, recordset);
    return;
  });
}

exports.getInstruments = function(callback) {
  var request = new sql.Request();
  request.query('select top 100 * from InstrumentsTLC', function(err, recordset) {
    if (err) { callback(err, null); return; }
    callback(null, recordset);
    return;
  });
}

Or should each request be wrapped in its own connection, for example:

exports.getLocations = function(callback) {
  sql.connect(config, function(err) {
    if (err) { callback(err, null); return; }

    var request = new sql.Request();
    request.query('select * from Locations', function(err, recordset) {
      if (err) { callback(err, null); return; }
      callback(null, recordset);
      return;
    });
  });
}

exports.getInstruments = function(callback) {
  sql.connect(config, function(err) {
    if (err) { callback(err, null); return; }

    var request = new sql.Request();
    request.query('select top 100 * from InstrumentsTLC', function(err, recordset) {
      if (err) { callback(err, null); return; }
      callback(null, recordset);
      return;
    });
  });
}

[patriksimek]

Absolutely the first way.

[cdeutsch]

Cool. Thanks for the fast response! You can close this if you like.

[christiaanwesterbeek]

As for the code in your first example, your probably have several route.js files and a primairy server.js. In that case, may I suggest the following.

It shows an Express 4 application that uses a single connection pool across several files where route logic is handled.

server.js

//require stuff
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('/path', set1.get);
//.post(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);
});

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;
};

UPDATE: This code has been tested.

[estvmachine]

Works for me too!, i suggest put this in the Main Page in the section of Global Configuration. Only one doubt, if the connection close or fails, i have to reset the app, how can handle that situation with this configuration?

[emazzu]

Hi, I need your help !!!

Somneone know ? by when I router /sample ? the browser doen't response ???

Regards
Eduardo

// call
app.get('/sample', set1.get);

set1.js

var sql = require('mssql');

module.exports = function(cn) {
var me = {
get: function(req, res, next) {
var request = new sql.Request(cn);
request.query('select top 100 Id, Nombre from pozos', function(err, recordset) {
if (err) {
console.error(err);
res.status(500).send(err.message);
return;
}
console.log(recordset);
res.status(200).json(recordset);
});
}
};

return me;
};

[christiaanwesterbeek]

@emazzu This may very well be unrelated to mssql. First try to get your browser to respond to /sample when you have almost nothing in your get function, like so:

module.exports = function(cp) {
  var me = {
    get: function(req, res, next) {
      res.status(200).json([{a: 123}]);
    }
  };

  return me;
};

PS: please wrap your code in your posts like so

if (isAwesome){
  return true
}

It's markdown. Read about it here and start using it at least to show your code.
https://guides.github.com/features/mastering-markdown/