Node.js and Microsoft SQL Server

Khuram Malik picture Khuram Malik · Mar 1, 2011 · Viewed 67.2k times · Source

Is there any way I can get my Node.js app to communicate with Microsoft SQL? I haven't seen any MS SQL drivers out there in the wild?

I'm putting a very simple app together and need to be able to communicate with an existing MS SQL database (otherwise I would have gone with mongoDB or Redis)

Answer

Christiaan Westerbeek picture Christiaan Westerbeek · Mar 26, 2014

The original question is old and now using node-mssql as answered by @Patrik Šimek that wraps Tedious as answered by @Tracker1 is the best way to go.

The Windows/Azure node-sqlserver driver as mentioned in the accepted answer requires you to install a crazy list of prerequisites: Visual C++ 2010, SQL Server Native Client 11.0, python 2.7.x and probably also Windows 7 SDK for 64-bit on your server. You don't want to install all these GB's of software on your Windows Server if you ask me.

You really want to use Tedious. But also use node-mssql to wrap it and make coding a lot easier.

Update August 2014

  • Both modules are still actively maintained. Issues are responded on quite quickly and efficiently.
  • Both modules support SQL Server 2000 - 2014
  • Streaming supported since node-mssql 1.0.1

Update February 2015 - 2.x (stable, npm)

  • Updated to latest Tedious 1.10
  • Promises
  • Pipe request to object stream
  • Detailed SQL errors
  • Transaction abort handling
  • Integrated type checks
  • CLI
  • Minor fixes

This is plain Tedious:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

var config = {
  server: '192.168.1.212',
  userName: 'test',
  password: 'test'
};

var connection = new Connection(config);

connection.on('connect', function(err) {
    executeStatement();
  }
);

function executeStatement() {
  request = new Request("select 42, 'hello world'", function(err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + ' rows');
    }

    connection.close();
  });

  request.on('row', function(columns) {
    columns.forEach(function(column) {
      if (column.value === null) {
        console.log('NULL');
      } else {
        console.log(column.value);
      }
    });
  });

  request.on('done', function(rowCount, more) {
    console.log(rowCount + ' rows returned');
  });

  // In SQL Server 2000 you may need: connection.execSqlBatch(request);
  connection.execSql(request);
}

Here comes node-mssql which has Tedious as a dependency. Use this!

var sql     = require('mssql');

var config = {
  server: '192.168.1.212',
  user:     'test',
  password: 'test'
};

sql.connect(config, function(err) {
    var request = new sql.Request();
    request.query("select 42, 'hello world'", function(err, recordset) {
        console.log(recordset);
    });
});