C#/SQL Integration Testing With NUnit

Just over 18 months ago I wrote a post about Integration Testing using NUnit, and at the end I stated that I would cover database integration testing in a separate post. Well here it is. Finally!

The first thing to clear up is what scope of testing we’re covering here because it seems that many teams test their database entirely through their Data Access Layer. Now, I’m not saying that’s wrong, but in “You Write Your SQL Unit Tests in SQL?” I describe the reasons why I believe that it’s not the only approach and that in fact there is great value in doing it separately. So, in this post I’m starting out with a fundamental assumption that you’ve already unit tested both your SQL and C# code and that what we’re doing here is building on that level of trust.

Testing what then?

If we’re not testing the behaviour of our SQL or C# code then you may rightly ask what exactly are we testing? Well, as the name Integration Test implies we’re focusing on the interaction between our C# and SQL code. What sits between these two worlds is the client-side database API (which itself is probably layered) and the database server - a lot of code. Not only that but these two worlds have different ideas of how numbers and strings are represented and errors handled.

There are two kinds of common errors that can only come out at this level of testing (and higher) - schema changes and data type mismatches. The former is essentially a breaking change in the database’s public interface, such as the renaming or re-ordering of parameters and result set columns; the breakage can be quite subtle sometimes[*]. A draconian change policy would try to ensure this never happens, but then that also closes the door to refactoring the database schema too. The second common error revolves around data representation, with floating-point numbers being the prime example as databases often have a far more flexible way of defining the scale and precision of non-integral type numbers. Unexpected trailing whitespace caused by the use of a char(n) instead of varchar(n) type can cause surprises too.

These effects are made all the more apparent when you have separate SQL and C# developers because their timescales may differ, along with their code commits, and that can cause feature-level integration testing to be passed up in favour of going straight to system-level testing.

Schema only, no data

It is a pre-perquisite that before you can run your C# tests against your database you must ensure that it is in a known empty state. This means that you cannot just restore a production database, unless you also empty it of data. The alternative is to build it from scratch before running the entire test suite. And that is what we do.

However, because this can take some time we optimise the process. The Continuous Integration build first builds the database and runs the set of SQL tests, then it builds the C# code and runs those tests too. Finally it re-uses the SQL unit test database to run the integration tests, and because this is one single build & testing sequence the SQL/C# source code is guaranteed to be consistent (assuming the check-ins are themselves atomic and consistent at feature-level).

For our day-to-day development we optimise the process even further by creating a one-off backup of the current production schema and then restoring that each time we want to run the integration tests (after having applied any new patches). This restore & patch approach takes just minutes compared to the length of time it takes to create the database from scratch.

The test framework

The tests themselves are written almost exactly as you would write any other in NUnit, but we have our own helpers which are exposed via a base class to work around some of the problems inherent in using a unit testing framework to do other sorts of testing. For example the biggest problem is that you have the large dependency that is The Database to somehow configure for use within the test, and for this we use an environment variable. I generally dislike any sort of configuration like this normally[+], but it’s a necessary evil, particularly when you can’t just assume that every developer (and the build machine) will be using a local copy of SQL Server Express. By default we all have a PERSONAL_DATABASE variable configured that we use day-to-day and can be discovered even if testing through Visual Studio/Resharper. If the need arises a batch file can always be used to redirect the tests to another instance with little fuss.

The skeleton of a test looks something like this:-

[TestFixture, Category(”Database”)]
public class CustomerDatabaseTests : DatabaseTestBase
{
  [Test]
  public void Add_ShouldInsertCustomer()
  {
    using (var connection = Connection)
    {
      . . .
    }
  }
}

As you can see we use a separate category for the database tests so that you only run them when you know your “personal database” is correctly built. The fixture derives from the DatabaseTestBase class as that is how all the helpers are exposed, such as the Connection property that reads the connection string stored in the PERSONAL_DATABASE variable and serves up a freshly opened connection for it.

One design constraint this implies is that we use Parameterise From Above, rather than ugly Singletons to get our connection passed to the code under test. This a good design practice anyway and we exploit that further by ensuring that we control the outer transaction that surrounds it. This is made possible because we also have our own thin wrapper around the underlying .Net database classes. Controlling the real transaction means we can undo all changes made in the test by simply rolling back the transaction no matter which way the test ends (e.g. intercepting Commit()).

