Mobile Services: Sharing a database schema between environments

I recently completed a project where we used Azure Mobile Services as the backend system for an Android application. I was pleased at how easy it was to setup and deploy to an environment, but there are a few details for production deployments that make things a bit tricky.

As your development team sprints full steam towards the finish line one major hurdle you’ll have to cross is database versioning. The default entity framework strategy is to use a database initializer that drops your database on start-up or any time the model changes, which obviously is not good and you can’t deploy to production with this enabled. The solution for this is to disable automatic upgrades and use code-first database migrations, which are also a bit tricky (I might blog about later). Here’s an initial primer on database migrations.

Before you run off to enable database migrations and start scripting out your objects into code, there’s something you should know. If you’re like me and you have separate azure environments for testing and production, the scripted objects will contain the schema name of your development environment and you’ll most likely get an error about the “__MigrationHistory” table on start-up.

You can avoid this hassle by making a small change to your DatabaseContext to ensure both environments use the same schema. The default boilerplate code uses the name of the Azure Mobile Service in your application-settings:

// part of MyMobileServiceContext : DbContext
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    string schema = ServiceSettingsDictionary.GetSchemaName();
    if (!string.IsNullOrEmpty(schema))
    {
        modelBuilder.HasDefaultSchema(schema);
    }

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

This simple change to use the same schema name ensures that your scripted objects and runtime time stay in sync:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //string schema = ServiceSettingsDictionary.GetSchemaName();
    string schema = "MySchemaName";
    if (!string.IsNullOrEmpty(schema))
    {
        modelBuilder.HasDefaultSchema(schema);
    }

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

Happy coding.