BenkoBlog

Confessions of an Evangelist

Setting a schema for the database in Azure Mobile Services

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)

Technorati Tags: ,,,

Comments (11) -

  • how to do a top knot

    7/17/2016 2:38:54 AM |

    Hello to every one, the contents existing at this website are really remarkable for people knowledge, well, keep up the good work fellows.

  • fashion gossips

    7/18/2016 1:23:54 PM |

    Thanks very nice blog!

  • fFhnXfNh

    7/19/2016 3:27:29 AM |

    95816 554488I discovered your weblog web site site on google and appearance some of your early posts. Preserve up the excellent operate. I just extra increase Feed to my MSN News Reader. Searching for toward reading far a lot more by you later on!� 655777

  • ideas for tattoos for men

    7/20/2016 4:41:19 AM |

    Hey! I could have sworn I've been to this website before but after browsing through some of the post I realized it's new to me. Anyhow, I'm definitely glad I found it and I'll be bookmarking and checking back often!

  • elan clothes

    7/20/2016 5:48:54 AM |

    My partner and I stumbled over here from a different page and thought I should check things out. I like what I see so now i am following you. Look forward to going over your web page again.

  • Eyes contact lenses Makeup

    8/2/2016 7:02:32 PM |

    I blog quite often and I seriously appreciate your content. This article has really peaked my interest. I will book mark your website and keep checking for new details about once per week. I opted in for your RSS feed too.

  • traditional skull tattoo

    8/12/2016 8:55:19 PM |

    Great post.

  • black girl crown braid

    9/18/2016 4:26:36 AM |

    Hi there, constantly i used to check blog posts here in the early hours in the break of day, since i like to find out more and more.

  • a crown braid

    2/15/2017 6:50:11 PM |

    What's Taking place i'm new to this, I stumbled upon this I have discovered It absolutely helpful and it has aided me out loads. I am hoping to contribute & aid other customers like its aided me. Great job.

  • crown braid a beautiful mess

    2/21/2017 3:55:59 AM |

    I simply couldn't go away your website prior to suggesting that I really loved the standard information an individual supply on your visitors? Is going to be again ceaselessly to check up on new posts

  • amazing makeup ideas

    4/4/2017 6:43:15 PM |

    This is very interesting, You're a very skilled blogger. I've joined your feed and look forward to seeking more of your wonderful post. Also, I have shared your site in my social networks!

Loading