What Is Database Unit Testing and Why?

What Is Database Unit Testing and Why?

Unit testing is a well-understood concept in application development, but the database community has not yet embraced the advantages and strategies of this approach.
Therefore, I'd like to start by exploring the fundamental tenets of the unit-testing methodology.
Unit testing provides a structured and automated way of testing individual components of a system.
Unit tests are most often authored by the developer of the component that is being tested.
Each unit test tests a specific module of the code in an isolated fashion to ensure that the component behaves as expected.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database's programmability objects. These objects include, for example, a database's stored procedures, functions, and triggers.

What might a unit test for a stored procedure look like?
Let's say that you are trying to test the CustOrderHist stored procedure in the Northwind database.
The stored procedure should return the order history for a given customer ID.
To test this behavior, you can imagine writing a SQL script that executed the stored procedure and then verified whether the expected number of rows was returned. Such a script might resemble the following:

DECLARE @CustomerId nchar(5)
SELECT @CustomerId = 'EASTC'
EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)
RAISERROR('Actual Rowcount not equal to expected 19',11,1)

So Why Perform Database Unit Tests?

As a methodology, unit testing has many advantages over manual, ad-hoc testing and debugging.
By developing database unit tests, you can create a collection of tests and run them during development to ensure that your features work as you expect
Because each unit test focuses specifically on an individual method, you can more easily determine the source of a failure for a failing unit test.
Therefore, database unit tests help you determine the sources of bugs in your code.

Such a collection of tests is very useful for regression testing.
As you implement new features, you can rerun existing tests to ensure that existing functionality has not been broken.
Such a regression test suite facilitates database changes, because you can now make changes knowing the implications of those changes.

Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine exactly how particular components should be consumed.

Types of Database Unit Tests

Database unit testing is not limited merely to testing the database's programmability objects. You might want to author the four classes of tests that this section describes.

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

Next Post on How To: Create DB Unit Test With Team System