Hi Anders,

As mentioned on twitter we use the migration runner that ships with Umbraco. This is what also is used to handle Umbraco db upgrades. I haven't tried it on UAAS yet, but it should work otherwise umbraco can't do upgrades themselves.

The code we have in startup looks like this :

        var currentVersion = new SemVersion(0, 0, 0);

        // get all migrations already executed on DB
    var migrations =
        ApplicationContext.Current.Services.MigrationEntryService.GetAll(
            "YourApplicationName");

        // get the latest migration executed on DB
    var latestMigration = migrations.OrderByDescending(x => x.Version).FirstOrDefault();

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

    var dbversion = new Version("1.0.4");

    var targetVersion = new SemVersion(
        dbversion.Major,
        dbversion.Minor,
        dbversion.Build,
        string.Empty,
        dbversion.Revision > 0 ? dbversion.Revision.ToInvariantString() : null);

    if (targetVersion == currentVersion)
    {
        // we are up to date
        return;
    }

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

    try
    {
        migrationsRunner.Execute(UmbracoContext.Current.Application.DatabaseContext.Database);
    }
    catch (System.Web.HttpException e)
    {
        // because umbraco runs some other migrations after the migration runner is executed we get httpexception
        // we cacht this error, but don't do anything
            // fixed in future versions see : http://issues.umbraco.org/issue/U4-8077
    }
    catch (Exception e)
    {
        // we catch all other errors
        LogHelper.Error<DatabaseRepository>("Error running migrations", e);
    }

A migration will look like this :

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

    /// <summary>
    /// The add read column to contact table.
    /// </summary>
   [Migration("1.0.2", 1, Common.Constants.Database.Migration.ApplicationName)]
    public class AddReadColumnToContactTable : MigrationBase
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="AddReadColumnToContactTable"/> class.
        /// </summary>
        /// <param name="sqlSyntax">
        /// The sql syntax.
        /// </param>
        /// <param name="logger">
        /// The logger.
        /// </param>
        public AddReadColumnToContactTable(ISqlSyntaxProvider sqlSyntax, ILogger logger)
            : base(sqlSyntax, logger)
        {
        }

        /// <summary>
        /// Upgrade instructions
        /// </summary>
        public override void Up()
        {
            this.Alter.Table(Common.Constants.Database.ContactFormTable.TableName)
                .AddColumn(Common.Constants.Database.ContactFormTable.IsRead)
                .AsBoolean()
                .Nullable()
                .WithDefaultValue(0);

            this.Execute.Sql("UPDATE [customContactForm] SET IsRead = 0");

            this.Alter.Table(Common.Constants.Database.ContactFormTable.TableName)
                .AlterColumn(Common.Constants.Database.ContactFormTable.IsRead)
                .AsBoolean()
                .NotNullable();            
        }

        /// <summary>
        /// Downgrade instructions
        /// </summary>
        public override void Down()
        {
            this.Delete.Column(Common.Constants.Database.ContactFormTable.IsRead)
               .FromTable(Common.Constants.Database.ContactFormTable.TableName);
        }
    }
}

You need to inherit from MigrationBase and add the Migration attribute to your class. You can have multiple migration classes in one version by the way.

Have a look at the Umbraco Migrations to see some more examples : https://github.com/umbraco/Umbraco-CMS/tree/dev-v7/src/Umbraco.Core/Persistence/Migrations/Upgrades

Dave

You can "just connect to the DB, create the two tables and use the PetaPoco in Umbraco to add entries and fetch them". :-)

In your case I guess your question is mostly "how do I deploy that"? So that the tables are also created in the next environment (dev, staging, live)? How would you do this common scenario normally?

I am just teasing a little bit from our twitter converstation about it. Although I AM curious how you normally do this (as it should be completely the same on a UaaS site as on any other site).

So.. how? Well, you have the connection string for each environment and can connect to that to create tables.

The better way to do this, without manual intervention is to check on startup if the tables exist and if not, to create them. Umbraco uses migrations for that. So when Umbraco detects that a site has been upgraded, the migrations runner runs to alter/add tables and do other database migrations. We want to open up the migrations framework for Umbraco plugins as well, but this is not yet available. So you can do a poor-man's migration:

• Add an application startup handler
• Check if there's a marker in App_Data\TEMP, the marker can be a totally empty file with a predictable filename, for example: TeaCommerceInstalled.txt
• If the file is there: great, you don't need to do anything, everything is already installed
  • The benefit of using a marker file is that you have a very inexpensive File.Exists operation instead of a very expensive database query to figure out if tables exist
  • Putting these marker files in App_Data\TEMP also makes sure that the marker file doesn't get committed to source control, so it does not exist on the environment this is deployed to until you create it from your startup handler
• Speaking of startup handlers: if the marker file is NOT detected you can "just connect to the DB, create the two tables and use the PetaPoco in Umbraco to add entries and fetch them"

We do this poor man's migration for this very site (Our Umbraco) so have a look at the source for those for inspiration: https://github.com/umbraco/OurUmbraco/blob/cbb752c93cddb4c974321714b603638a24195626/OurUmbraco/Our/MigrationsHandler.cs

Hi Dave

THANKS! So the first code you have. What .cs file is that in? You state startup - is it in an Umbraco event, or .NET startup event - could you maybe edit the source so its possible to see where it is located in a class? and maybe also where it is placed on disk.

Do you by chance have an example of the creation of a new table with a couple of columns? As that is what I need :)

Dave's solution is also great, forgot that most of this stuff is now pretty much available. The migrations sometimes (especially in Umbraco Core) will need to run before anything in Umbraco starts though, so sometimes they might run too late (depending on how your code relies on database schema existing. This might also be a problem for the poor man's approach.

Ideally we want to have the Umbraco installer run plugin migrations as well, so you'll get the upgrade screen when you deploy and it will run the custom migrations as well as Umbraco's own migration.

Extra ideally we want to be able to run those in an unattended mode as well so that people who happen to visit that site while the upgrade is running, they don't get redirected to the login screen.

Note that you can execute ANY code in Up and Down so it's not limited to database queries. Also note that you will want to check if tables exist before you try to create them.

Great answers!

@Sebastiaan - Normally we connect to live DB and only need to change that one DB. Don't have a setup where people have local databases, so we haven't had the need to figure these things out until now for a client site :)

Just being curious if there are any methods to also transfer data in custom tables from one environment to another.

Table creation is clear and i had used the migrations also for while now but never figured out how you could transfer data.

is working on a reply...