READ THIS FIRSTUpdate: 3-4-2016: If you're finding this blog and looking for information related to Azure Mobile Services, I'd strongly recommend checking out Azure Mobile Apps. Azure Mobile Apps is a new version (consider it a v2) of Azure's mobile backend support. All of the same features of Azure Mobile Services are there, with a lot of other very cool features to go along. You can read more about Azure Mobile Apps, and how to transition from Azure Mobile Services, here.
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.
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.
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.
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.
does this still implement the scripts in mobile services wesbite?
Yes, the scripts will still run and you can still use them to do anything you want. In this situation, since we're specifically defining the columns in the View, dynamic schematization won't work (if we added new columns they wouldn't get selected in the View (unless you add them to the view)).
Will this work in with the .net backed mobile services with no Data tab in Azure?
There are some other guides that talk about using existing databases with the .NET backend: http://azure.microsoft.com/...
I'm getting an error when trying to create a table with the mobile service after I create the SQL view. Do you have any idea what could be the issue here?
This is the error I'm getting:
The following table was not created: "TodoItem" Error 400
Cannot alter 'existingdb.TodoItem' because it is not a table.
Try creating thew view after you generate the table. There may have been a change to check and see if an existing item (even a view) exists with the name. Alternatively, create the view with a different name, then after you create the table, go back in and switch the view name to match the table.
you seem like the appropriate person to answer some of my queries (pun intended).
I am encountering a similar situation as you have given in your example. That is however, there are existing primary keys that are not a nvarchar(36) GUID and would not map to the required Id column. On top of this I am requiring the extra data sync system property columns; CreatedAt, UpdatedAt, Deleted, Version.
From reading this post I see that I could potentially modify the backed sql DB to give the required tables the appropriate system property columns and then map them to views in my services schema. Using the required id column as an index for the service and leaving the primary keys as is, to maintain compatibility with the other applications using the existing database.
My question is then, given my added requirements and constraints, do you consider this approach still best practice?
And also, does interfacing with the tables through views in this manner come with any limitations as to what operations can be performed? I do realise you state that all CRUD operations work through the view - is this only the case where all not-null or defaulted columns are included in the view?