Re: jOOQ assertj assertions

[Alf Lervåg]

Wow, thank you for this. Seems like something we’ll want to start looking into. 🤩

Alf Lervåg

28. feb. 2026 kl. 11:49 skrev Dominik Hirt <dominik...@gmail.com>:

# jOOQ-Aware Custom AssertJ Assertions — Verifying Database State with Type-Safe Fluent APIs

I'd like to share an approach we've been using in our project that combines AssertJ's custom assertion mechanism with jOOQ's type-safe DSL to create fluent, database-aware test assertions. The core idea: instead of manually fetching records and then asserting on them, we wrap jOOQ queries directly inside AssertJ assertions, bound to a specific table and primary key.

## The Problem

In integration tests that verify database state after business operations, you typically end up writing repetitive boilerplate:

```java
// Classic approach — verbose and not composable
Integer status = jooq.select(ORDER.STATUS)
    .from(ORDER)
    .where(ORDER.ID.eq(orderId))
    .fetchOne(ORDER.STATUS);
assertThat(status).isEqualTo(OrderStatus.SHIPPED);

Integer count = jooq.selectCount()
    .from(ORDER_POSITION)
    .where(ORDER_POSITION.ORDER_ID.eq(orderId))
    .fetchOneInto(Integer.class);
assertThat(count).isEqualTo(3);
```

This works, but it doesn't compose well, the intent gets buried in query mechanics, and you lose the fluent assertion chaining that makes tests readable.

## The Approach: `AbstractJooqAwareAssert<SELF>`

We created a generic abstract base class that extends `AbstractAssert<SELF, Integer>` — the `Integer` being the primary key of the entity under test. It holds a static `DSLContext` reference and the jOOQ `Table<?>` the assertion operates on:

```java
public abstract class AbstractJooqAwareAssert<SELF extends AbstractJooqAwareAssert<SELF>>
    extends AbstractAssert<SELF, Integer> {

    protected static DSLContext jooq;
    protected Table<?> table;

    protected AbstractJooqAwareAssert(Table<?> table, Integer id, Class<?> selfType) {
        super(id, selfType);
        this.table = table;
    }
}
```

The recursive generic `SELF extends AbstractJooqAwareAssert<SELF>` is the standard AssertJ pattern for returning the concrete subtype from every assertion method, enabling proper method chaining without casts at the call site.

### Core Assertion Methods

**`has(Field<T>, T)` — assert a column value for the entity's row:**

```java
public <T> SELF has(Field<T> field, T expected) {
    T actual = jooq.select(field)
        .from(table)
        .where(table.field("id", Integer.class).eq(this.actual))
        .fetchOne(field);
    if (actual == null || !actual.equals(expected)) {
        failWithMessage("%s with id '%d': expected '%s' for '%s' but was '%s'",
            table.getName(), this.actual, expected, field.getName(), actual);
    }
    return (SELF) this;
}
```

Since `field` is a jOOQ `Field<T>` and `expected` is `T`, you get compile-time type safety — you can't accidentally compare a `String` field against an `Integer`.

**`count(TableField<?, Integer>, Integer)` — assert the number of related rows:**

```java
public SELF count(TableField<?, Integer> tableField, Integer expectedCount) {
    Integer actualCount = jooq.selectCount()
        .from(tableField.getTable())
        .where(tableField.eq(actual))
        .fetchOneInto(Integer.class);
    if (!actualCount.equals(expectedCount)) {
        failWithMessage("expected count '%s' vs. actual count '%d'", expectedCount, actualCount);
    }
    return (SELF) this;
}
```

The `TableField<?, Integer>` parameter is the foreign key column in the related table. jOOQ infers the table from the field, so no additional `from()` specification is needed.

**`existForActualId(TableField<?, Integer>)` — shorthand for `count(..., 1)`.**

## Creating Concrete Assertions

### Minimal Example: `ProjectAssert`

For a table where you only need the generic `has()` and `count()` methods, the concrete class is trivial:

```java
public class ProjectAssert extends AbstractJooqAwareAssert<ProjectAssert> {

    public ProjectAssert(Integer projectId) {
        super(PROJECT, projectId, ProjectAssert.class);
    }

    public static ProjectAssert assertThat(Integer projectId) {
        return new ProjectAssert(projectId);
    }
}
```

That's it. The generics wire everything together: `ProjectAssert extends AbstractJooqAwareAssert<ProjectAssert>` ensures that `has()` and `count()` return `ProjectAssert`, not the abstract type.

Usage:

```java
ProjectAssert.assertThat(projectId)
    .has(PROJECT.STATUS, ProjectStatus.ACTIVE)
    .has(PROJECT.NAME, "Test Project")
    .count(ORDER.PROJECT_ID, 2);  // project has 2 orders
```

### Extended Example: `OrderAssert`

When a domain entity needs additional assertion logic beyond simple column checks, you add domain-specific methods:

```java
public class OrderAssert extends AbstractJooqAwareAssert<OrderAssert> {

    public OrderAssert(Integer orderId) {
        super(ORDER, orderId, OrderAssert.class);
    }

    public static OrderAssert assertThat(OrderRecord order) {
        return assertThat(order.getId());
    }

    public static OrderAssert assertThat(Integer orderId) {
        return new OrderAssert(orderId);
    }

    // Domain-specific: retrieve related IDs for further assertions
    public List<Integer> getGoodsReceiptPositions() {
        return jooq.select(GOODS_RECEIPT_POSITION.ID)
            .from(GOODS_RECEIPT_POSITION)
            .join(ORDER_POSITION).onKey()
            .join(ORDER).onKey()
            .where(ORDER.ID.eq(actual))
            .fetchInto(Integer.class);
    }
}
```

