How do I test database migrations?
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
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.