Database migrations in code – Part 2: RikMigrations

A few days ago I started trying some frameworks for maintaining my database migrations in code. I tried Migrator.NET and it didn’t quite ring with me. Now, I’ve been trying RikMigrations which I initially liked better.

RikMigrations does the same as Migrator.NET, it allows you to define your database in code and define the migration steps to bring a database from one point in the development to another, and back again.

   1: public class Add_Event_Table : IMigration

   2: {

   3:     public void Up(Schema db)

   4:     {

   5:         Table t = db.AddTable("Event");

   6:         t.AddColumn<int>("ID").PrimaryKey().AutoGenerate();

   7:         t.AddColumn<string>("Name", 256).NotNull();

   8:         t.Save();

   9:     }

  10:     public void Down(Schema db)

  11:     {

  12:         db.DropTable("Event");

  13:     }

  14: }

Each migration step is a class implementing the IMigration interface: an Up() and a Down() method. Those methods contains the steps to move the database forward or backwards in the development, respectively. I haven’t used Ruby on Rails but from what I’ve seen, much of the inspiration from RoR.

The advantages for a tool like this is that your database is under source control, it’s easier for multiple developers to work on the same code and make changes to the database and you can roll back the database to a known state and point in time. You can also use it to deploy changes to the database into a test, QA or even production environment.

You add multiple classes for multiple migrations and specify the order of the migrations in assembly directives:

   1: [assembly: Migration(typeof(Add_Event_Table), 1)]

   2: [assembly: Migration(typeof(Alter_Event_Table_Add_start_and_end_date), 2)]

   3: [assembly: Migration(typeof(Alter_Event_Table_Add_Description), 3)]

   4:  

   5: namespace GeekBeer.Data.DBMigration {

   6:     public class Add_Event_Table : IMigration

   7:     {

   8:         public void Up(Schema db) ...

   9:         public void Down(Schema db) ...

  10:     }

  11:  

  12:     public class Alter_Event_Table_Add_start_and_end_date : IMigration

  13:     {

  14:         public void Up(Schema db) ...

  15:         public void Down(Schema db) ...

  16:     }

  17:  

  18:     public class Alter_Event_Table_Add_Description : IMigration

  19:     {

  20:         public void Up(Schema db) ...

  21:         public void Down(Schema db) ...

  22:     }

   1: protected void Application_Start()

   2: {

   3:     // Run the database migrations, making sure that the database is up to speed. 

   4:     var rootWebConfig =

   5:         System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/");

   6:     var connString = rootWebConfig.ConnectionStrings.ConnectionStrings["GeekBeerConnectionString"];

   7:     Data.DBMigration.MigrateDatabase.Migrate(connString.ConnectionString);

   8: }

   9:  

  10: public class MigrateDatabase

  11: {

  12:     public static void Migrate(string connectionString) {

  13:         DbProvider.DefaultConnectionString = connectionString;

  14:         DbProvider db = new MssqlProvider(null);

  15:         MigrationManager.UpgradeMax(Assembly.GetExecutingAssembly(), db);

  16:     }

  17:  

  18: }

Works like a charm.

Is it useful?

Short answer, for me personally: No. YMMV.

I’m pretty comfortable with creating and changing SQL tables, columns and relations in SQL Management Studio. I’m not that comfortable with doing it in what’s effectively a new DSL for creating and editing databases. It adds friction that I don’t need and I don’t really see the need to learn a new language for SQL editing. SQL does that job pretty well and with SQL Management Studio I can use designers and have my change code generated for me (though it’s not always that nice code…)

It would most likely get easier over time, most likely, but I have other things I feel is more important to learn. There are also things that are not easily expressed in the current API, such as indexes.

This is also a problem with Migrator.NET which I tried last time.

The concept of database migrations is still interesting to me though, but I think I’m going to go for one of the solutions that uses plain SQL scripts for the migration. I think I’m going to try DBDeploy.NET next.