Mobile Services: Apply Database Migrations on Startup

As per my last post, if you’re using Entity Framework with Azure Mobile Services you’ll want to move away from Automatic Database upgrades and move to Code Based migrations. There are several strategies posted online, here’s the strategy that worked best for our project.

One of the challenges that my team identified early in the process was that our database content changed more frequently than our database schema. The default database initializer strategies only run the database migration and seeder scripts when the schema changes, so we found it more useful to manually apply the database migrations on start-up. This adds a bit of performance overhead to the start-up process, but since our database seeder scripts were relatively small, this seemed like an acceptable trade-off.

First, you’ll want to enable Code Based Migrations if you haven’t already. This involves opening the Package Manager Console window and typing the following:

Enable-Migrations

This power-shell command will add a few additional NuGet packages and make a few changes to your project. Most notably, the script will add a Migrations folder to your project and a Configuration class. We’ll make a few changes to the Configuration class by allowing automatic migrations and allowing data loss. The data loss feature will allow you to drop database columns even if they have data, so pay attention to your migration scripts.

internal sealed class Configuration : DbMigrationsConfiguration<MyServiceContext>
{
  public Configuration()
  {
    ContextKey = "MyProject.Models.MyServiceContext";
   
    AutomaticMigrationsEnabled = true;
    AutomaticMigrationDataLossAllowed = true;
  }

}

Next you’ll want to override the Seed method and populate your database with starter data. I highly recommend moving all your seeding logic to a static method so that you can reuse this logic in your test scripts, etc. Also note that the System.Data.Entity.Migrations namespace adds an AddOrUpdate extension method for DbSet<T> which greatly simplifies your database seed. Following this approach will allow you to run the seed as many times as you want without introducing duplicates.

protected override void Seed(MyServiceContext context)
{
    //  This method will always be called.

    //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
    //  to avoid creating duplicate seed data. E.g.
    //
    //    context.People.AddOrUpdate(
    //      p => p.FullName,
    //      new Person { FullName = "Nigel Tufnel" },
    //      new Person { FullName = "David St. Hubbins" },
    //      new Person { FullName = "Derek Smalls" }
    //    );
    //
    MyDatabaseSeeder.Seed(context);
}

Finally, to apply the database migrations on start-up you’ll need to add the following to your WebApiConfig.Register() method:

public static void Register()
{
   
   Database.SetInitializer<MyServiceContext>(null);

   var migrator = new DbMigrator(new Configuration());
   migrator.Update();

}

With this in place, the DbMigrator will apply any database changes when there are available and reliably call the Seed method with every deployment.

Happy Coding.

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.