30 Days of Zumo.v2 (Azure Mobile Apps): Day 14 – Linking Existing Tables

Thus far, I’ve done a bunch on the server that is associated with a new database and a new table. Most of the time, however, I am linking to an existing database and an existing table. Azure Mobile Apps requires that tables provide certain fields. In this article, I’m going to look at linking an existing table and exposing it as a Mobile Table.

I’m going to assume you have already gotten the database linked to the Azure Mobile Apps via the Data Connections Blade. You need to provide a suitable user for accessing the database (the user must have enough rights to read and write data).

Step 1: Prepare the table

There are five fields that should be considered “system fields” in all mobile tables. These fields need to be named specifically and have specific types:

Field Name Field Type Default Value
id nvarchar(255) newid()
createdAt datetimeoffset(3) sysutcdatetime()
updatedAt datetimeoffset(3) null
version timestamp
deleted bit 0

You can create an empty table like this:

CREATE TABLE [dbo].[tableName]
  [id] [nvarchar](255) NOT NULL CONSTRAINT [DF_tableName_id]  DEFAULT (CONVERT([nvarchar](255),newid(),(0))),
  [createdAt] [datetimeoffset](3) NOT NULL CONSTRAINT [DF_tableName_createdAt]  DEFAULT (CONVERT([datetimeoffset](3),sysutcdatetime(),(0))),
  [updatedAt] [datetimeoffset](3) NULL,
  [version] [timestamp] NOT NULL,
  [deleted] [bit] NULL DEFAULT ((0))


Similarly, you can use an ALTER TABLE statement to adjust the table instead. The major concern here is, of course, the id field – this field is commonly configured as an auto-incrementing number. Unfortunately, this is not suitable for mobile clients – two clients may asynchronously add a record and your service has to deal with this.

In addition to the table update, you need to create a trigger for setting the updatedAt record:

CREATE TRIGGER [TR_tableName_InsertUpdateDelete] ON [dbo].[tableName]
  UPDATE [dbo].[tableName] 
    FROM INSERTED WHERE INSERTED.id = [dbo].[tableName].[id]

Step 2: Define the table

If you are using an existing table, you generally don’t want Azure Mobile Apps changing it for you. As a result, you should turn off dynamic schema and define the columns you want to be included in the view:

var table = require('azure-mobile-apps').table();

table.dynamicSchema = false;
table.columns = {
    column1: 'string',
    column2: 'number',
    column3: 'boolean',
    column4: 'datetime'

module.exports = table

The translation of the types are:

Azure Mobile Apps type SQL Server type
boolean bit
datetime datetimeoffset(7)
number float(53)
string nvarchar(max)

If you need your database locked down even more and your types don’t conform to these types, then you will need to switch over to an ASP.NET project. The ASP.NET project has access to Entity Framework, which allows you to generate more specific types. You do this by locking down the model. JavaScript is a little more forgiving on the model specification, but at the price of flexibility in the SQL types available to you.

You can, of course, do all the authentication, per-table middleware and per-operation middleware that I’ve been discussing throughout this series. This table definition only includes the definition of the actual table.

Alternative Schema

Let’s say your database isn’t in [dbo] – it might be in [myschema] instead. You can handle this easily by setting the data.schema setting. This is set within an App Setting – MS_TableSchema, so there are two ways of setting it. Firstly, you can alter the initializer of Azure Mobile Apps in your app.js:

var mobileApp = azureMobileApps({
    data: {
        schema: 'myschema'

However, this doesn’t provide any flexibility. Let’s say you have a test version of your database in schema ‘test’ and the production database in ‘production’ – how do you do that? The answer is to set the App Settings:

  1. Log onto the Azure Portal and select your App Service
  2. Click on All Settings then Application Settings
  3. Under App settings, add a new app setting. The key should be MS_TableSchema and the value should be the schema name.

If you use deployment slots for deploying your site, you can make the app setting a deployment slot setting – this allows you to specify different schemas for (for example) staging vs. production automatically – when the slots are swapped, the app gets the replacement setting.

Next Time

It’s probably time I cover a killer feature of Azure Mobile Apps – Offline Sync. This is not just a feature to allow access to data when the network is not there – it’s also a feature used for performance – especially when there is a lot of data to transfer.