Three weeks ago, Scott Guthrie posted about updates to Azure Mobile Services – I’ve written a little about Mobile Services here in the past and which I’ve also demo’d at various conferences, user groups and so on around the UK (and beyond). Then just last week at Build, the second day keynote announced that Mobile Services had come out of its preview phase and was now a fully “released” product ready to go.

I thought it was about time that I tried to catch up with Scott’s post. Of course, you’ll get the definitive view from reading what Scott wrote or from going on over to the Windows Azure site and looking at the quick-starts, samples or reference documentation.

There are a lot of changes to Mobile Services detailed in Scott’s post but the main thing that interested me was the new support for custom APIs. In talking about Mobile Services (and in writing about it) I’ve encountered many people who are enthused by what the service provides but who questioned whether the previous capabilities of;

• Create a table.
• Associate scripts for read/insert/update/delete.

(along with creating scripts that could be run on a schedule or on demand)

were enough for their needs. The questions I’d get were generally along the lines of how Mobile Services would support scenarios where you wanted to provide services that aren’t directly associated with a single table and before these recent updates you’d either not be able to do these things or you’d perhaps struggle to try and shoe-horn this functionality into a script associated with a table where it didn’t perhaps fit.

With that in mind, I thought I’d experiment a little with custom APIs. One of the things about “going custom” is that it exposes me to more of node.js which I don’t really know very much about at all but I did notice a key line in Scott’s post;

“We will also be adding support for custom APIs written using .NET in the future as well.”

Which is something else I’ve heard from UK developers around Azure Mobile Services so it’s cool to know that .NET is on its way

I figured that I’d try a simplified customer/address kind of scenario and see how I got on in exposing a join across those tables from a service and so I wandered up to the azure management portal, created a new service called mtJoinTest and got hit with one of the changes that Scott talked about – a free 20MB SQL database. Nice to see;

I created a quick Customer table and an Address table in the mobile service and then went to SQL Management Studio and gave those tables a small, simple schema and added (via Management Studio, not via Mobile Services) a CustomerAddresses table;

That means that mobile services is happy to provide automatically generated (and scriptable) RESTful services to do CRUD for my Customer table and my Address table and they show up in the UI on the portal;

and I can get data from the table using (in this case) Fiddler to cruft up a quick HTTP GET to my Customer table’s JSON endpoint;

but I can’t get to CustomerAddresses because there’s no table for that surfaced into Mobile Services – I never told Mobile Services about that table and;

1. I don’t actually want direct access to that table.
2. I may not want to make that table fit with the requirements that Mobile Services has for a table (e.g. a primary key column of type bigint named id).

Attempting to hit the table with Fiddler reassures me that it’s not quietly been made available;

That’s excellent because I want to write my own custom service to join those two tables here and I can wander into the new “API” section of the Mobile Services management portal to do just that;

which raises this dialog;

which I found interesting from the point of view of offering both “PUT” and “PATCH”. I’ve said many times before that I’d never encountered “PATCH” until I started sniffing traffic from Mobile Services and saw that it was being used for updates rather than “PUT” so it’s interesting to see them both side by side and I’ll admit that I’m not so sure whether I’d ever have different handling for those 2 verbs.

As an aside, I believe PUT is intended to replace a whole entity whereas PATCH is intended to update bits of it but I’m not going to worry about that here.

I can then go and set about trying to edit the script behind those verbs;

and “GET” seemed like the place to start and so I had a bit of a stab at joining my tables together and allowing for the possibility that the user of the service may want to query for all customer/address combinations or they might want to find out which customers live at a particular address or which addresses are associated with a particular customer. Here’s my first attempt;

exports.get = function(request, response)
{       
    if (request.query.customerId && request.query.addressId)
    {
        response.send(statusCodes.BAD_REQUEST);
    }
    else
    {    
        var sql = 
            'select c.Id cId, c.firstName, c.lastName, c.email, ' + 
            'a.Id aId, a.houseNameNumber, a.streetName, a.postCode ' +
            'from mtJoinTest.Customer c ' +
            'inner join mtJoinTest.CustomerAddresses ca ' +
            'on c.id = ca.customerId ' +
            'inner join mtJoinTest.address a ' +
            'on a.id = ca.addressId';
        
        var params = [];
    
        if (request.query.customerId)
        {
            sql += ' where c.id = ?';    
            params.push(request.query.customerId);
        }
        if (request.query.addressId)
        {
            sql += ' where a.id= ?';
            params.push(request.query.addressId);
        }
        
        request.service.mssql.query(sql, params,
            {
                success: function(results)
                {
                    response.json(statusCodes.OK, results);
                },
                error : function()
                {
                    response.send(statusCodes.INTERNAL_SERVER_ERROR);
                }
            }
        );
    }
}

and that seems to work ‘reasonably well’ in my not-so-extensive testing from Fiddler, making a few requests and having a look at a few responses;

A Quick Word on URIs

In the example above I’ve ended up using URIs like /customeraddress?addressId=1 where the query string is being used to pass the identifier of the (optional) address or customer ID qualifier. I might have wanted to use a scheme like /customeraddress/address/1 or perhaps /customeraddress/customer/1 or something along those lines but as far as I can tell at the time of writing that’s not something that I can do via a custom API – that is, I think if I define an API called foo then I’m not sure that I can have any URI with additional path segments beyond /foo handled by my service.

