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.

Benefits of Database Testing

Complete integration testing: Verify API operations persist correct data to database
State validation: Assert database state before and after API calls
Test data management: Set up and tear down test data programmatically
Transaction verification: Confirm commits, rollbacks, and data consistency
End-to-end confidence: Validate the entire stack from API to database

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.

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.*;
import com.fasterxml.jackson.databind.ObjectMapper;

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

Feature: Using database helper

Scenario: Query users from database
* def DbUtils = Java.type('com.mycompany.DbUtils')

# Execute query
* def users = DbUtils.query('SELECT id, name, email FROM users ORDER BY id')

# Validate results
* match users == '#[3]'
* match users[0] == { id: 1, name: 'John Doe', email: 'john@example.com' }
* match users[1].name == 'Jane Smith'
* match users[2].email == 'bob@example.com'

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

        // Set parameters
        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;
}

Feature: Parameterized database queries

Scenario: Query user by ID
* def DbUtils = Java.type('com.mycompany.DbUtils')
* def userId = 123

# Use parameterized query
* def result = DbUtils.queryWithParams('SELECT * FROM users WHERE id = ?', userId)

* match result == '#[1]'
* match result[0].id == userId
* match result[0].name == 'Alice Johnson'

Scenario: Query users by multiple criteria
* def DbUtils = Java.type('com.mycompany.DbUtils')
* def status = 'active'
* def minAge = 18

* def users = DbUtils.queryWithParams(
    'SELECT * FROM users WHERE status = ? AND age >= ?',
    status, minAge
  )

* match users == '#[]'
* match each users contains { status: 'active', age: '#number' }
* match each users..age >= 18

