Handle Database Versioning with PetaPoco

Today I'd like to share some of the things I've learned when working with PetaPoco. Mind you if you're using a micro-ORM you're probably looking for something lightweight as opposed to Entity Framework. Specifically I'd like to share insights on handling database migrations and testing those migrations. As such, I've put together some code that I've borrowed from the Umbraco CMS project and the unit of work pattern by James Heppinstall. I don't want to take credit for the great work of others, rather I wanted to highlight and give them credit.

I've tried to simplify everything to the very basics. For instance, in the Umbraco project, they handle several different database types (SQL Server, MySQL, etc) whereas I'm sticking to just SQL Server for this example. The example code has very few dependencies and works on straight MVC sites and has no Umbraco dependency.

50,000 Foot View

From high above, we're trying to accomplish simple database migrations. Meaning, when we decide the schema needs to change due to business or performance needs, we'd like to have a mechanism in place to detect a change needs to be made, then make it. We'll also want an integration test in place to make sure that it can actually make the upgrade in a test database.

10,000 Foot View

As we narrow in on our build, let's assemble our grocery list of things we will need:

  • A transactional PetaPoco class (unit of work) - This will handle atomic operations of our database operation. Either all or none of the database changes will be successful.
  • Migration detection - We will need some code that detects at startup that a change needs to happen in our DB.
  • Migration helper - A helper that runs the migrations in order.
  • Migrations - We will want to be able to have a standardized interface that we can implement on a per-migration basis.
  • Integration tests - These tests will be able to simulate an upgrade in a test database.

Unit of Work

The unit of work pattern simply allows for one or more queries to be run as a transaction. If the transaction succeeds, then all of the individual queries have succeeded. In James Heppinstall's blog, he articulates the concept rather nicely. We will use this as a basis to run all of our queries throughout this blog. However to make the integration testing a bit easier, I've modified the class a bit to expose the ConnectionString property so we can easily trade out which DB will be used:

using System;
using PetaPoco;

namespace DAL
{
    public class PetaPocoUnitOfWork : IDisposable
    {
        private readonly Transaction _petaTransaction;
        private readonly Database _database;
        public static string ConnectionString = "testMvc";

        public PetaPocoUnitOfWork(string connectionString = "")
        {
            if (!string.IsNullOrEmpty(connectionString))
            {
                _database = new Database(connectionString);
            }
            else
            {
                _database = new Database(ConnectionString);
            }
            
            _petaTransaction = new Transaction(_database);
        }

        public void Dispose()
        {
            _petaTransaction.Dispose();
        }

        public Database Database
        {
            get { return _database; }
        }

        public void Commit()
        {
            _petaTransaction.Complete();
        }
    }
}

The code above can be found on a sample site here.

Migration Detection

Next we'll need to be able to detect when a migration should occur. The method I've chosen depends on two values, the DLL version and an AppSetting named "MyApp:Version" that you can find here. The AppSetting's holds the current version of the database. When a newer versioned DLL is added to the /bin, our code should trigger a review of the migrations to ensure it is up to date. Finally the detector will alter the AppSetting and set its value to the DLL version value. The following code is placed in the Application_Start method of my sample site:

var versionAppsettingKey = "MyApp:Version";

var dllVersion = MigrationHelper.GetDllVersion();

var currentVersion = ConfigurationManager.AppSettings[versionAppsettingKey];

if (currentVersion != dllVersion)
{
    var config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
    config.AppSettings.Settings.Remove(versionAppsettingKey);
    config.AppSettings.Settings.Add(versionAppsettingKey, dllVersion);
    config.Save();

    MigrationHelper.HandleMigrations(new Version(currentVersion));
}

This code handles the comparison of the DLL version and the AppSetting. It uses some code that lives in our next class, the Migration Helper.

Migration Helper 

This class will handle the detection of migrations that exist and handle getting the DLL version. It relies on discovering implementations of the IMigration interface:

using System;
using System.Diagnostics;
using System.Linq;
using System.Reflection;

