30 Days of Zumo.v2 (Azure Mobile Apps): Day 6 – Personal Tables

So far in this series, I’ve look at the following:

What I haven’t done is taken a look at the data interface – a central component to the Azure Mobile Apps story. If you are using the node.js server SDK (which includes if you are using in-portal editing), then you really only have an option of using a SQL Azure (or local SQL Server) database. ASP.NET users have more options available to them (but that’s another blog post).

In todays post, I’m going to talk about that data interface and do some simple changes to it.

What is the data interface

Azure Mobile Apps exposes an OData v3 interface to your SQL database. OData itself is a mechanism of describing queries to retrieve and publish data, so it’s a natural extension of databases into the web world. It’s RESTful in nature. Azure Mobile Apps adds a few fields to every record to support sync scenarios – most notably offline capabilities, row level conflict resolution and incremental sync.

Let’s start with the fields.

  • The ‘createdAt’ field is a DateTimeOffset field (in SQL data format terms) that represents the date and time that the record was created.
  • The ‘updatedAt’ field is also a DateTimeOffset field that represents the date and time of the last update.
  • The ‘version’ field is a base-64 encoded string that represents a unique version.
  • The ‘id’ field is a string – it can be anything, but it has to be unique as the id is a primary key. If you don’t specify it, Azure Mobile Apps will assign the string representation of a v4 GUID to this field.
  • The ‘deleted’ field is a boolean – it represents that the record has been deleted and is used to support cross-device (soft) deletion cases.

All tables have an endpoint in the /tables namespace and are case-insensitive. If you have a SQL table that you define called TodoItem (the typical starter project), then it can be accessed through /tables/todoitem and individual records can be accessed through /tables/todoitem/:id where you replace the :id with the id of the record. For example, to get the records within my todoitem table:

Screen Shot 2016-03-23 at 4.30.18 PM

And to get the specific ID:

Screen Shot 2016-03-23 at 4.31.11 PM

Defining a Table

There are lots of ways to define a table, but let’s get to the basics – I want a table. I am developing, so I want a dynamic schema – that way I can define the record structure on the client. To do that, I create a tables directory in my server project and create a tables/todoitem.js file:

var azureMobileApps = require('azure-mobile-apps');

// Create a new table definition
var table = azureMobileApps.table();

module.exports = table;

Then, within the app.js, I want to import all the tables and initialize the database:

var express = require('express'),
    azureMobileApps = require('azure-mobile-apps');

// Set up a standard Express app
var app = express();
var mobileApp = azureMobileApps({
    homePage: true,
    swagger: true
});

mobileApp.tables.import('./tables');
mobileApp.api.import('./api');

mobileApp.tables.initialize()
  .then(function () {
      app.use(mobileApp);
      app.listen(process.env.PORT || 3000);
  });

Line 11 imports the tables from the tables directory. Line 14 updates the database. The initialize() method returns a Promise – when the promise is resolved (i.e. the database is updated), I add the mobile API to the express app and start listening for connections.

Requiring authentication

This is the same table that I’ve been using all along. In fact, the authentication I’ve been setting up hasn’t actually prevented anyone from accessing the table via Postman or another app – it just means my app is doing the right thing. It’s time to require authentication for the entire table. To do that, I alter the table script (also known as a table controller):

var azureMobileApps = require('azure-mobile-apps');

// Create a new table definition
var table = azureMobileApps.table();

// Require authentication
table.access = 'authenticated';

module.exports = table;

Once I deploy this version of the script, I can go back to Postman and try out another GET operation on the table:

Screen Shot 2016-03-23 at 4.54.28 PM

I’m now getting an unauthorized message. Working through my Apache Cordova app will still work because I am authenticated there.

If you aren’t getting an Unauthorized message, make sure you are using v2.0.2 or later of the azure-mobile-apps server SDK.

Multi-user applications

This still isn’t the holy grail though. All my apps, irrespective of my authentication, still use the same records. That’s nice for sharing a todo list, but I want a little more control over my data. I want to be able to limit the data so that the logged in user can see just their records. To do that, I need to modify the table controller with some custom code.

There are four script snippets that you can add.

  • table.insert() for CREATE operations
  • table.read() for READ operations
  • table.update() for UPDATE operations
  • table.delete() for DELETE operations

One method for each of the typical CRUD operations. Each one allows you to adjust the query or the inserted data. Here is the general form:

table.read(function (context) {
    // Do something pre-execution

   context.execute()
   // .then(function (data) {
   //    Do something post-execution
   //
   //    return data;
   // });
});

context.execute() will return a Promise – the returned value upon resolution will be the inserted, updated or deleted item, or the list of items that are read (in the case of a read operation).

There is a bunch of good stuff in the context object. For the purposes of my investigation into authentication, I’m going to concentrate on just three items:

  • user is an object describing the authenticated user
  • query is a queryjs object (if you are familiar with LINQ, think of it as LINQ-lite) – this is used to convert an OData query into an SQL query
  • item is the item to be inserted

In particular, there is a user.id element – that is the sub field of the JWT that is passed to ensure authentication and is normally an identity provider based value. Let’s change this table controller to use the user.id to set up a personal table view:

