When functional testing is done from the front end of the application, is it ensured that the data stored in the database is correct or data fetched from the database and shown in the application is correct? Certainly not. For Instance, consider the need to display the profile information of a person by clicking his name. The information will be displayed. However, we will not be sure whether it belongs to the person’s name we clicked. As a result of data validation in the database, we can be sure that the information displayed is correct. In this blog, we will see What is data validation in the database, How to do that and how can we automate the same.
What Is Database Validation?
As part of functional testing, we will confirm that no error messages are displayed on providing correct inputs to the form or webpage after clicking the “Submit” button. But to confirm if the data we provided has been stored or not, we need to check the database. By doing so, we can ensure that the data stored in the database is the same as the data we provided while filling out the form.
In addition to this, some of the columns in the database will be automatically populated based on the input provided, which is not directly obtained from the UI. Let’s consider an example: Suppose you have created a new user with the necessary details, and someone has updated that user’s information. Now, you need to determine the exact date and time of the user creation or the most recent update. Additionally, you might want to check the enrollment status of the user, which is not displayed in the UI. In such cases, you can obtain this kind of data solely from the database.
Data Validation refers to the process of ensuring that the data provided from the front end is stored accurately in the appropriate tables and columns of the database, in the expected format. It also involves retrieving certain data from the database, which may not be displayed on the front-end webpage.
How to do Database Validation?
Firstly we saw what is database validation. Now let’s jump into the topic “How can we do that?”. This can be carried out manually or automatically. We can do this manually by checking the database through queries. Consider you have to test a feature that will provide you with a report about production in the year 2013-2023. By selecting the years and clicking on submit, you can see the result on a web page. To ensure your functional testing is comprehensive, you need to verify that the displayed data is accurate.
Manually validating it in the database is another way to accomplish the same. By connecting to your database, you can execute a query to retrieve the production data for the years 2013-2023. Through the comparison of both the UI result and the database query result, you will be able to figure out whether the information displayed is correct.
Why don’t we think about another Example? You need to test a feature that involves sending an email to a client about the end date of their subscription by clicking a button. When you click “send alert mail” under the subscription section on his profile page, the email will be sent. Can you confirm that the email is sent to the intended person? To accomplish that, we can retrieve his email from the database by querying using his name or ID, which is visible in the UI. Hence Database Validation can be achieved manually.
Is Data Validation can be Automated?
Why not? Data Validation can be Automated. We can connect to the database using any programming language. Each type of database has different APIs available in various programming languages to establish a connection with that specific database. It would be easy if we understand with an illustration. Most of the organizations are achieving automation using Selenium Java for Web UI Automation or Robot Framework or other open source tools. Will deeply jump into it and learn How Database Validation can be accomplished in both.
Data Validation through Robot Framework:
Robot Framework is a test automation framework that is written in Python. Robot Framework has lots of libraries written in Python which ease our work. Among that, we have a library called “Database Library”, which provides keywords for connecting to databases, executing SQL queries, and manipulating database data.
Database Library:
We need to import Database Library to our Project. Firstly to connect with the database we have a keyword called “Connect to Database”. This will take DB Name, DB Username, DB Password, DB host, and DB Port as arguments and connect to the database using “psycopg2” API. There are various Python libraries available that implement the Python DB-API and provide database connectivity and interaction capabilities. For example, Let’s consider the popular database Postgres. “psycopg2” -A PostgreSQL adapter for Python is used to connect and interact with Postgres Database.
Some of the commonly used Keywords:
1. Connect To Database: Establishes a connection to a database with the parameters such as driver name, database name, username, password, host, and port.
2. Disconnect From Database: Closes the database connection.
3. Execute SQL String: Executes an SQL query or statement. You pass the SQL query as an argument, and it returns the query result.
4. Execute SQL File: Executes an SQL script file. It executes the SQL statements from the path of the given file.
5. Execute SQL String Many: Executes multiple SQL queries or statements separated by semicolons. It can be used to execute multiple queries in one go.
6. Execute SQL Script: Executes an SQL script as a single transaction. You provide the path to the SQL script file, and it executes all the SQL statements in the script within a transaction.
7. Fetch All From Last Query: Retrieves all rows from the result of the last executed query.
8. Fetch One From Last Query: Retrieves the next row from the result of the last executed query.
9. Fetch Many From Last Query: Retrieves a specific number of rows from the result of the last executed query.
10. Query Should Be Empty: Checks if the result of the last executed query is empty.
11. Query Should Not Be Empty: Checks if the result of the last executed query is not empty.
12. Select From Table: Executes a SELECT query on a specific table.
13. Insert Into Table: Executes an INSERT statement on a specific table.
14. Update Table: Executes an UPDATE statement on a specific table.
15. Delete From Table: Executes a DELETE statement on a specific table.
We can utilize the aforementioned keywords to execute actions on a database as part of the database validation process, similar to how we would perform it manually.
Data Validation Using Selenium Java:
Unlike Robot Framework, here in selenium java, we need external drivers to connect with respective databases. Consider we use Postgres database, we need postgresql driver to connect with the same.
We can add the Jar Files of the driver directly to our Project or we can add maven dependency in pom.xml to download the needed jar file while execution.
To establish a connection and interact with the database in Selenium Java, JDBC (Java Database Connectivity) needs to be used. Java Database Connectivity is an API provided by Java for connecting and interacting with relational databases. It provides a set of Java classes and interfaces that enable Java applications to communicate with databases using standard SQL statements.
Key components of JDBC:
- Driver Manager: The Driver Manager class manages the available database drivers. It helps in loading the appropriate driver class and establishing the database connection.
- Driver: The Driver interface defines the methods that a database driver must implement. Each database vendor provides its own JDBC driver, which is responsible for communicating with the database server.
- Connection: The Connection interface represents a connection to a specific database. It provides methods for executing SQL statements and managing transactions.
- Statement: The Statement interface is used to execute SQL statements and retrieve the results. It supports different types of statements, such as simple queries, updates, and stored procedure calls.
- ResultSet: The result of a database query is represented by the ResultSet interface. It provides methods to traverse and retrieve the data returned by a query.
Firstly, objects for Connect, Statement, and ResultSet interfaces can be created. Then using the getConnection method in Driver Manager with DB URL, DB Username, and DB Password as parameters we can establish a connection with the database. Once the connection is established, various operations like executing queries, updating data, etc. can be performed using the Connection object. For example, executing a query and retrieving data. Retrieved results will be in ResultSet which can be manipulated based on our needs.
Refer to this link for the code snippet to Connect with database and fetch query results
Conclusion:
Fetching data from databases and leveraging it for automation brings immense value to organizations, enabling streamlined workflows, informed decision-making, and increased operational efficiency. Moreover by establishing a database connection, constructing SQL queries, fetching and processing the data, and integrating it into automation workflows, businesses can unlock the full potential of their data assets. Embrace the power of database automation and harness the benefits of data-driven automation in your organization. Hence you can achieve effective data validation through Automation
References:
- https://pypi.org/project/robotframework-databaselibrary/
- https://docs.robotframework.org/docs/different_libraries/database
- https://dev.to/dallington256/connecting-to-mysql-database-in-java-3lej
- https://www.guru99.com/database-testing-using-selenium-step-by-step-guide.html
For more interesting blogs, check our Engineering Blog site.
