30 Days of Zumo.v2 (Azure Mobile Apps): Day 2 – Local Development

I kicked off my foray into Azure Mobile Apps last time with a look at how to get set up for any sort of development. I downloaded the tutorial code and then checked it into GitHub. I followed that up with setting my Azure App Service up for continuous development – whenever my ‘azure’ branch on GitHub is changed, the site gets automatically deployed.

That’s great for production use. Whenever I do a release, all I have to do is merge master to azure and I’m done. However, what about local development? I tend to want to use tools locally when I am developing. There are a bunch of reasons for this. Firstly, I’m not that confident in my coding skills. I iterate rapidly – maybe every 2-3 changes – and then I re-spin the server so I can see what is going on. I want to run local debugging. There are lots of reasons to develop locally.

Today is all about being able to develop locally while using resources in the cloud. Today is a good lesson no matter what you are publishing to Azure App Service since it covers how the App Service will find resources like your SQL Azure instance. However, it’s particularly good for Mobile Apps.

Local Development with SQLite3 The team added support for SQLite3 in v2.1.0. This means that all you have to do to enable local development is to do an npm install --save sqlite3 to your program. The database is set up automatically for you. This is a great option for local development on a Mac or Linux platform.

Connecting to SQL Azure

One of the big pains when developing on the Mac is how I play with data in a database on SQL Azure. There are actually two pieces here:

  • How do I connect to a database sitting in the cloud?
  • How do I clone my database so that I have a development version?

Both of these things are definitely possible on the Mac. Let’s take a look at the first one by taking a look at the Portal facilities. I’ve got my Azure App Service from the first days work, and I’ll use that now. Let’s look at the App Settings:

Screen Shot 2016-03-12 at 4.16.48 PM

Note that connection string – it’s got the name MS_TableConnectionString. There is a way to read ANY app setting since they are presented as environment variables to your code. Let’s take a look at Kudu – you can find Kudu on the Tools menu. When you go to Kudu, a new browser tab will be opened. Click on ‘Environment’ in the top menu bar.

Screen Shot 2016-03-12 at 4.21.23 PM

Note that I have quite a few additional app settings. I also have a Local SQL Express instance available to me – that might come in useful! Now, let’s look at the environment variables, which are further down:

Screen Shot 2016-03-12 at 4.24.05 PM

Note that my connection string has been converted to SQLCONNSTR_MS_TableConnectionString – this is the important thing. As long as we present this information to the Azure Mobile App running locally, it will connect to the remote server. I can try this on the Mac like this:

SQLCONNSTR_MS_TableConnectionString="Data Source=tcp:zumo-sql-service.database.windows.net,1433;Initial Catalog=zumo-sql-db;User ID=azureadmin@zumo-sql-service;Password=mypassword" export SQLCONNSTR_MS_TableConnectionString
node app.js

Unfortunately, nothing happens. Yep – absolutely nothing. Even if I turn on debugging:

node --debug app.js

You only get a vague sense of what is going on:

Screen Shot 2016-03-12 at 4.29.19 PM

Setting up Local Debug Output

Fortunately, the developers have thought of that. You can create a file called azureMobile.js that contains information about the local development process. You should never check in an azureMobile.js file – I add it to my .gitignore file just to be sure. It contains overrides for the defaults. Take a look at mine:

var winston = require('winston');

