[Karate API Automation Framework] [MySQL server DB] Database Scenario Validation with Karate Framework

In last week, I tried to do some database validation with Karate. I had a specific scenario, where the validation need to be done only by DB. The scenario was:

Scenario: There are 4 different fields for giving ratings to some entity. Apart from these 4 fields, there is one more field, which is just providing “comment” while giving that rating. So overall, there are 5 fields from UI.

Apart from this, there is a column in DB, which records the date and time for above field updates.

Validation Point:

1.      If there is a change in rating from any of the 4 fields, it should update the date column in DB for all fields, (4 rating fields and comment field)

2.      If there is only a change in a comment, then just update the date-time cross ponding to the comment row. Rating field date/time should not update.

How you can update the Rating or Comment: There is an API available for updating the rating as well as the comment.

 

This scenario brought me to use the Karate framework for database validation. I had the DB details and queries for fetching the date column/ratings/comment section. 

Now let me show you the changes, I did to automate this scenario with the Karate framework.

Note: For demo purposes, I have used the default MySQL server DB.

1.    The first requirement was to make the database connection. For this purpose, I added below 2 jars springjdbc  and mysql-connector-java  in my pom file.

 

<dependency>

<groupId>org.springframework</groupId>

<artifactId>springjdbc</artifactId>

<version>5.1.9.RELEASE</version>

</dependency>

 

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>5.1.6</version>

</dependency>

 

2.  The second step is to create a java utility which will do connect with your database and through which, you can do read, insert operations.

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;

import java.util.Map;

public class DbUtils {

    private static final Logger logger = LoggerFactory.getLogger(DbUtils.class);

    private JdbcTemplate jdbc = null;

    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);

        System.out.println("connection set");

    }

    public Object readValue(String query) {

        return jdbc.queryForObject(query, Object.class);

    }

    public Map<String, Object> readRow(String query) {

        return jdbc.queryForMap(query);

    }

    public List<Map<String, Object>> readRows(String query) {

        return jdbc.queryForList(query);

    }

}

 

3.       The third step was to use readValue, readRow and readers' methods into your feature file.

Feature: Connect to DB



  Background:



    * def config = { username: "", password: "", url: "", driverClassName: "com.mysql.cj.jdbc.Driver" }

    * def DbUtils = Java.type("apiTest.DbUtils")

    * def db = new DbUtils(config)



  Scenario: Get a particular actor detail from mysql default saqila table



    * def actorMatchingLastNames = db.readRows("SELECT last_name FROM sakila.actor where first_name = 'PENELOPE' ")

    And print actorMatchingLastNames

   * def actorDetailFullRowAsList = db.readRow("SELECT * FROM sakila.actor where first_name = 'NICK' and last_name = 'STALLONE'")

    And print actorDetailFullRowAsList

    * def actorNameValueCheck =  db.readValue("SELECT last_name FROM sakila.actor where first_name = 'PENELOPE' and actor_id =1")

     And print actorNameValueCheck

 

For demo purposes, I used the MySQL default DB sakila. If you want to run this feature file, you need to install the MySQL server. It will come with this default DB.

If you already have the DB, then just reuse it here.

4. Once you will have the data from the database, you can validate it with your API responses, which I did in my case.

#KarateFrameowrk #DatabaseTesting #DatabaseTestingwithKarate #DatabaseTestingAutomation #APITestingWithDatabase #Automation