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

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 2

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.

Answer 3

You COULD do a comparison of database system objects, but you would need to have a target against which to compare - otherwise how would you know if passed or failed?

I think you may be better off creating a set of edge case CRUD operation test cases that exercise the entities or operations in the data layer. If any of these fail, the database is not in sync with what is required. i.e. if the insert of an field char(20) fails because it is only char(15) in the database. Then the db structure comparison can be done to see what if off.

You may be able to short circuit this by focusing only on the recently changed items, and assuming prior changes have been applied.

Answer 4

Treat migrations testing as part of your overall persistence testing strategy if using NHibernate, i.e. if you can create and save all of your entities without any errors, your database and your mappings should be correct.

Answer 5

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 6

I'm looking for an answer to this as well. I think this should be tested in an integration environment rather than a unit test one: For unit tests (DAL) I drop the database and re-create it.

However, ideally I'd like to have an integration environment were my DB is replicated from production and DB migration scripts run both ways: Upwards to ensure a smooth upgrade of production and Downwards to ensure rollbacks are possible.