30 Days of Zumo.v2 (Azure Mobile Apps): Day 22 – More on Custom APIs

I’ve been working on custom APIs for the last couple of days. Custom APIs are awesome for executing random pieces of code to do things like “complete an order” or “send a message”. One of the awesome things is that I get access to everything that Azure Mobile Apps has access to. This includes authentication information for the current user, the table controllers and the SQL instance. In ASP.NET, using these things is relatively straight forward. It’s “just” Entity Framework, and that is very well documented. Not so in the Node.js world. In this post, I’m going to go through some examples on what I can do with custom APIs through a series of samples.

Cleaning Up Offline Sync

When I was doing the offline sync items, I said that cleaning up the database for offline sync requires a separate process. In offline sync, records are marked as deleted by setting the deleted field to true. At the same time, the updatedAt field is set to the timestamp for the operation. I want to be able to do something like “delete all recorded where deleted is true and updatedAt is older than 7 days”. There are many mechanisms for doing this. I’m going to set this up as a custom API that is authenticated and requires the user to be in a valid list.

I want my API to be accessible via /api/cleanup – so I’m going to create a cleanup.js file with the following contents:

var allowedUsers = [

var api = {
     * POST /api/cleanup - deletes records during cleanup
    post: function (req, res, next) {

        // Authorization - you must be in the allowed set of users
        if (allowedUsers.indexof(req.azureMobile.user.emailaddress) == -1) {

        // Now execute the SQL Query
        var query = {
            sql: 'DELETE FROM TodoItem WHERE deleted = 1 AND updatedAt < DATEADD(d, -7, GETDATE())',
            parameters: []

        req.azureMobile.data.execute(query).then(function (results) {

api.post.access = 'authenticated';

module.exports = api;

The major takeaway of this example is that the entire context of the request from the table controllers is available under req.azureMobile. That means you can access the authenticated user information using req.azureMobile.user, for example. Where did the req.azureMobile.user.emailaddress come from? It’s the custom middleware I wrote back in Day 10 that queried the authentication endpoint. It’s available to both custom APIs and Tables. It’s actually available to any endpoint that appears after the Azure Mobile Apps middleware has been added to the Express router.

The other big piece I am using is the req.azureMobile.data object. This is a direct link to the data provider. One of the methods there is an execute statement that “executes a statement against the native provider”. In other words, you need to know what your data provider is going to do with the statement. The default mssql provider just runs the provided SQL and returns the result as a promise.

Customized Searching

One of the things I can do with the custom API is to return an ExpressJS router object instead of an API object. This offers significantly more flexibility. A boiler plate looks like this:

var express = require('express'),
    bodyParser = require('body-parser');

module.exports = function (configuration) {
    var router = express.Router();

    // Set up the sub-routes here

    return router;

For example, let’s say I have two tables – one is called categories and one is called items. Each item is in one or more categories. There are a number of ways I can do this, but the basics are that I want to have a REST endpoint called /api/search/category. Here is some example code:

var express = require('express'),
    bodyParser = require('body-parser');

module.exports = function (configuration) {
    var router = express.Router();

    // Retrieve all records in the specified category
    router.get('/category/:category', function (req, res, next) {
            .where({ category: req.params.category })
            .then(results => res.json(results))
            .catch(next); // it is important to catch any errors and log them

    return router;

I can issue a GET /api/search/category/foo via Postman and receive all the items that have the specified category.

WARNING: Don’t try to be too clever with nesting of promises. I’ve seen some code that does a query and then another query based on the results of that query. This ends up causing major delays in the custom code while the promises get resolved for each query you do, resulting is large waits for data. In general, compose your tables for mobile usage and try not to do more than one SQL operation per API call.

Offline Custom APIs

One of the drawbacks of custom APIs is that you have to be online to do them. How do you deal with Custom APIs in an offline sense? Well, first realize that not every custom API is suitable for being used offline. It depends on the situation, but you really need to understand what the custom API is trying to achieve and whether it is suitable to be made “offline-capable”. As an example, I can see an upload file operation being made offline-capable, but the search categories task being online only. The clean-up task is really an administrative task and you want to understand the timing of it. I wouldn’t even place that functionality within a mobile app.

One way to implement an offline custom API is to use an offline table. Insert the record to initiate the request (together with all of the information necessary to execute the custom API). In your table controllers insert method, just execute your custom API and then mark the record as deleted in the database. This has the added benefit that you know who executed the API, when it was initiated and when it was executed.

Wrap Up

I haven’t finished with Custom APIs yet, but this is the bulk of the work. You should be able to write relatively complex applications that combine data access, offline sync and custom workflows with ease. I’m going to move onto a new topic in the next post – push notifications.

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