Database Testing Complete Guide (Why, What, and How to Test Data)

By Vijay

By Vijay

I'm Vijay, and I've been working on this blog for the past 20+ years! I’ve been in the IT industry for more than 20 years now. I completed my graduation in B.E. Computer Science from a reputed Pune university and then started my career in…

Learn about our editorial policies.
Updated January 23, 2025

A Complete Guide to Database Testing with Practical Tips and Examples:

Computer applications are more complex these days with technologies like Android and also with lots of Smartphone apps. The more complex the front ends, the more intricate the back ends become.

So it is all the more important to learn about DB testing and be able to validate Databases effectively to ensure security and quality databases.

In this tutorial, you will learn all about Data Testing – why, how and what to test?

Database Testing guide

The Database is one of the inevitable parts of a Software Application.

It does not matter whether it is a web, desktop or mobile, client-server, peer-to-peer, enterprise, or individual business; the Database is required everywhere at the backend.

Similarly, whether it is Healthcare, Finance, Leasing, Retail, Mailing application, or controlling a spaceship; a Database is always in action behind the scene.

As the complexity of application increases, the need for a stronger and secure Database emerges. In the same way, for the applications with a high frequency of transactions (For Example, Banking or Finance application), the necessity of a fully-featured DB Tool is coupled.

Nowadays, we have big data that is large and complex that the traditional databases can’t handle them.

There are several Database tools are available in the market For Example, MS-Access, MS SQL Server, SQL Server, Oracle, Oracle Financial, MySQL, PostgreSQL, DB2, Toad, Admirer, etc. These tools vary in cost, robustness, features, and security. Each of these has its own benefits and drawbacks.

Further reading =>>PostgresSQL Vs MySQL

Why Test Database?

Below, we will see why the following aspects of a DB should be validated:

All About Database Testing

#1) Data Mapping

In software systems, data often travels back and forth from the UI (user interface) to the backend DB and vice versa. So these are some aspects to watch for:

  • Check whether the fields in the UI/frontend forms are mapped consistently with the corresponding fields in the DB table.  Typically this mapping information is defined in the requirements documents.
  • Whenever a certain action is performed at the front end of an application, a corresponding CRUD (Create, Retrieve, Update and Delete) action gets invoked at the back end. A tester will have to check if the right action is invoked and whether the invoked action in itself is successful or not.

#2) ACID Properties Validation

Atomicity, Consistency, Isolation, and Durability. Every transaction a DB performs has to adhere to these four properties.

DB ACID Properties

  • Atomicity means that a transaction either fails or passes. This means that even if a single part of the transaction fails- it means that the entire transaction has failed. Usually, this is called the “all-or-nothing” rule.
  • Consistency: A transaction will always result in a valid state of the DB
  • Isolation: If there are multiple transactions and they are executed all at once, the result/state of the DB should be the same as if they were executed one after the other.
  • Durability: Once a transaction is done and committed, no external factors like power loss or crash should be able to change it

Suggested reading =>> MySQL Transaction Tutorial

#3) Data Integrity

For any of the CRUD Operations, the updated and most recent values/status of shared data should appear on all the forms and screens. The value should not be updated on one screen and display an older value on another one.

When the application is under execution, the end-user mainly utilizes the ‘CRUD’ operations facilitated by the DB Tool.

C: Create – When user ‘Save’ any new transaction, ‘Create’ operation is performed.
R: Retrieve – When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve’ operation is performed.
U: Update – When user ‘Edit’ or ‘Modify’ an existing record, the ‘Update’ operation of DB is performed.
D: Delete – When a user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.

Any database operation performed by the end-user is always one of the above four.

So devise your DB test cases in a way to include checking the data in all the places it appears to see if it is consistently the same.

Data Integrity Testing

#4) Business Rule Conformity

More complexity in Databases means more complicated components like relational constraints, triggers, stored procedures, etc. So testers will have to come up with appropriate SQL queries in order to validate these complex objects.

