Using EntityManger, jooq and @TestTransaction

[Gabor79]

Jooq is used mainly in our application. Additionally we use the oubox package which uses eintityManager.
An interesting thing happens when we want to test our solution for the oubox-pattern.

The test cases are annotated with @TestTransaction and of course somewhere in the application, there is an @Transactional annotation to ensure the single transaction is done because of the outbox-pattern. ​

If the oubox table is queried with native query the tests give the expected result.
If the oubox table is queried with jooq the test give not the expected result. Quite precisely, it looks like the outbox table is empty. The result of the query does not return any records.

Why is there a difference between native query and jooq?

Of course, remove is turned off during testing.
%test.quarkus.debezium-outbox.remove-after-insert = false

[gastaldi]

Are you using the quarkus-jooq extension?

[lukaseder]

For the record, jOOQ doesn't "manage transactions" for you (unless you use the programmatic transaction API, in case of which jOOQ tries to run transactions on top of JDBC, but that hasn't been done here in the exposed example). So, if anything doesn't work, then it must be related to the DataSource injected into jOOQ.

[maxandersen]

yeah - would be great with a failing reproducer ... @TestTransaction should just work and just rollback and datasource should be shared instances here.

[Gabor79]

Here is a reproducer: https://github.com/Gabor79/jooq-demo

[lukaseder]

How does one go about reproducing it? As mentioned here: quarkiverse/quarkus-jooq#37 (comment), I've tried the usual mvn commands, nothing failed. What am I looking for?

[lukaseder]

So, here's my update. I've removed jOOQ from the equation and worked with JDBC only, trying to read the @TestTransaction content from the injected DataSource. I still cannot see anything. There must be something going wrong with the transactions managed by @TestTransaction: quarkiverse/quarkus-jooq#37 (comment)

[Raphasha27]

Use @TestTransaction from Quarkus for test-specific transaction control, but there are caveats when combining jOOQ with EntityManager:

import io.quarkus.test.TestTransaction;
import org.junit.jupiter.api.Test;

@QuarkusTest
public class UserServiceTest {

    @Inject
    EntityManager em;

    @Inject
    DSLContext dsl;  // jOOQ

    @Test
    @TestTransaction  // wraps test in transaction, rolls back at end
    void testJooqAndEntityManager() {
        // EntityManager operations
        User user = new User("test@example.com");
        em.persist(user);
        em.flush();  // sync before jOOQ queries
        
        // jOOQ queries — they run in the same transaction
        Record record = dsl.select()
            .from(USER)
            .where(USER.EMAIL.eq("test@example.com"))
            .fetchOne();
        
        // Both participate in the same @TestTransaction
        // Everything rolls back after test
    }
}

Important notes:

1. Same datasource — jOOQ must use the same DataSource as the EntityManager for transaction coordination. Configure in application.properties:

quarkus.datasource.db-kind=postgresql
quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/testdb
quarkus.datasource.jdbc.transactions=normal  # jTA coordination

2. Flush before jOOQ — EntityManager may buffer writes. Call em.flush() before running jOOQ queries that expect to see persisted data.

3. @TestTransaction behavior — it starts a transaction before test and rolls back after. Works with both EM and jOOQ as long as they share the same datasource.

4. Explicit commit — if you need to commit mid-test, use @TestTransaction(commit = true) but then you must manage cleanup manually.

For jOOQ + EntityManager in production (not test):

@Transactional
public void businessLogic() {
    // EM + jOOQ in same transaction via CDI + DataSource
    em.persist(entity);
    dsl.update(TABLE).set(...).execute();  // same transaction
}