Sunday, March 27, 2011

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!

4 comments:

  1. Or, you could just use something like http://www.fitnesse.info/dbfit.

    ReplyDelete
  2. Yes, although mysql support doesn't seem to be all the way there for .NET: http://www.fitnesse.info/dbfit:reference:integrationtests:installingdbfit

    ReplyDelete
  3. I m using selenium webdiver using nunit.My application is in server(source & Database).I am accessing the url in the client system.When i am writing the c# code to connect the database. When i run the code using nunit i am getting this error
    "MySql.Data.MySqlClient.MySqlException : Host 'bits-(Computer name)' is not allowed to connect to this MySQL server"

    ReplyDelete
  4. Hi Michael.
    I was much inspired by your article!

    MySql integration tests can be really difficult to handle, as they require a running test server.
    Some suggests using local in memory databases, but there can be differences in syntax and behaviour.

    I created the project MySql.Server (https://github.com/stumpdk/MySql.Server) that automates the setup and shutdown of a local running Mysql database.

    Thanks alot for your idea.

    ReplyDelete