Getting Started with Node.js on Microsoft SQL Server and Azure SQL Database

  • Article

This blog will show you how to create a sample Azure SQL Database connected to Node.js in five minutes. You will learn how to create a Microsoft Azure account, provision a logical server in the Azure management portal, create a database populated with sample data, set a firewall rule for the database, use Node.js to connect to SQL using Microsoft’s Node.js driver and create a working Node.js project

Step 1: Create a Microsoft Account

To create a Microsoft Azure account, click Free Trial and then return to Step 2 when complete.  If you already have a Microsoft Azure account, proceed to Step 2 now.

Step 2: Use the Azure Management Portal to create a logical server and a database

  1. Sign in to the Azure Management portal and then:
    1. Click New at the bottom of the page
    2. From the list of available options, click Data + Storage
    3. Then click SQL Database

  1. In the SQL Database blade:
    1. Choose a Name for your Database, in this example“AdventureWorks”. 
    2. To create the logical server, click Server, then Create a new server.
      • Enter a Server Name. Make sure this server name is unique otherwise creation will fail.
      • Enter the Server Admin Login, Password, and Confirm Password
      • Select the preferred geographical location, typically close to you or your application. 
      • When completed, click OK

    1. Specify the source of the database by clicking Select Source(optional if you would like to create an empty database)
      • Select Sample This will create a database prepopulated with the Adventure Works light schema. It is very important  you select this schema for the rest of the example to work
    2. Lastly, click Create at the bottom of the blade. This will create an Azure SQL Database and pin it the Startboard as shown below.

Step 3: Configure the firewall

The following steps will demonstrate how to add a specific IP address as a firewall rule so the database can be accessed from a local client.

  1. In the ribbon on the left-hand side of the screen, click Browse and then SQL Servers. Note: Make sure you click on SQL Servers.  SQL Databases will have no option for firewall rules.

  1. From the available options, click the SQL server that you created in Step 2.
  2. Click Settings, then Firewall.
  3. Click this link to get you current IP address from Bing: https://www.bing.com/search?q=my%20ip%20address
  4. In the Firewall Settings, enter a Rule Name, and paste your public IP address from the previous step in Start IP and End IP fields. When complete, click Save at the top of the page. For simplicity if you want your SQL Database to be accessible on every IP, just enter 0.0.0.0 for start ip and 255.255.255.255 for end ip.  

 

Step 4: Setup your environment.

To use Microsoft’s NodeJS driver you will have to configure your machine with the following:

  1. Node.js – Version 0.8.9(32 bit version). Make sure you download the x86 version and not the x64 version as the driver will not work with 64bit version. This is the only stable version supported. If you have one of the newer Node.js versions, you will have to downgrade.
  2. Python 2.7.6.
  3. Visual C++ 2010 - the Express edition is freely available from Microsoft
  4. SQL Server Native Client 11.0 - available as Microsoft SQL Server 2012 Native Client found in the SQL Server 2012 Feature Pack

It is very important that you install the packages in accordance with the aforementioned versions. 

Step 5: Create a Node.js project

  1. Run cmd.exe as administrator.

 

  1. Create a directory where you want store your Node.js project. For example NodeJSSampleProject.
  2. Enter ‘npm init’ inside the above mentioned directory.

 

  1. For simplicity purposes we will go with the default settings so hit enter till the project is created. You should now see a package.json file inside the NodeJSSampleProject directory. This is an indication that your project was configured successfully.

Step 6: Install the required modules

  1. This is where we install the modules for our Node.js application. To do so we first have to create a folder called node_modules by using the ‘mkdir node_modules’ command inside the NodeJSSampleProject.
  2. cd into the node_modules directory and enter  ‘npm install msnodesql’ .  This will install Microsoft’s NodeJS-SQL driver into your project. You should now have a folder called msnodesql inside node_modules. Note: This will only work with Node 0.8.9. If you had newer versions, you will have to downgrade.

 

  1. Next go to the file explorer and naviage to the the C:\NodeJSSampleProject\node_modules\msnodesql folder and run the executable(.msi file). Note: You will need Visual C++ 2010 Express before you run the executable. This can be downloaded from here .  

Step 7: Setup your Node.js application

  1. Now we can deploy the Node.js application. To do so create an index.js file inside the C:\NodeJSSampleProject directory and paste the following code inside it. Make sure you change the place holders with your server name and login credentials. With this code, we will be able
  • Execute a query and retrieve the result set
  • Insert a row, pass parameters, and retrieve the generated primary key value

 

  var http = require('http');
 var sql = require('msnodesql');
 var http = require('http');
 var fs = require('fs');
 var useTrustedConnection = false;
 var result = "";
 var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:<yourserver>.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=<yourusername>;PWD=<yourpassword>;") + "Database={AdventureWorks};";
 sql.open(conn_str, function (err, conn) {
 if (err) {
 console.log("Error opening the connection!");
 return;
 }
 else
 console.log("Successfuly connected");
 conn.queryRaw("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, results) {
 if (err) {
 console.log("Error running query1!");
 return;
 }
 for (var i = 0; i < 10; i++) {
 console.log(results.rows[i]);
 result += results.rows[i] + "\n";
 }
 });
 conn.queryRaw("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express 102', 'SQLEXPRESS 102', 0, 0, CURRENT_TIMESTAMP)", function (err, results) {
 if (err) {
 console.log("Error running query!");
 return;
 }
 for (var i = 0; i < results.rows.length; i++) {
 console.log("Product ID Inserted : " + results.rows[i]);
 result += "<h1>";
 result += "Product ID Inserted : ";
 result += "</h1>";
 result += results.rows[i];
 }
 });
 });
 http.createServer(function (req, res) {
 res.writeHead(200, { 'Content-Type': 'text/html' });
 res.write("<h1> First 10 Results of the sample query are : </h1> <h2> <pre>");
 res.end(result);
 }).listen(1337, "127.0.0.1");
 console.log('Server running at https://127.0.0.1:1337/');

 

  1. Save the file and run the following from the NodeJSSampleProject directory ‘node index.js’ .
  2. Go to your browser and go to https://127.0.0.1:1337/. You should see the following screen.   

We ran a query on the AdventureWorks sample data and printed the first 10 results to the screen. Additionally we inserted a new record in the AdventureWorks database, retrieved the primary key and printed it to screen.

 

Congratulations! You have just built your first Node.js app with Azure SQL Databases. The source code for the complete reference application can be downloaded here

If you have any questions, do not hesitate to reach me at mebha at microsoft dot com 

Meet Bhagdev
Program Manager
Microsoft Corporation