Using Existing Databases with Windows Azure Mobile Services without changing the Schema

A little while ago, I posted about how to use Windows Azure Mobile Services with an existing database.  One of the shortcomings of the method I described was that you have to change the schema name of the tables in your database.  If you are just uploading the database to Windows Azure to only be used by Mobile Services, this isn’t really a problem.  However, if you have a code base that already talks to the database, you may not want to change the schema because that would require you to change the codebase.  This actually happened to me when I started looking at connecting this site to Mobile Services.  I didn’t want to change the schema name because I didn’t want to change the codebase for my site to use the new schema name.  Thankfully, after a little playing around, I was able to figure out a way around changing the schema:  SQL Views. 

SQL Views

A SQL view is kind of like a virtual table.  You can use them to create something like a table using any query you want.  So for example, if you had many different columns in a table but only wanted to select a subset of them, you could create a view which only pulled the data for those columns instead of having to do a SELECT on the whole table.  Alternatively if you wanted to JOIN multiple tables, you could do so in a view.  What this means for us is that we can create a view whose name and schema match up with what Mobile Services would expect it to be and then Mobile Services will treat the view like a table.  As we’ll see, this will actually work for all CRUD operations so we can still do all our normal operations on the data. 

Our database

For today’s walkthrough we’re going to create a database with a single table in it that lines up with the TodoItem sample you can pull from Mobile Services’ quick start.  Specifically, we’ll have three columns:  TodoItemID, text, and complete.  The text and complete columns match up with what we’d get with the quick start.  However, while the TodoItemID is the same type and is an identity column, it doesn’t match the required id column needed by Mobile Services.  This will end up being fine though due to how we create our view.  Here’s a screenshot of our table in the SQL designer:

The next thing we need to do is create a new schema.  This schema should match the name of your mobile service.  Today, I’m creating a mobile service named existingdb so my schema name will be existingdb.  To create this, open a new query window for your database and execute the CREATE SCHEMA command:

CREATE SCHEMA existingdb;

With that done, we can generate our view.  We’ll do this by running another SQL query against our database:

There are a couple things worth pointing out about our view.  First the view name is [existingdb].[TodoItem].  This means that we’re creating the view in the existingdb schema.  This is how we can get the mobile service to recognize it.  Secondly, we’re setting the alias of TodoItemID to id.  This is so that things line up with Mobile Service’s required id column.  Now, if we go to the portal for our mobile service and go to the data tab, we’ll see that it doesn’t show any tables:

We still need to tell Mobile Services about our TodoItem table so click on Add a Table and create a TodoItem table.  Now, go ahead and pull down the quick start application in your preferred platform.  After adding a new item in your todo list you can return to the SQL Management Portal (or SQL Management Studio) and run a select query on your table and your view.  Here are the results from the select query was ran on our actual table (SELECT * from TodoItem):

And if we run the same query on our view (SELECT * FROM existingdb.TodoItem) we get this:

This is exactly what we want to see.  The same data is showing up in the “original” format and in the Mobile Services format in my view.  When Mobile Services did it’s insert on the view, that was translated to an insert against the actual TodoItem table instead.  Curiously, if we return to the data tab in the portal, we still won’t see any data in our table:

This has to do with how the portal looks up the data for each table.  Even though the code that handles the CRUD for Mobile Services works, the portal is looking for the actual table with the matching schema, which doesn’t exist.

Conclusion

Today we looked at how you can connect an existing database into Mobile Services.  There is another technique for doing so that requires you to make changes to your database tables, however, today we looked at how to get around that.  This means that if you already have a codebase talking to your database and don’t want to change your database and your codebase, you can do so.  This should open up some great opportunities for people that already have a database hosted in Windows Azure SQL Database or want to move a database and a connected app into Windows Azure.  You can sign up for a free trial for Windows Azure and start using Mobile Services today by going here.