In response to using database migrations for unit testing, Marc L writes,
“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?”
Marc, I’m really glad you went here. Here’s a perfect world scenario in my experience. When you start a brand new database, it is literally that. You don’t work with backups or restores, save points, copying from other environments. You start with database migrations.
The first database migration you make is the one that creates the database. You can start building from that point on adding security objects, users, schemas, roles. With that foundation you start adding other schema owned objects. If you create lookup tables, database migrations are created that populate the lookup data. All of this work is done through database migrations.
This provides you with a skeleton database read to go for any purpose. If you need to make a new production version, you build one with the create migration scripts.
What’s really neat about using migrations is that they can be versioned if you use the right tools such as FluentMigrator as a simple open system tool. What you are looking for in a tool is the ability to apply migrations to a point that supports a version of your application. Using database migrations you should be able to create a database supporting any deployable version of your application, because you can stop applying your migrations at any point. This allows you to build a version 1 of your database by getting all of the migrations in version control that were checked in up to the application code of version 1. A single version control branch is all you need in order to determine what migrations to run.
Now, lets talk about unit testing. In this case, you can’t simply work from the skeleton of a new database. You need instance data in order to exercise the code you are testing. A unit test is easiest to maintain if it creates all of its expectations as part of the test execution. In this case, if you wanted to test deleting a user, the test would first create the user to be deleted. The test would test deleting the user.
Some scenarios are require too much setup in order to do this for each individual test. If that is the case, I like having a separate set of migrations that create large amounts of data, specifically for the purpose of enabling a database to be tested with many different scenarios, instead of creating data one test at a time.
Again, b using migrations, you can have this be a constantly evolving database so that you can pick any version of your application, and the unit tests it contains, create a database for that point in source control, and validate that your tests continue to work.
If you didn’t pick it up, the key aspect to making this all work is version control. All of the code, the database migrations, the application code, the application unit tests, integration tests and system tests, along with your database tests must be in a single repository, and versioned together, because they all must synchronize as a single system in order to work.
Thanks Mark, for the great lead.