SQL Server driver for Node.js

Node.js is an intriguing technology that has garnered a lot of attention since it was created by
Ryan Dahl in 2009. Built around Google’s V8 JavaScript engine, it provides programmers with the
ability to write programs written in JavaScript using event driven, asynchronous I/O to minimize
overhead.

Support for Node.js on Windows platforms was initially non-existent unless you consider running it
in cygwin as an acceptable definition of support. Over the past several months, things have come a
long way on that front with a native installer, Windows Azure support and documentation, and even
a Microsoft SQL Server driver.

The Microsoft SQL Server driver was particullarly interesting, so I thought that I would write a
quick tutorial on how to quickly install it and get it runnning. If you do not have Node.js installed
yet, grab the installer from their official website.

nodejs.org/download

Once installed, create a folder for your project called nodesql. Inside the folder, create and empty
text file called server.js. To ensure everything is working, add the obligatory ‘Hello World’ web
server code.

var http = require('http');
http.createServer(function (request, response) {
  response.writeHead(200, {'Content-Type': 'text/plain'});
  response.end('Hello World\n');
}).listen(8080);
console.log('Server running on port 8080');

‍

To run the server, navigate to ~/nodesql in the command shell (cmd.exe) and type the following:

node server.js

You should see “Server running on port 8080” in the console window. Once verified, you can stop the
server with ctrl-c, but keep the console open for the next step.

To install the SQL Server driver, navigate to the nodesql folder (if you are not still there) and
issue the following command:

npm install node-sqlserver

‍

This will create a folder called node_modules, download the source for the module and compile it.
Once complete, copy sqlserver.node from the node_modules/node-sqlserver/build/Release folder to the
node_modules/node-sqlserver/lib folder. This is an important step that will ensure that the module
can be found when running the server.

For this test, I created a simple page that queries the database and lists the results in a simple
web page. Note that the query is run as soon as the server is started. This isn’t really practical,
but serves the purpose of this demonstration.

var http = require('http');
var sql = require('node-sqlserver');
var connection_string = "Driver={SQL Server Native Client 10.0};Server=YOUR_SERVER;Database=YOUR_DB;uid=YOUR_USER;pwd=YOUR_PASSWORD";
 
var data = "";
sql.open(connection_string, function (err, connection) {
    if (err) {
    data = "cannot open connection!";
    return;
    }
    connection.queryRaw("SELECT TOP 10 Name, EmailAddress FROM Customer", function (err, results) {
    if (err) {
        data = "query failed!";
        return;
    }
    for (var i = 0; i < results.rows.length; i++) {
        data += "Name: " + results.rows[i][0] + " Email: " + results.rows[i][1] + "<br />";
    }
    });
});
 
http.createServer(function (req, res) {
    req.on('end', function() {
        res.writeHead(200, { 
         'Content-Type': 'text/html'
        });
        res.end(data);
    });
}).listen(8080);
console.log('Server running on port 8080');

‍

You will of course want to update the connection string with your information and update the query.