Database Testing: A Full Guide

Database testing is the process of evaluating the accuracy, reliability, and performance of a database system. Its purpose is to ensure that the data stored there is consistent, valid, and can be correctly manipulated for business needs. The components to be tested are usually database schema, tables, and database triggers. Testers leverage SQL queries, data comparison tools, automation frameworks, or load testing tools to examine the data integrity, validity, security, performance, and structure.

Why Should We Test Our Database?

1. Prevent issues during data mapping

• There can be an incompatibility between the source system data type and the target system data type
• Incorrect data transformation logic applied, leading to inaccurate final data
• Discrepancies in data, number, or text formats
• Source data containing duplicates, empty values, or outliers
• Errors in mapping rules
• Nested data not handled correctly

These scenarios result in a misunderstanding between the two systems, causing disastrous consequences. Imagine a bug occurs in the patient record database of a healthcare billing system. When retrieving data in the patient database to calculate their billing, the wrong data field was chosen, and patients ended up receiving bills for services they never received in the first place. If this occurs at a wide enough scale, the impact will surely be costly to reverse.

2. Verify data accuracy and integrity

Data mapping issues can also originate from issues with data accuracy and data integrity. Data accuracy is how correctly the data represents real-world scenarios, while data integrity is more about its consistency throughout the entire life cycle (from data entry to storage and retrieval). We know that data undergoes so many changes throughout its lifecycle, and those changes can totally bring in new errors that eventually lead to issues in data mapping.

There are usually 4 types of data integrity that database testers need to pay attention to:

• Entity integrity: ensuring that individual data components (rows, columns, tables, etc.) has their own unique identifier (or having an identity)
• Referential integrity: ensuring that data from 2 or more connected tables is consistent and accurate
• Domain integrity: ensuring that each column or row has a standard data type and format so that there won’t be any conflicts when those data points are transferred to other systems with a different rules for interpretation
• User-defined integrity: ensuring that additional rules that users define to serve their specific needs are also valid

3. Ensure accuracy with business requirements

There is no point having millions of accurate but irrelevant data points. While not a particularly technical aspect of DB testing, conformity with business requirements is still an important indicator of data quality. Testers all have a clear understanding of business requirements since they are involved in the Requirements Collection process. These requirements should be written as a checklist, then testers and stakeholders can sit together to determine how to test each item in the list through a comprehensive methodology.

ACID Properties Of Database Transactions

Before further delving into each of these properties, we need to define the concept of a “transaction” in a database.

A transaction is a logical unit of work that consists of one or more operations performed on a database to access and modify its content. For example, in a retail inventory system, when a product is sold, the database needs to update both the available quantity of the product and the sales record. These updates are executed within a transaction.

ACID properties are a set of characteristics that ensure reliable and consistent transactions in a database system. Essentially they are the 4 major rules for a good transaction in any database, and testers can base their test plan on those rules. ACID stands for:

• Atomicity
• Consistency
• Isolation
• Durability

ACID Properties Explained

1. Atomicity

Every transaction in the system is treated as a single, indivisible unit, hence the term “atom-icity”. Either all of the operations in the transaction are successfully completed, or none of them are applied. There is no middle ground.

Following this property, there are only 2 outcomes for a transaction:

• Commit : all the operations within the transaction have been executed successfully and the changes made to the database are permanent. Its changes are also visible in other transactions. Committing a transaction also signifies that the database has successfully reached a consistent and valid state after the transaction's operations.
• Abort/Rollback : one or more operations within the transaction have failed, and the database needs to be reverted to its state before the transaction begins. Absolutely no changes were made to the database. This prevents partial updates from creating inconsistencies in the entire system.

2. Consistency

The correctness and integrity of the data and database’s state must be maintained before and after a transaction. In other words, the existing rules defining relationships between data fields must be kept the same when the database is brought from one state to another. The relationships, or integrity constraints , that should not be violated may include:

• Primary key uniqueness
• Foreign key references
• Domain constraints

Suppose we have a banking application with two tables: Accounts and Transactions.

An integrity constraint states that the balance in the Accounts table should always be consistent with the sum of transaction amounts in the Transactions table for that account.

Initial State:

• Account A has a balance of $100.
• Transactions: None.

Transaction 1:

• Withdraw $30 from Account A.
• Update the balance in the Accounts table to $70.
• Insert a transaction record with an amount of -$30.

Transaction 2 (Concurrently):