What To Test (Database Testing Checklist)

#1) Transactions

When testing Transactions it is important to make sure that they satisfy the ACID properties.

These are the statements commonly used:

  • BEGIN TRANSACTION TRANSACTION#
  • END TRANSACTION TRANSACTION#

The Rollback statement ensures that the database remains in a consistent state.

  • ROLLBACK TRANSACTION#

After these statements are executed, use a Select to make sure the changes have been reflected.

  • SELECT * FROM TABLENAME <tables which involve the transactions>

#2) Database Schemas

A Database Schema is nothing more than a formal definition of how the data is going to be organized inside a DB. To test it:

  • Identify the Requirements based on which the Database operates. Sample Requirements:
    • Primary keys to be created before any other fields are created.
    • Foreign keys should be completely indexed for easy retrieval and search.
    • Field names starting or ending with certain characters.
    • Fields with a constraint that certain values can or cannot be inserted.
  • Use one of the following methods according to the relevance:
    • SQL Query DESC<table name> to validate the schema.
    • Regular expressions for validating the names of the individual fields and their values
    • Tools like SchemaCrawler

#3) Triggers

When a certain event takes place on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.

For Example, a new student joined a school. The student is taking 2 classes: math and science. The student is added to the “student table”.  A Trigger could add the student to the corresponding subject tables once he is added to the student table.

The common method to test is to execute the SQL query embedded in the Trigger independently first and record the result. Follow this up with executing the Trigger as a whole. Compare the results.

These are tested in both the Black-box and White-box testing phases.

  • White box testing:  Stubs and Drivers are used to insert or update or delete data that would result in the trigger being invoked. The basic idea is to just test the DB alone even before the integration with the front end (UI) is made.
  • Black box testing:

a) Since the UI and DB, integration is now available; we can Insert/Delete/Update data from the front end in a way that the Trigger gets invoked. Following that, Select statements can be used to retrieve the DB data to see if the Trigger was successful in performing the intended operation.

b) The second way to test this is to directly load the data that would invoke the Trigger and see if it works as intended.

#4) Stored Procedures

Stored Procedures are more or less similar to user-defined functions. These can be invoked by Call Procedure/Execute Procedure statements and the output is usually in the form of result sets.

These are stored in the RDBMS and are available for applications.

These are also tested during:

  • White box testing: Stubs are used to invoke the stored procedures and then the results are validated against the expected values.
  • Black box testing: Perform an operation from the front end (UI) of the application and check for the execution of the stored procedure and its results.

#5) Field Constraints

The Default value, Unique value, and Foreign key:

  • Perform a front-end operation which exercises the Database object condition
  • Validate the results with a SQL Query.

Checking the default value for a certain field is quite simple. It is part of business rule validation. You can do it manually or you can use tools like QTP. Manually, you can perform an action that will add value other than the default value of the field from the front end and see if it results in an error.

The following is a sample VBScript code:

Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)
Set newregexp = new RegExp
newregexp.Pattern = “&amp;lt;Default value as required by the business requirements&amp;gt;”
newregexp.Ignorecase = True
newregexp.Global = True
VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)
End Function
Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)

The result of the above code is True if the default value exists or False if it doesn’t.

Checking the unique value can be done exactly the way we did for the default values. Try entering values from the UI that will violate this rule and see if an error is displayed.

Automation VB Script code can be:

Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)
Set newregexp = new RegExp
newregexp.Pattern = “&amp;lt;Unique value as required by the business requirements&amp;gt;”
newregexp.Ignorecase = True
newregexp.Global = True
VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)
End Function
Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)

For the Foreign Key constraint validation use data loads that directly input data that violate the constraint and see if the application restricts them or not. Along with the back end data load, perform the front end UI operations too in a way that will violate the constraints and see if the relevant error is displayed.

Data Testing Activities

Database Tester Should Focus on Following Testing Activities:

#1) Ensure Data Mapping:

