Introducing the Microsoft Driver for Node.JS for SQL Server

Dear Node.JS Community,

We are excited to announce a preview release of the Microsoft Driver for Node.JS for SQL Server. This new driver enables access to SQL Server and Windows Azure SQL Database from any Node.JS applications. Over the last few months our team has been working diligently toward developing this driver and preparing for a preview release so we can gather your feedback early in our development cycle. Today, we are announcing that our Microsoft Driver for Node.JS for SQL Server is ready for public preview.

Open First

The Microsoft Driver for Node.JS for SQL Server is hosted on Github, accentuating our continued involvement with the Open Source community. We will integrate features into the repository as we complete them, and you’ll be able to see all check-ins, issues and future roadmap discussions as we work to further improve this driver. Of course, we are also accepting contributions from the community, in accordance with our contributor guidelines.

Given this is a preview release, we look forward to and encourage all feedback from the community. Please feel free to look at existing issues, file new ones, or contact me on Twitter with feedback.

Simplicity by Design

In designing the API for this new driver, we aimed to keep a simple surface which is intuitive to Node.JS developers. Here’s an example of connecting to SQL Server from Node.JS:

// Query with explicit connection
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Person", function (err, results) {
        if (err) {
            console.log("Error running query!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log("FirstName: " + results.rows[i][0] + " LastName: " + results.rows[i][1]);
        }
    });
});

 

There are two modes for retrieving query results. The first mode returns all rows at once as a parameter to a callback:

// Simple Query with parameters
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

var match = "%crombie%";
sql.query(conn_str, "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE ?", [match], function (err, results) { 
    for (var i = 0; i < results.length; i++) {
        console.log("FirstName: " + results[i].FirstName + " LastName: " + results[i].LastName);
    }
});

Additionally, a statement object returned by a query can subscribe to events in order to receive individual rows and columns:

// Query with streaming
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

var stmt = sql.query(conn_str, "SELECT FirstName, LastName FROM Person.Person ORDER BY LastName OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY");
stmt.on('meta', function (meta) { console.log("We've received the metadata"); });
stmt.on('row', function (idx) { console.log("We've started receiving a row"); });
stmt.on('column', function (idx, data, more) { console.log(idx + ":" + data);});
stmt.on('done', function () { console.log("All done!"); });
stmt.on('error', function (err) { console.log("We had an error :-( " + err); });

Use in Windows Azure Web Sites

As you’ve probably heard, the refreshed Windows Azure portal was also released as a preview today. You can use the Node.JS driver in Windows Azure Web Sites by following this simple tutorial, and prior to the publish phase, copying the node-sqlserver driver into your node_modules directory. In the end, the sample application in the tutorial will look like so:

./server.js
./node_modules/node-sqlserver/package.json
./node_modules/node-sqlserver/lib/sql.js
./node_modules/node-sqlserver/lib/sql.node

Additionally, here’s a small example which builds on the tutorial application to connect to Windows Azure SQL Database:

var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server={tcp:servername.database.windows.net,1433};UID={username};PWD={Password1};Encrypt={Yes};Database={databasename}";

var http = require('http')
var port = process.env.port;
http.createServer(function (req, res) {
    sql.query(conn_str, "SELECT * FROM TestTable", function (err, results) {
        if (err) {
            res.writeHead(500, { 'Content-Type': 'text/plain' });
            res.write("Got error :-( " + err);
            res.end("");
            return;
        }
        res.writeHead(200, { 'Content-Type': 'text/plain' });
        for (var i = 0; i < results.length; i++) {
            res.write("ID: " + results[i].ID + " Column1: " + results[i].Column1 + " Column2: " + results[i].Column2);
        }
        res.end("; Done.");
    });
}).listen(port);

Documentation, Feedback and Download

We are documenting this driver as we develop it on our Github wiki. We’ll also be continuously monitoring issues on Github for your feedback and suggestions. We are releasing this as a preview so we can incorporate feedback about our design before we have a fully-fledged release.

To download the driver source, please visit our Github project page and clone or download the source onto your machine. You can then build the driver yourself using our freely-available Visual C++ Express compiler. Please take a look at the README included in the source tree for more information.

If you do not want to compile the driver yourself, you can download it from our Download Center. Once you download the package and have extracted it, please run node-sqlserver-install.cmd to generate the required directories. You can then drop the node-sqlserver directory into your node-modules directory and access SQL Server and Windows Azure SQL Database from your Node.JS application.

Please note that the driver requires the SQL Server Native Access Client – you can get the latest version from the SQL Server 2012 Feature Pack download page.

We’re pleased to show you our work to date and look forward to hearing from you!

 

Thank you,

 

Jonathan Guerin
Program Manager
SQL Server & Windows Azure SQL Database
Microsoft Corporation