Denned - Fotolia

Get started Bring yourself up to speed with our introductory content.

The main types of database testing explained

Most software testers know about ACID for database testing. But the importance of databases means software teams should perform functional and nonfunctional testing in addition to verifying ACID adherence.

Database reliability extends beyond the ACID tests that most software professionals know. Database testing is divided into functional testing, ACID validation and performance testing.

Functional tests and relationship mapping associate databases with both the front-end sources of information and the back-end usage of that information, to ensure consistency both in the formatting and in the editing that assures it. Atomicity, consistency, isolation and durability (ACID) validation follows functional testing. Finally, databases should undergo performance tests, including load testing. Some organizations put database security and compliance testing in this last category or create a fourth classification of database test types to accommodate it.

Let's examine the types of database testing and some popular database testing tools.

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.

Next Steps

SolarWinds breach news center

How to do API testing to improve application quality

Dig Deeper on Functional testing