Tutorials123: Database Validation in API Testing

Validating an API Response against the Database in Rest Assured means checking whether the data returned by the API is consistent with the actual data stored in the backend database. This ensures that the API is not only returning the correct structure but also the correct content.

To validate an API response against data in a MySQL database using Rest Assured, follow these steps:

Step-by-Step Guide

Step 1: Setup Project with Required Dependencies

<dependencies>
    <!-- Rest Assured for API Testing -->
    <dependency>
        <groupId>io.rest-assured</groupId>
        <artifactId>rest-assured</artifactId>
        <version>5.3.0</version>
        <scope>test</scope>
    </dependency>

    <!-- MySQL Connector for DB Connection -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>

    <!-- JSON handling -->
    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20230227</version>
    </dependency>

    <!-- TestNG (optional for test framework) -->
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.4.0</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Step 2: MySQL Database Setup

Suppose you have a table users with structure:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    avatar VARCHAR(255)
);

Insert some matching test data from https://reqres.in/api/users?page=2

Step 3: Java Code

import io.restassured.RestAssured;
import io.restassured.response.Response;

import java.sql.*;
import java.util.List;
import java.util.Map;

import static io.restassured.RestAssured.given;

public class APIDatabaseValidation {

    // JDBC details
    static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) throws Exception {
        // Step 1: Get API response
        RestAssured.baseURI = "https://reqres.in";
        Response response = given()
                .when()
                .get("/api/users?page=2")
                .then()
                .statusCode(200)
                .extract().response();

        // Step 2: Extract JSON data from API
        List<Map<String, Object>> apiUsers = response.jsonPath().getList("data");

        // Step 3: Connect to MySQL and validate
        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
        Statement stmt = conn.createStatement();

        for (Map<String, Object> apiUser : apiUsers) {
            int id = (Integer) apiUser.get("id");
            String email = (String) apiUser.get("email");
            String firstName = (String) apiUser.get("first_name");
            String lastName = (String) apiUser.get("last_name");
            String avatar = (String) apiUser.get("avatar");

            // Query DB for this user
            String query = "SELECT * FROM users WHERE id = " + id;
            ResultSet rs = stmt.executeQuery(query);

            if (rs.next()) {
                assert email.equals(rs.getString("email")) : "Email mismatch for ID " + id;
                assert firstName.equals(rs.getString("first_name")) : "First name mismatch for ID " + id;
                assert lastName.equals(rs.getString("last_name")) : "Last name mismatch for ID " + id;
                assert avatar.equals(rs.getString("avatar")) : "Avatar mismatch for ID " + id;
                System.out.println("User ID " + id + " matched successfully.");
            } else {
                System.err.println("User ID " + id + " not found in database.");
            }
            rs.close();
        }

        // Close DB connection
        stmt.close();
        conn.close();
    }
}

Code explanation:

(a) Define database details like db username, db password, db url, before main method

(b) In main method, define base URI, send API request and extract data from json response in a map object which is wrapped in a List object.

(d) Iterate map object by for loop and stored data in string objects

(e) Get data from database using ResultSet object and assert the data with string data that we got from list.

(f) Finally, close the database connection.

Points to Consider

Always ensure you are pointing to the same environment (API and DB should be connected to same Test/Stage DB).
Handle null values, trimming spaces, and data type mismatches while comparing.
Validate not just single records, but also multiple records if the API returns a list.
For performance testing, check if the response time of the API aligns with fetching from DB.

How to validate API Response from Database in Rest Assured

Step-by-Step Guide

Step 2: MySQL Database Setup