ADVANCED

Database Testing

Validate database state in integration tests using Java interop with JDBC connections, execute SQL queries, and assert results with Karate's match syntax.

On this page:

• Java interop approach - Create Java helper classes for JDBC operations
• Basic setup - DbUtils class for queries and updates
• Parameterized queries - Prevent SQL injection
• Setup and teardown - Manage test data in Background
• Transactions - Validate commits and rollbacks
• Configuration - JDBC URLs and Maven dependencies

How Database Testing Works

Karate does not have built-in JDBC support. Database testing works through Java interop where you create Java helper classes that execute JDBC operations and return JSON data for validation.

Gherkin

Feature: Database testing overview

Scenario: Basic database query
  # Call Java helper class that executes JDBC query
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def users = DbUtils.query("SELECT * FROM users WHERE active = true")
  # Returned data is JSON - use match for assertions
  * match users == '#[5]'
  * match users[0] contains { email: '#string', name: '#string' }

Java Interop Approach

You write Java classes that handle JDBC connections, execute queries, and return JSON. Karate calls these classes using Java.type() and validates results with match syntax.

Basic Database Setup

Create a simple Java helper class to execute database queries and return JSON results.

src/test/java/com/mycompany/DbUtils.java

package com.mycompany;

import java.sql.*;
import java.util.*;

public class DbUtils {

    private static final String DB_URL = "jdbc:h2:mem:testdb";
    private static final String USER = "sa";
    private static final String PASS = "";

    // Execute SELECT query and return results as List<Map>
    public static List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> results = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                results.add(row);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database query failed: " + sql, e);
        }

        return results;
    }

    // Execute INSERT/UPDATE/DELETE and return affected row count
    public static int execute(String sql) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            return stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException("Database execution failed: " + sql, e);
        }
    }
}

Gherkin

Feature: Using database helper

Scenario: Query users from database
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def users = DbUtils.query('SELECT id, name, email FROM users ORDER BY id')
  * match users == '#[3]'
  * match users[0] == { id: 1, name: 'John Doe', email: 'john@example.com' }
  * match users[1].name == 'Jane Smith'

Query Execution with Parameters

Use parameterized queries to prevent SQL injection and make tests more maintainable.

DbUtils.java - Add parameterized query method

public static List<Map<String, Object>> queryWithParams(String sql, Object... params) {
    List<Map<String, Object>> results = new ArrayList<>();

    try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }

        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (rs.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                row.put(metaData.getColumnName(i), rs.getObject(i));
            }
            results.add(row);
        }
        rs.close();
    } catch (SQLException e) {
        throw new RuntimeException("Parameterized query failed", e);
    }

    return results;
}

Gherkin

Feature: Parameterized database queries

Scenario: Query user by ID
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def userId = 123
  * def result = DbUtils.queryWithParams('SELECT * FROM users WHERE id = ?', userId)
  * match result == '#[1]'
  * match result[0].id == userId

Scenario: Query users by multiple criteria
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def users = DbUtils.queryWithParams('SELECT * FROM users WHERE status = ? AND age >= ?', 'active', 18)
  * match each users contains { status: 'active' }

SQL Injection Prevention

Always use parameterized queries (? placeholders) instead of string concatenation when including user input or dynamic values in SQL statements.

Database Setup and Teardown

Use Background to set up test data and cleanup before scenarios.

Gherkin

Feature: Database test data management

Background:
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  # Clean up existing test data
  * def deleted = DbUtils.execute("DELETE FROM users WHERE email LIKE '%@test.com'")
  # Insert test data
  * def inserted = DbUtils.execute("INSERT INTO users (name, email, status) VALUES ('Test User', 'user1@test.com', 'active')")

Scenario: Verify test data setup
  * def users = DbUtils.query("SELECT * FROM users WHERE email LIKE '%@test.com'")
  * match users == '#[1]'

Scenario: API creates user - verify in database
  Given url apiUrl
  And path 'users'
  And request { name: 'New User', email: 'new@test.com', status: 'active' }
  When method post
  Then status 201
  # Verify user exists in database
  * def dbUser = DbUtils.queryWithParams('SELECT * FROM users WHERE email = ?', 'new@test.com')
  * match dbUser == '#[1]'
  * match dbUser[0] contains { name: 'New User', status: 'active' }

Test Isolation

Use unique test data identifiers (e.g., emails ending in @test.com) and clean up in Background to ensure tests don't interfere with each other.

Transaction Testing

Validate transaction behavior including commits and rollbacks.

DbUtils.java - Add transaction methods

public static void executeInTransaction(String... sqls) {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        conn.setAutoCommit(false);

        Statement stmt = conn.createStatement();
        for (String sql : sqls) {
            stmt.executeUpdate(sql);
        }

        conn.commit();
        stmt.close();
    } catch (SQLException e) {
        if (conn != null) {
            try { conn.rollback(); } catch (SQLException ex) { }
        }
        throw new RuntimeException("Transaction failed", e);
    } finally {
        if (conn != null) {
            try { conn.close(); } catch (SQLException e) { }
        }
    }
}

