FluentMigrator getting started

I realized on the fluent migrator mailing list today that even though it’s been around for nearly two years, I’ve never written any blog posts describing what it is or example of how it works.

There is some documentation with a very basic walkthrough here: https://github.com/schambers/fluentmigrator/wiki

Brief history of FluentMigrator

When Ruby on Rails first became big everyone grew accustomed to their migration framework that was baked in called Rails migrations. Migration frameworks quickly popped up in other areas and languages. Fast forward a brief time and Nate KohariJustin Etheredge and myself threw together FluentMigrator. Initially, the three of us never took it to completion and some gracious community members took it to working completion.  Around the same time I was finally in a position to use it myself at a new workplace.

Over the last 12 months, there has been several new contributors and many bug fixes from the community. The community and usage of FluentMigrator has finally reached a place where it’s a living open source project.

What is a migration framework?

First things first. Many people aren’t really familiar with what a migration framework is or if they need one. Many shops I’ve been at have come up with their own way to version the database, many times only forward through time.

The concept is fairly straightforward. Any modification to your database warrants a “Migration”. This migration has both an “Up” method (forward through time), and a “Down” method (backwards through time). With the use of commandline, nant, msbuild you can automate the migration process during deployments to different environments. The migration framework stores a table in each database (by default FluentMigrator calls it’s table “VersionInfo”, in which it stores what migrations have been applied to that database), with this information, the framework can determine what migrations need to be applied to that database (along with what version you told it to go to on the commandline) and will then execute each migration in succession that it needs to apply.

This has some interesting benefits. Now that we have both a forward direction and a back direction, rolling back a database change is as easy as asking the migration framework to go “back” one or more versions. You could even go all the way back to version 0 (a completely empty database) if you wanted to.

FluentMigrator has a fluent interface for manipulating the database at an abstracted level. Because of this database abstraction we’re able to convert the semantic model built with the fluent interface into database commands on multiple different vendors. Currently FluentMigrator supports MSSQL 2000, 2005, 2008, Jet (Access), MySQL, Sqlite, PostgreSql and Oracle. Implementing a new vendor is as simple as adding your own visitee that gets called via the visitor when a migration is interpeted.

Enough with the jibber jabber. How about some samples?

Creating a table with columns, one of them being a primary key

[Migration(201104021153)]
public class CreateUsersTable : Migration
{
	public override void Up()
	{
		Create.Table("Users")
			.WithColumn("UserId").AsInt32().PrimaryKey().Identity()
			.WithColumn("Name").AsString()
			.WithColumn("PhoneNumber").AsString();
	}

	public override void Down()
	{
		Delete.Table("Users");
	}
}

Some things to note here:

Every migration is a normal csharp class. I usually place all my migrations in one assembly, I’ve seen other people place them where their data related code is. It’s up to you where they go.

Every migration needs a unique identifier in the Migration attribute. This number is interpeted as a long, so we usually use the format of YYYYMMDDHHMM, the chances of two developers creating a migration at the same exact minute are very slim. If we had just normal incremental numbers, we run the chance of two developers of stepping on each other with migration numbers.

Each migration must derive from the “Migration” class. This will make you implement both the Up() and Down() methods. As you can see, Up contains the migration code to go forward in time, Down() is the code that is what you would need to do, to undo the Up migration. Simple enough.

Changing a column name on Users table from “Name” to “FirstName”

[Migration(201104021208)]
public class RenameColumn : Migration
{
	public override void Up()
	{
		Rename.Column("Name").OnTable("Users").To("FirstName");
	}

	public override void Down()
	{
		Rename.Column("FirstName").OnTable("Users").To("Name");
	}
}

As you can see, on the Migration base class, there is a number of properties that allow you to begin the fluent interface chain. The most common hooks are Create, Delete, Rename, Insert and Execute.

We use the Execute command to call into sql scripts in instances where we need to do something more complex that the fluent interface doesn’t handle. Usually this is some form of changing data or moving data around that would be difficult with the fluent interface. Execute has two methods: Execute.Sql(“sql string”) and Execute.Script(“myscript.sql”).

That’s enough for a primer now, next time we can dive deeper into how we can run the migrations from commandline/nant.

In closing, here’s some links to FluentMigrator:

