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:
- Fire your database management app
- Connect to the database server
- Browse to the Post table
- Click in the menu to edit table schema
- Type : Note
- Select : varchar
Plus you have to write the migration code after anyway.
If you tough of some other tips let us know!