var azureMobileApps = require('azure-mobile-apps');

// Create a new table definition
var table = azureMobileApps.table();

// Require authentication
table.access = 'authenticated';

// CREATE operation
table.insert(function (context) {
  context.item.userId = context.user.id;
  return context.execute();
});

// READ operation
table.read(function (context) {
  context.query.where({ userId: context.user.id });
  return context.execute();
});

// UPDATE operation
table.update(function (context) {
  context.query.where({ userId: context.user.id });
  context.item.userId = context.user.id;
  return context.execute();
});

// DELETE operation
table.delete(function (context) {
  context.query.where({ userId: context.user.id });
  return context.execute();
});

module.exports = table;

I’ve also set up a HTML-based client – it does the server-flow authentication and it is designed to act just like the apps. I find this method is better for testing than app based testing for the server since you can use browser-based development tools. You can check out the code on the GitHub Repository. You will have to do some adjustments to the auth settings, but you have the tools now to do those adjustments. Refer to the Day 3 article if you are in doubt.

If you have stored items before doing this, you will notice on running this that you no longer have any items in the list. The items are not gone – they are just not accessible any more because the userId field does not match.

My project logs the token (just like I did on day 2), and I can use that to access the protected area. To do that, I add an X-ZUMO-AUTH header to the request in Postman – the value of which is the token.

Screen Shot 2016-03-23 at 7.45.00 PM

Finding the User Identity

Note the user Id. That’s not exactly friendly. How do I, for example, get rid of user data when the user wants to close their account? Generally, users don’t know their security ID. They know their email address. I want to have the records linked to an email address instead.

Let’s first of all take a look at what is in the token on the server, where we are doing the record adjustments. To do this, I added a logging statement to the read operations in tables/todoitem.js:

// READ operation
table.read(function (context) {
  console.log('context.user = ', JSON.stringify(context.user));
  context.query.where({ userId: context.user.id });
  return context.execute();
});

Use the Azure Portal to turn on Application Logs – this is located in the Diagnostics Logs section of the Settings blade. You can then go to Tools -> Log Stream to stream the log:

Screen Shot 2016-03-23 at 7.50.18 PM

Note that the user object has nothing of value in it. The next thing to check is the decode of the token – I do this on http://jwt.io. Just cut and paste the token into the Encoded box and see what the decode is:

Screen Shot 2016-03-23 at 7.52.23 PM

Once again, nothing is really of value here. I am still looking at a sid. Fortunately, there is one other area that is available with Azure App Service Authentication: the /.auth/me endpoint:

Screen Shot 2016-03-23 at 7.54.10 PM

This has all the claims that we are allowed to get from the original identity provider. The Azure Mobile Apps Server SDK has a method for this called getIdentity() – it’s available on the context.user object. The method returns a Promise that resolves to the contents of the /.auth/me file. If I change my table.read method to this, I’ll be able to see the contents:

// READ operation
table.read(function (context) {
  return context.user.getIdentity().then(function (userInfo) {
    console.log('user.getIdentity = ', JSON.stringify(userInfo));
    context.query.where({ userId: context.user.id });
    return context.execute();
  });
});

Looking at the data from this, I can see that the information I need is in userInfo.aad.claims.emailaddress property. I can use this to affect the table.

Screen Shot 2016-03-23 at 8.01.59 PM

The new set of functions become this:

// CREATE operation
table.insert(function (context) {
  return context.user.getIdentity().then(function (userInfo) {
    context.item.userId = userInfo.aad.claims.emailaddress;
    return context.execute();
  });
});

// READ operation
table.read(function (context) {
  return context.user.getIdentity().then(function (userInfo) {
    context.query.where({ userId: userInfo.aad.claims.emailaddress });
    return context.execute();
  });
});

// UPDATE operation
table.update(function (context) {
  return context.user.getIdentity().then(function (userInfo) {
    context.query.where({ userId: userInfo.aad.claims.emailaddress });
    context.item.userId = userInfo.aad.claims.emailaddress;
    return context.execute();
  });
});

// DELETE operation
table.delete(function (context) {
  return context.user.getIdentity().then(function (userInfo) {
    context.query.where({ userId: userInfo.aad.claims.emailaddress });
    return context.execute();
  });
});

When I use these functions and access the table via Postman, I get the following:

Screen Shot 2016-03-23 at 8.15.39 PM

Note that the userId property is now the email address. If I use the Auth0 custom authentication – where I provide a choice between Facebook, Twitter, Google, MSA and potentially AAD – the email address will always be the same, so this allows me to do cross-platform authentication.

What about performance?

The getIdentity() method a HTTPS request underneath to obtain the response from the /.auth/me endpoint. As a result, it isn’t going to be as performant as just dealing with the JWT. There are a couple of methods of dealing with this and we will cover these in a later blog post.

Next Steps

Todays foray into the table controller is not the last time we will visit this topic. We are really still covering authentication. In the next blog post, we’ll take a look at what you should do when the authentication token is about to expire.

Until then, you can find todays code on my GitHub Repository.