New tables in Azure Mobile Services: string id, system properties and optimistic concurrency

  • Article

We just released update to Azure Mobile Services in which new tables created in the services have a different layout than what we have right until now. The main change is that they now have ids of type string (instead of integers, which is what we’ve had so far), which has been a common feature request. Tables have also by default three new system columns, which track the date each item in the table was created or updated, and its version. With the table version the service also supports conditional GET and PATCH requests, which can be used to implement optimistic concurrency. Let’s look at each of the three changes separately.

String ids

The type of the ‘id’ column of newly created tables is now string (more precisely, nvarchar(255) in the SQL database). Not only that, now the client can specify the id in the insert (POST) operation, so that developers can define the ids for the data in their applications. This is useful on scenarios where the mobile application wants to use arbitrary data as the table identifier (for example, an e-mail), make the id globally unique (not only for one mobile service but for all applications), or is offline for certain periods of time but still wants to cache data locally, and when it goes online it can perform the inserts while maintaining the row identifier.

For example, this code used to be invalid up to yesterday, but it’s perfectly valid today (if you update to the latest SDKs):

  1. private async void Button_Click(object sender, RoutedEventArgs e)
  2. {
  3.     var person = new Person { Name = "John Doe", Age = 33, EMail = "john@doe.com" };
  4.     var table = MobileService.GetTable<Person>();
  5.     await table.InsertAsync(person);
  6.     AddToDebug("Inserted: {0}", person.Id);
  7. }
  8.  
  9. public class Person
  10. {
  11.     [JsonProperty("id")]
  12.     public string EMail { get; set; }
  13.     [JsonProperty("name")]
  14.     public string Name { get; set; }
  15.     [JsonProperty("age")]
  16.     public int Age { get; set; }
  17. }

If an id is not specified during an insert operation, the server will create a unique one by default, so code which doesn’t really care about the row id (only that it’s unique) can still be used. And as expected, if a client tries to insert an item with an id which already exists in the table, the request will fail.

Additional table columns (system properties)

In addition to the change in the type of the table id column, each new table created in a mobile service will have three new columns:

  • __createdAt (date) – set when the item is inserted into the table
  • __updatedAt (date) – set anytime there is an update in the item
  • __version (timestamp) – a unique value which is updated any time there is a change to the item

The first two columns just make it easier to track some properties of the item, and many people used custom server-side scripts to achieve it. Now it’s done by default. The third one is actually used to implement optimistic concurrency support (conditional GET and PATCH) for the table, and I’ll talk about it in the next section.

Since those columns provide additional information which may not be necessary in many scenarios, the Mobile Service runtime will not return them to the client, unless it explicitly asks for it. So the only change in the client code necessary to use the new style of tables is really to use string as the type of the id property. Here’s an example. If I insert an item in my table using a “normal” request to insert an item in a table:

POST https://myservice.azure-mobile.net/tables/todoitem HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: myservice.azure-mobile.net
Content-Length: 37
x-zumo-application: my-app-key

{"text":"Buy bread","complete":false}

This is the response we’ll get (some headers omitted for brevity):

HTTP/1.1 201 Created
Cache-Control: no-cache
Content-Length: 81
Content-Type: application/json
Location: https://myservice.azure-mobile.net/tables/todoitem/51FF4269-9599-431D-B0C4-9232E0B6C4A2
Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 22:39:16 GMT
Connection: close

