Dealing with Node.js and Microsoft SQL Server: Part-1

In a series of articles will look at how to use Node.js with some of the most popular products and platforms like SQL Server, MongoDB, Microsoft Azure, how to create applications using jQuery, jQuery UI and Ignite UI with Node.js

The first post is dedicated to Node and Microsoft SQL Server.

Initially let’s explain why many companies and professionals prefer to use Node.js in their solutions.

Why Node.js?

Node.js, for those that haven’t heard of it, is essentially server-side JavaScript.

You can take a look at the some of the advantages below:

• Practically no installation required
• Very lightweight (one 7MB static exe for MS Windows platform)
• Node.js encourages good software practices out of the box like async programming and inversion of control
• Node.js uses JavaScript syntax.
• There are more than 100 000 000 WEB developers over the world, who use JavaScript!
• The realistic reason:
  Cross platform applications with JS will be faster and easier
• The good parts of JavaScript are really awesome
• One language for the browser and the server
• Async by default
• Over 1 million concurrent requests

But it’s not just another trendy programming language – companies like LinkedIn and Walmart are already using it, and many other bug IT companies  have already announced that they use Node.js or they will add Node support for their platforms. In July 2011 Microsoft ported Node.js for MS Windows and later – in November 2011 they announced Microsoft Azure ( former Windows Azure) support for Node.js

Node.js is used also for Microsoft Azure Mobile Services

Node is used as a default platform for the back-end. It works with Azure SQL Database and it is offered out of the box for developers.
They just need to know JSON to SQL Type Mappings

JSON to SQL Type Mappings – Azure Mobile Services with Node.js

There are many modules that implement support for Microsoft SQL Server. It is not easy to choose the most appropriate module for a specific case.

Which Node.js driver to choose ? 

The objective of this article is to summarize the more popular node.js modules for MS SQL Server. We will compare the modules and describe their advantages and disadvantages

Node.js Modules for Microsoft SQL Server

• node-sqlserver & msnodesql

Both drivers use the same code from GitHub repository - https://github.com/Azure/node-sqlserver , but using different npm packages.

This is a Microsoft’s Node.js driver for SQL Server and Azure SQL Database: It is a Node.js module, based on SQL Server Native Client 11.0 - available as Microsoft SQL Server 2012 Native Client and it can be  found in the
SQL Server 2012 Feature Pack .

There are issues with node-sqlserver / msnodesql and WIndows 7.x / Windows 8.x.
This modules require prerequisites prior to install the driver:

1. Node.js - use the latest version if possible, but it has been tested on node 0.6.10 and later
2. node-gyp - latest version installed globally (npm install -g node-gyp)
3. python 2.7.x - for node-gyp (make sure it is in the path)
4. Visual C++ 2010 - the Express edition is freely available from Microsoft
5. SQL Server Native Client 11.0 - available as Microsoft SQL Server 2012 Native Client found in the SQL Server 2012 Feature Pack

It is an additional overhead to install these modules on the actual Windows platforms – you need to have some old versions of different libraries and frameworks. That is the reason to not recommend to use “ the official “ driver.

• node-sqlserver-unofficial

This is an unofficial binary distribution of that driver (node-sqlserver /msnodesql ) using the same GitHub repository - https://github.com/Azure/node-sqlserver .
if you are planning to use this driver – there is no need to compile from source (which requires Visual Studio and totally confuses some people). It runs on both x86 and x64 and with node versions 0.8 and 0.10. You have no need to use workarounds to run node-sqlserver-unofficial with Azure Web Sites.

• tedious

It is a  TDS driver, for connecting to Microsoft SQL Server databases. Tedious is an implementation of the TDS protocol, which is used to interact with instances of Microsoft's SQL Server. It is intended to be a fairly slim implementation of the protocol, with not too much additional functionality. Tedious is pure JavaScript, as are its dependencies. (the source is CoffeeScript.) So it should run without change on any platform where Node is supported. The tedious driver is compatible with all SQL Server versions from 2000 to 2014. Later versions of TDS protocol may be supported in the future, but it is unlikely that earlier versions will be supported. You can get tedious as a npm package or from GitHub - ttps://github.com/pekim/tedious 

• node-tds

This is a module that allows you to access Microsoft SQL Server 2005 and later. It is a pure JS implementation of the TDS protocol hosted on GitHub. Node-tds is similar to tedious . It could be installed as tds npm package or is source code from GitHub https://github.com/cretz/node-tds  .

•  mssqlhelper

  It is a Microsoft SQL Server Helper. Mssqlhelper is mostly written in JavaScript module only from one contributor.  21% of all source code lines are comments – in comparison,  mssqlhelper has only 6% of code commented. You can install mssqlhelper npm package here or find the source code in GitHub: https://github.com/play175/mssqlhelper    

• mssqlx

It is a  microsoft sql server nodejs command-line interface.  Mssqlx should work great on all platforms, especially mac and unix, without any other drivers, just nodejs. Currently, this only works on the command-line, the next version will work via stream api . This projects goal is to create an awesome cli experience for querying mssql, it hopes the follow the footsteps of the futon-cli .  You can use this mssqlx npm package or find the source code in GitHub: http://jackhq.tumblr.com/post/27992293043/mssqlx 

• Edge.js and edge-sql

Edge.js is a Node.js module that allows .NET code and assemblies to run in the same process with Node.js. This potentially enables a Node.js developer to leverage technologies that have traditionally been very hard or impossible to use in the past. You can create .NET libraries with the whole logic how to connect to MS SQL Server, run query, handle the results and you can use these libraries in Node.js applications using Edge-js.

Creating a .NET class library

   1: public class Sample1

   2:     {

   3:         public async Task<object> Invoke(object input)

   4:         {

   5:             ....

   6:         }

   7:  

   8:         public async Task<List<SampleUser>> QueryUsers(int pageNumber, int pageSize)

   9:         {

  10:             ....

  11:         }

  12:     }

Using a .NET library in Node.js + Edje.js application

   1: // Set up the assembly to call from Node.js

   2: var querySample = edge.func({

   3:     assemblyFile: 'EdgeSampleLibrary.dll',

   4:     typeName: 'EdgeSampleLibrary.Sample1',

   5:     methodName: 'Invoke'

   6: });

Edge-sql is a SQL compiler for edge.js. It allows accessing SQL databases from Node.js using Edge.js and ADO.NET. Via edge-sql you can query SQL Server direectly using Edge.js

   1: var params = {

   2:     connectionString: "Data Source=IGBGSOFEV06\\SQLEXPRESS;Initial Catalog=NodeJS;Integrated Security=True“ ,

   3:     source: "SELECT TOP 5 * FROM SampleUsers ORDER BY CreateDate DESC"

   4: };

   5:  

   6: var getTopUsers = edge.func( 'sql‘ , params);

Pros and Cons

In the next part of this article you can learn how to use different Node.js modules for MS SQL Server. We will take a look at the best practices and cover different use cases.

If you want more information about how to use Microsoft SQL Server & Node.js feel free to contact me at mmateev@infragistics.com

You can learn more about Azure Bootcamp Bulgaria if you follow us on Twitter @mihailmateev  and @Infragistics and stay in touch on Facebook, Google+, LinkedIn and Infragistics Friends User Group !