Node.js and Microsoft SQL Server

Question

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)

Solution 1

We just released preview drivers for Node.JS for SQL Server connectivity. You can find them here: http://blogs.msdn.com/b/sqlphp/archive/2012/06/08/introducing-the-microsoft-driver-for-node-js-for-sql-server.aspx

Solution 2

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

Solution 3

A couple of new node.js SQL server clients have just released recently. I wrote one called node-tds and there is another called tedious

Solution 4

There is another module you can use - node-mssql. It uses other TDS modules as drivers and offer easy to use unified interface. It also add extra features and bug fixes.

Extra features:

• Unified interface for multiple MSSQL drivers
• Connection pooling with Transactions and Prepared statements
• Parametrized Stored Procedures for all drivers
• Serialization of Geography and Geometry CLR types
• Smart JS data type to SQL data type mapper
• Support both Promises and standard callbacks

Solution 5

(duplicating my answer from another question).

I would recommend node-mssql, which is a nice wrapper for other connectors, the default being my previous choice (Tedious) bringing a bit nicer of an interface. This is a JavaScript implimentation, with no compilation requirements, meaning you can work in windows and non-windows environments alike.

Another option, if you don't mind bringing in .Net or Mono with a binary bridge would be to use edge.js. Which can be very nice if you want to leverage .Net libraries in node.js

node-tds is abandoned, node-odbc doesn't work with windows, and the MS node-sqlserver driver doesn't seem to work on non-windows (and has some goofy requirements).

View more solutions

Comments

[Khuram Malik]

Awesome. I didnt see that before. Thank you so much.

[Khuram Malik]

Im not particularly comfortable with linux so i try to do as little sys admin as possible. Doing a JDBC setup sounds messy, and if there is a node driver that is available as a module that will make life so much easier. You are right that not having stored procedures support is not great.

[Factor Mystic]

The project description now reads *EXPERIMENTAL and currently ABANDONED*

[booyaa]

This alternative github project might be of use until MS add official support. github.com/vivina/node-mssql

[Khuram Malik]

Thanks for the response. Your client doesnt rely on .net in any way, is that correct? Is this in any way related to oren mazor's tds project?

[Chad Retz]

My client is 100% native JS (actually CoffeeScript) as is Tedious. It is in no way related to Oren Mazor's project and were I better at coming up with library names, would have thought of something better and less ambiguous.

[ElHaix]

@Chad Retz - This looks like a this may be solution for a project I am working on. Currently there is a .Net entry point through JavaScript that makes a database call and returns JavaScript. This will eliminate that slower, middle step. However, what about connection pooling, and will you be writing support for SQL Server 2008 TVP's?

[Alba Mendez]

But these drivers are for Java only. How would you use JDBC in NodeJS?

[Eric Twilegar]

This is Windows only for the moment. I suppose that most people using SQL Server or Azure are windows folks anyways, but there are quite a few that just have their existing system deep into the SQL Server world. Below mentions the user of cross platform ODBC for those that may want to keep a Linux option in their future. And MS...tisk tisk...supporting node cross platform and then this junk.

[Tracker1]

Just noting that the MS drivers are rather immature at this point, and that the Tedious driver seems to work better (cross platform).

[Beau]

Haven't tested it but there is node-jdbc.

[Christian Westman]

I would not advise anyone to use these drivers in their current state

[Conor]

Thanks for your helpful answer. Looks like the authors strove for accurate nomenclature by naming their package "Tedious". At the time of this post, MSNodeSQL on github hasn't been touched in about 8 months, whereas this solution was last updated 20 days ago. This appears to be the only regularly updated Node.js MS SQL driver at this time.