Connect to SQL Server database from Node js

Question

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

User · Accepted Answer

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

User · Answer

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  lt  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

User · Answer

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    x000D   x000D  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        -- gt  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    x000D   x000D   x000D    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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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  lt  results rows length  i                       var jsonObject   new Object                    for  var j   0  j  lt  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

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

Examples related to sql-server

Examples related to node.js