Node.js + SQL Azure = Node-SqlServer

The Microsoft Driver for Node.js for SQL Server is seems to be the most reliable way of getting Node to talk to SQL Server. However, it’s a preview, and it’s not finished.

To install, just do:

npm install node-sqlserver

You can then start querying in your application like this:

var sql = require("node-sqlserver");

sql.query("CONNECTION STRING", "SELECT * FROM Foo WHERE ID = ?", [1], function(error, results){
	if (error){
		console.log(error);
	}
	results.forEach(function(result){
		console.log(result);
	});
});

The `query` function takes 4 arguments;

  1. Connection string
  2. SQL Command
  3. An array of parameters, which will replace ? characters in the command text (optional)
  4. A callback, with an error object and an array of results

You get an object for each record, with the fields as properties of the object, which is nice.

However, there are a few things to watch out for:

Installation

The modules consists of a C++ library, which is compiled when you install with npm. However, this doesn’t seem to get put in the right place. The compiled DLL is called sqlserver.node, and is deposited in the \build\Release folder. The javascript library is expecting to find it in \lib. You just need to manually copy it.

Multiple commands

If you insert some data, and then want a copy of the object back (not an unreasonable request – you’ll probably want to know the ID of it) you’ll write a command something like this:

INSERT Foo (bar) VALUES ('baz'); SELECT * FROM Foo WHERE ID = @@IDENTITY

However, I just got back an empty array or an array with this in it:

{ Columnremove: undefined }

I found that setting NOCOUNT to ON overcame this:

SET NOCOUNT ON; INSERT Foo (bar) VALUES ('baz'); SELECT * FROM Foo WHERE ID = @@IDENTITY

I now get a nice object back:

{ ID: 1, bar: 'baz' }

Errors

I managed to get a dialog to display when attempting to delete a record that doesn’t exist:

According to GitHub this is fixed, but I still seem to be causing me problems.

Empty Records

I can’t reproduce this in isolation, but I seem to get extra objects in my results array which are empty. The object contains all the properties of a record, but every value is undefined:

{ ID: 1, bar: 'baz' }
{ ID: undefined, bar: undefined }

Conculusion

Node-SqlServer looks really promising. I like the design, and the simplicity, I don’t think it’s quite ready though.

Advertisements