Data Mapping is one of the key aspects in the database and it should be tested rigorously by every software tester.

Make sure that the mapping between different forms or screens of AUT and its DB is not only accurate but also per the design documents (SRS/BRS) or code. Basically, you need to validate the mapping between every front-end field with its corresponding backend database field.

For all CRUD operations, verify that respective tables and records are updated when the user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

What you need to verify:

  • Table mapping, column mapping, and Data type mapping.
  • Lookup Data Mapping.
  • Correct CRUD operation is invoked for every user action at UI.
  • CRUD operation is successful.

#2) Ensure ACID Properties of Transactions:

ACID properties of DB Transactions refer to the ‘Atomicity’, ‘Consistency’, ‘Isolation’ and ‘Durability’. Proper testing of these four properties must be done during the database test activity. You need to verify that every single transaction satisfies the ACID properties of the database.

ACID Properties

Let us take a simple example through below SQL code:

CREATE TABLE acidtest (A INTEGER, B INTEGER, CHECK (A + B = 100));

The ACID test table will have two columns – A & B. There is an integrity constraint that the sum of values in A and B should always be 100.

Atomicity test will ensure any transaction performed on this table is all or none i.e. no records are updated if any step of the transaction is failed.

Consistency test will ensure that whenever the value in column A or B is updated, the sum always remains 100. It won’t allow insertion/deletion/update in A or B if the total sum is anything other than 100.

Isolation test will ensure that if two transactions are happening at the same time and trying to modify the data of the ACID test table, then these tractions are executing in isolation.

Durability test will ensure that once a transaction over this table has been committed, it will remain so, even in the event of power loss, crashes, or errors.

This area demands more rigorous, thorough and keen testing if your application is using the distributed database.

#3) Ensure Data Integrity

Consider that different modules (i.e. screens or forms) of application use the same data in different ways and perform all the CRUD operations on the data.

In that case, make sure that the latest state of data is reflected everywhere. The system must show the updated and most recent values or the status of such shared data on all the forms and screens. This is called as Data Integrity.

di

Test cases for validating Database Data Integrity:

  • Check if all the Triggers are in place to update reference table records.
  • Check if any incorrect/invalid data exists in the major columns of each table.
  • Try to insert wrong data in tables and observe if any failure occurs.
  • Check what happens if you try to insert a child before inserting its parent (try to play with Primary and foreign keys).
  • Test if any failure occurs if you delete a record that is still referenced by data in any other table.
  • Check if replicated servers and databases are in sync.

#4) Ensure the Accuracy of the implemented Business Rules:

Today, Databases are not meant only to store the records. In fact, Databases have been evolved into extremely powerful tools that provide ample support to the developers to implement the business logic at the DB level.

Some simple examples of powerful features are ‘Referential Integrity’, Relational constraints, Triggers, and stored procedures.

So, using these and many other features offered by DBs, developers implement the business logic at the DB level. The tester must ensure that the implemented business logic is correct and works accurately.

The above points describe the four most important ‘What To’ of testing DB. Now, let’s move on to the ‘How To’ part. 

How to Test the Database (Step-by-step Process)

The general test process testing database is not very different from any other application.

The following are the core steps:

Step #1) Prepare the environment
Step #2) Run a test
Step #3) Check test result
Step #4) Validate according to the expected results
Step #5) Report the findings to the respective stakeholders

Database Testing Process

Usually, SQL queries are used to develop the tests. The most commonly used command is “Select”.

Select * from <tablename> where <condition>

Apart from Select, SQL has 3 important types of commands:

  1. DDL: Data definition language
  2. DML: Data manipulation language
  3. DCL: Data control language

Let us see the syntax for the most commonly used statements.

Data Definition language Uses CREATE, ALTER, RENAME, DROP and TRUNCATE to handle tables (and indexes).

Data Manipulation language Includes statements to add, update and delete records.