Sadly the need to manually scope the connection with a using() requires a level of discipline when writing the test as otherwise the effects can leak across tests. The alternative, which has yet to be implemented by us, is to use that old stalwart Execute Around Method so that we can add as many smarts as we need in the helper method to ensure the test is as isolated as possible. This would make the test look like this instead:-

[Test]
public void Add_ShouldInsertCustomer()
{
  Execute
  (
    (connection) =>
    {
      . . .
    }
  );
}

Arranging

The first part of any test “dance” involves the arrangement of the dependent data and objects - which is hopefully minimal. With databases though it is common practice to use referential integrity to keep the data sane and so that may mean you have add some static data to avoid falling foul of it; it’s either that or drop the constraints which are there exactly to help find bugs.

NUnit supports both fixture and test level SetUp() methods to help keep them readable by factoring out common code. As a general rule we have used the fixture level setup for static (or reference) data that is orthogonal to the test and then used the other method or the test itself for data that is directly relevant to it. You often find yourself inserting the same static data time and again and so you have a choice of whether to create a C# based helper class or create some helper stored procedures in a separate schema so they can be shared across both the SQL and C# worlds, e.g.

[TestFixtureSetUp]
public void FixtureSetUp()
{
  using (var connection = Connection) 
  { 
    connection.Execute(“exec
                      test.InsertCustomerAndDetails
                      (1, ‘Bob’, ‘London’, . . .);”);
  }
}

Acting

The second part of the dance - acting - was pretty much covered in the overview of the framework as you just need to get your test controlled connection into the code under test.

Asserting

The final step is to verify the outcome by writing a bunch of asserts. If you’re just reading data then you can use the same style of setup shown above and then use the normal features of NUnit that you’d use to compare in-memory based sequences. But if you’re writing data then it’s a slightly more complicated affair and this is another area where a bunch of helpers are required. We still use the basic Assert mechanism, but invoke custom methods to query aspects of the data we expect to have written, e.g.

Assert.That(RowCount(“Customer”), Is.Equal.To(1));

Depending on the system you’re building you may be able to round-trip the data and get two tests for the price of one, as long as any caching is disabled between the write/read calls:-

{
  var expected = new Customer(1, . . .);

  dataMapper.InsertCustomer(expected);
  var actual  = dataMapper.FindCustomer(expected.Id);

  Assert.That(actual.Name, Is.EqualTo(expected.Name));
}

However testing more than one thing at once is generally frowned upon, and rightly so because you don’t know whether the read or the write action failed. But you can also end up duplicating a lot of SQL in your test code if you don’t leverage your own API and that creates a test maintenance burden instead. If you’re purely writing data then you may have little choice but to write some form of query:-

{
  . . .
  string actual = String.Format(“SELECT count(*) FROM 
              Customer WHERE Id={0} AND Name=’{1}’”,
              expected.Id, expected.Name);

  Assert.That(RowCount(“Customer”), Is.Equal.To(1));
  Assert.That(QueryResult(actual), Is.Equal.To(1));
}

The NUnit constraint model allows you to build your own more fluent style of interface so that you can avoid hand cranking SQL if you prefer:-

Assert.That(Table(“Customer”).HasRow.Where(“Id”).Equals(expected.Id).And(“Name”).Equals(expected.Name).Go());

One of the benefits of reusing the SQL unit test database is that you’ll be DBO and as such you’ll be able to exploit your god-like status to allow you to get access to the internals and write this sort of test, even if you’ve carefully constructed a tight interface to the database. It may feel painful writing out such long-winded queries but if you’re trying to ensure you maintain high-fidelity of your data through all the layers is there any alternative?

 

[*] The perfect example of a subtle breaking change comes with SQL Server and OLE-DB. In SQL Server any procedure parameter can have a default value, but with OLEDB defaulted parameters have[**] to be at the end of the argument list. A SQL developer could quite rightly add a new parameter anywhere in the argument list and provide a default value to ensure the interface appears to remain unchanged, but if its not at the end an OLE-DB based query would then fail.

[**] This may have been sorted now, but it was definitely still the case a few years ago.

[+] A new developer should be able to just install the version control client software, pull the source code (and preferably tools) locally and be able to build the software and run the tests out-of-the-box.

Unit Testing File-System Dependent Code

