Chapter 13
Testing with Databases

About This Chapter

In Chapter 12, Organizing Our Tests, we looked at techniques for organizing our test code. In this chapter, we explore the issues that arise when our application includes a database. Applications with databases present some special challenges when writing automated tests. Databases are much slower than the processors used in modern computers. As a result, tests that interact with databases tend to run much, much more slowly than tests that can run entirely in memory.

Even ignoring the potential for Slow Tests (page 253), databases are a ripe source for many test smells in our automated test suites. Some of these smells are a direct consequence of the persistent nature of the database, while others result from our choice to share the fixture instance between tests. These smells were introduced in Chapter 9, Persistent Fixture Management. This chapter expands on them and provides a more focused treatment of testing with databases.

Testing with Databases

Here is my first, and most critical, piece of advice on this subject:

When there is any way to test without a database, test without the database!

This seems like pretty strong advice but it is phrased this way for a reason. Databases introduce all sorts of complications into our applications and especially into our tests. Tests that require a database run, on average, two orders of magnitude slower than the same tests that run without a database.

Why Test with Databases?

Many applications include a database to persist objects or data into longer-term storage. The database is a necessary part of the application, so verifying that the database is used properly is a necessary part of building the application. Therefore, the use of a Database Sandbox (page 650) to isolate developers and testers from production (and each other) is a fundamental practice on almost every project (Figure 13.1).

Figure 13.1. A Database Sandbox for each developer. Sharing a Database Sandbox among developers is false economy. Would you make a plumber and an electrician work in the same wall at the same time?

Issues with Databases

A database introduces a number of issues that complicate test automation. Many of these issues relate to the fact that the fixture is persistent. These issues were introduced in Chapter 9, Persistent Fixture Management, and are summarized briefly here.

Persistent Fixtures

Applications with databases present some special challenges when we are writing automated tests. Databases are much slower than the processors used in modern computers. As a consequence, tests that interact with a database tend to run much more slowly than tests that can run entirely in memory. But even ignoring the Slow Tests issue, databases are a prime source of test smells in our automated test suites. Commonly encountered smells include Erratic Tests (page 228) and Obscure Tests (page 186). Because the data in a database may potentially persist long after we run our test, we must pay special attention to this data to avoid creating tests that can be run only once or tests that interact with one another. These Unrepeatable Tests (see Erratic Test) and Interacting Tests (see Erratic Test) are a direct consequence of the persistence of the test fixture and can result in more expensive maintenance of our tests as the application evolves.

Shared Fixtures

Persistence of the fixture is one thing; choosing to share it is another. Deliberate sharing of the fixture can result in Lonely Tests (see Erratic Test) if some tests depend on other tests to set up the fixture for them—a situation called Chained Tests (page 454). If we haven't provided each developer with his or her own Database Sandbox, we might spark a Test Run War (see Erratic Test) between developers. This problem arises when the tests being run from two or more Test Runners (page 377) interact by virtue of their accessing the same fixture objects in the shared database instance. Each of these behavior smells is a direct consequence of the decision to share the test fixture. The degree of persistence and the scope of fixture sharing directly affect the presence or absence of these smells.

General Fixtures

Another problem with tests that rely on databases is that databases tend to evolve into a large General Fixture (see Obscure Test) that many tests use for different purposes. This outcome is particularly likely when we use a Prebuilt Fixture (page 429) to avoid setting up the fixture in each test. It can also result from the decision to use a Standard Fixture (page 305) when we employ a Fresh Fixture (page 311) strategy. This approach makes it difficult to determine exactly what each test is specifying. In effect, the database appears as a Mystery Guest (see Obscure Test) in all of the tests.

Testing without Databases

Modern layered software architecture [DDD, PEAA, WWW] opens up the possibility of testing the business logic without using the database at all. We can test the business logic layer in isolation from the other layers of the system by using Layer Tests (page 337) and replacing the data access layer with a Test Double (page 522); see Figure 13.2.

Figure 13.2. A pair of Layer Tests, each of which tests a different layer of the system. Layer Tests allow us to build each layer independently of the other layers. They are especially useful when the persistence layer can be replaced by a Test Double that reduces the Context Sensitivity (see Fragile Test on page 239) of the tests.

If our architecture is not sufficiently layered to allow for Layer Tests, we may still be able to test without a real database by using either a Fake Database (see Fake Object on page 551) or an In-Memory Database (see Fake Object). An In-Memory Database is a database but stores its tables in memory; this structure makes it run much faster than a disk-based database. A Fake Database isn't really a database at all; it is a data access layer that merely pretends to be one. As a rule, it is easier to ensure independence of tests by using a Fake Database because we typically create a new one as part of our fixture setup logic, thereby implementing a Transient Fresh Fixture (see Fresh Fixture) strategy. Nevertheless, both of these strategies allow our tests to run at in-memory speeds, thereby avoiding Slow Tests. We don't introduce too much knowledge of the SUT's structure as long as we continue to write our tests as round-trip tests.