Note the overloaded `assertThat()` factory methods: you can pass either the raw ID or a jOOQ `Record`. This keeps the test code flexible — use whichever reference you have at hand.

## Real Test Example

Here's how it reads in an actual integration test (goods receipt workflow):

```java
@Test
void testGoodsReceiptWithDeliveryNote() {
    Order order = testFactory.createOrder();

    // Verify initial state: status ORDERED, 3 positions
    OrderAssert.assertThat(order)
        .has(ORDER.STATUS, OrderStatus.ORDERED)
        .count(ORDER_POSITION.ORDER_ID, 3);

    // ... UI interaction that triggers goods receipt ...

    // Verify state after goods receipt
    List<Integer> receiptPositionIds = OrderAssert.assertThat(order)
        .has(ORDER.STATUS, OrderStatus.DELIVERED)
        .getGoodsReceiptPositions();

    assertThat(receiptPositionIds).hasSize(1);

    GoodsReceiptPositionAssert.assertThat(receiptPositionIds.get(0))
        .hasOrder(order.getId())
        .hasDeliveryNote()
        .has(GOODS_RECEIPT_POSITION.DELIVERED_QUANTITY, expectedQuantity);
}
```

The chain reads top-down like a specification: *"assert that the order has status DELIVERED, then get its receipt positions, then assert that the first receipt position belongs to this order, has a delivery note attached, and has the expected quantity."*

## Setup

The `DSLContext` is injected statically during test setup, typically in a `@BeforeEach`:

```java
@BeforeEach
void setup(DSLContext jooq) {
    OrderAssert.setJooq(jooq);
    ProjectAssert.setJooq(jooq);
    // ...
}
```

This is admittedly not the most elegant part of the design (static mutable state), but it keeps the assertion call sites clean and avoids passing `DSLContext` into every `assertThat()` call. Since tests run single-threaded against the same database, this has not caused issues in practice.

## Summary

The key benefits of this approach:

- **Type safety from jOOQ propagates into assertions** — `has(ORDER.STATUS, OrderStatus.DELIVERED)` is checked at compile time. Passing a `String` where an enum is expected won't compile.
- **Fluent chaining** — multiple column checks and related-entity counts compose naturally.
- **Minimal boilerplate per table** — a new assert class is ~10 lines if you only need the generic methods. Add domain-specific methods as needed.
- **Readable test code** — the assertion chain reads like a business requirement, not like query plumbing.

We currently have assert classes for a couple of domain entities. Creating a new one takes less than a minute and immediately gives you `has()`, `count()`, and `existForActualId()` for any column in that table.

Alf Lervåg schrieb am Samstag, 14. Februar 2026 um 18:49:28 UTC+1:

This looks interesting. A lot of things between the lines here, and I suspect you’ve tried and discarded things. A longer form post about this subject would be fun. 🤩 

Alf Lervåg

14. feb. 2026 kl. 16:42 skrev Omar Aloraini <alorai...@gmail.com>:

I'v been developing my testing style around org.jooq.Record and I have settled on a small assertj extension. I have considered using CSVSoruce or raw strings and loading them using the jOOQ API, but at the end, I settled on a full Java approach for maximum flexibility. 

Curious how other are approaching assertions against the DB or org.jooq.Record.

Example assertions:

assertThat(meeting.resolutions())
.extracting(RESOLUTION.MAJORITY_VOTE.EN, RESOLUTION.MAJORITY_STANDARD_VOTE, RESOLUTION.MAJORITY_VOTE_COUNT.N)
.containsExactly(
row("For", StandardVote.FOR, 100L),
row("Acknowledge", StandardVote.ACKNOWLEDGE, 300L),
row("Option 2", null, 200L)
);

assertThat(meeting.resolutionVotes())
.extracting(RESOLUTION_VOTE.VOTE.EN, RESOLUTION_VOTE.STANDARD_VOTE, RESOLUTION_VOTE.VOTE_COUNT.N, RESOLUTION_VOTE.HAS_MAJORITY)
.containsExactly(
row("For", StandardVote.FOR, 100L, true),
row("Against", StandardVote.AGAINST, 0L, false),
row("Abstain", StandardVote.ABSTAIN, 200L, false),
row("Acknowledge", StandardVote.ACKNOWLEDGE, 300L, true),
row("Option 1", null, 100L, false),
row("Option 2", null, 200L, true),
row("Abstain", StandardVote.ABSTAIN, 0L, false)
);

I using the fields names as extractors.

Gist: https://gist.github.com/ooraini/03520ac699c5cecc4f65b16f3227d303

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/b24553f7-17c4-4be2-af47-67c1eae8f46en%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/8a86248a-6fe0-486d-a576-a783fe0ad043n%40googlegroups.com.

[Dominik Hirt]

just for the record and due to the missing rendering of markdown content here: one can read a more readable version of my approach using custom AssertJ assertions in https://www.dominikhirt.de/blog/post/2026-02-28-jooq-aware-assertions

[Alf Lervåg]

Unsolicited feedback, please ignore if it’s not wanted.

It would have been nice to see the end goal earlier in the article. For instance right after you show the problematic long form assertion queries.

Not a very big deal, I read it all and learned a lot. :)

Alf

1. mars 2026 kl. 11:00 skrev Dominik Hirt <dominik...@gmail.com>:

just for the record and due to the missing rendering of markdown content here: one can read a more readable version of my approach using custom AssertJ assertions in https://www.dominikhirt.de/blog/post/2026-02-28-jooq-aware-assertions

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/11bb4b00-4b39-4975-b640-a14072668c7fn%40googlegroups.com.

[Dominik Hirt]

Hey Alf,

I really appreciate all feedback and thank you very much for it. You're right, I'll revise the blog post.

Kind regards

Dominik