How to write unit tests for database calls

Question

I'm near the beginning of a new project and (gasp!) for the first time ever I'm trying to include unit tests in a project of mine.

I'm having trouble devising some of the unit tests themselves. I have a few methods which have been easy enough to test (pass in two values and check for an expected output). I've got other parts of the code which are doing more complex things like running queries against the database and I'm not sure how to test them.

public DataTable ExecuteQuery(SqlConnection ActiveConnection, string Query, SqlParameterCollection Parameters)
{
    DataTable resultSet = new DataTable();
    SqlCommand queryCommand = new SqlCommand();
    try
    {
        queryCommand.Connection = ActiveConnection;
        queryCommand.CommandText = Query;

        if (Parameters != null)
        {
            foreach (SqlParameter param in Parameters)
            {
                 queryCommand.Parameters.Add(param);
            }
        }

        SqlDataAdapter queryDA = new SqlDataAdapter(queryCommand);
        queryDA.Fill(resultSet);
    }
    catch (Exception ex)
    {
        //TODO: Improve error handling
        Console.WriteLine(ex.Message);
    }

    return resultSet;
}

This method essentially takes in all the necessary bits and pieces to extract some data from the database, and returns the data in a DataTable object.

The first question is probably the most complex: What should I even test in a situation like this?

Once that's settled comes the question of whether or not to mock out the database components or try to test against the actual DB.

Solution 1

What are you testing?

There are three possibilities, off the top of my head:

A. You're testing the DAO (data access object) class, making sure it's correctly marshaling the values/parameters being passed to the database,, and correctly marshaling/transforming/packaging results gotten frm the database.

In this case, you don't need to connect to the database at all; you just need a unit test that replaces the database (or intermediate layer, eg., JDBC, (N)Hibernate, iBatis) with a mock.

B. You're testing the syntactic correctness of (generated) SQL.

In this case, because SQL dialects differ, you want to run the (possibly generated) SQL against the correct version of your RDBMS, rather than attempting to mock all quirks of your RDBMS (and so that any RDBMS upgrades that change functionality are caught by your tests).

C. You're testing the semantic correctness of your SQL, i.e, that for a given baseline dataset, your operations (accesses/selects and mutations/inserts and updates) produce the expected new dataset.

For that, you want to use something like dbunit (which allows you to set up a baseline and compare a result set to an expected result set), or possibly do your testing wholly in the database, using the technique I outline here: Best way to test SQL queries.

Solution 2

This is why (IMHO) unit tests can sometimes create a false sense of security on the part of developers. In my experience with applications that talk to a database, errors are commonly the result of data being in an unexpected state (unusual or missing values etc.). If you routinely mock up data access in your unit tests, you will think your code is working great when it is in fact still vulnerable to this kind of error.

I think your best approach is to have a test database handy, filled with gobs of crappy data, and run your database component tests against that. All the while remembering that your users will be much much better than you are at screwing up your data.

Solution 3

The whole point of a unit test is to test a unit (duh) in isolation. The whole point of a database call is to integrate with another unit (the database). Ergo: it doesn't make sense to unit test database calls.

You should, however, integration test database calls (and you can use the same tools you use for unit testing if you want).

Solution 4

For the love of God, don't test against a live, already-populated database. But you knew that.

In general you already have an idea of what sort of data each query is going to retrieve, whether you're authenticating users, looking up phonebook/org chart entries, or whatever. You know what fields you're interested in, and you know what constraints exist on them (e.g., UNIQUE, NOT NULL, and so on). You're unit testing your code that interacts with the database, not the database itself, so think in terms of how to test those functions. If it's possible for a field to be NULL, you should have a test that makes sure that your code handles NULL values correctly. If one of your fields is a string (CHAR, VARCHAR, TEXT, &c), test to be sure you're handling escaped characters correctly.

Assume that users will attempt to put anything* into the database, and generate test cases accordingly. You'll want to use mock objects for this.

* Including undesirable, malicious or invalid input.

Solution 5

Strictly speaking, a test that writes/reads from a database or a file system is not a unit test. (Although it may be an integration test and it may be written using NUnit or JUnit). Unit-tests are supposed to test operations of a single class, isolating its dependencies. So, when you write unit-test for the interface and business-logic layers, you shouldn't need a database at all.

OK, but how do you unit-test the database access layer? I like the advice from this book: xUnit Test Patterns (the link points to the book's "Testing w/ DB" chapter. The keys are:

• use round-trip tests
• don't write too many tests in your data access test fixture, because they will run much slower than your "real" unit tests
• if you can avoid testing with a real database, test without a database

View more solutions

Comments

[MusiGenesis]

But in this case, you WANT your tests to fail when they encounter unexpected data, so that you can rewrite your component to handle the condition properly.

[James Black]

I think using the database test I added a reference to would be best, as it helps to limit what you need to do to prepare for a test, so you don't have to test setting up a connection, for example.

[Dan Esparza]

Actually -- you bring up an interesting point. Are there tools that assist in creating unit tests explicitly for the database layer? (In other words, unit testing the procs themselves?)

[Dan Esparza]

Ah -- it appears my question has been asked and answered, here: stackoverflow.com/questions/754527/best-way-to-test-sql-quer‌​ies/…

[ijoseph]

You're describing London (vs. Chicago) testing style, which is debatable. I like London better too, though, because Chicago makes it harder to swap out functionality underlying APIs.

[lolelo]

You have to maintain the mock and not make any mistakes. It assumes that the mock is more correct than the database.