Data control language: Deals with giving authorization to users for manipulation and access to the data. Grant and Revoke are the two statements used.

Grant syntax:
Grant select/update
On <table name>
To <user id1, user id2…useridn>;

Revoke syntax:
Revokeselect/update
on <table name>
from<user id1, user id2…useridn>;

Some Practical Tips

#1) Write Queries yourself:

To test the Database accurately, the tester should have very good knowledge of SQL and DML (Data Manipulation Language) statements. The tester should also know the internal DB structure of AUT.

You can combine GUI and data verification in respective tables for better coverage. If you are using the SQL server then you can make use of SQL Query Analyzer for writing queries, executing them and retrieving results.

This is the best and robust way of testing a database when the application is of a small or medium level of complexity.

If the application is very complex then it may be hard or impossible for the tester to write all the required SQL queries. For complex queries, you take help from the developer. I always recommend this method as it gives you confidence in testing and also enhances your SQL skills.

#2) Observe the data in each table:

You can perform data verification using the results of CRUD operations. This can be done manually by using application UI when you know the database integration. But this can be a tedious and cumbersome task when there is huge data in different database tables.

For Manual Data Testing, the Database tester must possess a good knowledge of database table structure.

#3) Get queries from the developers:

This is the simplest way to test the Database. Perform any CRUD operation from GUI and verify its impacts by executing the respective SQL queries obtained from the developer. It neither requires a good knowledge of SQL nor requires a good knowledge of the application’s DB structure.

But this method needs to be used cautiously. What if the query given by the developer is semantically wrong or does not fulfill the user’s requirement correctly? The process will simply fail to validate data.

#4) Make use of Database Automation Testing tools:

There are several tools available for the Data Testing process. You should choose the correct tool as per your needs and make the best use of it.

=> Here is the list of the TOP DB Testing Tools you should check

Conclusion

With all these features, factors and processes to test on a database, there is an increasing demand for the testers to be technically proficient in the key Database concepts. Despite some of the negative beliefs that testing a database creates new bottlenecks and is a lot of additional expenditure – this is a realm of testing that is attracting significant attention and demand.

Suggested reading =>> What is Database Security Testing

I hope this tutorial has helped to focus on why that is so and has also provided you with the basic details of what goes into testing a Database.

Please let us know your feedback and also share your personal experiences if you are working on  DB testing. 

Was this helpful?

Thanks for your feedback!

Recommended Reading

  • How-to-Test-Oracle-Database

    Oracle Database Testing - This shall be one of the 3 parts of the Oracle Database testing series. We will cover the following 3 parts in this database testing series: #1) Introduction to Oracle Database Testing#2) Testing the database with respect to Memory, Space, and CPU processing#3) Oracle Real Database Application…

  • ETL Testing Data Warehouse Testing (1)

    ETL Testing / Data Warehouse Process and Challenges: Today let me take a moment and explain my testing fraternity about one of the most demanding and upcoming skills for my tester friends i.e. ETL testing (Extract, Transform, and Load). This tutorial will present you with a complete idea about ETL testing…

  • Data Migration Testing

    Here is a detailed Data Migration Testing tutorial for the benefit of our readers. Let's get started. We often hear that an application is moved to a different server, the technology is changed, it is updated to the next version or moved to a different database server, etc., What does…

  • Database designing tools

    Introduction to free open source Database Design Tools: In this article, we will take a look at the top Database Designing Tools that are available in the market. Good database design is a pre-requisite to work with the database in an efficient way as it reduces the maintenance efforts, minimizes…

  • DataWaewhouse ETL Testing

    This Tutorial Covers Goals & Significance of Data Warehouse Testing, ETL Testing Responsibilities, Errors in DW and ETL Deployment in detail: In this In-Depth Data Warehouse Training Series, we had a look at the What Is ETL Process in Data Warehouse in detail in our previous tutorial. This tutorial will give…

  • 9 Database Security Testing

    Learn about Database Security and its related concepts like security threats, best practices to follow, testing types, techniques, testing processes, etc.: In this tutorial, we will explore what is database security, the type of database threat that exists, the importance of securing our database, and some tools that you can…

  • Alpha Versus Beta Testing

    Alpha and Beta testing are Customer Validation methodologies (Acceptance Testing types) that help in building confidence to launch the product and thereby result in the success of the product in the market. Even though they both rely on real users and different team feedback, they are driven by distinct processes,…

  • Database Testing With JMeter

    Overview: Hi Testers!! In this tutorial, you will learn how you can test the performance of your database using JMeter. You can probably check for any database you have in your project. MySQL is used in this tutorial. Below are the topics which you will learn in this section: Pre-Requisites…


