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!