Using Node.js with Windows Azure SQL Database
by
·
Join the DZone community and get the full member experience.
Join For FreeNode.js is often used to build highly asynchronous endpoints for web applications. Although application built with Node.js are often completely new applications that leverage new databases or services, there are a large number of existing applications that can be built with Node.js and can take advantage of existing SQL Server databases.
The canonical Hello World application written in Node.js for Windows Azure is shown below:
var http = require('http')
var port = process.env.port || 1337;
http.createServer(function (req, res) {
res.writeHead(200, { 'Content-Type': 'text/plain' });
res.end('Hello World\n');
}).listen(port);
An HTTP server is created, and the application listens on a specific port for incoming requests. An anonymous function is defined to handle incoming requests. When a request is received, the handler simply writes an HTTP 200 response header followed by the response text.
As an example of how to use the Windows Azure SQL Database with Node.js, we'll build a simple web application that tracks a simple list of tasks. In addition to Node.js, the application will use two popular community modules: Express and Jade.
Creating a Task List Database in Windows Azure
To get started, we'll first create a small database to store our task list. You will need a Windows Azure account in order to create the database or deploy the example application for this article. To create an account, browse to http://www.windowsazure.com. Click on the Free Trial link on the home page, and follow the instructions to sign up for an account.
After you have created an account, browse to http://manage.windowsazure.com to visit the management portal. Sign in using your credentials; the management portal will be displayed as shown in the figure below.
The management portal provides access to Windows Azure features that you're using as part of your subscription. Click on the SQL Database tab to manage your SQL Database instances. You'll see something like the screen below.
Click the Create a SQL Database link to navigate to a page that enables you to create a new database, as shown below.
Use the following values for the new database:
Name: TaskList
Edition: Web
Maximum Size: 1 GB
Server: New SQL Database Server
Click the right arrow to navigate to the next page, which enables you to select a user name, password, and location for the database. Provide appropriate values for these settings and click the checkbox to create the database. After the database is created, the database dashboard will be displayed, as shown in the figure below.
Your database will have a name similar to this:
7ok3age98.database.windows.net
This name is assigned by Windows Azure and cannot be changed. The database is protected by firewall rules, and by default, only Windows Azure servers are allowed to connect to the database. When you use the portal to access the database, you'll be prompted to add your current IP address to a firewall rule to enable access. If you don't allow access, you won't be able to manage the database.
The TaskList database has a table named Tasks that is used to store the tasks currently managed by the application. There are two ways to add the table and the initial data:
- You can connect to the database using SQL Server Management Studio (SSMS)
- You can use the management portal to add the tables
In this article, we'll use SSMS. If you don't currently have SSMS for SQL Server 2012 installed, you can download it as part of SQL Express.
Using SSMS, open a connection to the database and the TaskList database. Use the fully qualified name for the server (in our earlier example, 7ok3age98.database.windows.net), and your user name and password. Open a new query window, and execute the commands in Listing 1 to add the Tasks table to the TaskList database.
Listing 1. The commands used to initialize the TaskList database.
CREATE TABLE [dbo].[Tasks](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Description] [nvarchar](200) NOT NULL,
[Priority] [nvarchar](50) NOT NULL,
[Status] [nvarchar](50) NOT NULL
)
GO
INSERT INTO [dbo].[Tasks]
([Description], [Priority], [Status])
SELECT 'Shop for dinner', 'high', 'done'
UNION ALL
SELECT 'Get car washed', 'med', 'new'
UNION ALL
SELECT 'Cook dinner', 'high', 'in-progress'
GO
After creating the TaskList database, the next step is to install the Node.js SDK locally and start working with the database.
Installing the SDK
To get started developing with Node.js and Windows Azure, download the Windows Azure SDK for Node.js, which is available from the Windows Azure developer center at http://www.windowsazure.com/en-us/develop/nodejs/. The SDK includes the tools required to build Windows Azure applications with Node.js.
Installing the node-sqlserver Module
Begin by creating a new directory for your work with Node. Although I'll be using C:\NodeWork\TaskList, feel free to use any directory name - just be aware that my example paths may differ from yours.
The first step towards leveraging SQL Server in your Node.js application is to install the node-sqlserver module. Although most modules for a Node.js application are installed using the Node Package Manager (NPM), the SQL-Node module is currently in pre-release preview mode. The
As of this writing, the easiest installation requires you manually install the SQL-Node module using these steps:
1. Download the executable version of the module (currently node-sqlserver.0.1.0.EXE) from https://github.com/WindowsAzure/node-sqlserver/ to a temporary directory
2. Run node-sqlserver.0.1.0.EXE
3. When prompted, enter the path to a temporary directory to be used to expand the module
4. After extracting the files, run node-sqlserver-install.cmd to generate the required directories
5. Copy the node-sqlserver directory into your application's node-modules directory (create node_modules if it does not already exist)
When these steps are complete, the working directory is configured to use the node-sqlserver module to connect to either an on-premises instance of SQL Server, or a Windows Azure SQL Database.
Connecting to the Database
When using Node.js with Windows Azure and SQL Database, you follow the same three basic steps that you use when SQL Server is used with other platforms and systems:
- Connect to the database using the appropriate credentials
- Issue a query to the database
- Optionally display the information to the user
As an initial example of using Node.js and the node-sqlserver module, we'll connect to the Windows Azure SQL Database that we created above, displaying the contents of the TaskList table.
The first source file is provided below in Listing 2. All of the remaining listings in this article have three placeholders in the conn_str variable that must be updated with meaningful values for your project:
- [databasename], which should be the name of your database server (something like 5rt89g4 rather than TaskList)
- [username], which should be the name of a user name with access rights to the database. At least one name was assigned when you created the database earlier
- [password], which is the password associated with the user name
For example, if your database name is 5rt89g4, the user name is tasklist-user, and the password is 88Noodles, the connection string would look like:
var conn_str = "Driver={SQL Server Native Client 11.0};" +
"Server=tcp:5rt89g4.database.windows.net,1433;" +
"Database=TaskList;Uid=tasklist-user;" +
"Pwd=88Noodles;Encrypt=yes;Connection Timeout=30";
Save the contents of Listing 2 as server.js in your project's working directory (if you're using my directory names, save in C:\NodeWork\TaskList.)
Listing 2. Connecting to SQL Database with Node.js
var http = require('http');
var port = process.env.port || 1337;
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};" +
"Server=tcp:[databasename].database.windows.net,1433;" +
"Database=TaskList;Uid=[username];" +
"Pwd=[password];Encrypt=yes;Connection Timeout=30";
var query = "SELECT description, priority, status FROM dbo.Tasks";
http.createServer(function (req, res) {
res.writeHead(200, { 'Content-Type': 'text/plain' });
sql.open(conn_str, function (err, conn) {
if (err) {
res.end("Error opening the connection!");
return;
}
conn.queryRaw(query, function (err, results) {
if (err) {
res.write("Error running query!");
return;
}
console.log("row count = " + results.rows.length + "\n");
for (var i = 0; i < results.rows.length; i++) {
res.write("Description: " + results.rows[i][0] +
" Priority: " + results.rows[i][1] +
" Status: " + results.rows[i][2] + "\n");
}
res.end('Done --\n');
});
}); // sql.open
}).listen(port);
After saving the contents of Listing 2 as server.js, open a command window and navigate to the working directory (such as C:\NodeWork\TaskList.) Launch Node.js using the following command:
node server.js
Although there is no immediate feedback in the command window, as Node.js executes it loads the server.js file and opens an endpoint at http://localhost:1337. Open a browser and navigate to that address to see the current contents of the TaskList table, as shown in the figure below.
The command window will display any exceptions that occur during processing, as well as messages that are explicitly logged. The code in Listing 2 logs the number of rows returned for each result set.
In Listing 2, all interaction with the database occurs via the node-sqlserver module. A reference to the module is initialized near the top of the listing with this line:
var sql = require('node-sqlserver');
A connection to the database is opened by calling sql.open(). In keeping with the expected pattern in Node.js, an anonymous function is passed as an argument to handle the result:
sql.open(conn_str, function (err, conn) {
if (err) {
...
}
...
});
If the connection can't be opened, an error message is displayed. If the connection attempt is successful, a connection reference is passed to the handler, and query is made against the database by calling conn.rawQuery():
conn.queryRaw(query, function (err, results) {
if (err) {
...
}
...
});
If the query is successful, the handler is passed a result reference that holds the query data. The for loop in the fragment below iterates over the result, and displays the data in the browser:
conn.queryRaw(query, function (err, results) {
if (err) {
...
}
for (var i = 0; i < results.rows.length; i++) {
res.write("Description: " + results.rows[i][0] +
" Priority: " + results.rows[i][1] +
" Status: " + results.rows[i][2] + "\n");
}
res.end('Done --\n');
});
Although this version of the example demonstrates how to connect and retrieve data, in order to be useful, we’ll need to add some code to handle inserting new items, and a bit of web UI so that we can interact with the user.
Using Express
Building even a simple website would be tedious without a framework or other tools. In order to avoid having a significant amount of HTML markup in our JavaScript, we’ll use two popular Node.js modules to manage the presentation:
· Express is a routing framework that enables you to build maintainable applications that leverages the asynchronous features of Node.js.
· Jade is a templating engine for Express and Node.js that will be used to simplify the UI programming required for the example project.
Express enables you to define functions that handle requests based on the mappings (known as routes) that you define. To get started, install Express using the Node Package Manager (NPM) with the following command from your project’s working directory:
NPM install express
NPM will search for the express module and install it in the project's node_modules sub-directory.
Express replaces the HTTP module used in the first example, and rather than handling all requests with a single function, we can define handlers for specific HTTP verbs and incoming paths. For example, to handle a request to http://sitename/users, you would use a construction like this when using Express:
app.get('/users', function (req, res) {
res.write(...);
res.end(...);
});
The res variable passed to the handler is a reference to a response object that is used to send responses back to the browser.
The version of server.js in Listing 3 is similar to the earlier version, except that we're now using Express to manage routes. In this case, we're only managing the single base URL route. Replace the current version of server.js with this code to start leveraging Express. As with the earlier example, replace the values for database server, username, and password in the connection string.
Listing 3. Using Express with node-sqlserver to work with data.
var express = require('express');
var app = express.createServer();
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};" +
"Server=tcp:[databasename].database.windows.net,1433;" +
"Database=TaskList;Uid=[username];" +
"Pwd=[password];Encrypt=yes;Connection Timeout=30";
var query = "SELECT description, priority, status FROM dbo.Tasks";
var port = process.env.port || 1337;
app.get('/', function (req, res) {
var taskSet = [];
sql.open(conn_str, function (err, conn) {
if (err) {
res.end("Error opening the connection!");
return;
}
conn.queryRaw(query, function (err, results) {
if (err) {
res.end("Error running the task query");
return;
}
console.log("row count = " + results.rows.length + "\n");
for (var i = 0; i < results.rows.length; i++) {
res.write("Description: " + results.rows[i][0] +
" Priority: " + results.rows[i][1] +
" Status: " + results.rows[i][2] + "\n");
}
res.end('Done --\n');
});
}); // sql.open
});
app.listen(port);
After saving the contents of Listing 3 as the new version of server.js, open a command window and navigate to the working directory. Launch Node.js using the following command:
node server.js
As with the previous example, Node.js loads the server.js file and opens an endpoint at http://localhost:1337. Open a browser and navigate to that address to see the current contents of the TaskList table.
Although this version of server.js is similar to the earlier version, there is a key difference in how the request is handled. Rather than handling all possible requests with one handler, HTTP-GET requests to the root URL are explicitly handled by the one route defined in this version of the application.
Creating the Jade Templates
Use NPM to install Jade with the following command from your project's working directory:
NPM install jade
As with the earlier installation of the Express module, NPM will download and install Jade for you in the application's node_modules subdirectory.
As discussed earlier, leveraging Jade frees us from embedding HTML generation in our server-side JavaScript code. For this application, we need three fairly simple page templates that the Jade engine will render as HTML when output is sent to the browser:
- Index.js is the main page that displays a table of our tasks
- TaskInput.js is the new task input page
- Error.js is the generic error page for the application
To get started with some Jade templates, first create a subdirectory named Views in the application working folder. If you're using my example structure, create a C:\NodeWork\TaskList\Views\ directory.
The first template that we need is for the initial view that displays all of the tasks. Save the contents of Listing 4 as Index.js in the Views subdirectory.
Listing 4. A Jade template for the TaskList index page
!!! 5
html(lang="en")
head
title =pageTitle
body
h1 Task List
- if(tasks.length)
table
tr
th Description
th Priority
th Status
each task in tasks
tr
td #{task.description}
td #{task.priority}
td #{task.status}
- else
p Task list is empty
p
a(href="/taskInput") Create Task
Note that the file starts with a carriage return. The second line, containing !!! 5, is a directive to Jade to use HTML 5 for rendering the output. Indentation is significant in Jade, and you must use either spaces or tabs (but not both.) The relative indentation is just to indicate hierarchy in the element rendering, and rather than nested tags, a simple declaration is used. For example, a single p rather than a pair of tags (<p>...</p>) is used to indicate a paragraph.
Most of the template is fairly straight-forward if you're familiar with HTML with the exception of the table. The server.js file will pass the table contents to the template as an array named tasks. This line:
each task in tasks
marks the beginning of a loop that renders three columns for each item in the tasks array.
The next template that we need is a simple form to enable a user to enter the description, priority, and status for a task. Save the contents of Listing 5 as TaskInput.js in the Views subdirectory.
Listing 5. A Jade template for the task input page
!!! 5
html(lang="en")
head
title =pageTitle
body
form(method="POST"
action="/createTask")
h1 Task Input
p
p
label Description
br
input(type="text"
name="description"
id="description"
size="25")
p
label Priority
br
input(type="text"
name="priority"
id="priority"
size="25")
p
label Status
br
input(type="text"
name="status"
id="status"
size="25")
br
input(type="submit"
name="submit"
id="submit"
value="Submit")
In Listing 5, we have a Jade template for a simple form that accepts the three text values required for a new task. The next version of server.js will accept HTTP-POST verbs for the form action /createTask, and will insert tasks into the database.
Finally, the last template is used to provide basic structured error messages to the user. Although we've used the built-in console logging functionality included in Node.js, a simple error page will make is easier to debug any issues that you might encounter. Save the contents of Listing 6 as Error.js in the Views subdirectory.
Listing 6. A Jade template for the error page
!!! 5
html(lang="en")
head
title =pageTitle
body
h1 Error
p= error
p
a(href="/") Return to task list
Inserting Data into Windows Azure SQL Database
There's more to working with the database than simply reading data. When you need to insert or update data in the database, you should take steps to avoid injection attacks. For that reason, as a best practice you should parameterize your queries so that malformed input can't lead to unexpected results. Parameter positions are represented by question marks in a query string, like this:
INSERT INTO Tasks (description, priority, status) VALUES (?, ?, ?)
You're expected to package the parameter arguments in an array, and pass them immediately after the parameterized query string, like this:
var query = "INSERT INTO Tasks (description, priority, status)" +
" VALUES (?, ?, ?)";
var params = ["Wash the car", "Low", "New"];
sql.open(conn_str, function (err, conn) {
...
conn.queryRaw(query, params, function (err, results) {
...
});
The complete version of server.js that handles inserting and reading tasks is provided in Listing 7. Save the contents of Listing 7 as the final version of server.js.
Listing 7. The complete version of server.js
var express = require('express');
var app = express.createServer();
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};" +
"Server=tcp:[databasename].database.windows.net,1433;" +
"Database=TaskList;Uid=[username];" +
"Pwd=[password];Encrypt=yes;Connection Timeout=30";
var query = "SELECT description, priority, status FROM dbo.Tasks";
var port = process.env.port || 1337;
app.use(express.bodyParser());
app.get('/', function (req, res) {
var taskSet = [];
sql.open(conn_str, function (err, conn) {
if (err) {
res.render('error.jade',
{
pageTitle: 'Error opening the connection!',
layout: false,
error: 'Could not open the database connection'
});
return;
}
conn.queryRaw(query, function (err, results) {
if (err) {
res.render('error.jade',
{
pageTitle: 'Error running query!',
layout: false,
error: 'Error running the task query'
});
return;
}
for (var i = 0; i < results.rows.length; i++) {
taskSet[i] = {
description: results.rows[i][0],
priority: results.rows[i][1],
status: results.rows[i][2]
};
}
res.render('index.jade',
{
pageTitle: 'My tasks',
layout: false,
tasks: taskSet
});
});
}); // sql.open
});
app.get('/taskInput', function (req, res) {
res.render('taskInput.jade',
{
pageTitle: 'Add a task',
layout: false
});
});
app.post('/createTask', function (req, res) {
if (req.body) {
var query = "INSERT INTO Tasks (description, priority, status)" +
" VALUES (?, ?, ?)";
var params = [req.body.description,
req.body.priority,
req.body.status];
sql.open(conn_str, function (err, conn) {
if (err) {
res.render('error.jade',
{
pageTitle: 'Error running query!',
layout: false,
error: 'Could not open database connection'
});
return;
}
else {
res.redirect('/');
}
conn.queryRaw(query, params, function (err, results) {
if (err) {
res.render('error.jade',
{
pageTitle: 'Error running query',
layout: false,
error: 'Database insert failed'
});
return;
}
});
}); // sql.open
}
else {
res.render('error.jade',
{
pageTitle: 'Error posting task',
layout: false,
error: 'Posted task not found'
});
}
});
app.listen(port);
Run this final version of the application by using the following command:
node server.js
Open a browser and navigate to http://localhost:1337 to see the current contents of the TaskList table. Unlike the earlier versions of the application, the table will be rendered in HTML rather than text, as shown in the figure below.
In addition to displaying the tasks in a table, you can also follow the link to add new tasks to the database.
This is a complete version of the application, running against an instance of Windows Azure SQL Database. In the next section, we'll deploy the Node.js web application to Windows Azure.
Deploying to Windows Azure
In this section, we'll create a Windows Azure website, and use Git to publish the application to Azure. Start by going to the Window Azure management portal at http://manage.windowsazure.com (this is the same site that was used earlier to create the Windows Azure SQL database.) Click on the Web Sites tab to display your current list of web sites, as shown in the figure below.
Click the link to Create a Web Site to enable you to enter a URL for the site. Enter a value that's meaningful to you; I've used TaskList92653, as shown below.
The green checkbox is your indication that the URL is available.
Click the Create Web Site link to create the site. After the site is created, it will be added to your list of available websites, as shown in the figure below.
Click the name of the website, in my case TaskList92653, to display the web site management dashboard, shown below.
The dashboard displays information about site usage as well as links to commonly performed tasks.
Click the link to set up Git publishing for the web site. If this is the first time that you've used Git to deploy to Windows Azure, you'll be prompted to create a user name and password. A Git repository will be created for you, and details about the Git configuration and necessary commands for deployment will be presented.
The first task for you is to install Git if it's not on your machine.
After installing Git, open a command prompt, and go to the application's root directory. In my example, I'm using C:\NodeWork\TaskList. Commit the current version of the application by entering these three commands:
git init git add . git commit –m “initial commit”
After your files have been committed, publish the application by typing these two commands, substituting <Git URL> with the values provided on the page:
git remote add azure <Git URL> git push azure master
Browse to the Windows Azure endpoint for your application. It's listed on the dashboard page, in my case, http://tasklist92653.azurewebsites.net/. If the deployment was successful, the functionality in Windows Azure will match the local deployment.
Summary
This article has walked through the steps required to use Node with the Windows Azure SQL Database. We've also looked at using Node.js with the Express and Jade frameworks, and deployed the application to Windows Azure by publishing our application with Git.
Opinions expressed by DZone contributors are their own.
Comments