Testing MySQL queries with NUnit

Even the most adamant unit-testing purist will admit that database queries need to be tested. If possible, these tests should run against the same database engine that will be used in production.

With a SQL Server database, testing queries is fairly easy, since most Visual Studio installations will include SQL Server Express. Just create the database in a SetupFixture, connect with Windows Authentication, and you're all set. MySQL presents a few more challenges, however. First, it needs to be installed. Second, the tests need the correct credentials to connect to the installed MySQL instance.

One simple way to handle this is to set up a central MySQL instance for testing only. However, this means team members must be on the network to run the tests. There is also the issue of multiple team members running the tests at the same time causing unexpected failures in each other's test runs, or worse, causing unexpected failures in an automated build.

A better alternative is to keep the MySQL binaries in version control with a known configuration that can be used in test runs. The binary distribution of MySQL is fairly large; however, with the right command line arguments, we only need two files: bin\mysqld-nt.exe and share\english\errmsg.sys (or whichever language you want to use). Include both of these as content files in a test project and set the build action to "Copy If Newer". In a SetupFixture, start MySQL with some code like this in the setup method:

var process = new Process();
var arguments = new[]
{
    "--standalone",
    "--console",
    "--basedir=.",
    "--language=.",
    "--datadir=.",
    "--skip-grant-tables",
    "--skip-networking",
    "--enable-named-pipe"
};

process.StartInfo.FileName = "mysqld-nt.exe";
process.StartInfo.Arguments = string.Join(" ", arguments);
process.StartInfo.UseShellExecute = false;
process.StartInfo.CreateNoWindow = true;

process.Start();

The first two arguments (--standalone and --console) tell MySQL to run as a standalone instance and to keep the console window open (i.e. do not run as a service). The next three arguments (--basedir=., --language=. and --datadir=.) tell MySQL to run from the current directory, load language files (errmsg.sys) from the current directory, and write data files to the current directory. The --skip-grant-tables argument disables security so that the tests do not need to worry about providing credentials when connecting. The final two arguments (--skip-networking and --enable-named-pipe) tell MySQL not to listen for TCP connections and instead allow named pipe connections. This prevents our standalone MySQL instance from interfering with any other MySQL installations on the machine.

Once the instance has started, we can connect with a connection string like this: Data Source=localhost;Protocol=pipe;. Finally, kill the MySQL process in the SetupFixture teardown method.

On my team, we have rolled this functionality (and a few other goodies) into a NUnit addin, but that's a story for another blog post.

Happy testing!

Over-testing: the data-driven edition

Learning to test at the right level of abstraction is tough. A common mistake involves the abuse of data-driven testing tools. Data-driven testing tools, like TestCase in NUnit, are convenient and powerful. You can write a lot of tests really fast, but if you're not careful, you can end up repeating yourself.

For example, suppose we define the following (woefully oversimplified) utility function to test if a string is a valid email address:

public static bool IsValidEmail(string email)
{
    return Regex.IsMatch(email, @"\w+@\w+\.com");
}

The test suite for this function should be complete. A data-driven approach works really well here:

[TestCase("a@test.com")]
[TestCase("1@test.com")]
//... lots more
public void TestValidEmail(string email)
{
    Assert.That(IsValidEmail(email));
}
[TestCase(null)]
[TestCase("not an email address")]
//... and so on
public void TestInvalidEmail(string email)
{
    Assert.That(IsValidEmail(email), Is.False);
}

Now, suppose we use this method in some application logic:

public void SendConfirmation(string message, string email)
{
    if (!IsValidEmail(email))
    {
        throw new ArgumentException("email");
    }
    SendEmail(message, email);
}

Awesome, let's test this bad boy... er, I mean here's the test we wrote before writing the method:

[TestCase("a@test.com")]
[TestCase("1@test.com")]
//... lots more
public void TestSendEmailIfValid(string email)
{
    SendConfirmation("test", email);
    //Assert sends email...
}
[TestCase(null)]
[TestCase("not an email address")]
//... and so on
public void TestSendEmailThrowsIfInvalid(string email)
{
    SendConfirmation("test", email);
    //Assert throws exception...
}

Okay, stop... do we really need to re-test every single valid and invalid email address? Data-driven tests make this easy, but that doesn't make it right. In this function, there are really only two paths that matter: an invalid email address throws an exception, and sending an email successfully. Let's try again:

public void TestSendEmailIfValid(string email)
{
    SendConfirmation("test", "validemail@test.com");
    //Assert sends email...
}
public void TestSendEmailThrowsIfInvalid()
{
    SendConfirmation("test", "not an email address");
    //Assert throws exception...
}

Mockists might even take this so far as to stub out the IsValidEmail function for the purposes of testing this function, but for this simple case, it might be overkill since IsValidEmail has no external dependencies to begin with.

In the real world, this kind of mistake is harder to spot. Generally, if you have an explosion of TestCase attributes covering all kinds of permutations, consider revisiting the code under test. There may be some opportunities to simplify both your code and your test suite.

"Good Enough" Test Automation

If you are practicing Agile and you want to stay releasable at the end of every iteration, you must automate your tests. If you do not automate, one of two things will happen:

1. Your velocity will decrease each sprint as you need to reserve more and more time for regression testing.
2. You will not run (or will forget to run) regression tests, and you run the risk of breaking something unintentionally. Best-case, your testers will catch any problems before release. Worst-case, your customers will find problems after release.

Now, here's the fun part: you have 2-3 weeks to build and test something potentially releasable. How can you possibly squeeze test automation into that time frame? The same way you attack that next killer feature: start small and iterate.

A few days ago I was testing some changes to a multi-step online signup process. The current code base is, shall we say, less than testable at the unit level. As a result, all testing for this feature has been manual in the past. After a few manual test runs, introducing some automated tests at the browser level seemed like a good idea. I immediately thought about continuous integration, but quickly realized dealing with all the necessary data setup and tear-down was going to take several days to get right. All I needed at that moment was a script to fill out the forms and click through the whole process. I coded a short Watir script, ran it a few times, and visually verified the process still worked end-to-end.

If all you need is a script to do the boring, repetitive parts of testing for you, write the script. If the script is useful, you'll keep coming back to it, and eventually get it running under continuous integration. Just take the first step, choose a tool, and start automating.