Perform Database testing in Karate

When it comes to API testing there's a good chance of needing to validate the databases in the flow.
You can perform database validations with karate by following the below steps.

1. Insert spring-jdbc and mysql-connector-java  to pom.xml

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

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

}

The above methods,
readValue - Returns a specific single value from the datatable
readRow - Returns one row from the database
readRows - Returns multiple rows from the datatable

3.  Create JDBC connection in the feature's "Background:" 

In your .feature file create the jdbc connection as follows. config will include username, password and url of the database you are going to test.

  Background:

    # Create JDBC connection with DbUtils java class

    * def config = { username: 'root', password: 'root', url: 'jdbc:mysql://localhost:3306/sample_db', driverClassName: 'com.mysql.jdbc.Driver' }

    * def DbUtils = Java.type('util.DbUtils')

    * def db = new DbUtils(config)

4. Write database validating test steps

Since we have written the DbUtils returns a Java Map, it becomes a JSON here, which means that you can use Karate's 'match' syntax for assertions.

    * def vehicles = db.readRows('SELECT * FROM vehicles')

    Then match vehicles contains {vehicle_id:1, availability:'true', type:#ignore }

    * def vehicle = db.readRow('SELECT * FROM vehicles D WHERE D.vehicle_id = 2;')

    Then match vehicle.availability == 'false'

    * def test = db.readValue('SELECT ID FROM vehicles D WHERE D.ID = ' + 3;')

    * match test == id

5. Use JdbcTemplate's batchUpdate to insert data into database

In some cases you will need to insert data in to the database prior to running tests 

Insert the following method in to DbUtils.java

    public void insertRows(final String sql){

jdbc.batchUpdate(new String[]{sql});

    }

Add the following test steps in to the "Background:"

    * def query = read('insert_query.txt')

    * db.insertRows(query)

5. Cleaning data-tables after each scenario or after feature

Create after-scenario.feature in the current package.

@ignore

Feature: Database cleaning

Scenario: clean the database before each scenario begins

       #Create JDBC connection with DbUtils java class

  * def config = { username: 'root', password: 'root', url: 'jdbc:mysql://localhost:3306/sample_db', driverClassName: 'com.mysql.jdbc.Driver' }

  * def DbUtils = Java.type('util.DbUtils')

  * def db = new DbUtils(config)

  * db.cleanDatatable("TRUNCATE sample_db.vehicles;")

You do not want this to run when running all the features. Adding '@ignore' tag will exclude this feature.

Insert  the following step in to the "Background:". This will configure the after scenario to call after-scenario.feature at the end of every scenario

* configure afterScenario = function(){ karate.call('after-scenario.feature'); }