Can You Unit Test Your Database? You Bet

Historically, if you asked what unit testing was, you’d probably get a curious mix of definitions. Personally, I’ve seen them run the gamut. This has included everything from very specific definitions of automated testing to the extremely vague: “it’s when you test the stuff you just did.” I attribute this phenomenon to the juxtaposition of two fairly general, subjective concepts: “test” and “unit.”

The one thing that you’ll probably get everybody to agree on, however, is that, whatever it is, they should be doing more of it. Unit testing is the flossing of the technical world. When we don’t do it we should start. And when we do it, we don’t do it enough.

Modern Test Case Management Software
for QA and Development Teams

Unit Testing: Let’s Get Specific with a Definition

So let’s level set a bit. The subject here specifically relates to database unit testing. But before we can tackle that, let’s make sure we get our ducks in a row with regular unit testing.

I don’t aim to say that my definition is the canonical one. As you can see, there’s a fair bit of nuance and debate on the subject. I just want you to know, specifically, what I’m talking about for the purpose of clarity in the rest of the post. So let’s look at what I mean by unit test.

It’s an automated test, generally written by a software developer, that isolates a granular component of code and tests it independently.

To help the definition along a bit, consider a really simple example. Let’s say that I write a method: Add(int x, int y), and that this method (hopefully) does what it advertises. A unit test of this method would mean calling Add(2, 2) and subsequently verifying that the result is 4. I might then write another unit test of Add(2, -2) and verify that the result is zero.

Properties of Unit Tests

Having defined it, let’s examine some of the particular properties of unit tests, against the backdrop of the Add() example.

  • Unit tests are automated. I would use a unit test framework to execute my verification and return a pass/fail verdict.
  • Unit tests are granular. That Add() function is just a tiny cog in the overall application, but I test it.
  • Unit tests isolate their target. I don’t need to setup a bunch of application settings, files, or global variables to test Add().
  • Unit tests are deterministic. Add(2, 2) returns 4 every single time it’s run, predictably and repeatably.
  • Unit tests are independent. They don’t depend on the prior execution of any other unit tests or have any concept of sequential ordering.

This might seem prescriptive if you don’t have much experience with automated unit testing. But with a bit of practice, you get used to doing it, and it becomes old hat.

The Unit Test Database Conundrum

The trouble is, it becomes old hat specifically with application code. If you write code in Java, you become comfortable writing unit tests in Java. Those Java unit tests exercise your Java application code. Noticing a common thread? Hint: it’s “Java.”

You get used to writing these unit tests, and you get used to structuring your code in such a ways to make it testable. Among other things, this means using mock objects at the edges of the application. If the application code opens a file or connects to a database, that violates the principles of granularity, isolation, and determinism. So you mock those things out and omit them from your unit test suite.

How, then, do you test them? Well, that’s what integration testing is for. You pay special attention to every nook and cranny of your application code. And then you slam all database-related testing concerns into the general heading of integration testing.

But don’t your databases count? Aren’t they part of your technical work product? Don’t they deserve unit testing as well?

A lot of people would say, “yeah, probably.” But you don’t see a whole lot of database unit testing. I think this is mainly because people don’t think you can. And that’s a misconception.

Receive Popular Monthly Testing & QA Articles

Join 34,000 subscribers and receive carefully researched and popular article on software testing and QA. Top resources on becoming a better tester, learning new tools and building a team.

We will never share your email. 1-click unsubscribes.

You Can Absolutely Unit Test Your Database

Covering all of the finer points and techniques of how to do this would exceed the scope of this post. But let demonstrate to you that not only is it possible, it’s somewhat conceptually straightforward.

In all likelihood, you’re source controlling the creation scripts for the various tables, views, triggers, sprocs, etc in your database. Conceptually, this gives you the ability to take a blank database instance and create a minimum subset of these in isolation. From there, you can test all sorts of incremental behaviors.

And you can achieve all of the prerequisites of unit tests in application code.

  • Unit tests are automated. You can script a set of database operations as easily as you can application code execution.
  • Unit tests are granular. You can test the behavior of individual triggers, views, sprocs, etc.
  • Unit tests isolate their target. You can deal with only the part of your database that you need without recreating all components and populating all data.
  • Unit tests are deterministic. If you setup schema and data as part of the test, you will have deterministic results.
  • Unit tests are independent. When you manage any needed setup and tear-down as part of the test, the tests need not have any relationship.

Now, databases are inherently different in some key ways from application code. You have to take some steps, like placing increased emphasis on putting the database into known states and making sure that each individual developer has a copy of the database server. But the leap isn’t as big as you might think.

Stand on the Shoulders of Giants

Now, before you start getting ideas in your head that you’re going to write a whole framework for database setup and tear-down, hold on a moment. You wouldn’t write your own application unit testing framework because plenty of these already exist. Well, the same holds true with database unit testing frameworks, even if they’re not as well known.

Here are a handful of tools to check out to kick-start your research.

  • DBUnit, which helps put your database into known states for testing.
  • SQL Server actually supports unit testing the database as part of its feature suite, but you can also test it with SQL Test.
  • DbFit, which supports all major RDBMS and helps you test-drive your database.
  • NDBUnit, which also supports a number of RDBMS.
  • DB Test Driven, which even gives you code coverage.

So, as you can see, while unit testing a database might not be as common or familiar as unit testing application code, it’s perfectly achievable. And it’s worth it.

Your database is a critical part of your overall application. It shouldn’t be a testing blind spot.

This is a guest post by Erik Dietrich, founder of DaedTech LLC, programmer, architect, IT management consultant, author, and technologist.

All-in-one Test Automation
Cross-Technology | Cross-Device | Cross-Platform