The attributes of an effective database testing strategy

The importance of database testing

There are plenty of reasons why database testing is critical, the most obvious being concerns over data loss, data quality, security and application connectivity. Database testing also focuses on stored data format validation, the integrity of API-based data transfer processes, overall application resilience and other important considerations.

To be effective, a database testing strategy should do the following:

• Ensure that basic create, read, update and delete (CRUD) operations work as expected.
• Verify that queries occur in the correct order and can properly recover from failure.
• Check that the database schema's tables, fields and data types are properly named and formatted, as well as inspect any views or indexes.
• Certify that event triggers and stored procedures behave properly and won't induce failure.
• Confirm that database constraints are being enforced and that data is being handled securely.
• Test the database to see if it responds to failures appropriately and in a predictable manner.

Database testing approaches and tools

Testers can execute database testing using an automated, manual or hybrid strategy. The first step in choosing a strategy is to define the type of databases used within the application and all its connections to APIs or other messaging systems. Strategy choice also depends on the development of team's needs and how much the existing unit testing covers database test objectives.

If the database is backed by a formal platform provider, testers can check to see if any testing tools are already included or specially designed for their needs. Otherwise, they can search to find individual tools that will allow them to execute queries and perform data verification. Popular tools dedicated to database testing include the following:

• MockupData.
• DTM Data Generator.
• MS SQL Server.
• SQL Test.
• NoSQLUnit.
• SeLite.
• SLOB.

Database testing may be performed within the continuous integration, continuous development and continuous testing process -- or CI/CD/CT. The more often a team can execute automated test scripts, the better. If possible, embed all database test scripts within a continuous test execution that executes whenever code is added or merged within a main branch. Another option is to simply schedule scripts to run at the same time that CI/CD/CT tests are executed for regression or other routine testing procedures.

SQL vs. NoSQL database testing

The approach to database testing that a team adopts will depend heavily on whether it operates a SQL or NoSQL database. With manual testing, teams can often test both SQL and NoSQL databases in the same manner that they would perform black box testing for an application. Automated testing, however, will require teams to write scripts geared specifically toward either a SQL or NoSQL database.

For SQL databases, most test cases will involve automated database verification scripts also written in SQL. Luckily, it is quite common for QA testers to possess strong SQL scripting skills. However, SQL scripts can vary in complexity and detail, and more challenging script requirements may need to fall in the hands of a full-fledged SQL programmer.

For NoSQL databases, a tester will need some experience and familiarity with the individual database in question. For example, to conduct testing on a NoSQL database built on MongoDB, the tester must understand things such as MongoDB's use of collections to store data and how the Binary JSON data format works.

To perform effective NoSQL database testing, teams will need to create scripts written in the language used to program the database in question. Luckily, many testing tools include a default language, database viewer tool, as well as options to create code for unit type database tests. The database QA tester can then learn to create and execute these tests, or developers can create the database test scripts for QA to execute.

Combining manual and automated database testing

Database testers who would like to use both manual and automated techniques can create a hybrid strategy that combines the two. This is done by creating a manual set of database tests to handle transaction functions and data manipulation operations; add these manual test cases to existing execution or regression test suites.

Next, create a set of automated test scripts calibrated for the type of database in use (using either SQL queries or specific language scripts for NoSQL). These automated tests can take the form of unit tests developers write and schedule to execute routinely. Automated database tests can also be scripted by QA testers familiar with the database structure and schema, and who know how to execute triggers and stored procedures.

A hybrid strategy tends to work best when release cycles are short. By creating a set of manual and automated test suites separately, at least one suite gets executed on a frequent basis. The tests executed may not cover the entire database testing need, but some tests are better than none.