Database Setup and Teardown

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

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 1', 'user1@test.com', 'active'),
    ('Test User 2', 'user2@test.com', 'inactive'),
    ('Test User 3', 'user3@test.com', 'active')
    """
  )

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

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' }

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("Rollback failed", ex);
            }
        }
        throw new RuntimeException("Transaction failed", e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // Ignore close errors
            }
        }
    }
}

Feature: Database transaction testing

Scenario: Verify multi-step transaction commits
* def DbUtils = Java.type('com.mycompany.DbUtils')

# Execute multiple statements in transaction
* DbUtils.executeInTransaction(
    "INSERT INTO orders (user_id, total) VALUES (123, 99.99)",
    "INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 456, 2)",
    "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]'

* def items = DbUtils.query('SELECT * FROM order_items WHERE order_id = ' + orders[0].id)
* match items == '#[1]'

* def inventory = DbUtils.queryWithParams('SELECT quantity FROM inventory WHERE product_id = ?', 456)
* match inventory[0].quantity == '#number'

Scenario: API operation triggers database transaction
Given url apiUrl
And path 'orders'
And request { userId: 789, items: [{ productId: 101, quantity: 5 }] }
When method post
Then status 201
And match response.orderId == '#number'

# Verify order in database
* def orderId = response.orderId
* def dbOrder = DbUtils.queryWithParams('SELECT * FROM orders WHERE id = ?', orderId)
* match dbOrder[0].user_id == 789

# Verify order items in database
* def dbItems = DbUtils.queryWithParams('SELECT * FROM order_items WHERE order_id = ?', orderId)
* match dbItems == '#[1]'
* match dbItems[0] contains { product_id: 101, quantity: 5 }

Advanced Patterns

Stored Procedure Calls

Execute stored procedures and handle output parameters.

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

Feature: Stored procedure testing

Scenario: Call stored procedure
* def DbUtils = Java.type('com.mycompany.DbUtils')

# Call procedure to calculate user statistics
* def result = DbUtils.callStoredProc('calculate_user_stats', 123)

* match result.resultSet == '#[1]'
* match result.resultSet[0] contains { total_orders: '#number', total_spent: '#number' }

Complex Queries with Joins

Validate complex database operations involving multiple tables.

Feature: Complex database queries

Scenario: Verify user with related data
* def DbUtils = Java.type('com.mycompany.DbUtils')

* def sql =
  """
  SELECT
    u.id, u.name, u.email,
    COUNT(DISTINCT o.id) as order_count,
    COALESCE(SUM(o.total), 0) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.email = ?
  GROUP BY u.id, u.name, u.email
  """

* def result = DbUtils.queryWithParams(sql, 'john@example.com')

* match result == '#[1]'
* match result[0] contains { name: 'John Doe', order_count: '#number', total_spent: '#number' }
* assert result[0].order_count >= 0
* assert result[0].total_spent >= 0

Common Database Patterns

Database-Driven Data for API Tests

Use database queries to provide dynamic test data.

Feature: Database-driven API testing

Scenario: Test API with real database users
* def DbUtils = Java.type('com.mycompany.DbUtils')

# Get active users from database
* def activeUsers = DbUtils.query("SELECT id, email FROM users WHERE status = 'active' LIMIT 5")
* match activeUsers == '#[5]'

# Test API login for each user
* def testLogin =
  """
  function(user) {
    var result = karate.call('classpath:features/login.feature', { email: user.email });
    return result.token != null;
  }
  """

* def loginResults = karate.map(activeUsers, testLogin)
* match loginResults == '#[5]'

State Validation in End-to-End Flows

Verify database state at multiple points in a workflow.

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.00 }
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', amount: 50.00 }

# Step 6: Verify order status updated
* def order = DbUtils.queryWithParams('SELECT status FROM orders WHERE id = ?', orderId)
* match order[0].status == 'paid'

Connection Pooling for Performance

Implement connection pooling for better test performance.

DbUtils.java - Add connection pooling
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DbUtils {

    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:testdb");
        config.setUsername("sa");
        config.setPassword("");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30000);
        dataSource = new HikariDataSource(config);
    }

    private static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

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

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // ResultSet processing...

        } catch (SQLException e) {
            throw new RuntimeException("Query failed", e);
        }

        return results;
    }

    public static void closePool() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

Configuration Reference

JDBC URL Formats

Common JDBC connection string formats for different databases:

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

Add JDBC driver dependencies to your pom.xml:

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>

Best Practices

Test Isolation:

Use unique test data identifiers (e.g., emails ending in @test.com)
Clean up test data in Background
Use transactions with rollback for read-only validation
Consider separate test database schema

Performance:

Use connection pooling for repeated queries
Limit result set sizes with SQL LIMIT clauses
Index frequently queried columns
Close resources properly (use try-with-resources)

Security:

Use parameterized queries to prevent SQL injection
Store credentials in environment variables or config files
Use read-only database users for validation queries
Never expose database credentials in feature files

Troubleshooting Database Tests

Connection Failures

Problem: Cannot connect to database

Solutions:

Verify JDBC URL format is correct
Check database is running and accessible
Confirm credentials are valid
Add database driver JAR to classpath
Check firewall/network settings

Data Type Mismatches

Problem: Unexpected data types in query results

Solutions:

Use explicit type casting in SQL queries
Handle NULL values appropriately
Convert timestamps to strings for easier matching
Use #string, #number matchers for flexibility

Transaction Isolation

Problem: Tests interfere with each other

Solutions:

Use unique test data identifiers
Implement proper cleanup in Background
Consider using test-specific schemas
Use database transactions with rollback

Next Steps

Continue advanced integration testing:

Call Java code: Java API
Reuse database queries: Calling Features
Parameterize tests: Data-Driven Tests
Background setup: Hooks
Validate responses: Response Validation

Benefits of Database Testing​

How Database Testing Works​

Basic Database Setup​

Query Execution with Parameters​

Database Setup and Teardown​

Transaction Testing​

Advanced Patterns​

Stored Procedure Calls​

Complex Queries with Joins​

Common Database Patterns​

Database-Driven Data for API Tests​

State Validation in End-to-End Flows​

Connection Pooling for Performance​

Configuration Reference​

JDBC URL Formats​

Maven Dependencies​

Best Practices​

Troubleshooting Database Tests​

Connection Failures​

Data Type Mismatches​

Transaction Isolation​

Next Steps​

Benefits of Database Testing

How Database Testing Works

Basic Database Setup

Query Execution with Parameters

Database Setup and Teardown

Transaction Testing

Advanced Patterns

Stored Procedure Calls

Complex Queries with Joins

Common Database Patterns

Database-Driven Data for API Tests

State Validation in End-to-End Flows

Connection Pooling for Performance

Configuration Reference

JDBC URL Formats

Maven Dependencies

Best Practices

Troubleshooting Database Tests

Connection Failures

Data Type Mismatches

Transaction Isolation

Next Steps