Gherkin

Feature: Database transaction testing

Scenario: Verify multi-step transaction commits
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * DbUtils.executeInTransaction("INSERT INTO orders (user_id, total) VALUES (123, 99.99)", "UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 456")
  # Verify all changes committed
  * def orders = DbUtils.query('SELECT * FROM orders WHERE user_id = 123')
  * match orders == '#[1]'

Stored Procedure Calls

Execute stored procedures and handle result sets.

DbUtils.java - Add stored procedure support

public static Map<String, Object> callStoredProc(String procName, Object... params) {
    Map<String, Object> result = new HashMap<>();

    try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
        String call = "{call " + procName + "(" + "?,".repeat(params.length).replaceAll(",$", "") + ")}";
        CallableStatement cstmt = conn.prepareCall(call);

        for (int i = 0; i < params.length; i++) {
            cstmt.setObject(i + 1, params[i]);
        }

        boolean hasResults = cstmt.execute();
        if (hasResults) {
            ResultSet rs = cstmt.getResultSet();
            List<Map<String, Object>> rows = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                rows.add(row);
            }
            result.put("resultSet", rows);
            rs.close();
        }
        result.put("updateCount", cstmt.getUpdateCount());
        cstmt.close();
    } catch (SQLException e) {
        throw new RuntimeException("Stored procedure call failed: " + procName, e);
    }

    return result;
}

Gherkin

Feature: Stored procedure testing

Scenario: Call stored procedure
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def result = DbUtils.callStoredProc('calculate_user_stats', 123)
  * match result.resultSet == '#[1]'
  * match result.resultSet[0] contains { total_orders: '#number', total_spent: '#number' }

End-to-End State Validation

Verify database state at multiple points in a workflow.

Gherkin

Feature: End-to-end state validation

Scenario: Complete order flow with database validation
  * def DbUtils = Java.type('com.mycompany.DbUtils')
  * def userId = 999
  # Step 1: Verify initial state
  * def initialOrders = DbUtils.queryWithParams('SELECT COUNT(*) as count FROM orders WHERE user_id = ?', userId)
  * def initialCount = initialOrders[0].count
  # Step 2: Create order via API
  Given url apiUrl
  And path 'orders'
  And request { userId: '#(userId)', items: [{ productId: 200, quantity: 1 }] }
  When method post
  Then status 201
  * def orderId = response.orderId
  # Step 3: Verify order created in database
  * def newOrders = DbUtils.queryWithParams('SELECT COUNT(*) as count FROM orders WHERE user_id = ?', userId)
  * assert newOrders[0].count == initialCount + 1
  # Step 4: Complete payment via API
  Given url apiUrl
  And path 'orders', orderId, 'payment'
  And request { method: 'credit_card', amount: 50 }
  When method post
  Then status 200
  # Step 5: Verify payment recorded in database
  * def payment = DbUtils.queryWithParams('SELECT * FROM payments WHERE order_id = ?', orderId)
  * match payment[0] contains { order_id: '#(orderId)', status: 'completed' }

Configuration Reference

JDBC URL Formats

Database	JDBC URL Format	Example
H2 (In-Memory)	jdbc:h2:mem:<dbname>	jdbc:h2:mem:testdb
H2 (File)	jdbc:h2:file:<path>	jdbc:h2:file:./data/testdb
MySQL	jdbc:mysql://<host>:<port>/<db>	jdbc:mysql://localhost:3306/testdb
PostgreSQL	jdbc:postgresql://<host>:<port>/<db>	jdbc:postgresql://localhost:5432/testdb
SQL Server	jdbc:sqlserver://<host>:<port>;database=<db>	jdbc:sqlserver://localhost:1433;database=testdb
Oracle	jdbc:oracle:thin:@<host>:<port>:<sid>	jdbc:oracle:thin:@localhost:1521:orcl

Maven Dependencies

pom.xml

<dependencies>
    <!-- H2 Database (for testing) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.2.224</version>
        <scope>test</scope>
    </dependency>

    <!-- MySQL Driver -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.2.0</version>
        <scope>test</scope>
    </dependency>

    <!-- HikariCP Connection Pool (optional) -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>5.1.0</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Credentials Security

Store database credentials in environment variables or external config files. Never expose credentials in feature files. Use read-only database users for validation queries.

Troubleshooting

Problem	Solution
Cannot connect to database	Verify JDBC URL format, check database is running, confirm credentials, add driver JAR to classpath
Unexpected data types	Use explicit SQL type casting, handle NULL values, use #string/#number matchers
Tests interfere with each other	Use unique test data identifiers, clean up in Background, consider test-specific schemas

Next Steps

• Java API - Call Java code from Karate
• Calling Features - Reuse database queries
• Data-Driven Tests - Parameterize tests
• Hooks - Background setup patterns