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

Article
11/22/2013

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):

private async void Button_Click(object sender, RoutedEventArgs e)
{
var person = new Person { Name = "John Doe", Age = 33, EMail = "john@doe.com" };
var table = MobileService.GetTable<Person>();
await table.InsertAsync(person);
AddToDebug("Inserted: {0}", person.Id);
}
public class Person
{
[JsonProperty("id")]
public string EMail { get; set; }
[JsonProperty("name")]
public string Name { get; set; }
[JsonProperty("age")]
public int Age { get; set; }
}

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.

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.

function insert(item, user, request) {
request.execute({
systemProperties: ['__createdAt']
});
}

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.

function insert(item, user, request) {
request.execute({
systemProperties: ['__createdAt'],
success: function () {
var created = item.__createdAt;
// Set the created date to one day in the future
created.setDate(created.getDate() + 1);
item.__createdAt = created;
tables.current.update(item, {
// the properties can also be specified without the '__' prefix
systemProperties: ['createdAt'],
success: function () {
request.respond();
}
});
}
});
}

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’).

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.

Comments

Anonymous
November 26, 2013
Great post, as usual! Your blog is my reference for Mobile Services! I have quoted this post in my article marcominerva.wordpress.com/.../using-the-new-system-properties-of-azure-mobile-services-tables-from-a-client-application.
Anonymous
November 26, 2013
Hi Carlos,I created a table and noticed the new columns. Thinking what was going on I found your blog. The GUID is a great because before I always deleted the Id from the response, just because I don't want the give the world insight in the size of my tables.In some more complex cases you want to use SQL statements for querying the DB, but then the columns do become part of the response. Most notable is the __version column, it is represented as a json object !? "id": "F8981A35-2FAB-4553-8980-*********", "__createdAt": "2013-11-26T16:08:29.851Z", "__updatedAt": "2013-11-26T16:08:29.851Z", "__version": { "0": 0, "1": 0, "2": 0, "3": 0, "4": 0, "5": 0, "6": 7, "7": 236, "length": 8 }To delete the new columns from the response (not the DB) one can just do something like this:var sql = "SELECT c. FROM Companies c INNER JOIN ********'"; request.service.mssql.query(sql, { success: function(companies) { for (var i=0; i<companies.length; i++) { delete companies[i].__createdAt; delete companies[i].__updatedAt; delete companies[i].__version; } request.respond(200, companies); } });Great job Microsoft!
Anonymous
December 09, 2013
Hi Carlos,Do you have any pointers on the best way to alter an existing table to replace the auto-increment integer id with the string id column?Keep up the great work!Eric
Anonymous
December 10, 2013
@Marco, thanks!@Freddy, the __version column is read by the node-sql driver as a Buffer object in JavaScript (node.js). What you see is the default serialization of that object, but you can change it by passing a function to JSON.stringify if you want.@Eric, I tried a few things without success - I don't think it will work. The main problem is that every table in SQL Azure requires one, and only one column with a clustered index; for tables with integer ids, that column is the "id", and we can't drop the index to change the column type (since it needs one of those), nor add a second index to then drop the index from the "id" column (since it needs exactly one). I'll ask around and post if I find someone who knows how this is done, but my instict tells me it's not possible... So if you want to really change one table from int to string id, you may consider copying the data to a second table, deleting the original, recreating it (this time with string ids), and copying the data back. This will mean that your service will be down while this happens, so whether it works depends on your scenario...
Anonymous
January 08, 2014
Hi Carlos,Can you provide a link to the latest iOS SDK that supports GUIDs for record id?The official link: www.windowsazure.com/.../downloads contains still the old SDK dated June 2013.I'm trying to get the update() function to work with the new tables that have switched type of record id from integer to guid string.
Anonymous
January 08, 2014
The comment has been removed
Anonymous
February 13, 2014
This sounds great.Why not use uniqueIdentifier though?
Anonymous
May 20, 2014
Carlos, Great info as always...I noticed when trying to use model-first, the new columns are not auto-generated, but when I uses code-first the entities do create the _* fields. It looks like the CF table inherit from EntityData which is why this is happening but should we steer clear from model-first until this is more consistent across both methodologies? We are using the .Net backend.
Anonymous
May 21, 2014
The comment has been removed
Anonymous
June 17, 2014
With the type of the id column being nvarchar(255), you cannot create a pure join table between two of those tables, because the primary key of that table will be a clustered index of size 1020 bytes (255 * 2 + 255 * 2). SQL Server has a limit of 900 bytes.You will get an error like this when you try to create the table: "Warning! The maximum key length is 900 bytes. The index 'xyz' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail."So I am unable to use a combination of Azure Mobile Services tables along with EF6 generated pure join tables. Other than reverting back to integer ids, what other options do we have?
Anonymous
September 02, 2014
Carlos, when doing an update using the mobile services client to a .net backend, the __updatedat field is not being updated, however the __version field is being updated correctly.Your article mentions that __updatedat is updated via a trigger. There don't appear to be any triggers for this table (code first created). Is that the issue, or does that only apply to the js backend?
Anonymous
October 03, 2014
@Bob, if you define your model type deriving from the EntityData base class, then the trigger should be generated for you. If you don't, then the framework will not create that trigger for you and you'll need to do it instead. If you're using EntityData, and you still don't get the trigger, can you post a question in the MSDN forum at social.msdn.microsoft.com/.../home Thanks!
Anonymous
May 20, 2015
What if I have a database with data that I want to use in azure with mobile services, do I have to add the id, _created fields to all my existing tables?
Anonymous
September 02, 2015
Yes. Also you can map your entities to a DTO and use the DTOs, in this way you avoid much changes to your database.

Share via