module.exports = {
    logging: {
        transports: [
            new winston.transports.Console({ colorize: true, timestamp: true })
       level: 'verbose'

The Azure Mobile Apps SDK uses Winston underneath to do logging, so you can link in any transport you want. In fact, I wrote a Splunk transport logger for Winston just for this purpose.

The problem is, of course, that the process cannot connect to the SQL Azure instance. That’s because there is a firewall in the way by default. To fix this, go back to the Azure Portal, click on All Resources, select your SQL service (zumo-sql-service in my case), click on Settings then Firewall.

Screen Shot 2016-03-12 at 4.40.04 PM

Your client address is conveniently included in the list, so you can just click on Add Client IP followed by Save. Don’t forget to remove the firewall rule at the end of your developing session if your IP address changes often. I’m on Comcast and it changes often enough that I remove the firewall rule at the end of the day.

When you run the service now, it should connect.

Managing your SQL Service

You are going to want to take a look at the data in your SQL Azure instance at some point. There are a few tools that you can use – here is a list from StackOverflow:

  • Adit – free, uses Java and the jTDS JDBC Driver
  • Navicat for SQL Server – free to try (14 days), costs $350 (ouch)
  • JetBrains DataGrip – free to try (30 days), costs $199 for first year
  • SQLPro – free to try (7 days), available in the Apple App Store, costs $80.
  • Valentina Studio – free download, available in the Apple App Store, pro edition costs $200.
  • Eclipse – IDE that has a Database Development mode. Good luck getting it to work though.

The information that you need to create a connection is in the connection string:

  • Data Source is the server name
  • Port is always 1433
  • Initial Schema is the database name

I don’t have a strong preference for one or the other. I like cheap, but Adit needed way too much setup to maintain (and I’m not a Java person). Eclipse was similarly problematic. Valentina Studio seemed to require more access than I could give it to work, so I can’t recommend that one. The others seem to be more expensive long term, but reasonable for a trial. I used the SQLPro Edition trial for this tutorial.

Creating an item by hand

Now that you’ve got something you can look at data with, what about that TodoItem table? If you run the query again:

Screen Shot 2016-03-12 at 5.20.45 PM


Note that the table doesn’t exist yet:

Screen Shot 2016-03-12 at 5.03.07 PM

Tables are not created until you actually put something in them. I can do that with Postman. Postman is a Chrome-based REST client. At the end of the day, Azure Mobile Apps is an OData v3 resource. So let’s treat it as one and do a GET of the table – this will bring back all the records in the table:

Screen Shot 2016-03-12 at 5.06.06 PM

Note how I’ve added an extra header – ZUMO-API-VERSION. This is required by the protocol, so don’t miss this off. It’s always set to 2.0.0. Right now, there is nothing in the database, so there is just an empty JSON array returned. Now, let’s insert an item by sending a POST with some information:

Screen Shot 2016-03-12 at 5.09.49 PM

Note that the body must be of type application/json and there must be precisely one object. My table has “dynamic schema” turned on – which is great during development since I can just invent fields to add. When you run it, you will get the full object back:

Screen Shot 2016-03-12 at 5.11.15 PM

But wait – what are all those other fields? Well, Azure Mobile Apps isn’t just “OData v3”. It has extensions to support offline sync capabilities. There are 5 fields that are added to every record:

  • id is a string – it can be anything. By default, the server generates a GUID for you, but you can also set it.
  • createdAt and updatedAt are technically DateTimeOffset(3) values transmitted as ISO8601 date-time values.
  • version is a base-64 encoded blob that indicates the current version
  • deleted is a flag to indicate if the record has been deleted – its there to support cross-device deletion

The Azure Mobile Apps Server SDK maintains these for you – there is nothing for you to do. On the client side, use an Azure Mobile Apps Client SDK to deal with these.

If you repeat the GET operation now, you will see the full record that you just created.

Cloning your database

It’s not really a good idea to be using your production SQL Azure instance as a development database. Fortunately, it’s relatively easy to clone or copy your database. Go to the Azure Portal, select All Resources then your source database (mine is zumo-sql-db). Finally, click on Copy:

Screen Shot 2016-03-12 at 5.52.57 PM

You can either keep it on the same server or put it on a new server. Your connection string will change – easily modified locally.

Putting your Connection String into azureMobile.js

It’s a little obnoxious to remember to set the environment variable whenever you start up a new session. Fortunately, you can put this in the azureMobile.js file:

var winston = require('winston');

module.exports = {
    logging: {
        transports: [
            new winston.transports.Console({ colorize: true, timestamp: true })
       level: 'verbose'
    // See http://azure.github.io/azure-mobile-apps-node/global.html#dataConfiguration
    // Normally this is not required for Azure hosted environments - we pick it up
    // from the ConnectionString for SQL Azure in the App Settings of the Azure Portal
    data: {
      provider: 'mssql',
      server: 'zumo-sql-service.database.windows.net',
      database: 'zumo-sql-db',
      user: 'azureadmin@zumo-sql-service',
      password: 'db-password'

Be really really careful about NOT DEPLOYING AZUREMOBILE.JS. You could easily have a hard to diagnose production problem because your database connection string is not what you expect.

Engines and NPM

A final note here. You can easily end up with having different node versions and npm module versions between production and development. I always recommend you use the engines section of package.json to specify the node version you are testing with as an absolute requirement, like this:

"engines": {
    "node": "=5.7.1",
    "npm": "=3.6.0"

You should also specify the versions of the npm packages in dependencies as a specific version.

Wrap Up

I spent more time than I intended researching SQL handling submissions, and I didn’t really find a satisfactory answer for people who want to learn this stuff. However, I did get some things working:

  • Local Running of the Node server.
  • Connecting to the SQL Azure instance.
  • Producing a local only configuration.

The next few articles will have me talking about authentication and authorization, so stay tuned.

3 thoughts

Comments are closed.