• info@helpingtesters.com
  • helpingtesters

Basics of Database Testing

December 29, 2016 Types of Testing
database testing, db tester, test database, basic database

Database testing is one of the important testing techniques. Such testing practice is generally incorporated in banking, financial, e-commerce or projects that deal with huge amount of data. But given the expertise, time and resources required to perform such testing, is it really worth it? In this article, we will figure out the importance of database testing and the basic approach adopted by testers to test the database of an application. 

What is Database

  • A database in a location present in/ off the system where an application’s data is stored. The forms of data stored in the database depending on the action performed and varies from one application to the next. The type of data can be as simple as integers or character or as complex as images or encrypted high volume data.
  • When a user interacts with the application on the UI, depending on the functionality data manipulation occurs in the database.
  • Sometimes when a request is sent to the database, it simply retrieves the existing data while in other cases it edits or deletes existing data.
  • Generally, every application database has to handle multiple concurrent requests and manage high volume data.

Why is there a need for Database Testing

Below mentioned are some of the reasons, why database testing is widely prevalent in different IT companies and shows the importance of testing database.

To Verify Data Mapping – Most applications consist of 3 layers, namely UI, business, and Database. User Interactions are captured through the UI, processed by the business layer and ultimately saved/ mapped in the database.

  • Such interactions with the database manipulate a lot of data, thus it needs to be scrutinized.
  • Every front end interaction triggers either a create, read, update or delete action in the database. Such interactions should be compared with the ideal behavior mentioned in the requirement document.
  • Verifying data mapping not only strengthens the application as a whole but also lessens the need for intense manual testing.


To Validate ACID Properties

By database testing, we can ensure whether every database transaction strictly adheres to the ACID properties.

  • ATOMICITY – This is also known as the “all or nothing” rule. It states that a transaction can either pass or fail. Even if a transaction partially fails, it would mean that the whole of it has failed. Atomicity is highly considered as software assurance activity.
  • CONSISTENCy.Y Every transaction should be consistent and only valid data would be stored in the database.
  • ISOLATION If multiple transactions take place on a database, at any point of time, one transaction won’t affect the other.
  • DURABILITY Once a transaction occurs, the data can’t be manipulated by any external factor, like a power outage or system crashes.

To verify business rules – For applications having complicated business rules, the database interaction would be governed by the business rules. So the Database testing team needs to ensure that for a specific business rule the right database manipulations occur. For this reason, database triggers, stored procedures, and other independent SQL queries need to be tested.

What are the different components that need to be tested

Normally a database consists of a number of tables with numerous relations amongst them. So the best way to test these relations and verify the integrity of the database is to test the different components in it.

Transactions – Transactions are a way of manipulating data present in the database. By testing various transactions, we can verify the ACID properties of the database.

  • A transaction can be initiated using “BEGIN TRANSACTION #NO” and terminated using “END TRANSACTION #NO”.
  • Data consistency can be confirmed by performing “ROLLBACK”, while “SELECT” can be used to view the resulting outcome.

Schema – The schema of the database should be verified against the requirement document.

  • The relationship between different tables (primary and foreign key constraints) and field constants ( Default/ unique value) should be tested.

Trigger – When some data manipulation occurs on a table, a trigger can be used to automatically trigger another transaction.

  • For example, when a user creates an account for a website, some privileges are provided to the user. On the database front, a trigger associates the newly acquired privileges for the user’s account.
  • One way to validate the triggers is by executing the independent queries of the trigger and match the result by executing the trigger as a whole.
  • Tiggers can also be tested directly from the UI by performing an action and querying the database and matching the expected result with the actual data present.

Stored procedures – Stored procedures are a lot similar to functions. To test stored procedures, all the list of stored procedures that are executed need to be listed.

  • Sequentially all the stored procedures should be executed and the database entries verified after each execution.
  • This will help break down a complex transaction into parts and help validate each small steps.
  • We can also trigger the stored procedure by performing various user actions and later verifying the final result.

Database Testing Best Practices

  • To ensure complete test coverage for database testing, the best practice would be to test all database transactions.
  • The test data used for Database testing should be valid and be verified from the client. This would not only add value to the test cases but also prevent improper data accumulation in the database.
  • Boundary value analysis and equivalence partition techniques can also be used to create the quality data for database testing.
  • Along with manual verification, automation scripts too can be utilized to test all database queries and components at regular intervals.
  • Taking periodic database backup must be practiced.

About the author

arindam bandyopadhyay author

Arindam Bandyopadhyay is an automation tester with over 5 years of experience in software testing. While during the day he juggles between Eclipse and spreadsheets, at night he lets his fingers do the talking as he writes about anything and everything his paradoxical mind desires.

1 Comment


May 23, 2017 at 5:28 am

Thanks for your thoughts. It’s helped me a lot.

Leave a Reply

Your email address will not be published.