Database Validation in API Testing: Verifying API Calls with JDBC and SQL

A 201 Created response proves the API answered, not that the data was stored correctly. Database validation closes that gap: after an API call, query the database and check the row exists, the fields match, related tables were updated and nothing extra was written. This guide shows a clean way to do it in a REST Assured and TestNG suite.

Why Check the Database After an API Call?

JDBC Database Assertions with TestNG

// DbUtils.java — reusable JDBC assertion helpers
public class DbUtils {

    private static Connection connection;

    @BeforeSuite
    public static void initDb() throws SQLException {
        connection = DriverManager.getConnection(
            ConfigReader.get("db.url"),
            ConfigReader.get("db.username"),
            ConfigReader.get("db.password")
        );
    }

    public static <T> T queryScalar(String sql, Object... params) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) ps.setObject(i + 1, params[i]);
            ResultSet rs = ps.executeQuery();
            return rs.next() ? (T) rs.getObject(1) : null;
        }
    }

    public static Map<String, Object> queryRow(String sql, Object... params)
        throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) ps.setObject(i + 1, params[i]);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) return null;
            Map<String, Object> row = new LinkedHashMap<>();
            ResultSetMetaData meta = rs.getMetaData();
            for (int i = 1; i <= meta.getColumnCount(); i++)
                row.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i));
            return row;
        }
    }

    public static int queryCount(String sql, Object... params) throws SQLException {
        Integer count = queryScalar(sql, params);
        return count != null ? count : 0;
    }
}

// Test: API creates order → verify DB record
@Test
public void createOrder_verifyDBRecord() throws Exception {
    CreateOrderRequest request = TestDataFactory.validOrder();

    // Call API
    String orderId = given().spec(withAuth("customer")).body(request)
    .when().post("/api/orders")
    .then().statusCode(201)
           .extract().jsonPath().getString("id");

    // Verify DB record created
    Map<String, Object> dbRow = DbUtils.queryRow(
        "SELECT * FROM orders WHERE order_id = ?", orderId
    );

    assertThat(dbRow).isNotNull();
    assertThat(dbRow.get("status"))      .isEqualTo("PENDING");
    assertThat(dbRow.get("customer_id")) .isEqualTo(testUser.getId());
    assertThat(dbRow.get("total_amount")).isEqualTo(new BigDecimal("1500.00"));
    assertThat(dbRow.get("created_at"))  .isNotNull();
}

// Test: DELETE API → verify record removed from DB
@Test
public void deleteUser_verifyRemovedFromDB() throws Exception {
    String userId = createTestUserViaApi();

    given().spec(withAuth("admin")).pathParam("id", userId)
    .when().delete("/api/users/{id}")
    .then().statusCode(200);

    // Verify not in main table (hard delete) or soft-deleted
    int count = DbUtils.queryCount(
        "SELECT COUNT(*) FROM users WHERE id = ? AND deleted_at IS NULL", userId
    );
    assertThat(count).isZero();  // user is gone (or soft-deleted)

    // Verify audit log recorded the deletion
    int auditCount = DbUtils.queryCount(
        "SELECT COUNT(*) FROM audit_log WHERE entity_id = ? AND action = 'DELETE'", userId
    );
    assertThat(auditCount).isEqualTo(1);
}

Best Practices for Database Checks

• Use a read-only database user for assertions so tests can't damage data by mistake.
• Always use PreparedStatement parameters, never string concatenation, for any value in a query.
• Query by the ID the API returned, not "latest row", which breaks when tests run in parallel.
• Allow for asynchronous writes: if the API queues work, poll with Awaitility until the row appears.
• Clean up the data your test created, through the API where possible, or in an @AfterMethod.
• Don't assert on everything: check the fields the API is responsible for, plus audit fields such as created_by and timestamps where they matter.

FAQs

Should API tests check the database?

For important write operations, yes: the database check proves data was stored correctly. For read operations, the API response is usually enough.

How do you connect to a database in a Java API test?

Use JDBC with the database driver dependency: open a connection with DriverManager.getConnection, run a PreparedStatement, read the ResultSet, and close everything with try-with-resources.

How do you handle database checks when tests run in parallel?

Give every test its own data (unique emails or IDs), query by the exact ID the API returned, and avoid shared rows that other tests modify.