Replacing the database with a Test Double works well as long as we use the database only as a data repository. Things get more interesting if we use any vendor-specific functionality, such as sequence number generation or stored procedures. Replacing the database then becomes a bit more challenging because it requires more attention to creating a design for testability. The general strategy is to encapsulate all database interaction within the data access layer. Where the data access layer provides data access functionality, we can simply delegate these duties to the "database object." We must provide test-specific implementations for any parts of the data access layer interface that implement the vendor-specific functionality—a task for which a Test Stub (page 529) fits the bill nicely.

If we are taking advantage of vendor-specific database features such as sequence number generation, we will need to provide this functionality when executing the tests in memory. Typically, we will not need to substitute a Test Double for any functionality-related object because the functionality happens behind the scenes within the database. We can add this functionality into the in-memory version of the application using a Strategy [GOF] object, which by default is initialized to a null object [PLOPD3]. When run in production, the null object does nothing; when run in memory, the strategy object provides the missing functionality. As an added benefit, we will find it easier to change to a different database vendor once we have taken this step because the hooks to provide this functionality already exist.¹

Replacing the database (or the data access layer) via an automated test implies that we have a way to instruct the SUT to use the replacement object. This is commonly done in one of two ways: through direct Dependency Injection (page 678) or by ensuring that the business logic layer uses Dependency Lookup (page 686) to find the data access layer.

Testing the Database

Assuming we have found ways to test most of our software without using a database, then what? Does the need to test the database disappear? Of course not! We should ensure that the database functions correctly, just like any other software we write. We can, however, focus our testing of the database logic so as to reduce the number and kinds of tests we need to write. Because tests that involve the database will run much more slowly than our in-memory tests, we want to keep the number of these tests to the bare minimum.

What kinds of database tests will we require? The answer to this question depends on how our application uses the database. If we have stored procedures, we should write unit tests to verify their logic. If a data access layer hides the database from the business logic, we should write tests for the data access functionality.

Testing Stored Procedures

We can write tests for stored procedures in one of two ways. A Remote Stored Procedure Test (see Stored Procedure Test on page 654) is written in the same programming language and framework as we write all of our other unit tests. It accesses the stored procedure via the same invocation mechanism as used within the application logic (i.e., by some sort of Remote Proxy [GOF], Facade [GOF], or Command object [GOF]). Alternatively, we can write In-Database Stored Procedure Tests (see Stored Procedure Test) in the same language as the stored procedure itself; these tests will run inside the database (Figure 13.3). xUnit family members are available for several of the most common stored procedure languages; utPLSQL is just one example.

Figure 13.3. Testing a stored procedure using Self-Checking Tests (see page 26). There is great value in having automated regression test for stored procedures, but we must take care to make them repeatable and robust.

Testing the Data Access Layer

We also want to write some unit tests for the data access layer. For the most part, these data access layer tests can be round-trip tests. Nevertheless, it is useful to have a few layer-crossing tests to ensure that we are putting information into the correct columns. This can be done using xUnit framework extensions for database testing (e.g., DbUnit for Java) to insert data directly into the database (for "Read" tests) or to verify the post-test contents of the database (for "Create/Update/Delete" tests).

A useful trick for keeping our fixture from becoming persistent during data access layer testing is to use Transaction Rollback Teardown (page 668). To do so, we rely on the Humble Transaction Controller (see Humble Object on page 695) DFT pattern when constructing our data access layer. That is, the code that reads or writes the database should never commit a transaction; this allows the code to be exercised by a test that rolls back the transaction to prevent any of the changes made by the SUT from being applied.

Another way to tear down any changes made to the database during the fixture setup and exercise SUT phases of the test is Table Truncation Teardown (page 661). This "brute force" technique for deleting data works only when each developer has his or her own Database Sandbox and we want to clear out all the data in one or more tables.

Ensuring Developer Independence

Testing the database means we need to have the real database available for running these tests. During this testing process, every developer needs to have his or her own Database Sandbox. Trying to share a single sandbox among several or all developers is a false economy; the developers will simply end up tripping over one another and wasting a lot of time.² I have heard many different excuses for not giving each developer his or her own sandbox, but frankly none of them holds water. The most legitimate concern relates to the cost of a database license for each developer—but even this obstacle can be surmounted by choosing one of the "virtual sandbox" variations. If the database technology supports it, we can use a DB Schema per TestRunner (see Database Sandbox); otherwise, we have to use a Database Partitioning Scheme (see Database Sandbox).

Testing with Databases (Again!)

Suppose we have done a good job layering our system and achieved our goal of running most of our tests without accessing the real database. Now what kinds of tests should we run against the real database? The answer is simple: "As few as possible, but no fewer!" In practice, we want to run at least a representative sample of our customer tests against the database to ensure that the SUT behaves the same way with a database as without one. These tests need not access the business logic via the user interface unless some particular user interface functionality depends on the database; Subcutaneous Tests (see Layer Test) should be adequate in most circumstances.

What's Next?

In this chapter, we looked at special techniques for testing with databases. This discussion has merely scratched the surface of the interactions between agile software development and databases.³ Chapter 14, A Roadmap to Effective Test Automation, summarizes the material we have covered thus far and makes some suggestions about how a project team should come up to speed on developer test automation.