• Deposit $50 into Account A.
• Update the balance in the Accounts table to $120.
• Insert a transaction record with an amount of $50.

Consistency Check:

At this point, the consistency property ensures that:

• The sum of all transaction amounts in the Transactions table matches the balance in the Accounts table for Account A.
• If Transaction 1 commits first, the balance should be $70.
• If Transaction 2 commits first, the balance should be $120.
• If both transactions commit concurrently, the balance should be consistent with the combined effects of both transactions: $120 - $30 + $50 = $140.

Transaction

Account Balance

(Accounts Table)

Transactions Table

(if both commit concurrently)

Sum of transaction amounts matches account balance

3. Isolation

Isolation means ensuring that each transaction is executed in isolation from other concurrently executing transactions. Even though multiple transactions may be occurring simultaneously, each transaction should not interfere with others. This prevents issues that can arise when one transaction reads or modifies data that another transaction is in the process of modifying, such as:

• Dirty read: when one transaction reads data that has been modified but not yet committed by another transaction. That data is “dirty” because the data was changed, but its transaction was not completed yet, leading to misleading information being retrieved.
• Non-repeatable read: when a transaction reads the same data twice within its own scope, but the data has been modified by another transaction in between the two reads. In other words, the second read returns a different value compared to the first read, causing confusion.
• Phantom read: when a transaction reads a set of records that match a certain condition, and then another transaction inserts or deletes records that match that same condition before the first transaction is complete. If that first transaction tries to read those same records again, the identity or value has changed.

Isolation levels are therefore necessary to govern how transactions in a database interact with each other, preventing such scenarios from happening.

4. Durability

Durability guarantees that once a transaction is committed, its changes are permanent and will survive system failures, crashes, or power outages. Even if the system restarts or fails, the database will be brought back to a consistent state by applying the committed changes from a durable storage medium.

Types of Database Testing

1. Structural Testing

Structural testing, also known as glass box testing or white-box testing, focuses on examining the internal structure of the system (which is the database and its components in this case). The goal is to ensure that the database schema, tables, relationships, constraints, and other structural elements are designed and implemented correctly.

Several types of structural testing include:

