How to Unit Test Your Database

This article has been updated on 04/07/21 by Nishi Grover.

Historically, if you asked what unit testing was, you’d probably get a curious mix of definitions.

This has included everything from very specific definitions, like, “Unit testing is the process of writing code to test the behavior and functionality of your system,” to the extremely vague: “It’s when you test the stuff you just did.” 

But the one thing you’ll probably get everybody to agree on is that, whatever unit testing is, everyone should be doing more of it. Unit testing is the flossing of the technical world: If we don’t do it, we should start; and if we already do it, we don’t do it enough!

Unit testing basics

A unit test is an automated test, generally written by a software developer, that isolates a granular component of code and tests it independently.

Consider a simple example. Let’s say that we write a method, Add(int x, int y), that adds two integers.

A unit test of this method would mean calling Add(2, 2) and subsequently verifying that the result is 4. We might then write another unit test of Add(2, -2) and verify that the result is zero.

Learn more about different unit testing techniques.

Properties of unit tests

• Unit tests are automated. A unit test framework executes the verification and returns a pass/fail verdict.
• Unit tests are granular. That Add() function is just a tiny cog in the overall application, but we test it individually.
• Unit tests isolate their target. We don’t need to set up 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 repeatedly.
• Unit tests are independent. They don’t depend on the prior execution of any other unit tests or have any concept of sequential ordering.

Unit tests need to follow certain guidelines and need to be maintainable. Here are five elements of good, maintainable unit tests.

Why to unit test your database

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

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 under 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?

While unit testing a database might not be as common or familiar as unit testing application code, it’s perfectly achievable! Let’s see how.

How to unit test your database

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 factors in isolation. From there, you can test all sorts of incremental behaviors.

• Write a script that will wipe out the database before you start unit tests, then populate the database with a predefined set of data and run the tests. You can also do that before every test; it’ll be slow but less error-prone.
• Ensure that the database is in a known state before each test runs, and verify the state after the run using assertions logic.
• You can also look for problems like missing references that can happen due to accidentally removing or renaming objects, like columns that are still being referenced by a module such as a view. 
• In the end, make sure that the database is restored to its original state after the test execution.

Here are some good ideas you can adopt.

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.

Tools to use

Just as you wouldn’t write your own application unit testing framework because plenty of these already exists, 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.

Properties of a database unit test

You can achieve all of the prerequisites of unit tests that are in the application code in your database as well.

• Unit tests are automated. You can script a set of database operations as easily as you can apply 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 set up schema and data as part of the test, you will have deterministic results.
• Unit tests are independent. When you manage any needed setup and teardown as part of the test, the tests need not have any relationship.

Database unit testing is not only possible, it’s worthwhile. Don’t let your database be a testing blind spot. Leverage your existing frameworks and tools to begin database unit tests now. And, if you want to read more about unit testing, check out the post “The 7 Sins of Unit Testing“.

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