The main types of database testing explained

Why is database testing important?

Few, if any, business applications run without a database.

Database and application testing are related, and some organizations and project teams do them together. However, the growing interdependence of application components means a growing independence of the databases that support them. Database elements are often separate components of applications, not developed alongside app code. For these reasons, software teams should test databases, and consider their unique properties and requirements when devising test methodologies.

The big debates in database testing revolve around whether to test on a per-vendor or per-product level and whether to test databases through the query functions -- SQL -- or more directly via APIs. For companies that have only one database vendor in the IT organization, the first issue is moot. When there's a mixture, consider testing each product with different tools, which often results in a better assessment than using one tool for all cases. For the second issue, SQL-based testing is usually easier for database administrators, and easier to relate to actual application use, than the other approach.

Database testing is best conducted at an installation level, or at least a development-team level, rather than an individual level. Databases are increasingly shared across applications and organizations; testing for a specific mission invites incomplete or redundant testing. That means developers and testers should gather database-testing requirements broadly and address them through common tools and practices.

Functional testing

Functional testing validates that users and applications can access and update data in the database. Some testers prefer to validate database functionality by testing the application or applications that rely on the database. This approach poses a risk where the application tests fail to exercise all the fields and conditions within the database.

To ensure that tests fully explore each field in the database, use a test generator and drive the database directly. The risk with this approach is poor analysis due to the test data. It's easy to miss problems in field size or data format if the test data isn't based on real application inputs.

To ensure database quality, pick the best functional database testing tool for the project, then supplement it for the other testing functions required.

Functional testing tools. IT organizations can use generalized or database-specific functional testing tools. Some teams test databases through SQL, using tools such as SeLite for browser automation.

Generalized products include:

• SolarWinds Database Performance Analyzer
• MockupData
• DTM Test Data Generator

Database-specific tools include:

• SolarWinds Orion
• Oracle SQL Developer for Oracle
• tSQLt for Microsoft SQL Server

ACID testing

ACID tests are probably what most organizations think of as database testing. ACID validates overall database integrity, what could be called logical integrity: the fundamental ability of the database to reflect the conditions it's designed to record, without ambiguity or duplication.

ACID testing resembles database programming. Most test approaches use SQL to run through a validation of each of the four requirements:

• The transaction is complete within itself, and it passes or fails as a whole.
• The activity does not cause the database to act in an unexpected way. Either a transaction succeeds and creates a new state, or it fails and the database reverts to its previous state.
• Transactions occur simultaneously or sequentially without creating an inconsistent state for the database.
• The database activities withstand power failures and other outages.

ACID testing, procedures and tools are widely available and established within the software industry. Thanks to the prevalence of ACID testing, software teams probably have existing information to lay out their approach here without difficulty. That can mask an issue, though. Many users focus on ACID as their only database testing type, and, as a result, miss errors that can create major problems.

ACID testing tools. DTM Data Generator and MockupData are popular for this type of test, as well as functional testing. Many users prefer more database-specific tools for ACID testing, such as those from Microsoft and Oracle for SQL development. Most companies use mass data generators driving a SQL script to validate databases.

Nonfunctional testing

Load and other performance testing checks database operation, in terms of its ability to handle user activity in production. Many organizations historically tested databases and applications together, when databases were tied to single software, not shared across multiple software components. These teams might still lump together all performance testing, but in the age of interrelated and componentized software, that's a trap.

Stress, delay, load and related tests of how the database works in various situations can be contaminated by other sources of performance issues. To test performance, run tests locally to the database, not remotely, where network delays could contaminate the result. A good performance test might require a combination of tools. Testers can use an appropriate tool to generate load at a chosen level for the type of test, and another to monitor performance.

Performance testing tools. Popular tools for nonfunctional testing include:

• SolarWinds Database Performance Analyzer
• Data Factory on SourceForge
• MockupData

Database Performance Analyzer is an example of a broad in situ analyzer of database performance. Data Factory and MockupData are test data generators for direct testing.