[sql-server] Connect to SQL Server database from Node.js

The question duplicates some older questions, but the things may have changed since then.

Is there some official support for connecting to SQL Server from Node.js (e.g. official library from MS)? Or at least some well-maintained third-party library appropriate for a production-grade application?

We usually use ASP.NET MVC/SQL Server combination, but currently I have a task for which express/Node.js seems to be more appropriate (and I'd like to play with something new), so the question is whether we can rely on a Node.js and SQL Server interaction.

UPD: It seems that Microsoft has, at last, released the official driver: https://github.com/WindowsAzure/node-sqlserver

This is mainly for future readers. As the question (at least the title) focuses on "connecting to sql server database from node js", I would like to chip in about "mssql" node module.

At this moment, we have a stable version of Microsoft SQL Server driver for NodeJs ("msnodesql") available here: https://www.npmjs.com/package/msnodesql. While it does a great job of native integration to Microsoft SQL Server database (than any other node module), there are couple of things to note about.

"msnodesql" require a few pre-requisites (like python, VC++, SQL native client etc.) to be installed on the host machine. That makes your "node" app "Windows" dependent. If you are fine with "Windows" based deployment, working with "msnodesql" is the best.

On the other hand, there is another module called "mssql" (available here https://www.npmjs.com/package/mssql) which can work with "tedious" or "msnodesql" based on configuration. While this module may not be as comprehensive as "msnodesql", it pretty much solves most of the needs.

If you would like to start with "mssql", I came across a simple and straight forward video, which explains about connecting to Microsoft SQL Server database using NodeJs here: https://www.youtube.com/watch?v=MLcXfRH1YzE

Source code for the above video is available here: http://techcbt.com/Post/341/Node-js-basic-programming-tutorials-videos/how-to-connect-to-microsoft-sql-server-using-node-js

Just in case, if the above links are not working, I am including the source code here:

var sql = require("mssql");_x000D_
_x000D_
var dbConfig = {_x000D_
    server: "localhost\\SQL2K14",_x000D_
    database: "SampleDb",_x000D_
    user: "sa",_x000D_
    password: "sql2014",_x000D_
    port: 1433_x000D_
};_x000D_
_x000D_
function getEmp() {_x000D_
    var conn = new sql.Connection(dbConfig);_x000D_
    _x000D_
    conn.connect().then(function () {_x000D_
        var req = new sql.Request(conn);_x000D_
        req.query("SELECT * FROM emp").then(function (recordset) {_x000D_
            console.log(recordset);_x000D_
            conn.close();_x000D_
        })_x000D_
        .catch(function (err) {_x000D_
            console.log(err);_x000D_
            conn.close();_x000D_
        });        _x000D_
    })_x000D_
    .catch(function (err) {_x000D_
        console.log(err);_x000D_
    });_x000D_
_x000D_
    //--> another way_x000D_
    //var req = new sql.Request(conn);_x000D_
    //conn.connect(function (err) {_x000D_
    //    if (err) {_x000D_
    //        console.log(err);_x000D_
    //        return;_x000D_
    //    }_x000D_
    //    req.query("SELECT * FROM emp", function (err, recordset) {_x000D_
    //        if (err) {_x000D_
    //            console.log(err);_x000D_
    //        }_x000D_
    //        else { _x000D_
    //            console.log(recordset);_x000D_
    //        }_x000D_
    //        conn.close();_x000D_
    //    });_x000D_
    //});_x000D_
_x000D_
}_x000D_
_x000D_
getEmp();

The above code is pretty self explanatory. We define the db connection parameters (in "dbConfig" JS object) and then use "Connection" object to connect to SQL Server. In order to execute a "SELECT" statement, in this case, it uses "Request" object which internally works with "Connection" object. The code explains both flavors of using "promise" and "callback" based executions.

The above source code explains only about connecting to sql server database and executing a SELECT query. You can easily take it to the next level by following documentation of "mssql" node available at: https://www.npmjs.com/package/mssql

UPDATE: There is a new video which does CRUD operations using pure Node.js REST standard (with Microsoft SQL Server) here: https://www.youtube.com/watch?v=xT2AvjQ7q9E. It is a fantastic video which explains everything from scratch (it has got heck a lot of code and it will not be that pleasing to explain/copy the entire code here)

We just released preview driver for Node.JS for SQL Server connectivity. You can find it here: Introducing the Microsoft Driver for Node.JS for SQL Server.

The driver supports callbacks (here, we're connecting to a local SQL Server instance):

// 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]);
        }
    });
});

Alternatively, you can use events (here, we're connecting to SQL Azure a.k.a Windows Azure SQL Database):

// Query with streaming
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 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); });

If you run into any problems, please file an issue on Github: https://github.com/windowsazure/node-sqlserver/issues

msnodesql is working out great for me. Here is a sample:

var mssql = require('msnodesql'), 
    express = require('express'),
    app = express(),
    nconf = require('nconf')

nconf.env()
     .file({ file: 'config.json' });

var conn = nconf.get("SQL_CONN");   
var conn_str = "Driver={SQL Server Native Client 11.0};Server=server.name.here;Database=Product;Trusted_Connection={Yes}";

app.get('/api/brands', function(req, res){
    var data = [];
    var jsonObject = {};    

    mssql.open(conn_str, function (err, conn) {
        if (err) {
            console.log("Error opening the connection!");
            return;
        }
        conn.queryRaw("dbo.storedproc", function (err, results) {
        if(err) {
                   console.log(err);
                   res.send(500, "Cannot retrieve records.");
                }
       else {
             //res.json(results);

             for (var i = 0; i < results.rows.length; i++) {
                 var jsonObject = new Object()
                 for (var j = 0; j < results.meta.length; j++) { 

                    paramName = results.meta[j].name;
                    paramValue = results.rows[i][j]; 
                    jsonObject[paramName] = paramValue;

                    }
                    data.push(jsonObject);  //This is a js object we are jsonizing not real json until res.send             
            } 

                res.send(data);

            }       
        });
    });
});

//start the program
var express = require('express');
var app = express();

app.get('/', function (req, res) {

    var sql = require("mssql");

    // config for your database
    var config = {
        user: 'datapullman',
        password: 'system',
        server: 'localhost', 
        database: 'chat6' 
    };

    // connect to your database
    sql.connect(config, function (err) {

        if (err) console.log(err);

        // create Request object
        var request = new sql.Request();

        // query to the database and get the records

        request.query("select * From emp", function (err, recordset) {            
            if  (err) console.log(err)

            // send records as a response
            res.send(recordset);

        });
    });
});

var server = app.listen(5000, function () {
    console.log('Server is running..');
});

//create a table as emp in a database (i have created as chat6)

// programs ends here

//save it as app.js and run as node app.js //open in you browser as localhost:5000

There is a module on npm called mssqlhelper

You can install it to your project by npm i mssqlhelper

Example of connecting and performing a query:

var db = require('./index');

db.config({
    host: '192.168.1.100'
    ,port: 1433
    ,userName: 'sa'
    ,password: '123'
    ,database:'testdb'
});

db.query(
    'select @Param1 Param1,@Param2 Param2'
    ,{
         Param1: { type : 'NVarChar', size: 7,value : 'myvalue' }
         ,Param2: { type : 'Int',value : 321 }
    }
    ,function(res){
        if(res.err)throw new Error('database error:'+res.err.msg);
        var rows = res.tables[0].rows;
        for (var i = 0; i < rows.length; i++) {
            console.log(rows[i].getValue(0),rows[i].getValue('Param2'));
        }
    }
);

You can read more about it here: https://github.com/play175/mssqlhelper

:o)