What Is There to Test?
When I describe database testing to people, they're often puzzled—what could there be to test? The answer is that there is quite a bit, and it's all very important. Figure 1 uses threat boundaries (the dashed lines) to indicate that there are two categories of database testing—database interface testing and internal database testing. Database interface testing is focused on ensuring that correct data is being put into the database and taken out of it, whereas internal database testing ensures that the database runs as expected.
Common database interface tests include validating data values before saving them into the database and validating the data values coming back from the database. SQL code is still code, therefore you should test it. If your team uses a persistence framework such as Hibernate or Genome, then you'll want to test your mappings as well.
Internal database testing isn't as common as database interface testing, likely due to a current dearth of testing tools, although it is arguably more important. The most obvious need is for unit testing your stored procedures and functions—not-so-obvious tests that validate your referential integrity (RI) rules. Because RI is typically implemented by triggers, and triggers can get updated and/or dropped, you'll want tests in place to ensure that your database is still working properly. Tests that validate your view definitions are also important because they often implement critical calculations and data combinations. Finally, data quality tests such as validating the default value of a column and ensuring invariants of a single data column, invariants between columns, and invariants between rows should also be performed.
Databases are shared resources, therefore there should ideally be a common database test suite that can be invoked by any application team. A single test suite would enable your organization to support consistent database testing between teams and ensure that your testing investment is spent wisely—do you really want 50 application teams writing the same basic interface tests yet ignoring internal testing?
