Using Umbraco migrations to deploy changes

In yesterday's blog post I boldly claimed that it's "just as easy" to create a custom table for a counter. Well, this is kinda true, kinda not true. It's really easy to go into your local SQL instance, create a table with two columns and use a bit of PetaPoco to insert or update it, this part is true. Then you have to deploy this change to your staging environment.. rinse, repeat. You will have to find a way to create that same table in an existing database. This gets harder when you don't have (easy) access to the database on the remote server.

This is where Umbraco migrations come in. Migrations were introduced in version 6 of Umbraco to help us change database schema's during an upgrade of Umbraco. Migrations use code instead of using SQL upgrade scripts, while scripts are nice they are limited to executing SQL. We realized that sometimes there's a need to execute some code during upgrades as well. For example: we sometimes want to migrate data that is difficult to handle in a SQL script but would be really easy to deal with using a bit of code.

As of Umbraco version 7.3 these migrations are also very useful for making schema changes in different environments by just deploying your code, no more need to write manual SQL scripts that need to run on each environment.

How it works

In the Umbraco database we have a table called umbracoMigration that tracks which migrations have ran for that specific database, so on a fresh 7.4.2 install you'd see the following:

When Umbraco starts, the first thing it does is that it gets the current version of Umbraco using `Umbraco.Core.Configuration.UmbracoVersion.Current` which in this case is 7.4.2 but when you update the Umbraco dlls to version 7.4.3 it will return 7.4.3. If Umbraco then cannot find the migration with name "Umbraco" and version "7.4.3" in the database, you will get the upgrade installer screen. 

Once you click the Continue button Umbraco goes and finds all of the classes that have a `Migration` attribute with a version between the newest version in the `umbracoMigration` table and the `Umbraco.Core.Configuration.UmbracoVersion.Current` version. For example, if I've upgraded my Umbraco dlls from 7.3.5 to 7.4.2 it would find migrations targetting versions higher than 7.3.5 and lower than or equal to 7.4.2.

We don't do any migrations for patch releases, only for minor and major versions (remember a version is: major.minor.patch). So in reality the upgrade from 7.3.5 to 7.4.2 would only find migrations targeting version 7.4.0 like the ones above. After these have been executed, a new entry will appear in the `umbracoMigration` table, indicating the latest migration that ran on this database. For the Our Umbraco database, for example, you can see exactly which upgrades were done when:

The important part to understand about this is that when you deploy your website to the next environment, it will do this same comparison: find `Umbraco.Core.Configuration.UmbracoVersion.Current` and compare that to the highest migration in the `umbracoMigration` table. They will be different because the migration only ran on our local environment. You will again see the upgrade screen on your website, click continue and the migrations run on that environment after which the upgrade is complete. This means that any environment you deploy to will be consistent. 

Sidenote: we didn't always use to do this and people would often forget to run the upgrade installer after deploying their upgraded website from local to the next environment. Most times this wasn't a problem, but when there was actually changes to be made to the database they might have been forgotten, leading to an inconsistent database, leading to problems later on. This is also why you sometimes see database upgrade errors when these migrations run, at some point the proper changes were not made to your database years ago, leading to wrong assumptions on our end and an inability to upgrade your database.

You too can do this

Long story short: migrations are great! Now let's see how you can utilize them.

The table that I mentioned in my previous blog post could, for example) consist of a nodeId (a reference to a page in Umbraco) and a count (the number of times this page was visited). In this example we're going to be Umbraco's ORM called PetaPoco, and when using that, we can describe the table we want to use in a C# class like so:

 using Umbraco.Core.Persistence;

 namespace Example.Models
 {
   [TableName("Statistics")]
   [PrimaryKey("nodeId", autoIncrement = false)]
   public class Statistics
   {
     [Column("nodeId")]
     public int NodeId { get; set; }

     [Column("count")]
     public int Count { get; set; }
   }
 }

In order to build a migration, we can make a class that has the `Migration` attribute and inherits from `MigrationBase`. Inheriting from that requires you to implement the `Up()` and the `Down()` methods, for doing and upgrade and, if necessary, a downgrade.

using Example.Models;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Persistence.SqlSyntax;

namespace Example.Migrations
{
  [Migration("1.0.0", 1, "Statistics")]
  public class CreateStatisticsTable : MigrationBase
  {
    private readonly UmbracoDatabase _database = ApplicationContext.Current.DatabaseContext.Database;
    private readonly DatabaseSchemaHelper _schemaHelper;

    public CreateStatisticsTable(ISqlSyntaxProvider sqlSyntax, ILogger logger) 
      : base(sqlSyntax, logger)
    {
      _schemaHelper = new DatabaseSchemaHelper(_database, logger, sqlSyntax);
    }

    public override void Up()
    {
      _schemaHelper.CreateTable<Statistics>(false);
 
      // Remember you can execute ANY code here and in Down().. 
      // Anything you can think of, go nuts (not really!)
    }

    public override void Down()
    {
      _schemaHelper.DropTable<Statistics>();
    }
  }
}

The migration attribute needs to be provided with a version number, since we're just starting out this is set to "1.0.0". The next argument is the sort order, if there's multiple migrations necessary to upgrade to "Statistics" version 1.0.0 you can run them in the correct order. We use the `Statistics` class we created to describe the table earlier to create or drop the table.

Finally, we need to make this migration run. Since the attribute has the third argument "Statistics" it will not run and trigger when you upgrade Umbraco, only migrations with the name "Umbraco" run automatically. So we need to run it manually somehow. In the future we want to change Umbraco so that it also runs your custom migrations through the Umbraco upgrade installer, for now you'll need to handle it yourself.