• Table and Column Validation
  • Verify that all required tables have been created in the database schema.
  • Check that each table contains the expected columns with the correct data types.
  • Test that columns with specific constraints (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL) are defined correctly.
  • Ensure that default values are correctly set for applicable columns.
  • Validate that column lengths match the defined requirements.
  • Test for consistency in naming conventions of tables and columns.
  • Check for any duplicate columns across different tables.
  • Validate that appropriate indexes are defined for frequently queried columns.
  • Test that unique constraints are enforced for columns that should have unique values.
  • Verify that columns used for joins have proper indexing to optimize query performance.
    
  • Test stored procedures with various input parameters to ensure they produce expected results.
  • Validate that stored procedures handle exceptions and error conditions gracefully.
  • Check that stored procedures execute in a reasonable time frame under typical load.
  • Test for proper authentication and authorization checks within stored procedures.
  • Verify that triggers are correctly fired based on defined conditions.
  • Test nested or recursive stored procedures to ensure proper execution.
  • Validate that functions return accurate and expected results for different input values.
  • Test stored procedures that involve multiple tables or complex data manipulations.
  • Check the impact of stored procedures on data consistency and ACID properties.
    
  • Test queries with and without indexes to measure performance differences.
  • Verify that indexes are correctly defined on columns used in WHERE clauses.
  • Check the impact of indexes on INSERT, UPDATE, and DELETE operations.
  • Validate that composite indexes are correctly defined for multi-column queries.
  • Test the behavior of queries when using covered indexes.
  • Verify that the order of columns in composite indexes is optimal for query performance.
  • Test the impact of indexes on join operations involving multiple tables.
  • Validate that unnecessary or redundant indexes are not present.
    
  • Validate that data types and values are consistent after migration.
  • Test migration of both structured and unstructured data (e.g., BLOBs, CLOBs).
  • Check the mapping of relationships and foreign keys between tables post-migration.
  • Validate that data migration doesn't violate unique constraints or integrity rules.
  • Test migration of large datasets to ensure performance and data integrity.
    
  • Validate that all required tables, views, and indexes are part of the schema.
  • Test the schema upgrade process to ensure compatibility with previous versions.
  • Check that schema changes are correctly tracked and versioned.
  • Verify that the schema follows naming conventions and coding standards.
  • Test schema security settings to ensure proper access controls.
  • Validate that schema objects have appropriate comments and documentation.
  • Test the schema's response to changes in data volumes or data characteristics.
  • Check schema changes' impact on application performance and query execution plans.
    
  • Test database server connectivity and ensure it's accessible from the application.
  • Validate that the database server is configured with the correct authentication and authorization settings.
  • Test database server performance by running various types of queries.
  • Check the server's response to high loads and concurrent connections.
  • Validate that database backups and recovery processes work as expected.
  • Test database server failover and redundancy mechanisms.
  • Check for server logs to identify errors, warnings, and performance bottlenecks.
  • Test compatibility of the database server with the operating system and other software components.

  2. Functional Testing

  For database testing specifically, functional testing focuses more on the functional aspects of the database, ensuring that it can perform its intended purposes

  • SQL Query Testing
    • Test a SELECT query to retrieve specific columns from a single table.
    • Test a JOIN query to retrieve data from multiple related tables.
    • Test a subquery to retrieve data based on a nested condition.
    • Test a GROUP BY query to retrieve aggregated data.
    • Test a query with ORDER BY to sort results in ascending and descending order.
      
    • Test a transaction that performs multiple INSERTs and ensures all are committed.
    • Test a transaction that updates data in one table and inserts into another, verifying both are committed or rolled back.
    • Test a transaction that involves multiple database operations, including SELECT, UPDATE, and DELETE.
    • Test a transaction that is deliberately designed to violate a unique constraint, ensuring proper rollback.
    • Test a transaction that is rolled back due to an error in the middle of its execution.
      
    • Test input validation by attempting to insert data with incorrect data types into specific columns.
    • Test validation rules by inserting data that violates CHECK constraints.
    • Test input lengths by trying to insert data longer than specified column lengths.
    • Test validation triggers by attempting to insert data that triggers a constraint violation.
    • Test validation for foreign key constraints by attempting to insert a reference to a non-existing record.
      
    • Test an INSERT statement to add a new record to a table.
    • Test an UPDATE statement to modify existing data in a table.
    • Test a DELETE statement to remove a specific record from a table.
    • Test combining INSERT, UPDATE, and DELETE operations within a single transaction.
    • Test data manipulation within a stored procedure or function.
      
    • ​​Test a basic SELECT query to retrieve all rows from a table.
    • Test a SELECT query with a WHERE clause to retrieve specific rows based on a condition.
    • Test a SELECT query with a JOIN to retrieve data from related tables.
    • Test a SELECT query with GROUP BY and HAVING clauses to retrieve aggregated data.
    • Test a SELECT query with LIMIT or TOP to retrieve a limited number of rows.
      

    3. Non-functional Testing

    In the scope of this type, we focus on performance testing, load testing, stress testing, security testing, and any type of testing whose scope goes beyond the core functionalities, ensuring that these non-functional aspects of the database meet the business requirements.

    • Performance testing: evaluate the database’s response time, throughput, or resource utilization under different load conditions.
    • Load Testing: assess the database’s performance and responsiveness under expected user load. It is a more specific type of performance testing, and the goal is to simulate real-life usage as closely as possible.
    • Stress Testing: assess the database’s performance under extreme load. It is essentially load testing put to the extreme. The database can be subjected to unusually large amounts of users and/or over an extended period of time to see if there are any performance issues.
    • Security Testing: identify the vulnerabilities of the database, ensuring that it is protected against unauthorized access, data breaches, and any security risks. Many databases implement role-based access control mechanisms to safeguard the system, and testers need to verify that only users with certain roles can access and perform certain activities in the database.

    Here are some examples of non-functional test cases you can do on a database:

    • Execute a complex query and measure the response time, comparing it with a response time standard
    • Ensure the response time remains within acceptable limits for all defined user loads.
    • Gradually increase the number of concurrent users accessing the database and observe how the database handles the increased load and whether performance degrades.
    • Attempt to access the database with incorrect credentials.
    • Verify that unauthorized access attempts are denied and appropriate error messages are displayed.
    • Validate that sensitive data, such as passwords or personal information, is properly encrypted in the database.
    • Test data retrieval and verify that decrypted data is displayed correctly.
    • Load the database with a large dataset similar to expected production volumes.
    • Apply a load that exceeds the expected maximum user load.
    • Attempt SQL injection attacks by inputting malicious code into queries to see how this system responds.

    Stages of Database Testing

    DB testing is similar to any other type of testing: it has to follow all of the stages in the software testing life cycle to ensure the highest level of test coverage. The key stages can be found in the flowchart below:

    

    During the Requirement Analysis phase, database testers work with stakeholders involved in the development process to identify and understand test requirements. All of the information collected during this stage should be noted in a Requirement Traceability Matrix (RTM) document, which will be the foundation to build the test strategy and test plan .

    Following the plan, the QA team will create test cases, set up the suitable environment for the execution , and run those tests. The results will be carefully analyzed to identify the root cause of issues found so that the development team can better address them. Finally, a report will be generated to consolidate the findings into insights and recommendations for future decision-making.

    Database Testing Frameworks and Tools

    There are several excellent database testing tools available that cater to different testing needs. Here are 5 widely used and highly regarded database testing frameworks and tools:

    • DbUnit : supports database-driven unit testing . It comes with certain libraries to help you set up and tear down test data, execute queries, and validate test results.
    • PHPUnit: a unit testing framework specifically designed for PHP code, and it can also be used to test database code.
    • SQLUnit: also a framework designed for DB testing, allowing testers to write test cases in XML format, supporting various types of databases.
    • Oracle SQL Developer: a tool used to develop and test Oracle databases with a decent variety of features for database development, testing, and tuning. It supports many database types, including Oracle, Microsoft SQL Server, IBM DB2, and MySQL.
    • Apache JMeter: one of the most popular performance testing tools, Apache JMeter can also be used for performance DB testing, simulating multiple users accessing the system, executing SQL queries, and monitoring response time. Read More:Top 10 Performance Testing Tools For Your Team

    Database Testing FAQs

    1. What skills are needed for database testing?

    When performing DB testing, testers must first understand SQL and database concepts. They should be comfortable writing and analyzing SQL queries for data retrieval, manipulation, and validation. Knowledge in testing, especially how to leverage automation testing tools to design test cases, is also crucial.

    2. How to write effective test cases for database testing?

    To write effective test cases, testers must first identify the scenarios they want to test, then define the initial state of the database for each test case. There should be clear test steps and expected results to compare against. Beside the “common” test cases, testers should also explore boundary and edge cases to ensure higher coverage.

    3. What is ETL testing? Is ETL testing similar to database testing?

    ETL (Extract, Transform, Load) testing is a subset of database testing that focuses on verifying the data extraction, transformation, and loading processes within a data integration pipeline. ETL testing is much more specific, focusing on the data movement and transformation process, while database testing encompasses the entire range of testing activities in the database.

    4. Can we automate DB testing?

    Yes, database testing can be automated using testing frameworks and libraries designed for database interactions. Tools like DbUnit, SQLUnit, and various scripting languages allow you to automate database testing tasks, execute SQL queries, validate results, and compare expected outcomes.

    5. Is database testing functional or non-functional?

    Database testing includes both functional and non-functional aspects. Functional testing ensures that the database performs its intended operations correctly, such as data retrieval, manipulation, and validation. Non-functional testing covers performance, security, scalability, and other attributes that ensure the database system's quality.

    6. Can we use Selenium for DB testing?

    Selenium is primarily designed for web UI testing and is not the recommended tool for direct database testing. However, you can use Selenium to indirectly test database-related functionality by interacting with web applications that use databases. For instance, you can automate web UI actions that trigger database operations and validate their outcomes indirectly through the UI.

    Aspect

    Manual Testing

    Automation Testing

    Tester Involvement

    Human testers execute test cases manually.

    Automated tools execute test cases automatically.

    Test Case Execution

    Test cases executed manually without tools.

    Test cases executed by automation scripts.

    Suitable for

    Exploratory testing, usability testing,

    Regression testing, performance testing

    Testing Types

    ad-hoc testing, small projects.

    large-scale testing, repetitive tasks.

    Human Intervention

    Requires human intervention for each test case.

    Limited human intervention after script setup.

    Error Prone

    Prone to human errors and subjectivity.

    Reduces human errors, increases repeatability.

    Project Scale

    Suitable for small projects or when test cases change frequently.

    Suitable for large and complex projects with consistent requirements.

    Efficiency

    Time-consuming for repetitive or large-scale testing.

    Efficient for repetitive tasks and regression testing.

    Initial Setup

    Simpler initial setup; doesn't require scripting skills.

    Initial setup requires scripting skills and may take longer.

    Adaptability

    Offers more flexibility in adapting to changes.

    Less adaptable to changes in the application.

    Speed of Execution

    Slower compared to automated testing.

    Faster test execution once scripts are set up.