namespace TestMvc2.Migrations
{
    /// <summary>
    /// Class that is used to facilitate the migrations.
    /// </summary>
    public class MigrationHelper
    {
        /// <summary>
        /// Handles the migrations by using reflection to grab instances of MigrationBase.
        /// </summary>
        /// <param name="currentVersion">The current version.</param>
        public static void HandleMigrations(Version currentVersion)
        {
            var migrations = typeof(IMigration)
                .Assembly.GetTypes()
                .Where(t => t.GetInterfaces().Contains(typeof(IMigration)))
                .Select(t => (IMigration)Activator.CreateInstance(t))
                .OrderBy(x => x.TargetVersion);

            foreach (var migration in migrations)
            {
                if (migration.TargetVersion > currentVersion)
                {
                    try
                    {
                        migration.Excecute();
                    }
                    catch (Exception ex)
                    {
                        //log the error
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Helper that gets the DLL version.
        /// </summary>
        /// <returns></returns>
        public static string GetDllVersion()
        {
            var asm = Assembly.GetExecutingAssembly();
            var fvi = FileVersionInfo.GetVersionInfo(asm.Location);

            return fvi.FileVersion;
        }
    }
}

Migrations

Now that we've got a mechanism in place to detect when to run migrations and how to find them, it's time to look at a real migration. For the purpose of this blog, let's create a sample table in our database that we'll use as the 'starting' table that we'll associate with version 0.0.1:

CREATE TABLE [dbo].[sample](
    [id] [int] NULL,
    [domain] [nvarchar](max) NULL,
    [path] [nvarchar](max) NULL,
    [legacy] [nchar](10) NULL
)

Now let's pretend our business requirement has change and it now requires us to drop the 'legacy' column. Now we could just go to the DB and drop the column. However there will be an amount of time where the code being run might have an issue with the column being there or not. It's safer to have the column be removed on startup before any other code is run. Let's see that migration that will be targeted for version 0.1.0:

using System;
using DAL;

namespace TestMvc2.Migrations
{
    public class TargetVersionZeroOneZeroMigration : IMigration
    {
        public System.Version TargetVersion
        {
            get { return new Version("0.1.0"); }
        }

        public void Execute()
        {
            using (var uow = new PetaPocoUnitOfWork())
            {
                uow.Database.Execute(@"
                    ALTER TABLE [Sample]
                    DROP COLUMN legacy
                ");

                uow.Commit();
            }
        }
    }
}

The assembly meta data will need to be altered so that its version is now 0.1.0. On application startup, the helpers will notice that the AppSetting is still set for 0.0.0 but the DLL is 0.1.0. As a result our migration will be run. Finally the AppSetting will be updated to 0.1.0. Any subsequent restarts will not trigger a migration unless a new difference in versions is detected.

Several migrations can be run if the several have been detected. One drawback to the code written as-is, is that it doesn't rollback the AppSetting value if an exception occurs during a migration.

Migrations can get do all kinds of things such as alter columns, change datatypes or even and/remove constraints. The next migration targeted for version 1.0.0 changes the data types:

using System;
using DAL;

namespace TestMvc2.Migrations
{
    public class TargetVersionOneZeroZeroMigration : IMigration
    {
        public Version TargetVersion
        {
            get { return new Version("1.0.0"); }
        }

        public void Execute()
        {
            using (var uow = new PetaPocoUnitOfWork())
            {
                uow.Database.Execute(@"
                    ALTER TABLE [Sample]
                    ALTER COLUMN domain NVARCHAR(75)
                ");

                uow.Database.Execute(@"
                    ALTER TABLE [Sample]
                    ALTER COLUMN path NVARCHAR(255)
                ");

                uow.Commit();
            }
        }
    }
}

The migrations I've created can only upgrade whereas the Umbraco Project is setup to handle downgrades. You can modify this code to suit your needs. Once you get to this point, adding a new migration just involves implementing a new IMigration and adding a test.

Integrations Tests

Now that we can perform migrations, it'd be really awesome to make sure they will work in a test DB before attempting on a production box. For these example tests, we'll be using NUnit. Rather than go into the details of how NUnit works, we'll just jump into our first test:

using DAL;
using NUnit.Framework;
using TestMvc2.DAL;
using TestMvc2.Migrations;

namespace TestMvc2.Tests
{
    [Category("Migrations")]
    [TestFixture]
    public class ZeroOneZero
    {
        [TestFixtureSetUp]
        public void Setup()
        {
            PetaPocoUnitOfWork.ConnectionString = "testDb";

            using (var uow = new PetaPocoUnitOfWork())
            {
                //remove current tables
                if (uow.Database.DoesTableExist("Sample"))
                {
                    uow.Database.Execute(@"DROP TABLE [Sample]");
                }

                //add v0.0.1 table
                uow.Database.Execute(@"
                    CREATE TABLE [dbo].[sample](
	                    [id] [int] NULL,
	                    [domain] [nvarchar](max) NULL,
	                    [path] [nvarchar](max) NULL,
	                    [legacy] [nchar](10) NULL
                    ) 
                ");

                uow.Commit();
            }
        }

        [Test]
        public void Can_Migrate_To_Zero_One_Zero()
        {
            //assert table exists
            using (var uow = new PetaPocoUnitOfWork())
            {
                Assert.That(uow.Database.DoesTableExist("Sample"));
            }

            var migration = new TargetVersionZeroOneZeroMigration();

            migration.Execute();

            using (var uow = new PetaPocoUnitOfWork())
            {
                Assert.That(uow.Database.DoesTableExist("Sample"));

                var result = uow.Database.ExecuteScalar<int?>(@"
                    SELECT object_id FROM sys.columns 
                    WHERE Name = N'legacy' AND Object_ID = Object_ID(N'sample')
                ");

                Assert.That(result == null);
            }
        }
    }
}

Remember earlier when I said I wanted to make the unit of work stuff easier to unit test? Well I do it by setting the ConnectionString to the 'testDb' which I've setup as a completely empty DB. In your test project, you'll want to setup a connection string like this (and change the credentials to your real ones).

When this test is run, the test ensures that the previous schema is in place before performing a migration. After that it performs the migration then asserts that the column no longer exists. For the next migration we do a similar drill but instead we're checking that the data types are now correct:

using DAL;
using NUnit.Framework;
using TestMvc2.DAL;
using TestMvc2.Migrations;

namespace TestMvc2.Tests
{
    [Category("Migrations")]
    [TestFixture]
    public class OneZeroZero
    {
        [TestFixtureSetUp]
        public void Setup()
        {
            PetaPocoUnitOfWork.ConnectionString = "testDb";

            using (var uow = new PetaPocoUnitOfWork())
            {
                //remove current tables
                if (uow.Database.DoesTableExist("Sample"))
                {
                    uow.Database.Execute(@"DROP TABLE [Sample]");
                }

                //add post v0.1.0 table
                uow.Database.Execute(@"
                    CREATE TABLE [dbo].[Sample](
	                    [id] [int] NULL,
	                    [domain] [nvarchar](max) NULL,
	                    [path] [nvarchar](max) NULL
                    )
                ");

                uow.Commit();
            }
        }

        [Test]
        public void Can_Migrate_To_One_Zero_Zero()
        {
            //assert table exists
            using (var uow = new PetaPocoUnitOfWork())
            {
                Assert.That(uow.Database.DoesTableExist("Sample"));
            }

            var migration = new TargetVersionOneZeroZeroMigration();

            migration.Execute();

            using (var uow = new PetaPocoUnitOfWork())
            {
                Assert.That(uow.Database.DoesTableExist("Sample"));

                var sql = @"
                    SELECT CHARACTER_MAXIMUM_LENGTH
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE 
                        TABLE_NAME = 'Sample' AND 
                        COLUMN_NAME = 'domain'
                ";

                var result = uow.Database.ExecuteScalar<int>(sql);

                Assert.AreEqual(75, result);

                sql = @"
                    SELECT CHARACTER_MAXIMUM_LENGTH
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE 
                        TABLE_NAME = 'Sample' AND 
                        COLUMN_NAME = 'path'
                ";

                result = uow.Database.ExecuteScalar<int>(sql);

                Assert.AreEqual(255, result);
            }
        }
    }
}

These tests can be run independently of each other due to the individualized setup of each test. Running these tests ensure your migration will likely be successful. Please be sure that your tests do not point to a production or development server!

Though as always, make a backup of your DB before you execute on production!

Summary

The takeaway from this blog is that others have found pretty clever ways of adding some extra functionality to PetaPoco without having to go with a full-blown ORM like Entity Framework. I've stripped down everything so that this should work with most peoples implementations. Adjust the functionality to suit your needs. I have an entire test project here if you'd like to download it.

A big thanks to the Umbraco folks who run a fantastic open-source project allowing others to learn by example.