Category Archives: Migrator

Database migration tool for .NET based on Ruby on Rails migration

Agile Database fun with the Migrator

Since I released the Database Migrator for .NET, a lot of people have asked me if I’ve used it on real/large/live projects and how was my experience. Like any tools, it can be good and … it can be bad. Here’s a couple of guidelines or what some would call “Good Practices” (note my voluntary abstinence over the incorrect “Best Practices” term).

Coming from hand-written SQL script to beautifully manage database migrations is a big step towards programming nirvana. But like the use of any revision control tools you ought to follow some guidelines.

Tip #1 : Test your migrations

Like any code in your application you must test your migrations. Ups and downs code. Do it part of your continuous build process and test it on as many different databases and environment as you can.

Tip #2 : Never edit a released migration

Once your application is released you should never alter it. Doing so would permit that two different database schema have the same version number, which is purely evil and can lead to programming darkness.
A good way to enforce this practice is to allow modifications to the migrations as long as they are on a feature branch or on the trunk and not on a release branch.
I guess one could code a Subversion pre-commit hook for this.

Tip #3 : Keep them short and sweet

Don’t create one migration for version 1.0 of your app. Split it in as many migrations as possible (be logical here). Adding a table ? 001_CreateUserTable, Removing a column ? 002_RemoveSomeColumn. This is simple SoC.
It also ease-up debugging when a migration fails and you only have 3 lines of code in there.

Tip #4 : Be migration-driven

Stop using any GUI to alter the database. Code it!
As a coder you should find that writing:


generate migration AddPostNoteColumn
edit db/migrations/004_AddPostNoteColumn.cs
...
Database.AddColumn("Post", "Note", typeof(string));
CTRL+S
nant migrate

is a lot faster then:

  1. Fire your database management app
  2. Connect to the database server
  3. Browse to the Post table
  4. Click in the menu to edit table schema
  5. Type : Note
  6. Select : varchar
  7. CTRL+S

.
Plus you have to write the migration code after anyway.

If you tough of some other tips let us know!

22 Comments

Filed under C#, Migrator

migrate NAnt task

Just added an NAnt task to the migrator project. So if you would like to use it without the Castle Generator you could use this simple NAnt task on your own super projects:

<loadtasks assembly=".../Migrator.NAnt.dll" />
<target name="migrate" description="Migrate the database" depends="build">
  <property name="version" value="-1" overwrite="false" />
  <migrate
    provider="MySql|PostgreSQL|SqlServer"
    connectionstring="Database=MyDB;Data Source=localhost;User Id=;Password=;"
    migrations="bin/MyProject.dll"
    to="${version}" />
</target>

Note that this version of the Migrator as not yet been updated in the Castle Generator libs because some work is still in progress for next release (something like SQLite support and other cool stuff) so you have to get the source and build it yourself from http://code.macournoyer.com/svn/migrator/trunk/.

Thanks to all people supporting the project!

5 Comments

Filed under Migrator