Introducing the Microsoft Driver for Node.JS for SQL Server

Introducing the Microsoft Driver for Node.JS for SQL Server

Rate This
  • Comments 16

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

 

  • Thanks for sharing

  • Does this compile on Linux?

  • Hi Kishor,

    This initial release is Windows-only for now.

    Thanks,

    Jonathan

  • Good work!

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

  • forward only Datareader?

  • 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.

  • 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

  • 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

  • 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

  • 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!

  • 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

  • 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...

  • 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

  • How do you set connection strings for multiple environments? Adding a string to 'var conn_str' seems to support only one connection... is there a db config file that I can store multiple connection strings in and use the most appropriate version (dev, test, prod) based on my environment?

Page 1 of 2 (16 items) 12