I’ll update the post if I find out that I can.

If I wanted to flesh this out a little with a delete and an insert operation then I can do that. I’m not sure whether it makes sense to offer an “update” operation because if customer C is living at address A and they then move to address B then it possibly makes sense to delete the original customer address record and insert a new one especially given that it’s possible that the customer might reside at both address A and address B for a little while. So…I didn’t write update but I made an attempt to write delete;

exports['delete'] = function(request, response)
{
    if ((!request.query.id) || (Object.keys(request.query).length > 1))
    {
        response.send(statusCodes.BAD_REQUEST);
    }
    else
    {
        var sql = 'delete mtJoinTest.CustomerAddresses where id=?';
        
        request.service.mssql.query(sql, [request.query.id],
            {
                success: function()
                {
                    // think a 204 is the right response.
                    response.send(204);
                },
                error: function()
                {
                    // bit of an assumption.
                    response.send(statusCodes.NOT_FOUND);
                }
            }
        );
    }
}

and I attempted to allow post;

exports.post = function(request, response)
{    
    var sql = 
     'insert mtJoinTest.CustomerAddresses(customerId,addressId) values(?,?);' +
     'select @@identity as id';
    
    // It took me longer than I would expect to get this call to mssql.query to work because
    // I failed to understand that it calls your success handler for each resultset
    // from the DB (I have two).
    var firstTime = true;
    
    request.service.mssql.query(sql, [request.body.customerId, request.body.addressId],
        {
            success: function(results)
            {                         
                if (!firstTime)
                {
                    // this is not very nice, I need a better way to deal with URIs
                    // to be more like a RESTful service.
                    response.location('?id=' + results[0].id);
                    response.send(201);
                }          
                firstTime = !firstTime;           
            }
        }
    );
}

I must admit that I spent over an hour trying to write that post method and the simple reason for that was that I’m new to using mssql and I didn’t understand that the success handler that I passed to it is called multiple times – once for each resultset that SQL is sending back so in my case my SQL statements cause it to be called twice which I hadn’t expected and I spent a long time before I asked somebody and, thankfully, Josh told me how it worked

With those two additional verbs in place I can invoke my service (from Fiddler in this case) in order to delete an entry in my customeraddresses table;

or to insert an entry and tie up a customer to an address;

although I’d admit that I changed the code slightly from what I pasted above to try and do a bit of a better job on the location header that I’m sending back.

Consuming Custom APIs on the Client

Of course, the client for these kinds of services isn’t always going to be Fiddler so I thought I’d take a look at what the Mobile Services guys had done with their client stack to make it “easy” to consume these custom APIs.

I spun up a new Windows 8 blank project (in .NET), added in the Mobile Services bits from NuGet and it seemed pretty discoverable to me that the MobileServiceClient (as doc’d here) has grown a new couple of methods called InvokeApiAsync.

If you’ve looked at the client-side APIs in .NET before then you’d know that they follow a pattern of offering a strongly typed variant which relies on data-contract serialization and a JSON variant where you pass in/out JSON objects.

These APIs seem to follow a similar model. For instance, I can invoke my GET API with something like;

      IMobileServiceClient client = new MobileServiceClient(
        "http://mtJoinTest.azure-mobile.net/");

      JToken token = await client.InvokeApiAsync("customeraddress", HttpMethod.Get,
        new Dictionary<string, string>()
        {
          { "customerId", "1" }
        }
      );

and then work with the JSON object that I get back ( the framework makes use of JSON.NET as it did in previous versions ) or I can do some “strong typing” as in;

      IMobileServiceClient client = new MobileServiceClient(
        "http://mtJoinTest.azure-mobile.net/");

      var addresses = await client.InvokeApiAsync<IEnumerable<CustomerAddress>>(
        "customeraddress",
        HttpMethod.Get,
        new Dictionary<string, string>()
        {
          { "customerId", "1" }
        }
      );

and, of course, that needs a CustomerAddress class to work with which I defined as;

  [DataContract(Name = "customeraddress")]
  public class CustomerAddress
  {
    [DataMember(Name = "cId")]
    public int CustomerId { get; set; }
    [DataMember(Name = "firstName")]
    public string FirstName { get; set; }
    [DataMember(Name = "lastName")]
    public string LastName { get; set; }
    [DataMember(Name = "email")]
    public string Email { get; set; }
    [DataMember(Name = "aId")]
    public int AddressId { get; set; }
    [DataMember(Name = "houseNameNumber")]
    public string HouseNameNumber { get; set; }
    [DataMember(Name = "streetName")]
    public string StreetName { get; set; }
    [DataMember(Name = "postCode")]
    public string PostCode { get; set; }
  }

What surprised me a little was that I could magically use IEnumerable<CustomerAddress> as the generic parameter type to that call to InvokeApiAsync and something in the framework figured out how to do the right thing and give me back a List<T> from the results of de-serialization. I was impressed by that

That all seems fairly simple and understandable and, over-all, it opens up Mobile Services to be more broadly applicable for server-side scenarios that need more than just CRUD operations against a table – very nice to see this custom API feature come to fruition.