{"text":"Buy bread","complete":false,"id":"51FF4269-9599-431D-B0C4-9232E0B6C4A2”}

No mention of the system properties. But if we go to the portal we’ll be able to see that the data was correctly added.

SystemPropertiesInPortal

If you want to retrieve the properties, you’ll need to request those explicitly, by using the ‘__systemProperties’ query string argument. You can ask for specific properties or use ‘__systemProperties=*’ for retrieving all system properties in the response. Again, if we use the same request but with the additional query string parameter:

POST https://myservice.azure-mobile.net/tables/todoitem ?__systemProperties=createdAt HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: myservice.azure-mobile.net
Content-Length: 37
x-zumo-application: my-app-key

{"text":"Buy bread","complete":false}

Then the response will now contain that property:

HTTP/1.1 201 Created
Cache-Control: no-cache
Content-Length: 122
Content-Type: application/json
Location: https://myservice.azure-mobile.net/tables/todoitem/36BF3CC5-E4E9-4C31-8E64-EE87E9BFF4CA
Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 22:47:50 GMT

{"text":"Buy bread","complete":false,"id":"36BF3CC5-E4E9-4C31-8E64-EE87E9BFF4CA", "__createdAt":"2013-11-22T22:47:51.819Z" }

You can also request the system properties in the server scripts itself, by passing a ‘systemProperties’ parameter to the ‘execute’ method of the request object. In the code below, all insert operations will now return the ‘__createdAt’ column in their responses, regardless of whether the client requested it.

  1. function insert(item, user, request) {
  2.     request.execute({
  3.         systemProperties: ['__createdAt']
  4.     });
  5. }

Another aspect of the system columns is that they cannot be sent by the client. For new tables (i.e., those with string ids), if an insert of update request contains a property which starts with ‘__’ (two underscore characters), the request will be rejected. The ‘__createdAt’ property can, however, be set in the server script (although if you really don’t want that column to represent the creation time of the object, you may want to use another column for that) – the code below shows one way where this (rather bizarre) scenario can be accomplished. If you try to update the ‘__updatedAt’ property, it won’t fail, but by default that column is updated by a SQL trigger, so any updates you make to it will be overridden anyway. The ‘__version’ column uses a read-only type in the SQL database (timestamp), so it cannot be set directly.

  1. function insert(item, user, request) {
  2.     request.execute({
  3.         systemProperties: ['__createdAt'],
  4.         success: function () {
  5.             var created = item.__createdAt;
  6.             // Set the created date to one day in the future
  7.             created.setDate(created.getDate() + 1);
  8.             item.__createdAt = created;
  9.             tables.current.update(item, {
  10.                 // the properties can also be specified without the '__' prefix
  11.                 systemProperties: ['createdAt'],
  12.                 success: function () {
  13.                     request.respond();
  14.                 }
  15.             });
  16.         }
  17.     });
  18. }

Finally, although those columns are added by default and have some behavior associated with them, they can be removed from any table which you don’t want. As you can see in the screenshot of the portal below, the delete button is still enabled for those columns (the only one which cannot be deleted is the ‘id’).

DeletingSystemColumn

Conditional retrieval / updates (optimistic concurrency)

Another feature we added in the new style tables is the ability to perform conditional retrieval or updates. That is very useful in the case where multiple clients are accessing the same data, and we want to make sure that write conflicts are handled properly. The MSDN tutorial Handling Database Write Conflicts gives a very detailed, step-by-step description on how to enable this (currently only the managed client has full support for optimistic concurrency and system properties; support for the other platforms is coming soon) scenario. I’ll talk here about the behind-the-scenes of how this is implemented by the runtime.

The concept of conditional retrieval is this: if you have the same version of the item which is stored in the server, you can save a few bytes of network traffic (and time) by having the server reply with “you already have the latest version, I don’t need to send it again to you”. Likewise, conditional updates work by the client sending an update (PATCH) request to the server with a precondition that the server should only update the item if the client version matches the version of the item in the server.

The implementation of conditional retrieval / updates is based on the version of the item, from the system column ‘__version’. That version is mapped in the HTTP layer to the ETag header responses, so that when the client receives a response for which it asked for that system property, the value will be lifted to the HTTP response header:

GET /tables/todoitem/2F6025E7-0538-47B2-BD9F-186923F96E0F?__systemProperties=version HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: myservice.azure-mobile.net
Content-Length: 0
x-zumo-application: my-app-key

The response body will contain the ‘__version’ property, and that value will be reflected in the HTTP header as well:

HTTP/1.1 200 OK
Cache-Control: no-cache
Content-Length: 108
Content-Type: application/json

ETag: "AAAAAAAACBE=" Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 23:44:48 GMT

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","__version":"AAAAAAAACBE= ","text":"Buy bread","complete":false}

Now, if we want to update that record, we can make a conditional GET request to the server, by using the If-None-Match HTTP header:

GET /tables/todoitem/2F6025E7-0538-47B2-BD9F-186923F96E0F?__systemProperties=version HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: myservice.azure-mobile.net
If-None-Match: "AAAAAAAACBE="
Content-Length: 0
x-zumo-application: my-app-key

And, if the record had not been modified in the server, this is what the client would get:

HTTP/1.1 304 Not Modified
Cache-Control: no-cache
Content-Type: application/json
Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 23:48:24 GMT

If however, if the record had been updated, the response will contain the updated record, and the new version (ETag) for the item.

HTTP/1.1 200 OK
Cache-Control: no-cache
Content-Length: 107
Content-Type: application/json
ETag: "AAAAAAAACBM="
Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 23:52:01 GMT

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","__version":"AAAAAAAACBM= ","text":"Buy bread","complete":true}

Conditional updates are similar. Let’s say the user wanted to update the record shown above but only if nobody else had updated it. So they’ll use the If-Match header to specify the precondition for the update to succeed:

PATCH /tables/todoitem/2F6025E7-0538-47B2-BD9F-186923F96E0F?__systemProperties=version HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: myservice.azure-mobile.net
If-Match: "AAAAAAAACBM="
Content-Length: 71
x-zumo-application: my-app-key

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","text":"buy French bread"}

And assuming that it was indeed the correct version, the update would succeed, and change the item version:

HTTP/1.1 200 OK
Cache-Control: no-cache
Content-Length: 98
Content-Type: application/json
ETag: "AAAAAAAACBU="
Server: Microsoft-IIS/8.0
Date: Fri, 22 Nov 2013 23:57:47 GMT

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","text":"buy French bread","__version":"AAAAAAAACBU= "}

If another client which had the old version tried to update the item:

PATCH /tables/todoitem/2F6025E7-0538-47B2-BD9F-186923F96E0F?__systemProperties=version HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
Host: ogfiostestapp.azure-mobile.net
If-Match: "AAAAAAAACBM="
Content-Length: 72
x-zumo-application: wSdTNpzgPedSWmZeuBxXMslqNHYVZk52

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","text":"buy two baguettes"}

The server would reject the request (and return to the client the actual version of the item in the server)

HTTP/1.1 412 Precondition Failed
Cache-Control: no-cache
Content-Length: 114
Content-Type: application/json
ETag: "AAAAAAAACBU="
Server: Microsoft-IIS/8.0
Date: Sat, 23 Nov 2013 00:19:30 GMT

{"id":"2F6025E7-0538-47B2-BD9F-186923F96E0F","__version":"AAAAAAAACBU= ","text":"buy French bread","complete":true}

That’s how conditional retrieval and updates are implemented in the runtime. In most cases you don’t really need to worry about those details – as can be seen in the tutorial on MSDN, the code doesn’t need to deal with any of the HTTP primitives, and the translation is done by the SDK.

Creating “old-style” tables

Ok, those are great features, but you really don’t want to change anything in your code. You still want to use integer ids, and you need to create a new table with that. It cannot be done via the Windows Azure portal, but you can still do that via the Cross-platform Command Line Interface, with the “--integerId” modifier in the “azure mobile table create” command:

azure mobile table create --integerId [servicename] [tablename]

And that will create an “old-style” table, with the integer id and none of the system properties.

Next up: clients support for the new features

In this post I talked about the changes in the Mobile Services runtime (and in its HTTP interface) with the new table style. In the next post I’ll talk about the client SDK support for them – both system properties and optimistic concurrency. And as usual, please don’t hesitate in sending feedback via comments or our forums for those features.