Mobile Services and Windows Azure Storage

Update 3-20-2015: If you are running into a cursor error when trying to upload a blob from the Android version like the following:
  Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it

I'd recommend replacing the code which uses a cursor to get the image bytes with the following:

  Bitmap bitmap = BitmapFactory.decodeStream(getContentResolver().openInputStream(mImageUri));
  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  bitmap.compress(Bitmap.CompressFormat.PNG, 100, bos);
  byte[] bytes = bos.toByteArray();

That should fix it

Since we launched Mobile Services, one of the common questions we get is how to connect Mobile Services with Windows Azure Table and Blob Storage.  Mobile Services is built off of SQL Database which is really great for many reasons.  The motivations for connecting to Table and Blob storage usually have to do with scalability, cost, and file storage.  Table storage is a NoSQL data storage implementation that offers really great scalability in addition to geo-replication.  Additionally, table storage is a good deal cheaper than SQL database.  Blob storage is specifically suited to storing files be they images, videos, or anything else.  SQL Storage makes Mobile Services a bit more straightforward but does come at the costs I just mentioned.  Today, I’m happy to announce a sample which will demonstrate how you can connect your Mobile Services into Table and Blob Storage.  It’s not quite as easy as flipping a switch and saying “don’t use SQL, use Tables and Blobs” but this should give you a pretty thorough understanding of how to take advantage of Tables and Blobs.  Additionally, after this you should understand enough to be able to use them in conjunction with the built in SQL storage if you want.  This article will cover how to handle things on the server (Mobile Services) side and the next two will cover connecting to them with the iOS and Android clients.  Source code, including the server scripts, is available for iOS and Android.

An important note on the difference between SQL Database and Table Storage

A very important thing to point out is that you can’t just replace SQL Database with Table storage and think you’ll get all of the same features. Specific design and functionality has to be taken into account when using either type of storage.  One very specific example of this has to do with querying your data.  With Table Storage, you are able to do look ups on a table against it’s Partition Key and Row Key.  That’s in.  With SQL Database you can index and look up by any column in any table.  It is extremely outside the scope of this article to discuss the advantages and disadvantages of the different storage systems and it would be impossible to provide a “one size fits all” answer to what you should use.  When in doubt, do the proper research to understand your options. 

Setting up your Mobile Service and Storage Account

The first step we need to take is to create a new Mobile Service and Storage Account.  Before you can do that, you’ll need a Windows Azure account.  If you don’t have one yet, you can sign up for a free 90 day trial here.  Next, as opposed to walking through the steps to setup a new Mobile Service yet again, you can reference the Setting up the app portion of this article.  Now that that’s done, let’s set up our Storage account.  Click the + NEW button at the bottom left of the portal and choose DATA SERVICES and then STORAGE:

Next choose QUICK CREATE, name your account, and choose it’s location:

Click the CREATE STORAGE ACCOUNT button and your account will start being created.  Once it is, click into your storage account.  Before we can move back into our Mobile Service, we need to get our storage account key.  Once in your storage account, click the MANAGE KEYS button at the bottom:

In the window that pops up you’ll see a primary and secondary key.  You’ll need to copy the name of your storage account and the primary key for use later:

Accessing Tables

Now that we’ve set up our Mobile Service, let’s take a look at accessing Tables.  Go into your Mobile Service in the portal and go to the DATA tab (it’s the link near the top under your Mobile Service name).  If you started with a new service, you won’t see any tables so go ahead and click the + CREATE button at the bottom.  We’ll name this table Tables and leave the permissions to the default with the exception of READ PERMISSION.  Set that to Everyone.  We’re only going to do that so that we can also check our tables from the browser later on. 

After that, click the checkmark to create your table.  Once your table is generated, click on it’s name to be taken into the table.  You’ll start by seeing that your table has no records.  That’s fine and in fact, since this view is tied to what’s in the SQL Database and we’re going to use Table Storage, we’ll never see our data here.  Click on the SCRIPT tab at the top.  We’ll first handle the Insert script by replacing the default with this:

Make sure you replace the accountname and accountkey with the values you grabbed above for your storage account.  You’ll need to do that in all of the scripts going forward.  The first thing the script does is get access to the azure module.  This is a Node.js module that gives us access to a lot of different things including Table and Blob storage.  After we set our account name, key, and host variables, we call createTableService with them.  This gives us a service we can use to access Table Storage.  Next we call the service’s createTable method and pass in the tableName property that was sent into the method as JSON (into the item object).  If the table was created we respond with a 200 and the item object.  If not, we respond with a 500.  As we continue on, you’ll see that most of the scripts look very similar.  We get the account name, key, and host and use them to generate a service using the azure module.  Then we do whatever we want with that service.  Save that script and then use the drop down at the top to switch to the Delete script:

The delete script is nearly identical to the create script.  Instead of calling createTable we’re calling the deleteTable method.  Also, since the delete script doesn’t take in a JSON item like the other scripts, we need to get the tableName from the request.parameters object.  Lastly, we’re not returning any data on success, just the 200.  Save that and then switch to the Read script:

The read script uses the queryTables method and returns either the error in case of an error or the actual tables data on success.  That’s all we have to implement in order to insert, delete, and read tables.  Next we’ll look at accessing the data inside of those tables!

Accessing Table Rows

Return back to the DATA tab (you can click the big arrow pointing left if you’re still on the read scripts for the tables table).  Go ahead and create another table with the same permissions and name it TableRows.  Click on your table after it’s created and go to the SCRIPTS tab.  We’ll start with the Insert script:

Everything is the same (again) up until we get to the insertEntity method.  This method takes in the name of the table, which we’re getting from request.parameters, and the data for the table which we’ve passed over in the JSON item.  Save that and move on to the Update script:

