Using Umbraco migrations to deploy changes

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.

Sebastiaan Janssen

Dutch guy living in (and loving) Copenhagen, working at Umbraco HQ. Lifehacker, skeptic, music lover, cyclist, developer.

 

12 comments on this article

Avatar for Nick Nick | March 29 2016 18:25
Glad to see more info about this. Definitely appreciate the deep dive that even shows how it works at the database level. All I had to go on previously was this forum thread: https://our.umbraco.org/forum/developers/api-questions/73340-how-do-umbraco-db-migrations-work-not-upgrades

Avatar for Stephen Adams Stephen Adams | March 29 2016 19:29
This is great. Can this be incorporated into the Umbraco documentation (if it isn't already)?

Avatar for Ali T Ali T | March 30 2016 10:59
Really great and useful post! Thank you Seb !
Just a quick question, when Umbraco runs your custom migration, does it load Umbraco Upgrade page? or it does it behind the scene ?

Avatar for Sebastiaan Janssen Sebastiaan Janssen | March 30 2016 11:44
@Ali No, right now your custom migrations always runs during startup in your event handler, not in the upgrade installer screen.

As said in the post above, we want to run this in the upgrade installer as well but that's not there yet.

Avatar for Ali T Ali T | March 30 2016 16:16
Seb It's a big advantage that it doesn't run the Umbraco upgrade page. (I am a big fan of that now)

Adding that to the upgrade installer is also is useful but not essential.

I prefer the Umbraco Installer just updates Umbraco related stuff and shouldn't really care about what is happening outside of that.

Thanks for reply and really useful post. Keep up the good work !

Avatar for Sebastiaan Janssen Sebastiaan Janssen | March 31 2016 17:30
The problem then, of course, is that there's no nice UI to show people if something went wrong, so you're going to be stuck on either throwing a YSOD or sending out email notifications or something.
There's also the timing issue, if your migrations take a long time to execute, then that might cause your site to seem unresponsive, hard to tell if it's completely broken or still working on the updates.

Avatar for Rasmus Eeg M&#248;ller Rasmus Eeg M&#248;ller | June 10 2016 11:57
Thank you for this post. Works wonders!

Avatar for Micha&#235;l Vanbrabandt Micha&#235;l Vanbrabandt | September 26 2016 11:37
Really nice and detailled explained. Love the way Umbraco is designed to do almost anything!

Thanks for helping me out with upgrading custom tables!

/Michaël

Avatar for Shannon Shannon | December 28 2016 06:17
No migration should be using any reference to ApplicationContext.Current or the Database or DatabaseContext directly. The usage of this `_schemaHelper.CreateTable<Statistics>(false);` to create a table is incorrect and will result in you not being able to rollback if something goes wrong since this executes outside of the migrations transaction. You can see in the Umbraco Core the code we use to create/modify tables and all of that is done on the underlying migration context.

Also ApplicationContext.Current singletons shouldn't be used on views since it's already exposed as a property

Avatar for asdasd asdasd | February 21 2017 18:10
<script>alert('hi')</script>

Avatar for Alex Alex | May 19 2017 06:12
Hi.
I have a question, what will happened if upgrade fails?
Does all migration run in transaction?
Can I just restore only files in web site folder to rollback previous version?

Avatar for Harvey Williams Harvey Williams | June 8 2017 12:52
This article really helped!

The only thing I didn't like with the implementation is that you have to tell what the field type is when a new field is created on a table:

```
Alter.Table("Statistics").AddColumn("Category").AsString().Nullable();
```

It has to have `.AsString()` or an error will occur!

I have already implemented the property on my model - if I create a new table using that model using the schema helper, it automagically decides what type the field should be:

```
schemaHelper.CreateTable<Statistics>(false);
```

I feel like I should be able to alter the table using an existing property on my model rather than having to use dodgy strings. Something more like:

```
Alter.Table<Statistics>().AddColumn<Statistics>(x => x.Category);
```

Where `Category` has already been decorated with `[NullSetting(NullSetting = NullSettings.Null)]`.

Add your comment