30 Days of Zumo.v2 (Azure Mobile Apps): Day 8 – Table Controller Basics

Over the last six articles, I’ve covered just about everything there is to do about Authentication. It’s time to turn my attention to data. Specifically, SQL table data. Azure Mobile Apps has two SDKs on the server side – an ASP.NET 4.6 version and a Node.js version. The node.js version is also used for the “in-portal” version of Azure Mobile Apps. I’m going to do another series of Zumo Days explicitly about the ASP.NET version of Azure Mobile Apps, so stay tuned for that.

So, what is a Table Controller?

If you strip away all the “mobile” specific pieces of Azure Mobile Apps, it’s an OData v3 data source at its core. A table controller responds to the GET, POST, PUT and DELETE HTTP requests and gives you data within the SQL table that backs it.

The bad way of defining a Table Controller

You don’t need anything special to define a table controller. In you app.js file, do the following:

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

var app = express(), 
    mobile = zumo();

mobile.tables.add('todoitem', tableDefinition);

// Rest of the app definition

We will get to the table definition later on. This is a bad way of defining a table. It doesn’t provide any separation of duties. It just mixes up the configuration of the tables in code. Don’t do this.

The good way of defining a Table Controller

Create a tables directory. For each table that you need to expose to your mobile applications, create a JavaScript file with the same name. For example, let’s look at the TodoItem table. This would be called todoitem.js, with the following contents:

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

module.exports = table;

In your app.js file, you need to import your table files:

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

var app = express(),
    zumo = azureMobileApps({
    homePage: true,
    swagger: true
});

zumo.tables.import('./tables');
app.use(zumo);
app.listen(process.env.PORT || 3000);

Defining a Table

There are several options that you can define on a table – the tableDefinition from earlier. Here are all the options:

  • name – the name of the endpoint. You don’t need to set this – it’s set from the name of the file (in import mode) or the first parameter of the add() method.
  • databaseTableName – the name of the SQL table you are using for this endpoint. It’s assumed to be the same as name if you don’t see it.
  • schema – the schema that the SQL table is in. By default, that’s the dbo schema, or whatever is set in the MS_SchemaName app setting.
  • access – one of ‘anonymous’, ‘authenticated’ or ‘disabled’ – controls access to this endpoint.
  • dynamicSchema – a boolean. If true, turns dynamic schema on (more on this later).
  • columns – a definition of the model to export if dynamicSchema is not turned on.
  • maxTop – the maximum number of rows to return in one page of results.
  • softDelete – true if soft delete is turned on.

You can define all of these in your table definition file like this:

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

table.name = 'todoitem';
table.schema = 'dbo';
table.databaseTableName = 'Tasks';
table.access = 'disabled';
table.dynamicSchema = true;
table.maxTop = 1000;
table.softDelete = false;

module.exports = table;

Dynamic or Static Schema?

The node.js Server SDK defines four data types for you to use:

  • string converted to NVARCHAR(255)
  • number converted to FLOAT(53)
  • boolean converted to BIT
  • datetime converted to DATETIMEOFFSET(7)

The “converted to” indicates what column type the value will be in the SQL Azure table. If you turn dynamicSchema on by setting the property to true in the table definition, then the SDK will accept any field and create fields if necessary. This is great during development because you don’t have to think about what your tables are going to look like – you just send data to the backend and it will store it. However, this is really problematic during production – malicious users can overwhelm your database and store data secretly, using up your data allotment. You will want to lock down the schema before going to production. Let’s take a look at a more typical todoitem schema:

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

table.dynamicSchema = false;
table.columns = {
    userid: 'string',
    text: 'string',
    complete: 'boolean',
    due: 'datetime',
    alert: 'number'
};

module.exports = table;

This shows off all the data types. There is one more wrinkle here. The Azure Mobile Apps SDK creates tables defined with dynamic schema turned on automatically on first insert. That table creation doesn’t happen automatically when you are using static schema. You have to initialize your database like this:

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

var app = express(),
    zumo = azureMobileApps({
    homePage: true,
    swagger: true
});

zumo.tables.import('./tables');
zumo.tables.initialize().then(() => {
    app.use(zumo);
    app.listen(process.env.PORT || 3000);
});

The initialize() method creates the database, returning a Promise that resolves when the database creation is complete. In this case, I don’t start listening for requests until the database is created. If I don’t do that then there is the possibility that I receive a request that I cannot fulfill – the SDK will return a HTTP 500 status (Internal Server Error) in this case. If you have dynamic schema turned on then initialize() becomes a no-op. As a result, you should ALWAYS include the initialize() call to ensure your users never get a 500 Internal Server Error. In fact, I recommend you never use dynamic schema – it’s a bad idea unless you are just playing around.

Seeding Data

Since you are running initialize() all the time now, you may want to store some data by default:

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

table.dynamicSchema = false;
table.columns = {
    userid: 'string',
    text: 'string',
    complete: 'boolean',
    due: 'datetime',
    alert: 'number'
};

table.seed = [
  { userid: '', text: 'My First Item', complete: false, due: null, alert: '7' }
];

module.exports = table;

When you have a seed property, the data contained within the array will be pushed into the table when the table is created. If the table already exists (and so initialize() skips over the table), then seeding doesn’t happen.

Controlling Access

You can control access to the table as a whole with the access property:

  • ‘authenticated’ means you must be logged in to access the endpoint. If you are not authenticated, a 401 Unauthorized response will be returned
  • ‘anonymous’ means anyone can access the endpoint.
  • ‘disabled’ means the table is not visible. The server will return a 405 Method Not Allowed response.

Normally, you would only use authenticated as an access value at the table level. You can also set access at the operation level, which is why this option exists. Anonymous access is the default.

Soft Delete

When a user sends a HTTP DELETE operation, there are two mechanisms. Firstly, you can actually delete the record – a hard delete. The record is removed from the database table immediately. In the second case, a soft delete, the deleted property on the record is set to true. It will no longer be returned by the server unless explicitly requested. This is useful in multi-client situations where some clients are using offline sync. The sync table will be updated to remove the record. You will, however, have to manually remove the actual database record – it will no longer be removed from the database through the SDK.

You can configure soft delete as an option on the table like this:

table.softDelete = true;

Next Steps

Today I took a look at almost all the table level operations that you can perform with the Azure Mobile Apps SDK. There is one more, but I’ll leave that for a later post as it’s particularly awesome. I know – teaser! In the next article, I’m going to go down one level and look at what I can configure at the HTTP operation level. In the mean time, you can review the API documentation for the Azure Mobile Apps SDK.

2 thoughts

    • Not easily – I am going to be covering that particular case for ASP.NET on Day 19 (which, if you need a date, would be around May 11th). I asked an engineer to reply to use elsewhere as well as there are always things you can do.

      Like

Comments are closed.