Partager via

Introducing the Microsoft Driver for Node.JS for SQL Server

  • Article

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

Comments

  • Anonymous
    June 07, 2012
    Thanks for sharing

  • Anonymous
    June 07, 2012
    Does this compile on Linux?

  • Anonymous
    June 07, 2012
    Hi Kishor, This initial release is Windows-only for now. Thanks, Jonathan

  • Anonymous
    June 07, 2012
    Good work!

  • Anonymous
    June 07, 2012
    Reminds me of accessing SQL Server with ADO in JScript on IIS (Classic ASP). It's come full circle!

  • Anonymous
    June 08, 2012
    forward only Datareader?

  • Anonymous
    June 08, 2012
    Guys, Pre-requisites => Visual C++ 2010 - the Express edition is freely available from Microsoft SQL Server Native Client 11.0 - available as Microsoft SQL Server 2012 Native Client found in the SQL Server 2012 Feature Pack Why should i not use the official node js sql driver in my Mac ? Show some courtesy.

  • Anonymous
    June 09, 2012
    Hi Prem Kumar, Thanks for your feedback - a Mac driver is on our roadmap. This very early preview is primarily to gather feedback for our API. Thanks, Jonathan

  • Anonymous
    June 09, 2012
    Have you an idea about the MSDN/Technet forum which will related to this driver ( PHP or SQL Server Data Access or another one ) ? Have you any idea about the official release date for the driver ? You had a good idea to recall that the SQL Server Native Access Client is compulsory to be installed. Thanks for these valuable informations

  • Anonymous
    June 09, 2012
    Hi Papy, For now, given the feedback we are looking for is more about the design of the driver, we'd like people to use Github to report any issues they find. In the future, we'll define exactly where the support is, but for now given the very early preview nature, it's just the development team supporting it. As for a release date, we'll have to say 'when it's done' for now. Unlike the PHP driver, where there were many established Database driver which had thereby set a pseudo-standard for Database APIs, we're right at the beginning of the curve of Node.JS's install numbers. Given this, we feel that it's important that we involve the comnmunity in helping us design the best API we can, and hence why we have such an early preview out. Thanks! Jonathan

  • Anonymous
    June 27, 2012
    Hello. This is working great on 0.6, but is not compatible with Node 0.8. By any chance, will it be updated any time soon to work on it? We've got 0.8 on server and rollback is not quite an option.. Thanks a lot!

  • Anonymous
    June 27, 2012
    Hi Dragos! Very glad to hear it's all working great. Would you be able to file an issue on Github for your feature request? github.com/.../issues Thanks! Jonathan

  • Anonymous
    August 13, 2012
    Hi Jonathan, You said that this release is windows-only and a mac driver is in the road map. what are your plans? support essentially windows azure or creating a really portable package (at least linux, mac, windows)? or you still have not decided? I'd like to use your package but not being locked to azure or windows hosting...

  • Anonymous
    August 13, 2012
    Hi Chami_B, Please feel free to file an issue at Github and request a Mac and/or Linux version. We do have it on the roadmap, but prioritisation of this task depends on customer demand. If you file the issue, we can get feedback from all of our customers about the appropriate timing of this version. :) github.com/.../issues Thanks! Jonathan

  • Anonymous
    August 14, 2012
    The comment has been removed

  • Anonymous
    August 14, 2012
    Hi Daniel, Could you please post your questions on the Github issues page so that we can answer you better therE? github.com/.../issues Thanks! Jonathan