95 thoughts on “Database Testing Complete Guide (Why, What, and How to Test Data)”

  1. Hi,
    Your article was pretty detailed and useful. Keep posting article like this. Thanks for sharing this helpful article.

    Reply
  2. Very nice information about database testing….
    I’m preparing for an interview on DB testing….
    I hope it will help me…

    Reply
  3. Hi Vijay,
    It is indeed a very informative post. I was just wondering if you can in general tell us which will be a better option to go for: DATABASE Testing or AUTOMATION testing.
    Note: I am already Oracle Certified Professional(did it 3 years ago) and ISTQB CTFL. Now I am in manual testing(eg; website, software, process flow testing, simple database testing etc..). Want to grow professionally. So plz throw some light.

    Thanks,
    Sachin

    Reply
  4. This is great article and very concise and straightforward information. It gives me an idea on how to test database. Actually, I am only doing Manual Testing. I am interested to learn on how test DB. Thank you.

    Reply
  5. HI, I need to know about database testing . How it ill be Is it like manual testing(Regression,functional….) or else is there any tools for database testing

    Note: I need to know this in perspective of Tester (Black box tester/manual)

    Reply
  6. Hello Guys,

    I wanted to know the queries to be used in database testing alone with proper database testing concepts
    Can you please do the needful

    Thanks

    Reply
  7. Thank you for the article. Many of the things described is what I’m doing, but it’s always helpful to name them, which helps put things into another perspective.

    Reply
  8. Hello, can you give me advise anybody?
    Which tool can be used for this case:

    1. Data Mapping /
    Whenever a certain action is performed in the front end of an application, a corresponding CRUD (Create, Retrieve, Update and delete) action gets invoked at the back end. A tester will have to check if the right action is invoked and the invoked action in itself is successful or not.

    My point is to test database – I put some action on the front-end and I have to check its influence to DB – means this action on front-end can create/update more fields in DB.

    Thanks a lot!

    Reply
  9. vijay can u please help us in DB testing like QTP tutorials.

    as well as the key features in DB Testing. Thanks a lot for the article.

    Reply
  10. is there any tools or ide that can i use for database testing whether i can check that data flow is correct and following the parenting data sequence ? i am a fresher tester joined in the job 3 days ago. My project manager told me to test the database with data input. So i am in dilemma what should i do. I simply want to load that sql or dba file into the tools and manually input some data that will check my database working flow….
    please help me out. Since the day 1 of my job i am following this website and helped me a lot.

    Reply
    • I can totally understand how streessful you are. I’m in the same road just as you walking I’m running, it’s my third week first job which I involve with backend testing it’s very different then UI testing as I have learned but regarding backend just be patient and try to start first with API’s you will get all by running the time. Sorry that I cannot help you much due to I have the same issues that your suffering at the moment.

      Reply
  11. Hello Guys,

    I want to do the database testing and quite puzzled as I am not sure how to do the database testing .. not sure about the queries to be used
    Can you guys please add the queries and interview questions for database testing

    Thanks

    Reply
  12. @Rupalli Jadhav. I suppose you can do SOME tests even if you don’t know SQL, as long as you can use an API to modify your database. For example, if a foreign key is supposed to perform an action when you delete a record from a table, you can delete the record using an API. But I definitely recommend some SQL skills, to test an RDBMS.

    Reply
  13. hi,
    I’m testing payment gateway how i’ll make sure that user data and transaction is secure from third party? Basically i want to test security of Payment Gateway.

    Reply
  14. Very interesting article,database testing is often seen as a overhead. But does not have to be. With testing tools such as SOAP UI you can also build database assertions with SOAP UI JDBC functionality.
    In this way you can build Sql queries into your automated tests. When testing at the web service layer.

    Reply
  15. @Vijay please tell me what is most important feature to be tested during database testing and what factors kept in mind before starting database testing for a simple desktop based application.

    Reply
  16. DataBase perfomance testing is basically testing your sql queries. Infact this is done in unit testing or by SQL DBA.
    In SQL Server. In sql server 2000 there was a tool called Qery Analyzer. Frm SQL SErver 2005 onwards Micrsoft changed everything to Managment Studio. In management studio there is something called execution plan. Execution plans can tell you how a query will be executed, or how a query was executed.
    There are 2 major stages are there
    1. Processes that occur in the relational engine
    2. Processes that occur in the storage engine.
    In the relational engine the query is parsed and then processed by the Query Optimizer

    Reply
  17. @AdarshTrivedi

    There is no such thing as most important feature during a database testing.

    Everything depends upon whatever the business objective.

    If for example say it is a data transformation and data conversion project then it would be very important to validate the business rules as well as data integrity before and after the application of the business rule logic.

    If it is a data load project from one database to another then the mapping between the different columns in the source and target databases need to be validated.

    In general database schema , database mapping , data integrity , consistency between front end and back end data and validation of the business rules are considered as most important.

    It all depends upon what type of desktop application you have to validate. If it involves data transactions then data consistency between front end and back end , data integrity during transactions and business rule validations need to be taken into consideration.

    I hope it helps you to understand the issue in question.

    Take care
    Debanjan

    Reply
  18. Hello ,
    I have a question . What things need to be consider or plan when we have to test Data cleanup feature of an application. For example , in some application there is a feature any inactive (not used any more) data older than one year should get deleted.
    What should be the test Strategie to perform such test.

    Thanks

    Reply
  19. Great post. When you are dealing with lots of data, the easier way to test database is to automate it. DB testing is usually ignored and if tested the usual way is to use any data mining tool which again requires manual intervention. Just sharing the article by TestingWhiz on the need of automation for your db testing, hope this would be a good further read – http://bit.ly/1s7BIFx

    Reply
  20. How to test data records of all the tables from SQL and Postgresql?
    If the data from SQL is being migrated to postgresql and I am supposed to test that all the records of all the tables are correct. This is periodic job. I am not supposed to used paid tool.

    One difficult way is I can write java or python code to get the data from respective tables from both the DB’s then compare it but the records are in millions.

    Please suggest if there are any easier way? Free tool plz?

    Reply
  21. Hi Vijay,

    Really good and helpful information for begginer.

    I just have one doubt about “Database schema” topic in “What to test – different components”.

    Content mentioned below point:
    SQL Query DESC to validate the schema.

    Can you explain it? I thought column name should be there instead of table name.

    Please correct me if wrong or misunderstood.

    Reply
  22. Nice post. If we can do data certification, data validation, query execution, do activities on frontend and query db to verify it, compare two databases, sync the activity, combine database testing with functional testing and some such are routine activities and unless some tools are used it may not be really effective testing strategy. I have done all above activities using Selenium and TestingWhiz and found both equally satisfying my need. Definitely both has its pro and cons and one can evaluate them in person and conclude.

    Reply
  23. Hi, all. I have a question. What if the tested does not know the DB at all of a project and mostly on same project working on UI side? If tester has to test DB related to a test case how a tester can find out which table to use, how to use it, should required document have table name already written or tested himself need to find in DB?

    Reply

Leave a Comment