In order to run this, we can create an EventHandler that runs when Umbraco starts. In this event handler we will look for the newest migration that ran for the "Statistics" product to check if we need to actually run any migrations. If the database tells us: version 1.0.0 of "Statistics" has been ran, we do nothing. If the version doesn't exist or is lower than the current version, we of course need to run the migration to get the database in a consistent state.

using System;
using System.Linq;
using Semver;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Web;

namespace Example.Eventhandlers
{
  public class MigrationEvents : ApplicationEventHandler
  {
    protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
      HandleStatisticsMigration();
    }

    private static void HandleStatisticsMigration()
    {
      const string productName = "Statistics";
      var currentVersion = new SemVersion(0, 0, 0);

      // get all migrations for "Statistics" already executed
      var migrations = ApplicationContext.Current.Services.MigrationEntryService.GetAll(productName);

     // get the latest migration for "Statistics" executed
     var latestMigration = migrations.OrderByDescending(x => x.Version).FirstOrDefault();

     if (latestMigration != null)
       currentVersion = latestMigration.Version;

     var targetVersion = new SemVersion(1, 0, 0);
     if (targetVersion == currentVersion)
       return;

     var migrationsRunner = new MigrationRunner(
       ApplicationContext.Current.Services.MigrationEntryService,
       ApplicationContext.Current.ProfilingLogger.Logger,
       currentVersion,
       targetVersion,
       productName);

     try
     {
       migrationsRunner.Execute(UmbracoContext.Current.Application.DatabaseContext.Database);
     }
     catch (Exception e)
     {
       LogHelper.Error<MigrationEvents>("Error running Statistics migration", e);
     }
   }
 }
}

Note: for versions before 7.4.2 you'll need to build in an extra `catch` as Umbraco was doing something silly, which is now fixed. So before the `catch (Exception e)` you can add a specific `catch`

catch (System.Web.HttpException e)
{
  // because umbraco runs some other migrations after the migration runner 
  // is executed we get httpexception
  // catch this error, but don't do anything
  // fixed in 7.4.2+ see : http://issues.umbraco.org/issue/U4-8077
}

Cool, we now have a new table we can use and the migration has been noted for our local database. When we deploy this site, the migration will run again as it's not been recorded in that database yet.

Just as some code to test this I've added the counter to my Master template so it will execute on each page, it's not great architecture but it at least allows me to do some quick testing.

@{
   Layout = null;

   var database = ApplicationContext.Current.DatabaseContext.Database;

   var query = new Sql()
       .Select("*")
       .From<Statistics>()
       .Where<Statistics>(x => x.NodeId == Model.Content.Id);

   var result = database.Fetch<Statistics>(query).FirstOrDefault();

   if (result == null)
   {
     database.Insert(new Statistics { NodeId = Model.Content.Id, Count = 1 });
   }
   else
   {
     result.Count = result.Count + 1;
     database.Update(result);
   }
}
<span>Views: @(result == null ? 1 : result.Count) - NodeId: @Model.Content.Id</span>

And after a few refreshes of the page I can see that this works like a charm.

And in our browser:

Now imagine you want to count things in different categories, like PageViews, Downloads, Clicks, etc. You can still use this table but you might want to add a category name to it so you can track different types of counters. 

First, we can update our `Statistics` class and add the Category there. 

  [Column("category")]
  public string Category { get; set; }

Then we can add a new migration that adds a column to the existing table.

using Umbraco.Core.Logging;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Persistence.SqlSyntax;

namespace Example.Migrations
{
  [Migration("1.0.1", 1, "Statistics")]
  public class AddCategoryToStatisticsTable : MigrationBase
  {
    public AddCategoryToStatisticsTable(ISqlSyntaxProvider sqlSyntax, ILogger logger) 
      : base(sqlSyntax, logger)
    { }

    public override void Up()
    {
      Alter.Table("Statistics").AddColumn("Category").AsString().Nullable();
    }

    public override void Down()
    {
      Delete.Column("Category").FromTable("Statistics");
    }
  }
}

The last thing we need to do is tell the EventHandler that we're expecting our "Statistics" product to be of a new version now, 1.0.1. Note that the migration above is also created to update the product to version 1.0.1.

    var targetVersion = new SemVersion(1, 0, 1);

When this runs we can see in the `umbracoMigration` table that, once again, the migration ran. We also see the new column on the `Statistics` table that we have there.

A quick update of our code now allows us to log the category of our counter as well.

@using Example.Models
@using Umbraco.Core.Persistence
@inherits UmbracoTemplatePage
@{
   Layout = null;

   var database = ApplicationContext.Current.DatabaseContext.Database;
   var category = "PageView";

   var query = new Sql()
       .Select("*")
       .From<Statistics>()
       .Where<Statistics>(x => x.NodeId == Model.Content.Id && x.Category == category);

   var result = database.Fetch<Statistics>(query).FirstOrDefault();

   if (result == null)
   {
     database.Insert(new Statistics { NodeId = Model.Content.Id, Count = 1, Category == category });
   }
   else
   {
     result.Count = result.Count + 1;
     database.Update(result);
   }
}
<span>Views: @(result == null ? 1 : result.Count) - NodeId: @Model.Content.Id</span>

Conclusion

This post was very much inspired by a recent question on the forum and the answers there, where I learned not to do this in a "hacky" way.

In this article we've seen that we can create migrations, "things" that need to be executed once on each environment that you deploy your website to. These "things" could be database tables, but you could also imagine that you might want to add a property to a document type, anything is possible. Migrations can help you make sure that all of your environments are in a consistent state when you deploy it to the next environment.