msnodesql is working out great for me. Here is a sample:
var mssql = require('msnodesql'),
express = require('express'),
app = express(),
nconf = require('nconf')
nconf.env()
.file({ file: 'config.json' });
var conn = nconf.get("SQL_CONN");
var conn_str = "Driver={SQL Server Native Client 11.0};Server=server.name.here;Database=Product;Trusted_Connection={Yes}";
app.get('/api/brands', function(req, res){
var data = [];
var jsonObject = {};
mssql.open(conn_str, function (err, conn) {
if (err) {
console.log("Error opening the connection!");
return;
}
conn.queryRaw("dbo.storedproc", function (err, results) {
if(err) {
console.log(err);
res.send(500, "Cannot retrieve records.");
}
else {
//res.json(results);
for (var i = 0; i < results.rows.length; i++) {
var jsonObject = new Object()
for (var j = 0; j < results.meta.length; j++) {
paramName = results.meta[j].name;
paramValue = results.rows[i][j];
jsonObject[paramName] = paramValue;
}
data.push(jsonObject); //This is a js object we are jsonizing not real json until res.send
}
res.send(data);
}
});
});
});