All About Database Testing – Why to Test, How to Test, and What to Test?

Database Testing Guide:

Computer applications are more complex these days with technologies like Android and also with lots of smart phone 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 secure and quality databases.

In this article you will learn all about Database Testing. Why to test- How to test- What to test.

Why do we test a database?

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

All About Database Testing 1

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/front end 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 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 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. (image credit)

DB Testing

  • Atomicity means that a transaction either fails or passes. This means that even if a single part of a 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

3) Data integrity:

This means that following any of the CRUD operations, the updated and most recent values/status of shared data should appear on all the forms and screens. A value should not be updated on one screen and display an older value on another one. 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.

How to test – Database Testing Process

The general test process for DB testing is not very different from any other application. The following are the 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:
on <table name>
from<user id1, user id2…useridn>;

What to test – different components

1) Transactions:

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

These are the statements commonly used:


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


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 schema:

A database schema is nothing more than a formal definition of the 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 searching.
    • 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) Trigger:

When a certain event takes places 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 during 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 – 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 a value other than the default value into 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 = “&lt;Default value as required by the business requirements&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 = “&lt;Unique value as required by the business requirements&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 which 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.


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

I hope this article 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 have done this before. Your comments and feedback are important to us and our readers to learn from your experience.

If you Like it, Share it!

Recommended reading


#1 Sahil

Awesome share Vijay…was needing it

#2 Kapil Sharma

Again one of the greatest and deepest post from software testing help.

You guys rock!

#3 AdarshTrivedi

@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.

#4 Bidya Paul

Thanks a lot Vijay… was awaiting for this concepts

#5 Pravin

Thanks a lot

#6 Hema

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.

#7 Kanif

Once again great article Vijay, its really to the point.

Few nice database testing articles on below link:

#8 Sunil

Thanks a lot ….Gr8 job…..

#9 Preet

Good one…really very knowledgeable. Thanks.

#10 Topher

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.

#11 Suresh

Very Useful post!! Thanks

#12 Vins

Gr8 Vijay……really very useful.

Thanks a lot for such a wonderful article.

#13 nivedita

very helpful……a complete package for my students!! thankyou admin!!!! keep goin guys!!

#14 Mallikarjun


Simply superb, appreciate you. Keep on posting and do help to the job seekers your level best.

#15 neha


Very informative.
Could you please answer the question :’What are the challenges faced while doing DB testing”?

#16 Aswathy

hi vijay,
u r doing a great job…Thnx a lot….Its very helpful for ISTQB preperation…

#17 Kishore

Very nice post. Thanks Vijay

#18 Debanjan


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

#19 mahalaxmi

hi Friends

can any body know “what is save point ?” explain me with examples. Here i always confuse.

#20 Sangram Kumar Das

Very useful post..This is going to help the testers who are new to the DB testing..Thanks :)

#21 Rupalli Jadhav

explanation of db testing is very useful,and explnation of subject is so simple

#22 rafeeq

I am not comfortable in sql can I do software testing or not

#23 Federico Razzoli

@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.

#24 Arivazhagan

it’s really very helpful .



#26 sheeba mathew


#27 Deepika

Thanks so much Vijay … it’s very useful … Good Job your doing …

#28 Sanjeev

Great Job Vijay

#29 Swathi

This article is very good.I found useful when my interview scheduled in sometime..

#30 vijayc

what are the shortest testing techniques to test a page ?
(we have to tell BVA, ECP,Response time

#31 vijayc

how many times u take NCR?

#32 Prabhu

used your web page for many issues and it was very helpfull.

#33 shiva

This information was very useful

Thank you

#34 usdaknk

thanks ,
good article

#35 Himanshu

Good content . Thanks a lot .

#36 Prashant Chambakara

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 –

#37 Savita

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.

#38 Peter

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!

#39 Amit Sharma

Very Helpful


#40 Sana

Very Helpful

#41 asdfa


#42 nix

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.

#43 krishna

Hii friends This is Krishna
I Want Database Testing 3 plus Experience resume,, Plz Plz Send My mail Id =

#44 sunil

hi i want to know for data base testing it is mandatory to know the coding of the application or not.

#45 murali

Thanks mr.vijay
Its very helpfull to me and you did a good job

#46 sudha

Superb,it is very useful.thanqqq

#47 Anup

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

#48 Ahmed

Easy to understand, vijay appreciated

#49 shivam lavania

It is very help full for beginners and experienced person

#50 rekha

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)

#51 Sachin

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.


#52 R Sharma

This was very helpful. Thanks :)

#53 Akbar Mohamed

Excellent post, it is good to understand

#54 Mohd Salman Aryan

Really Great

#55 saurabh

Nice Post…Very helpful

#56 jeffro

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.

#57 rajesh kuchana

Waste article there is no proper information how a tester can test a store procedures in db

#58 Bindu

This information was useful

#59 akanksha

when will start database testing?

#60 Singh

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?

#61 tanbir

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.

#62 Barath

Great work Vijyay…

#63 Mekala Ramesh

Valuable information… Great Work…

#64 Kannan

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

#65 mohit

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.

#66 prasad

This useful information but also say about db connections and about sql also….tq

#67 Shivaji

Good Post. The contents are adequate for beginners to understand subject.

#68 Mahesh

What is the diff between Data Validity and Data Integrity in DB Testing ? Please explain with a simple example.

#69 raman

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


#70 raman

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


#71 qba
Application to compare results of two SQL queries and more

#72 Raj

Nice information about database testing.

#73 Shini

Very impressive artical on database testing. Quick and clear.

#74 K

Very Nice ..

#75 K

nice article

Leave a Comment