How do I test database migrations?

Question

I'm using Migrator.NET to write database migrations for the application. Marc-André Cournoyer wrote:

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.

How do I do that? Say I have the Up() method which creates a table and the Down() method which drops the same table and I'm using SQL Server. How would a test look like? Should I be running SQL query against the system tables, like select * from sys.columns, to check if the table was created and that it has the proper structure? What if we're using NHibernate?

EDIT I mean migrations in the Rails ActiveRecord Migrations sense (creating, modifying and tearing down databases in small steps based on C# code).

EDIT 2 And here's where I read about that we should test migrations. The blog post is actually linked from Migrator's wiki.

Answer 1

Do you test your DAL - some sort of integration test?

You need more than a migration script, you also need a baseline script. When you want to test a database upgrade, you should run all the scripts from the baseline on a testing/staging server to create the newest version of the database. Then test your DAL against the up-to-date test database. If all the DAL tests succeed then your migration should have been successful (otherwise your DAL tests are not complete enough).

It's an expensive test to run, but it's pretty much rock solid. I'll personally admit to doing a lot of this manually at the moment; we have an in-house migration tool that will apply all scripts (including the baseline), so the test database setup and DAL tests are separate steps. It works though. If you want to make sure that a table was created, there's no better method than to actually try to insert data into it!

You can try to verify the results by looking at system catalogs and INFORMATION_SCHEMA views and so on, but ultimately the only way to be sure it's actually working is to try to use the new objects. Just because the objects are there doesn't mean that they're functional.

Answer 2

maybe this scrip can help you :

http://www.benzzon.se/forum/uploads/benzzon/2006-03-27_134824_sp_CompareDB.txt

this script compare two db.(structure and data)

Answer 3

Source control is for taking a snapshot of your current code base. Migration is for moving changing your database from one version to the next. So that at some future point you can take an old database, apply migrations and work with the latest code base.

I've never seen the actual migrations tested. I have seen the results tested, and they have caught/reminded me to run the latest migrations.

describe User do
  it { should have_column :name, :type => :string }
  it { should validate_presence_of :name}       
end

So someone changes the model. Adds a test to reflect the model. Adds the migration. Then commits the source.
You grab the latest, run tests. Tests fail because the database doesn't correspond. You remember to run migrations, then rerun tests. Success.