Cloudy Developer

Using WebMatrix to Build a Node.js Application That Targets SQL Azure

Mixing and matching Microsoft and non-Microsoft technologies has never been easier and in this post, we going to do just that. We’re going to build a Node.js application that serves up data from SQL Azure using a REST endpoint. We’ll develop the application in WebMatrix and Git deploy it to Azure, Let’s get coding.

If you don’t feel like creating the code from scratch, you can clone my Git repository containing this walkthrough code. Once you’ve downloaded the code, run an “npm install” in the root directory to pick up required Node modules. You’ll also need to create your own SQL Azure test data and update the connection string in “server.js” to point at it.

 

Creating a new Node.js application in WebMatrix

Let’s start by using WebMatrix to create a new Node.js site using the “Empty Site” template. We could have gone with the “Starter Site” template, but it includes far more than we need for this walkthrough so we’ll keep things simple.

 

If this is the first Node.js application created in WebMatrix, some dependencies will need to be installed. Notably, we need the IIS Node module for IIS Express. This module is what allows Node.js to run under IIS. To learn more about the module, Tomek, the author and developer at Microsoft, has written a post about Hosting Node.js Applications in IIS on Windows.

We can now hit F12 to run the application and verify everything is working. We should see “Hello World”.

 

Creating test data in SQL Azure

For us to serve up anything from our REST endpoint, we’re going to need some data stored in SQL Server. Since we’re eventually going to deploy the application to Azure, we’ll use SQL Azure.

Unfortunately, the Database workspace in WebMatrix v2 doesn’t fully support working with SQL Azure (yet) so we’ll need to take a different approach. I’ll go with the standard the SQL Azure management portal instead. I’ll skip over the details of how to do this here as there are many other walkthroughs available that show the steps, but I simply signed up for a free Azure trial account and then used the portal to create a new SQL Azure server and database.

I created a table called “People” with a column for storing a person’s “Name” and “Age”. I also added a few rows of test data.

 

Talking to SQL Azure from Node.js

Now that we have our data in place, we need a way to access it from our Node application.

Microsoft has authored a Node module for SQL Server called “node-sqlserver”. Warning – Per the GitHub page, “[t]his is an initial preview release, and is not production ready”. Currently, there’s no module to download via NPM. Instead, we either need to build it from source or download a prebuilt version. Regardless of the path we choose, we also need to install the SQL Server Native Access Client for the driver to run.

I chose to build from source. With the driver built, we need to copy it to the “node_modules/node-sqlserver/lib” directory.

For details on how to use the module, see Introducing the Microsoft Driver for Node.JS for SQL Server.

 

Exposing a REST endpoint in Node.js

Next, we need to expose an endpoint for clients to get at the SQL Azure data. Ideally, we’d use a REST framework specific module, but I’m going with “Express” since it’s a well documented and popular.

Install it via NPM – “npm install express”.

Now we can update our code to include two endpoints. One endpoint for requesting all rows in our “People” table and one for finding a specific person by name.

Here’s the “server.js” file:

You could now add additional endpoints to support insert and delete REST actions and there are some good tutorials on how to use the “Express” framework to do this.

 

Testing the  application locally

Before we get to deploying our application to Azure, we want to make sure it runs correctly locally. In WebMatrix, we can do a quick F12 to run the site under IIS Node and use Fiddler to do a GET to the “/people” endpoint which returns a JSON blob.

We can also retrieve a specific person by name (e.g., “/people/Mark”).

 

Deploy to Azure via Git

We’re finally ready to deploy the application to Azure. You can follow the Publishing a Website with Git tutorial to setup an Azure application. In my case, I’ve already got a application configured for Git based deployment so I’ll target that.

One quick thing to notice is that the application’s“.gitignore” file includes an entry for the “Express” module in the “node_modules” directory. There’s no need to check this module into Git. When we publish to Azure via Git, an “npm install” will automatically be run against our application which will rehydrate the needed Node modules. Normally, the “.gitignore’ file would have an entry for the entire “node_modules” directory, but since we have to explicitly include the “node-sqlserver” module, we can’t ignore the entire directory.

Speaking of the “package.json” file, we don’t have one yet. We need to head back to WebMatrix and create a “package.json” file.

Now we can commit our changes to Git and push the site to Azure. In the screenshot below, notice the line where it shows us running NPM.

And back in the Azure portal, we can see that our site has been deployed successfully.

And we’re done!

We’ve used Microsoft’s WebMatrix IDE to build a Node.js application that serves up data from SQL Azure and we deployed it to Azure via Git.

Hope this helps.

24. July 2012 by Mark Berryman
Categories: Azure, Git, Node.js, SQL, WebMatrix | 3 comments