Changing Database schemas & unit tests

Question

     Before we start I know a fair few people consider tests that hit the database not "unit tests". Maybe "integration tests" would be a better name. Either way developer tests that hit the database.

     To enable unit-testing I have a developer local database which I clear and the populate with a know set of data at the start of each test using dbUnit. This all works well enough until a table used by the test changes in some way and I have to manually update all the XML datasets. Which is a pain. I figure other people must have hit the same problem and hopefully found a nice neat solution to it. So for tests that require populating a database what do you use and how do you handle table definitions changing? (While I use Java I am open to solutions utilizing different technologies.)

EDIT: To clarify a little. I have a contrived test like:

void testLoadRevision() {
    database.clear(); // Clears every table dbUnit knows about.
    database.load("load/trevision.xml", "load/tissue.xml");
    SomeDatabaseThingie subject = new SomeDatabaseThingie(databaseProvider);
    Revision actual = subject.load();
    assert(actual, expected);
}

In that I have two tables - tRevision and tIssue. A loaded revision uses a small amount of data from tIssue. Later on tIssue acquires a new field that revisions do not care about. As the new field is "not null" and has no sensible default this test it will fail as the tIssue.xml will be invalid.

With small changes like this it is not too hard to edit the tIssue. But when the number of XML files starts to balloon with each flow it becomes a large amount of work.

Cheers,
    mlk

Answer 1

Well, as I see it, it is a matter of combining what is already there.

The scenario described above:

1. Write a database migration
2. Apply the database migration (manually or automatically at the start of the test run)
3. Watch your tests break due to a constraint violation (not null)

You could extend it so that you a small program that does the following:

1. Populate the database with the DbUnit XML
2. Apply the database migration
3. Extract the contents of your database in-place in your DbUnit XML (and optionally also the DTD) (See DbUnit Home Page -> DbUnit FAQ -> How to extract a flat XML dataset from my database?)
4. Check your updated DbUnit XML (and DTD) into source control.

For applying the migration, I heartily recommend Flyway. It supports both Sql (with placeholder replacement) and Java-based migrations. You can then apply the migrations using the Maven Plugin or programmatically using the API. The latter fits this case perfectly.

The complete workflow then becomes:

1. Write your database migration
2. Execute your DbUnitXmlDtdUpdater program
3. Watch your unit tests pass

Happy days,

Axel

Disclaimer: I am one of Flyway's developers.