Integration testing with Jooq

Question

How does one write integration tests in JUnit which use JOOQ as data access layer, and which rollback after test completion?

It seems that Jooq provides only very limited transactional management. It offers a method

DSLContext.transaction(TransactionalRunnable transactional)

This method will rollback a transaction if exception is thrown by the passed lambda. But it is not obvious how to make this work with JUnit integration tests. What I really want is an equivalent of @Transactional interface without using Spring.

@Transactional
class UserApiTest
{
  UserApi api;

  @Test
  public void testUpdateUserLastName() {
    User user = getUserByUsername();
    user.setLastName("NewLastName");
    api.updateUser(user);
    assertEquals("NewLastName", user.getLastName());
    // database should be unchanged after test completion because of automatic rollback
  }
}

class UserApiImpl implements UserApi
{
  private final DSLContext db;

  @Override
  public void updateUser(LegacyUser user) {
    UserRecord userRecord = db.newRecord(USER, user);
    db.executeUpdate(userRecord);
  }
}

Any suggestions would be appreciated.

Answer 1

A better approach than rolling back

First off, I would recommend you do not roll back your transactions in such integration tests, but instead, reset the database to a known state after your test. There are so many ways a rollback can fail, such as:

• Calling stored procedures that have autonomous transactions that commit anyway
• Integration testing some service that commits the transaction anyway
• Rollbacks causing trouble because of the transaction model of the database

If you reset the database to a well known state, you will avoid all of the above problems, which are likely going to cost you much more in test maintenance.

Use something like https://www.testcontainers.org instead. Here's an example showing how to set it up: https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-example

Rolling back at the end of a test

You shouldn't use jOOQ for this. You could use Spring's JUnit extensions, and make all your tests @Transactional. This way, whenever you have an assertion error, you will automatically roll back your transaction. Also, your test will automatically be transactional.

This works in simple scenarios, but again, I'm sure you'll run into one of the aforementioned issues.