BenkoTIPS

I’ve been working on a project using Xamarin.Forms and Azure Mobile Services for a while, and one issue I came across that wasn’t entirely clear is how do you work with an existing database, yet support updates to the tables and be able to deploy to different environments (like Test, QA and Prod)? Hopefully I can shed some light with this post.

Azure Mobile Services is a feature rich cloud service that promises to take care of some of the complexities of building connected mobile apps. It supports things like 3rd party federated identity, push notifications, logging and more. With the original node.js release it supported JavaScript configuration for the CRUD operations against a table along with a dynamic schema that adapts to the request received via REST. In the .NET release they replace node.js with the ability to roll your own logic in C# and handle the data interaction with WebAPI type controllers. It requires a bit more work, and handling the database changes can be confusing.

Fortunately I’ve found a couple articles that help illustrate how this is done (on MSDN), but it doesn’t specify how to handle ongoing updates. To make it work for my scenario I either needed a way to support data model changes to a .NET backend mobile service, but have a consistent schema name when I move between environments. In the second article they show how to enable code-migrations, and to replace the default database initializers, by using the NuGet package manager and modifying code in the WebApiConfig.cs file. The steps were:

1. Use NuGet Package Manager to Enable-Migrations
2. Add a starting migration
3. Update the WebApiConfig.cs file to use a DbMigrator to update the context instead of calling the default initializer

   
    public static class WebApiConfig
    {
       public static void Register()
       {
           // Use this class to set configuration options for your mobile service
           ConfigOptions options = new ConfigOptions();
   
           // Use this class to set WebAPI configuration options
           HttpConfiguration config = ServiceConfig.Initialize(new ConfigBuilder(options));
           
           // *** BENKO: Enable database migrations for the service
           //Database.SetInitializer(new MobileServiceInitializer());
           var migrator = new DbMigrator(new Configuration());
           migrator.Update();
       }
    } 
    

4. Test local and confirm it’s working

I added a couple additional changes to set the schema name for my app in the MobileServiceContext.cs file (in the Models folder).

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
    // *** BENKO: This is what sets the schema name to the service name...
    string schema = ServiceSettingsDictionary.GetSchemaName();
    schema = "mySchema";
    if (!string.IsNullOrEmpty(schema))
    {
        modelBuilder.HasDefaultSchema(schema);
    }

    modelBuilder.Conventions.Add(
        new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
            "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
 }
 
 

I also need to make sure to create the schema on my database instance in SQL Azure and grant rights to the service user account. You can get the user account using SQL Server Mgmt tool (expand the users of the database node, or by running a SQL Script to select name from the SysLogins table of the master db. Once you have it you will need to create the schema an grant rights to it. Assuming the service user is ABC123Login_myServiceUser, the script looks like this:

 create schema mySchema

 -- Grant specific access rights to use based on Schema
 GRANT 
    SELECT, INSERT, UPDATE, DELETE,
    ALTER, CONTROL,  EXECUTE,  
    REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION 
 ON SCHEMA::[mySchema] 
    TO [abcdefghijLogin_democityUser]
 

When you publish the mobile service it will attempt to update the database using the schema provided. If there are error you can use the service’s logs to figure out what’s missing. By specifying the schema name instead of using the service’s name I’m able to deploy to multiple environments but integrate this data with my other applications.

Happy Coding! (originally posted on www.benkotips.com)