Till next time!

Related Articles:

    Post Footer automatically generated by Add Post Footer Plugin for wordpress.

    About Sean Chambers

    I am a Senior software developer from Palm Coast, Florida. An advocate of Domain Driven Design, Behavior Driven Development, creator of FluentMigrator and community activist. I am married to my beautiful wife Erin and am the proud father of two wonderful children. I currently reside at ACI, a local insurance industry/mortgage software company that excels in creating solutions using Agile methodologies.
    This entry was posted in fluentmigrator. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
    • Pingback: The Morning Brew - Chris Alcock » The Morning Brew #826

    • http://profiles.google.com/yadazula Gokhan Demir

      great project, like the codebase.

    • Perets7

      Thanks…it needed a good introduction…please keep up with the posts

    • http://profiles.google.com/kassem.darwish89 Kassem Darwish

      Does FluentMigrator work with Entity Framework 4.1 Code First? If it does, I think MANY developers (including myself of course) would be VERY thankful!!

      Well done guys, keep it up!

      • Anonymous

        FluentMigrator really isn’t concerned with what ORM you are using. It meets the need of versioning your database schema. It is used to take snapshots of your schema at specific points. Your orm however will change as your entities change.

        I guess I don’t see your point or how fm would fit into EF. Can you elaborate?

        • http://www.pagedesigners.co.nz/ CVallance

          As Sean says, FluentMigrator (FM) isn’t concerned with what ORM you are using, but I thought I’d share how I’m using FM with the EntityFrameworl (EF) 4.1.

          Basically I’ve created a custom IDatabaseInitializer that creates a RunnerContext and then spins up a TaskExecutor to execute it. So when ever my web application starts up, it’ll run any migration automatically.

          It works great for my little project!

    • Wes Mcclure

      FWIW: why reinvent the wheel when we have SQL for migrations?

      We wrote and use this and it’s worked for every scenario we have ever conjured up in SQL Server: http://tsqlmigrations.codeplex.com/

      It’s just a wrapper around doing things via the SMO api and writing migrations as sql scripts.

      • Anonymous

        That would work as well.

        However, FM supports many more databases than only sql server as stated in the post. The link you posted will only work for MSSQL.

        In addition, I personally don’t like writing sql. I rather write code that I can easily parse to sql.

        Personal preference I guess.

    • http://profiles.google.com/rkeurel Kris Garcia

      Looks very promising and I’m glad to hear that the project is gaining momentum. I hope that you get around to writing a post as to how one might approach deployments with this tool. Certainly there are many considerations that would depend on one’s environment, but I would be interested in hearing/reading about your experiences and approach regarding deployments with FluentMigrator. Keep up the good work.

    • http://www.hollman-alu.nl Aluminium Kozijnen

      Of course.. This has a lot of interesting benefits.. Thanks for sharing this codes..

    • Anonymous

      Hi Sean, did you get my email asking for the world? :)

    • Pingback: Migrations with Rake, Albacore and FluentMigrator | danlimerick

    • http://matfiz.myopenid.com/ Konstantin Spirin

      Can it generate Down actions based on Up actions (at least for CREATE)?

      • Lukas Pöllabauer

        Derive your migrations from AutoReversingMigration

    • Thomas Eyde

      How can I run the migrations from my unit tests?

    • Suresh

      I mistakenly typed column name as “ColumnName ” (blank space at last) but not able to rename using
      Rename.Column(“ColumnName “).OnTable(“TableName”).To(“ColumnName”);

      Drop and Recreate is not what I want.

    • http://blog.benpowell.co.uk Ben Powell

      I’m struggling to grok this. I have an existing database. I need to import my current schema and then I can use the fluent interface to write upgrade scripts. I then need to provide my ops team with a bunch of SQL scripts to deploy to our UAT and LIVE servers.

      The bits I don’t get are importing current schema (and some data). Secondly, how do I compile a bunch of deployment SQL scripts for my ops team?

      • seanchambers

        You should put a question up on the google group. That’s the best place to get guidance.

    • Emerson Beims Coimbra

      I´m used a extended property “MS_FieldValues”, to save a permited values in my columns. Can i extend WithColumnDescription and use MS_FieldValues ?