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});
});
}
I must voice my passion for your generosity giving support to those people who require help on your concept. Your real commitment to passing the solution all through came to be astonishingly powerful and have in most cases allowed others like me to achieve their ambitions. Your amazing useful guide implies a great deal a person like me and still more to my peers. Thanks a lot; from all of us..