This script is completely the same except we’re calling updateEntity instead of insert.  Let’s go on to Delete where things will be slightly more interesting:

Here, we need to send over the table name as well as the Partition Key and Row Key.  All of these values are retrieved from request.parameters because, again, we can’t pass this data though on a JSON item as the delete method only takes in the id of the item to delete.  Let’s wrap things up with the Read script:

Here we’re actually creating a azure.TableQuery to query Table Storage.  We’re doing a very simple query here and just saying “select all the rows from the table with this name”.  However, if you wanted to do more advanced querying (such as pulling all the items from a table with a certain Partition Key) this is where you would do it.  Once we’ve created that TableQuery we pass that into the queryEntities method.  On success we’ll return the rows of data that are returned from our query.

That’s all there is to the server side of accessing Table Storage.  We’ll next look at dealing with Blob Storage.

Accessing Containers

As the first part of dealing with Blob Storage, we need to see how to look at the Containers.  Containers “contain” blobs so before we can load up blobs we need to deal with what they’re in.  Return to the DATA tab for your Mobile Service and create a new table with the same permissions as before and name it BlobContainers.  After your table is created, click into it and go to the SCRIPT tab.  We’ll start with the Insert script:

Things for setting up are just a little different here than in the table scripts.  First, the host is set to the accountname + .blob.core.windows.net.  If the host name was left to tables.core.windows.net we wouldn’t be able to access Blob Storage.  Next, we’re calling azure.createBlobService in order to get a blobService for accessing Blob Storage.  That’s going to be the same for the rest of the scripts that deal with blobs.  Next, we’re checking the isPublic flag in request.parameters.  A container can be public or private (it’s a little more complex but that will do for this article).  If it’s public, I don’t need any special permissions to access the blobs inside of it.  If it’s private, it requires a bit more work but we’ll talk about that when we get down to blobs below.  Either way, we’re calling the createContainerIfNotExists method and passing in the container name from the JSON item.  Since the default is private, if the isPublic flag is set to true, we pass in some options that specify that the publicAccessLevel is for blobs.  Once created we return to the calling app.  Let’s move on to the Delete script:

Deleting is very similar to the table deletion, we just call deleteContainer and pass the container name from request.parameters.  Let’s wrap up containers with the Read script:

Here we just call the listContainers method and return the results to the calling app.  Containers are now covered.  We’re ready to talk about Blobs.

Accessing Blobs

The last step before we move to our client is how to load blobs.  Go back to the DATA tab in your Mobile Service and create a new table with the same permissions named BlobBlobs.  Once it’s created go in and let’s tackle the Insert script:

This script is by far the most complex one we’ll look at today.  Before we talk about the code, let’s talk about how we handle inserting a blob.  To insert a blob, we need to have a URL we can write to.  In order for Blob Storage to be secure, we can’t just write to http://myaccount.blob.core.windows.net/mycontainer/myNewBlobName.  Blob Storage needs to know that there is a blob there and approve us writing to it.  To facilitate that, we generate a Shared Access Signature (SAS) which gives us write (and read) access to a specific blob’s URL.  We can then return that SAS URL to the client who can read or write to the blob.  We’re going to use the Insert method to both generate new blobs and get access to existing blobs.  We could split this out and have one method for reading and one for writing, but for simplicity it’s all in one here.

So in the script after we call createBlobService we generate a sharedAccessPolicy.  Here we’re saying we want to be able to read and write and that the SAS URL should expire in 5 minutes.  If we only wanted read access, we could just change the permissions.  After that we get the sasUrl object with the generateSharedAccessSignature method.  That method takes in the access policy as well as the blob and container names.  After that, we need to build our SAS URL from the properties in the sasUrl object.  To do this, we use the stringify method of the querystring module to build our query string parameters.  Finally we return that to the calling app.  Let’s move on to the Delete script:

Back to simplicity with this as we just call deleteBlob and pass in the container and blob name.  Now let’s look at the Read script:

Here we call the listBlobs method and pass in the container name.  This will return the details for all of the blobs in our container.  That’s all for the server side scripts for accessing Table and Blob storage.

A word about how things are set up

These scripts and this sample were built to be a demonstration of how you might access Windows Azure Storage from Mobile Services.  To that end, some of the things I’ve done could certainly be done in other ways that might fit better with what you’re trying to accomplish.  For example, I used four “dummy” tables for Tables, TableRows, Containers, and Blobs.  It might make more sense for you to use one table in Mobile Services to match up to each table in Table Storage (at least for ease of knowing where things are at).  Alternatively, you could store some data in SQL Database and some in Table Storage.  As I pointed out above, instead of using one script to get read AND write access to a blob, I could split these into two separate functions / table methods.  Additionally, we could have used fields on the JSON item to specify some things (like the Table name on the TableRow update script) and then just deleted the TableName from the item before passing it over to storage if we wanted.  There isn’t necessarily one way to do any of these things. 

Finally, for more information about the azure module such as how to do more advanced queries against Table Storage, the different method overrides, as well as how to access other things like Service Bus or Queues, check out the module details in GitHub.

The Client

Now that we’ve covered everything on the server side, we’re ready to tackle the clients.  I’ve split things up so there is one article that will cover everything for Android and one for iOS.  It’s important to remember though that we’re using the same Mobile Service for BOTH clients.  There is no need to create a different Mobile Service for each client.  You can find the specific articles for each client here:

• the Android Client
• the iOS Client

Conclusion

Today we looked at how you can easily make use of Table and Blob Storage from your Mobile Services.  You should be capable of understanding how to take advantage of all the different kinds of storage from Mobile Services now.  Remember that there are advantages and disadvantages to all of the storage providers.  Additionally, there are many different ways of doing the same things.  This sample is meant to give you the basics that you can work with to craft your own solution for your apps.