Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 291
penartur Avatar asked Apr 25 '12 04:04

penartur


People also ask

CAN node js connect to SQL Server?

You can connect to a SQL Database using Node. js on Windows, Linux, or macOS.

Can JavaScript connect to SQL Server?

There is no common way to connect to SQL Server database from JavaScript client, every browser has it's own API and packages to connect to SQL Server.

CAN node js Access database?

Node. js supports all kinds of databases no matter if it is a relational database or NoSQL database. However, NoSQL databases like MongoDb are the best fit with Node. js.


2 Answers

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

like image 93
user203687 Avatar answered Oct 08 '22 13:10

user203687


I am not sure did you see this list of MS SQL Modules for Node JS

Share your experience after using one if possible .

Good Luck

like image 39
Futur Avatar answered Oct 08 '22 14:10

Futur