RSS

Tag Archives: Connecting To SQL Server From NodeJs

Connecting to SQL Server from NodeJs

With NodeJs it is more common to use MySQL, MongoDB, PostgreSQL than Microsoft SQL server as the database server, at least what I have encountered. However if we happens to prefer using Microsoft SQL with NodeJs there are few of options we could use.  Advantage of using NodeJs server for SQL server communication is it can be hosted in any platform with minimal issues.

Using an ORM ~ Using Sequelize?

Sequelize is a great ORM utility to use with NodeJs which also has the advantage of having abstraction layer for separate database drivers to interact with Microsoft SQL server, PostgreSQL, MySQL, MariaDB, SQLite and MSSQL. This is by far the best option we had so far. Other major benefit is the friendly API that it supports with promise based API which many NodeJs, AngularJS developers are following these days. Sequelize uses appropriate NodeJs package internally for each database server.

For Microsoft SQL server tedious is the node package to be used with Sequelize. Following two steps should be followed to getting started with connecting SQL server from NodeJs.

npm install --save sequelize //sequelize package

npm install --save tedious // SQL server TDS driver

Connecting SQL Server using Tedious without ORM?

Tedious is the npm package that is being internally used by Sequelize to connect to SQL server. This actually uses Tabular data stream protocol(TDS) for connecting to SQL server natively. Another compelling reason to use Tedios is that it is being actively contributed by Microsoft from recent past.

If you do not prefer using ORM for connecting to SQL server from NodeJs, you can also use Tedious without using Sequelize by using following steps. Tedious also supports SQL Azure by supporting encryption.

var Connection = require('tedious').Connection;

  var config = {
    userName: 'test',
    password: 'test',
    server: '192.168.1.210',

    // If you're on Windows Azure, you will need this:
    options: {encrypt: true}
  };

  var connection = new Connection(config);

  connection.on('connect', function(err) {
    // If no error, then good to go...
      executeStatement();
    }
  );

Connecting to SQL server from Sequelize

Connecting to SQL server from node js is simple you can abstract this in your factory class in a way that consumer code of backed database will be unaware about the database server completely.

var sequelize = new Sequelize('database name', 'username', 'password',
	{ host: 'localhost', dialect: 'mssql',
	  port: 1433,
	  pool: { max: 5, min: 0, idle: 10000 },
	  dialectOptions: { instanceName: 'instancename'}
	});

Any database driver specific code as above can be passed to dialect options.

Advertisements
 
Leave a comment

Posted by on December 14, 2016 in ASP.NET, MS SQL Server, NodeJs

 

Tags: , , , ,