Perform Data Manipulations in Karate DSL

Hello Audience !!

Hope you all are safe !!

Covid pandemic has affected every individuals in different ways and in my case it has developed a flair to create my first blog based on my learning journey in Karate DSL.

During my learning process, I observed that there are very limited sources available on how do we perform data manipulations using this fantastic test automation framework.

So here it is !!!

What is the first thing that comes in your mind when you think of API Testing/Automation?

API is a service which helps two different applications to communicate with each other. Most APIs are used to abstract the business logic and direct database access to any application.

In simple terms, API is like a carrier of information/data between two end points of an application. Data are stored in multiple tables of the database depending on the API functionality.

When I think of API testing/Automation, first and foremost thing that comes to my mind is Database validations.

Why is it important to do data validation when it comes to API testing ?Because Database is the place from where all application data is present through which APIs gets desired response for requested parameters while DB interaction.
So in this case, validating DB becomes an integral part of API Automation or testing to ensure the APIs are fetching correct data from databases.

How do we achieve this using Karate DSL for API Automation?

This can be done by following steps:

1. Insert dependencies in pom.xml, spring-jdbc and mysql-connector-java.

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId><version>5.1.6</version>
</dependency>

2. Create util.DbUtils java class under src/main/java and add the following java code snippet.

package util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import com.mysql.cj.xdevapi.Statement;
import java.util.List;
import java.util.Map;
public class DbUtils {
private static final Logger logger = LoggerFactory.getLogger(DbUtils.class);
private final JdbcTemplate jdbc;
public DbUtils(Map<String, Object> config) {
String url = (String) config.get("url");
String username = (String) config.get("username");
String password = (String) config.get("password");
String driver = (String) config.get("driverClassName");
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
jdbc = new JdbcTemplate(dataSource);
logger.info("init jdbc template: {}", url);
}
// SELECT statement: Return single value from the database public Object readValue(String query) {
return jdbc.queryForObject(query, Object.class);
}
// SELECT statement: Returns one row from a tablepublic Map<String, Object> readRow(String query) {
return jdbc.queryForMap(query);
}
// SELECT statement: Returns multiple rows from a data tablepublic List<Map<String, Object>> readRows(String query) {
return jdbc.queryForList(query);
}
// UPDATE RECORD of a tablepublic String update(String query) {
int count = jdbc.update(query);
return "Record updated successfully : " + count;
}
// DELETE RECORD of a tablepublic String delete(Integer id, String query){
int count = jdbc.update(query, id);
return "Number of records deleted : " +count ;
}
}

3. Define JDBC connection parameters in the“Background:” section of the Feature file.

Background:
# Create JDBC connection with DbUtils java class
And def config = { username: 'root', password: 'root', url: 'jdbc:mysql://localhost:3306/sample_db', driverClassName: 'com.mysql.cj.jdbc.Driver' }And def DbUtils = Java.type(‘util.DbUtils’)And def db = new DbUtils(config)

4. Write database validation test steps

a. Select statement returning with single row:

And def test_id = response.idAnd def test_name = response.nameAnd def query = "select * from test_table where id = " + test_id #test_id is dynamic in this case, in order to hardcode id's value we can do it by passing it in double inverted quotesAnd def dbResult = db.readRow(query)And print dbResultAnd match dbResult.name contains test_name

b. Select statement returning with multiple rows:

And def test_id = response.idAnd def test_name1 = response[0].nameAnd def test_name2 = response[1].nameAnd def test_name3 = response[2].nameAnd def query = "select * from test_table where id = " + test_id #test_id is dynamic in this case, in order to hardcode id's value we can do it by passing it in double inverted quotesAnd def dbResult = db.readRows(query)And print dbResultAnd match dbResult[0].name contains test_name1And match dbResult[1].name contains test_name2And match dbResult[2].name contains test_name3

c. Update statement:

And def query = "update test_table set id = 4 where name = 'ABC'" And def dbResult = db.update(query)And print dbResult// To ensure that record is updatedAnd def query = "select * from test_table where id = 10 "And def dbResult = db.readRows(query)And match dbResult contains "No record found"

c. Delete statement:

# Delete recordAnd def query = "delete from test_table where id = ? "And def dbResult = db.delete( 10 , query)And print dbResult// To ensure that record is deleted And def query = "select * from test_table where id = 10 "And def dbResult = db.readRows(query)And match dbResult contains "No record found"

5. On running above feature file, you can validate the DB result either through select query or by manually checking the DB.

Hope this blog has helped you for performing DB validations using Karate-DSL.

Stay tuned for more blogs !!! Till then happy karating !! :D

QA Automation engineer @ Paytm Insider| Ex-Automation Tester @ Reliance Jio Infocomm Ltd. | Ex-Software Developer @Tata Consultancy Services Ltd.