I’ll admit that I have a love/hate relationship with Unit Testing. Unit Testing has absolutely saved my bacon a number of times where a seemingly inconsequential change would have crippled our software. The reality is that the only way you can verify that those tiny changes don’t wreck havoc is to script your testing for known results. By having your build process validate those results you add a very strong safety net to your development process.
But… in most software systems there’s this pesky layer that throws a real wrench into the entire thing – it’s called the database.
The problem is this… a Unit Test is meant to be autonomous. It should be free standing and require no external dependencies. In reality the code you’re executing typically interacts with an existing database and performs INSERTS/UPDATES/DELETES to verify that certain functions still work. What get’s awkward is that the “Unit Test” database is typically an external dependency for your Unit Tests – it just has to be there be in the correct state or the Unit Tests will crash hard. Things like database schema changes, new testing datasets, and changing database locations really screw the unit tests up.
I’m going to describe a solution to this problem that we’ve been using for the last couple weeks. It’s worked extremely well by streamlining issues we had with databast style unit tests and by increasing our code quality. Everything here is based on Visual Studio 2005, nUnit, and nAnt but it’s more the concept that’s interesting.
How It Works!
We started off by adding a special project as part of each module called the Schema. Basically this is a simple class library with a bunch of embedded SQL scripts. These SQL scripts are responsible for taking a database and putting it into a known state for the Unit Tests. Execution of these scripts is done by a class called the SchemaManager, it has three methods which will become obvious in a minute.
The Unit Tests now add a reference to the Schema project. As a nUnit Setup Method we'll call out to a special class called the TestConfiguration. This TestConfiguration will be responsible for triggering the SchemaManager. Here's what a typical Unit Test in our UnitTest project would look like.
The TestConfiguration is a little more interesting. It uses a singleton pattern to create a SchemaManager, tell it to Drop the current Schema, and then Create it fresh. This executes the Drop Table/Procedure commands to clear out our database, and then uses the Create Table commands to make a fresh database.
Perfect! Now the database schema is in a state that our Unit Tests can work with – table are there, stored procedures are there, life’s good.
Now we need to create some canned data before we start our tests. Things like maintenance objects, customer codes, etc. are all expected to be in the database before our Unit Tests run so we need to create scripts in our UnitTest database which setup this data. Something like this...
We’ll embed this scripts into the UnitTest project and then change our TestConfiguration so that it gets the SchemaManager to insert this data after calling CreateSchema. We'll simply use the ExecuteResourceSql call we defined above and call it from our TestConfiguration.
And that's pretty much it! I think the beauty of this SchemaManager is that is makes the database a source-controlled resource. Instead of having some unknown dependency that the unit tests must rely on, the unit tests actually create their environment using scripts generated by the developer. If the developer breaks the Schema or the Business Logic, the unit test will catch it right away and any failed unit tests are expected to be logic errors instead of logistical problems between the development and unit test environments.
If you have any comments or suggestions on this area please drop me a line - it's a very interesting (and often underused) development practice!
PS - The foundation for a lot of this stuff is from a 4guysfromrolla posting, check it out for more details...
http://aspnet.4guysfromrolla.com/articles/040605-1.2.aspx