What is AssertJ-DB?

AssertJ-DB is an extension of the AssertJ assertion library designed specifically for database testing in Java.
It enables developers and testers to write fluent, readable, and chainable assertions directly against relational database content like tables, requests (queries), and changes (insert/update/delete operations).

Unlike traditional database verification methods that rely heavily on manual SQL queries and complex checks, AssertJ-DB allows you to test database states declaratively, in plain Java, improving test maintainability, readability, and developer productivity.

AssertJ-DB works with JDBC-compatible databases such as MySQL, PostgreSQL, Oracle, SQL Server, H2, and more.

It is particularly valuable in unit tests, integration tests, and end-to-end testing where validating the database is a crucial aspect.

Major Use Cases of AssertJ-DB

1. Database State Validation
   Confirm that after performing operations (e.g., creating an order, updating a profile), the database reflects the expected data.
2. Change Tracking Tests
   Validate that a specific number and type of database changes (insert, update, delete) occur after an action.
3. SQL Query Result Assertions
   Test specific queries or stored procedures by asserting the structure and data of the returned result set.
4. Transactional Testing
   Assert the state of tables during or after transactions without writing raw JDBC code.
5. Data Migration Verification
   When migrating data from one schema/version/system to another, assert that the resulting tables match expected conditions.
6. CI/CD Pipeline Testing
   Integrate AssertJ-DB tests into automated pipelines to ensure database consistency after deployments.

How AssertJ-DB Works – Architecture Overview

Core Components:

• Table:
  Represents the full content of a specific database table at a point in time.
• Request:
  Represents the result of executing a custom SQL query.
• Changes:
  Captures and asserts differences in database state over time (e.g., between two operations).
• Assertions API:
  Fluent Java assertions for columns, rows, values, and changes.

How It Works:

1. Connection Setup
   AssertJ-DB connects to the database using JDBC.
2. Snapshot
   It captures snapshots of table states or query results.
3. Comparison & Assertions
   Developers write fluent assertions comparing expected values with actual database contents.
4. Change Tracking
   Optionally, developers can capture and compare database state before and after actions to assert changes.

Visual Architecture:

+------------------+
| JDBC Connection  |
+--------+---------+
         |
         v
+------------------------+
| Table / Request / Changes |
+------------------------+
         |
         v
+----------------------------+
| AssertJ-DB Fluent Assertions |
+----------------------------+
         |
         v
+---------------------+
| Test Pass / Fail    |
+---------------------+

Basic Workflow of AssertJ-DB

1. Connect to Database
   Use JDBC URL, user, and password to set up the connection.
2. Take a Snapshot
   Capture the current state of a table, a custom SQL query, or database changes.
3. Perform Actions
   Trigger the operations you want to test (e.g., insert data, update records).
4. Capture Changes (Optional)
   Capture new database state if change tracking is needed.
5. Write Assertions
   Use AssertJ-DB fluent API to assert database values, rows, columns, or changes.
6. Run Tests
   Execute tests in your JUnit/TestNG testing framework.

Step-by-Step Getting Started Guide for AssertJ-DB

✅ Pre-requisites

• Java 8 or higher
• JUnit 5 or TestNG installed
• Maven or Gradle build system
• A running database instance (MySQL, PostgreSQL, H2, etc.)

🛠️ Step 1: Add AssertJ-DB to Your Project

For Maven:

<dependency>
  <groupId>org.assertj</groupId>
  <artifactId>assertj-db</artifactId>
  <version>2.0.2</version> <!-- Use latest stable version -->
  <scope>test</scope>
</dependency>

For Gradle:

testImplementation 'org.assertj:assertj-db:2.0.2'

🛠️ Step 2: Setup JDBC Connection

Connection connection = DriverManager.getConnection(
    "jdbc:h2:mem:testdb", "sa", ""
);

(Replace with your database URL, username, password.)

🛠️ Step 3: Create a Table Assertion

Example: Validate Table Content

import org.assertj.db.type.Table;
import static org.assertj.db.api.Assertions.assertThat;

Table table = new Table(connection, "users");

assertThat(table)
    .hasNumberOfRows(2)
    .row(0)
    .value("username").isEqualTo("admin")
    .value("email").isEqualTo("admin@example.com");

🛠️ Step 4: Track and Assert Changes

Example: Validate Changes After an Insert

import org.assertj.db.type.Changes;

Changes changes = new Changes(connection);
changes.setStartPointNow();

// Perform action: insert a user
statement.executeUpdate("INSERT INTO users(username, email) VALUES('john', 'john@example.com')");

changes.setEndPointNow();

assertThat(changes)
    .hasNumberOfChanges(1)
    .change()
    .isCreation()
    .rowAtEndPoint()
    .value("username").isEqualTo("john")
    .value("email").isEqualTo("john@example.com");

🛠️ Step 5: Run Tests

Run the above test class as a JUnit 5 or TestNG test.
✅ You now have strong and readable database assertions inside your Java tests!