As we know that Database is used in every application where user information needs to be stored. For example: an eCommerce website. All the products which are listed on the screen are stored at back-end with complete information such as product availability, payment details, etc. So, whenever any action is performed on user interface such as someone orders something on this e-Commerce site then the state of this product in database gets changed. It may happen that there was a single product which user has ordered and now it becomes out of stock. This information will get updated in database first and then it will be reflected on user interface.
Here, testers have to check the database after every action just to ensure that all the information gets saved in correct tables with correct information.
There are two ways to test a database:
In manual testing, tester has to look on each table manually and can report the issue. But, it becomes more complex when there are multiple actions on the screen and checking all the tables one by one is time-consuming. For better performance, we can test the database with the help of automation. It will reduce manual effort up to a great extent and can deliver quality work on time.
Here, I am using Selenium Web driver for automating database testing of the application. Selenium Web driver is an automation tool used for automating web and mobile applications. It provides in-built APIs which helps in writing user-friendly and easy to understand code. It interacts with the web elements and performs the expected function. Tester can use any programming language such as Java, C#, Ruby, etc to write Selenium based test scripts which will be executed on different browsers.
Here, in my blog, I will be discussing database testing using selenium webdriver. Selenium webdriver will simply interact with my browser and do the expected tasks which I have scripted in test script using java.
Let’s Discuss database testing in more details.
What is Database Testing?
Database Testing is the verification of retrieved values from the database by a web or desktop application. Data which is displayed on UI should match as per the records stored in the database.
The importance of database testing in software testing should not be ignored as it is the data which is visible to the user. As software industry grows day by day, applications get more complex. So, there is a need to test its various parameters to make it reliable and robust.
Let’s take a real world example of banking application where we can understand about the significance of database testing in more depth. Suppose, there is client A who deposits it’s amount in the bank and gets acknowledgement, which should have a transaction id and payment amount with its status.
But, due to some technical issue in the banking application, transaction id gets generated in combination of alphanumeric + special characters and gets saved into database only. Ideally, it should be a numeric value.
User raises this issue with the bank. The bank finds that database migration was done yesterday for the application and it might cause this problem in generating transaction id for the transactions. They forward this issue to technical team and after analyzing, root cause was:
On saving transaction information from application UI, a transaction id gets generated in the Database. But, due to migration of this database from older to newer version, now, transaction id gets saved after manipulation. Hence, this manipulated id starts displaying on acknowledgement screen and for verifying it, database testing is required so that tester can retrieve the correct data from the correct table.
In this way, database testing came into existence and become more important for all platforms.
Aspects to be tested:
Data Mapping refers to the mapping of data between GUI and its relational database. So, here question arises, how to test data mapping. Tester needs to verify that all the information which is entered into the application, should be saved in the correct DB tables as well as in correct format. For example: If user is adding current date in application, and in the DB table it should be saved in dd/mm/yyyy format then here verification check is that entered date should also gets saved in same DB format i.e. dd/mm/yyyy. If it is any other format which is different from db table definition then tester can log it as a bug.
Also, if user is performing any CRUD (Create, Retrieve, Update and Delete) operation, then here also, data mapping should be verified. The same value which is displayed on UI should be saved in DB and vice-versa.
Ensure ACID properties
ACID Properties refer to Atomicity, Consistency, Isolation, and Durability. For a DB transaction, these properties must always hold true
- . For more details about ACID properties, please refer the Link.
High-Level diagram of database testing
Scenario under consideration:
I will sign in into the Gmail application using selenium script and after this, will verify this username entry into Database. So, this interaction of application with its database will be performed by JDBC driver and will process SQL queries written by me in a property file.
Following are the step by step guide to automate database testing using the selenium script written in Java.
Make a connection with DB:
Install MySql and MySql Workbench. Don’t forget to note down the following items:
Now, connect to MySql server from MySql Workbench.
Click on “Connect to Database” and enter the following details:
A name to your DB connection
NOTE: These Port Number, Username and Password are same as which we noted down in Step 1.
Click on “Test Connection” just to check whether the DB connection is established or not with the entered credentials.
After successful connection, now, you need to create a database which is having a table with some data. Here, I am taking an example of “emp” named database having table named as “employee”.
Now, insert some values in the “employee’ table as following:
Now, its time to do some coding. For this, you will need Eclipse and create a Java Project under it.
Here, I have created “Database” named Java Project having ‘dbpackage” as package name with few classes.
Download the MySQL JDBC connector and add this jar into your project. JDBC (Java Database Connectivity) connector helps java application to interact with the database.
Now, we will create a property file which keeps key/value pair. Purpose of using property file is to manage dynamic data easily. If any property changes, then you need to change in property file only, not in massive code. Here, I am keeping:
- DBConnectionString (of underlying Database)
- Url (of application under consideration),
- DB username and password,
- Sql query which will fetch the record from table where sign-in data is stored.
- Specific table column name which we need to verify. (optional)
Create a property file and paste the following code into it.
Now, create a class for reading data from property file and paste the code as following-
Create a separate class for verifying DB table data. This class is responsible for fetching the matched data as per the passed SQL query and db column name.
Now, we need to write a selenium script which will help in automating Gmail sign in process. For this, create a separate class for all the methods used in the test script. The written script is-
Here, we have a Main class which will call the above classes. First of all, property file is loaded and then login() method (written in selenium script) is called which performs the Gmail sign in action and then control is sent to the dbVerification() class where cross checking of both (Username with which user Signs in and an entry in Database after Sign in) will be done.
User login with firstname.lastname@example.org into the Gmail account . After login this username entry gets inserted into under “emp” table name (as provided in SQL query). Here, dbVerification() class cross checked this username and found that this entry is available in “employee” table under passed column name as “Emp_Name”.
As this record exists in Database, hence, this test results as “UI and DB value matches” which means that username with which user sign in into Gmail account is available in Database.
- For testing database using Selenium, you will need to:
- Setup a connection to the Database
- Send Queries to the Database
- Process the results
The Syntax to connect to Database is
DriverManager.getConnection(URL, “userid”, “password” )
For sending SQL queries, Statement Object is used as:
Statement stmt = con.createStatement();
- Store the query result in resultset as:
ResultSet rs = stmt.executeQuery(select * from employee;);
- Use verification method to verify DB and UI values.
Source Code for downloading:
- Link – http://www.guru99.com/database-testing-using-selenium-step-by-step-guide.html
- Youtube Video – https://www.youtube.com/watch?v=HDRoiK0BWYs