NodeJS-Linux-SQL-DB.md

Using SQL Database with NodeJS on Ubuntu

Install the required modules

Open your terminal and install node and npm if you do not have it on your machine

sudo apt-get install node
sudo apt-get install npm

Once your machine is configured with node and npm, navigate to a directory where you plan on creating your nodejs project and enter the following commands

sudo npm init
sudp npm install tedious

npm init creates a node project. To keep the defaults during your project creation press till the project is created. Your should now see a package.json file in your project directory

Create a database and retrieve your connection string

See the getting started page to learn how to create a sample database and retrieve your connection string. It is important you follow the guide to create an AdventureWorks database template. The examples shown below will only work with the AdventureWorks schema.

Connect to your SQL Database

var Connection = require('tedious').Connection;
var config = {
	userName: 'yourusername',
	password: 'yourpassword',
	server: 'yourserver.database.windows.net',
	// If you're on Windows Azure, you will need this:
	options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
// If no error, then good to go...
	console.log("Connected");
});

Execute a query and retrieve the result set

var Connection = require('tedious').Connection;
var config = {
	userName: 'yourusername',
	password: 'yourpassword',
	server: 'yourserver.database.windows.net',
	// If you're on Windows Azure, you will need this:
	options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
	// If no error, then good to go...
	console.log("Connected");
	executeStatement();
});	
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
function executeStatement() {
	request = new Request("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function(err) {
  	if (err) {
   		console.log(err);} 
	});
	var result = "";
	request.on('row', function(columns) {
	    columns.forEach(function(column) {
	      if (column.value === null) {
	        console.log('NULL');
	      } else {
	        result+= column.value + " ";
	      }
	    });
	    console.log(result);
	    result ="";
	});

	request.on('done', function(rowCount, more) {
	console.log(rowCount + ' rows returned');
	});
	connection.execSql(request);
}

Inserting a row, passing parameters, and retrieving the generated primary key value

var Connection = require('tedious').Connection;
var config = {
	userName: 'yourusername',
	password: 'yourpassword',
	server: 'yourserver.database.windows.net',
	// If you're on Windows Azure, you will need this:
	options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
	// If no error, then good to go...
	console.log("Connected");
	executeStatement1();

});

var Request = require('tedious').Request
var TYPES = require('tedious').TYPES;

function executeStatement1() {
	request = new Request("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP);", function(err) {
	 if (err) {
	 	console.log(err);} 
	});
	request.addParameter('Name', TYPES.NVarChar,'SQL Server Express 2014');
	request.addParameter('Number', TYPES.NVarChar , 'SQLEXPRESS2014');
	request.addParameter('Cost', TYPES.Int, 11);
	request.addParameter('Price', TYPES.Int,11);
	request.on('row', function(columns) {
	    columns.forEach(function(column) {
	      if (column.value === null) {
	        console.log('NULL');
	      } else {
	        console.log("Product id of inserted item is " + column.value);
	      }
	    });
	});		
	connection.execSql(request);
}

Transactions

var Connection = require('tedious').Connection;
var config = {
	userName: 'yourusername',
	password: 'yourpassword',
	server: 'yourserver.database.windows.net',
	// If you're on Windows Azure, you will need this:
	options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
	// If no error, then good to go...
	console.log("Connected");
	executeStatement2();
});	
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
function executeStatement2() {
//TODO
}

Stored procedures

var Connection = require('tedious').Connection;
var config = {
	userName: 'yourusername',
	password: 'yourpassword',
	server: 'yourserver.database.windows.net',
	// If you're on Windows Azure, you will need this:
	options: {encrypt: true, database: 'AdventureWorks'}
};
var connection = new Connection(config);
connection.on('connect', function(err) {
	// If no error, then good to go...
	console.log("Connected");
	executeStatement3();
});	
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
function executeStatement3() {
//Todo
}