Way back last year before being distracted by my impending ACCU conference talk I wrote a post about integration testing using NUnit. At the time I was still in two minds about whether or not it was worth the effort trying to mock the file-system API, especially given that you often have some extra layer of code between you and the file-system to actually read and parse the file, e.g. an XML reader. The alternatives seem to be either to focus on writing integration tests that actually do touch the file-system (which is reasonably quick and reliable as a dependencies go) or injecting more abstractions to create other seams through which to mock, thereby allowing you to write unit tests that get you close enough but not all the way down to the bottom.

Of course if you’re creating some sort of data persistence component, such as the aforementioned XML reader/writer, then you probably have a vested interest in mocking to the max as you will be directly accessing the file-system API and so there would be a good ROI in doing so. What I’m looking at here is the code that lightly touches the file-system API to provide higher-level behaviour around which files to read/write or recovers from known common error scenarios.

Impossible or hard to write test cases

The main incentive I have found for making the effort of mocking the file-system API is in writing tests for cases that are either impossible or very hard to write as automated integration/system tests. One classic example is running out of disk space - filling your disk drive in the SetUp() helper method is just not a realistic proposition. Using a very small RAM disk may be a more plausible alternative, but what you’re really likely to want to test is that you are catching an out-disk-space exception and then performing some contingent action. The same can apply to “access denied”[*] type errors and in both cases you should be able to get away with simulating the error by throwing when the code under test tries to open the file for reading/writing rather than when they actually try to pull/push bytes to the file (this assumes you’re doing simple synchronous I/O).

The reason this makes life easier is that the file Open() method can be a static method and that saves you having to mock the actual File object. It was whilst discussing this kind of mocking with my new team-mate Tim Barrass that we made some of the existing API mocks I had written much simpler. Whereas I had gone for the classic facade, interface and factory based implementation without thinking about it Tim pointed out that we could just implement the facade with a bunch of delegates that default to the real implementation[+]:-

namespace My.IO
{

public class File
{
  public static bool Exists(string path)
  {
    return Impl.Exists(path);
  }

  public static File Open(string path, . . .)
  {
    return Impl.Open(path, . . .);
  } 
 
  . . .

  public static class Impl
  {
    public Func<string, bool> Exists =
                              System.IO.File.Exists; 
    public Func<string, . . ., File> Open = 
                                 System.IO.File.Open; 
    . . .
  }
}

}

