The Importance of Database Unit Test Automation

Whether you’re developing a new application or adding features to an existing system, writing unit test automation for relational database objects can dramatically upgrade code quality. Database unit test automation can significantly reduce the amount of critical bugs, and provide software developers an early indication of issues that might raise and make their software behave not as expected.

Unit test automation as a part of the regression testing cycle

Developing unit tests for database objects become especially crucial in the case of product maintenance. As more and more changes are made to the database schema, stored procedures, functions, triggers and other SQL objects; It’s important to verify that these changes work as expected and do not break any existing functionality. Database unit test automation can provide an early indication about such changes, which can potentially generate functional software bugs later on.

Database aspects that can be tested by unit test automation

Schema – Schema unit tests validate the structure and validity of the database objects.  For example, schema validation is useful for checking that the database indeed contains 500 tables, or that a specific view/stored procedure returns the expected set of columns within the expected order. Failure of such test might indicate that something has changed in the infrastructure and will probably make the system misbehave.

 

Performance – Database in some cases, poor system performance might originate in a misconfigured database (e.g. incorrect table indices). Unit testing might be the classic solution to help and identify these bottle necks. For example, executing a complex query on a large table, or testing a heavily used stored procedure to measure its’ execution time and compare to a pre-defined and expected result. Long execution times may indicate that system users will suffer from low performance.

 

Security – A database may contain highly sensitive information, which security is extremely important. Using unit tests is the best practice to ensure a correct database access policy. For example, it’s possible to create a test that attempts to retrieve information from a specific table, view or schema using unauthorized credentials.

 

Data integrity – Verify data wholeness and prevent data corruption. It’s possible to create unit tests for validating static data (data that shouldn’t change) as part of the CRUD operations, or check the format and quantity of the data generated by ETL flows.

 

Functional testing – Mostly applicable for applications that implement most (if not all) of the business logic on the database side using stored procedures. Without discussing whether it’s the optimal way to build an application, database unit test automation is definitely the optimal solution for covering functional testing.

 

How to make sure the unit tests will execute successfully regardless of the data in the DB?

Some of the tests don’t actually rely on “real” data, but on the database schema, permissions or some other database aspect. The tests that depend on actual data can be addressed in two different ways:

• As a general guideline, it is highly recommended that the tests will be executed on an “isolated” testing environment. In this case, a unit test that relies on actual data can expect a set of pre-defined and fixed values. Such an isolated environment can be created on-demand, and a dedicated SQL script can populate the DB with the necessary testing data.
• Creating pre-test events – events that are executed before the actual test, and responsible for adding/deleting the necessary testing data. In some cases, a post-test event should be used for cleanup.

This concludes the basic overview of the database unit test automation methods. In the next article, we’ll provide a review and example of database automation using Microsoft technologies.

---

You’re welcome to share your experience and your thoughts regarding Database Unit Testing in the comments!

---