Independent Software Developer

Total.js – Connection with SQL Server on Portal Azure

One of the best node.js modules for connection to the SQL Server database on Azure Portal is mssql (https://www.npmjs.com/package/mssql). The documentation states that to connection with the database, we use the following construction connection string

“mssql://username:password@localhost/database”.

Problems begin in when we need to use the specific driver with which cooperates our database or we want to use the connection string, eg.

"Driver={SQL Server Native Client 11.0};Server={tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword"

generated by applications Widnows e.g. SQL Server Management Studio, Visual Studio or other programs. Then the problems begin – during try to establish connection we receive connection error with our database on portal azure. How to solve this problem? Below explains step by step how to solve this issue.
First we have to install standard modules listed in the total.js documentation:

$ npm install totaljs
$ npm install mssql
$ npm install sqlagent

In addition to the standard modules which have been listed above also we have to install the important module msnodesqlv8

$ npm install msnodesqlv8

It remains properly convey structures connection string to our sql agent. Please note of two very important things: give the name of the driver and the connection string is placed in the object property defined as connectionString. A whole construction we pass through the defined object

sample of mssql.js in /definitions:

var config = {
 driver:'msnodesqlv8',
 connectionString: "Driver={SQL Server Native Client 11.0};Server=  {tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword"
};

require('sqlagent/sqlserver').init(config,true);

Connection string, we can also put in the config file project

var config = {
 driver:'msnodesqlv8',
 connectionString: F.config.SQLAZURECONNSTR_AzureSQL 
};

require('sqlagent/sqlserver').init(config,true);

Sample of config in /:

// Mail settings
mail.smtp : smtp.maxprog.net.pl
mail.smtp.options : {"secure":true,"port":465,"user":"maxproga","password":"password15,"timeout":10000}
mail.address.from : info@maxprog.net.pl
mail.address.reply : info@maxprog.net.pl

SQLAZURECONNSTR_AzureSQL : Driver={SQL Server Native Client 11.0};Server={tcp:db1.database.windows.net,1433};Database=DatabaseDev;Uid=admin@db1.database.windows.net;Pwd=myPassword

Sample of controller.js in /controllers :

exports.install = function() {

F.route('/db', db);

};
function db() {
const self = this;

users(function(item){
self.json(item);

});

}
function users(callback) {

const sql = DATABASE();

sql.select('users', 'UserTable').make(function(filter) {

filter.where('UserID', '>',1000);
filter.like('Name', 'K%');
filter.fields('Name', 'Email');
});

sql.exec(function(err, response) {

// No users === end
if (!response.users.length)
return callback();
callback({success: true, data: response.users});

});
}