The default implementation just forwards the call to the real API, whereas a test can replace the implementation as they wish, e.g. [#]

{
  File.Impl.Exists = (path) =>
  {
    return (path == @“C:\Temp\Test.txt”)
              ? true : false
  }
}

{
  File.Impl.Open = (path, . . .) =>
  {
    throw new UnauthorizedAccessException();
  }
}

This is a pretty low cost solution to build and may well suffice if you only have this kind of restricted usage. You can easily add a simple File mock by using memory based streams if you just need to simulate simple text or binary files, but after that you’re getting into more specialised API territory.

Replacing the file-system with strings

So what about the case where you are using a 3rd party component to provide simple serialization duties? I’m not talking about large complex data graphs here like a word document, but the simpler text formats like .ini files, .csv files or the <appSettings> section of .config files. If you’ve decided to leverage someone else’s work instead of writing your own parser it’s better if the parser exposes its behaviour through interfaces, but not all do. This is especially true in C++ where there are no formal interfaces as such and concrete types or templates are the norm.

However many text file parsers also support the ability to parse data stored as an in-memory string. You can exploit this in your testing by introducing a static facade (like that above) that encapsulates the code used to invoke the parser so that it can be redirected to load an “in-memory” file instead. This allows you to avoid the performance and dependency costs of touching the actual file-system whilst remaining in full control of the test.

namespace My.IO
{

public class XmlDocumentLoader
{
  public static XmlDocument Load(string path)
  {
    return Impl.Load(path);
  }

  public static XmlDocument LoadFromFile(string path)
  {
    // Load document via file-system.
    . . .
  }

  public static XmlDocument LoadFromBuffer(string 
                                            document)
  {
    // Load document from in-memory buffer.
    . . .
  }

  public static class Impl
  {
    public Func<string, XmlDocument> Load = 
                                       LoadFromFile;
  }
}

}

... and here is an example test:-

{
  XmlDocumentLoader.Impl.Load = (path) =>
  {
    string testDocument = “<config>. . .”;

    return XmlDocumentLoader.LoadFromBuffer
                                     (testDocument);
  }
}

Strictly speaking this fails Kevlin’s definition of a unit test because of the “boundary of trust” that we have crossed (into the parser), but we do control the test input and we should be able to rely on a parser giving consistent performance and results for a consistent small in-memory input and so we’re pretty close. Fundamentally it’s deterministic and isolated and most importantly of all it’s automatable.

With a more complex component like an XML parser it may even require a fair amount of work to mock even though you only use a tiny subset of its features; but that in itself may be a design smell.

Reinventing the wheel

The use of static facades is often frowned upon exactly because it isn’t possible to mock them with the non-industrial strength mocking frameworks. I’m a little surprised that the mocking frameworks focus all their attention on the mechanics of providing automated mocks of existing interfaces rather than providing some additional common facades that can be used to simplify mocking in those notoriously hard to reach areas, such as the file-system and process spawning. Perhaps these ideas just haven’t got any legs or I’m not looking hard enough. Or maybe we’re all just waiting for someone else to do it...

 

[*] Depending on the context an access denied could be an indication of a systemic failure that should cause alarm bells to go off or it could just be a transient error because you’re enumerating a file-system that is outside your control.

[+] As he was refactoring the lambda used for the initial implementation the notion of “methodgroups” suddenly came into focus for me. I’d seen the term and thought I roughly knew what it was about, but I still felt smug when I suggested it was a methodgroup a split second before Resharper suggested the same. Another +1 for Resharper, this time as a teaching aid.

[#] I doubt you’d ever really do a case-sensitive path name comparison, but hopefully you get the point.

Your Task Bar Can Hold More Than 7 Windows Open, but Can Your Brain?

Here is something that never fails to amuse me when watching other users[#] - the number of windows open on their desktop. The task bar is an interesting idea that has somehow managed to stand the test of time. Personally I miss the old 16-bit Windows desktop with all those neat little apps that had animated minimised icons like Coffee Mug, Cigarette, Bit Recycler and Tiny Elvis[+]. Exactly what metaphor is the task bar aiming at? Whatever it is it isn’t working because users are still moaning about the fact that they can’t see which icon is the one they’re looking for. And grouping similar icons is just a band aid that was only needed in the first place to overcome the ridiculous one-window-per-web-page model of everyone’s most hated web browser - IE6.

Here’s the thing though. You do know that you can close those windows, don’t you? You may have heard of The Window Tax, but that was an historical event and anyway it was based on the number of windows you had, not how many times you opened them. Making the task bar bigger or stretching it across multiple desktops still doesn’t make it any easier because the fundamental limitation is in your short term memory, not the size of your desktop or how many monitors you can wire up. A long time ago (but in the same galaxy) I went to university where I was taught that the brain can only store 7 “chunks” of short term information (give or take a couple of extra slots) and so you can open as many windows as you like, but you’ll never remember why you opened them all and that’s why the task bar looks cluttered and confusing.

My wife complains that my short term memory is more limited than that of a goldfish, but that’s not true. It’s just that it gets flushed whenever it encounters shiny things, and unfortunately the world is full of shiny things. Therefore I have a policy of closing windows the moment I believe I don’t need them anymore. This also has the jolly useful side-effect of meaning I don’t fall foul to those niggling reliability problems in applications caused by long-term use and I’ve never had the pleasure of (unintentionally) exhausting the Windows desktop heap.

I know these days everyone’s big on recycling and so shouldn’t I be leaving a few spare Explorer and IE windows around rather than expending fresh carbon on firing up new instances whenever the need arises? I don’t think so because most mainstream applications start up pretty quickly these days[~] and companies like Microsoft spend more effort on improving the warm start-up rather than the cold start-up time. Also by judicious use of favourites, network places, desktop icons, etc. I can navigate to the 80% of places I use most often within a few clicks which probably balances out the time lost “tool tipping” all the task bar icons looking for an existing window that I can reuse...

...Oh, wait, so that’s what the Search window in the Start menu is for - finding things on your task bar!

 

[#] And by users I mostly mean other developers. And my wife. And kids.

[+] I wonder what Toggle Booleans are up to these days?

[~] The one exception I have recently come across is the “Issue Management System” that my current client uses. This literally takes minutes to start up and is a truly painful tool to use, not least because every issue I’ve tried raising is met with an error message box that fills the screen and contains more SQL than our entire database!