Unit Testing Through Database Migrations

Today I wanted to finish up on the concept of test data for Unit testing. We talked about using Table Value Functions or other SQL objects that may be called from our testable database code such as stored procedures. We talked about using schemas to dynamically point to a different instance of a table created with data specific for unit testing. Today I want to talk about using database migrations as a method for creating static re-usable test data.

One of the big problems we have when unit testing our procedural code is establishing a baseline against which to perform our tests. Perhaps these could be called system tests instead. But, if you want to test a procedure for Inser/Update/Delete or something else altogether, you need to be able to establish, prior to the execution of the test, data conditions allowing you to perform the tested function.

If you are wanting to test a procedure creating a user, either your test must create a new dynamically assigned user, or the static user you are going to create must not already exist. The dynamic method is preferred on my part because there is less work required to get things going. What this means is that not only my execution of the test needs to be dynamic in some way, but the assertion must also be dynamic in a way that matches. So, creating a user you might have a unique ID for them in your table. If you can create the ID you may be able to do something with the current date and/or time to create a new instance every time the test is executed.

Another way of handling this issue is to have a pre-process delete the existence of a static user designed for your test. This is more difficult to do when referential integrity is involved.

My favorite method is to use database migration scripts. We already have to maintain database migration scripts to be able to apply changes to our production system when a new release is promoted, in order to keep our code and database synchronized. So, for unit testing I like to maintain a set of migrations that are only used in my unit test environment. These migrations do whatever is necessary to enable my unit testing, or even system testing. With these migrations in hand, I can restore a backup from my production environment, apply my database migrations, and apply my test migrations. At that point I have a new database ready for Automated Unit testing and System testing.

If it hasn’t been made clear, I am assuming you are doing all of this in order to enable automated unit testing. If you aren’t doing the automated testing, then the value of this work is diminished. If you are a believer in unit testing, and find the practice applies to database systems as well, this some of the different implementations may be of great value to you.



  • Marc L

    I’m relatively new to the “migration first” method, so how’s this: for the unit-test environment, we use the “dynamic” method above–all data that is inserted or modified for a given test is isolated to that test. However, instead of working from a backup or anything like that, we work directly from the migrations: at the beginning of the unit test run, a “from scratch” migration is run *once*. Since all the tests are (or should be!) isolated from one another, they can all run within the same test-run instance. We find this is a good way to test both migrations (initially, obviously we’re not testing the cases where data is already present) and the application functionality. Thoughts?