How to select the correct TransactionManager when using R2DBC together with Flyway and JDBC

Question

Setup:

• micronaut 3.7.2
• micronaut-data 3.8.1, micronaut-data-r2dbc, r2dbc-postgresql 0.9.2
• Flyway 8.5.13, micronaut-jdbc-hikari, micronaut-flyway 5.4.1, postgresql 42.5.0
• testcontainers (jdbc, r2dbc, postgresql) 1.17.5
• io.micronaut.test-resources 3.6.2

Test Configuration (conf4k):

datasources {
    default {
        dialect=POSTGRES
        options {
            currentSchema=default
        }
    }
}
r2dbc {
    datasources {
        default {
            dialect=POSTGRES
            options {
                currentSchema=default
            }
        }
    }
}
flyway {
    datasources {
        default {
            enabled=true
            locations="classpath:databasemigrations"
            schemas=["default"]
            create-schemas=true
        }
    }
}
test-resources {
  containers {
    postgres {
      image-name="postgres:12.12"
      hostnames=["localhost"]
    }
  }
}

Preconditions:

To make Flyway and micronaut data use the same database and testcontainer, the datasource of both needs to be named alike.

Problem:

Because of JDBC and R2DBC beeing used at the same time, synchronous and reactive TransactionManagers beans are created and I get the following error, when I use @Transactional:

Multiple possible bean candidates found: [io.micronaut.transaction.jdbc.DataSourceTransactionManager, io.micronaut.transaction.sync.SynchronousFromReactiveTransactionManager]

Thoughts:

I thought, I could solve that with @TransactionalAdvice, but since both datasources need to have the same name, this is not possible. I tried to name the datasources differently, but does not work at all.

Answer 1

As Hantsy mentioned in his post, adding transactions programmatically using R2dbcOperations#withTransaction() is a temporary workaround until Flyway finally supports R2DBC. Please upvote here.

Since I use Kotlin and Coroutines, the code is suboptimal but ok for a hopefully ephemeral solution. To use that in Kotlin, one has to wrap the body of the function passed to withTransaction in mono {}.

Answer 2

You can try to change the datasource name of Jdbc(default) and Flyway(Flyway depends on Jdbc Datasouce) to another name, then in your Jdbc Repository, use a @TransactionalAdvice to select it.

I have a Micronaut example using Flyway and R2dbc in the same project.

But I have not tried @Transactional, for the transaction handling, I was using R2dbcOperations.withTransaction, it works well.

public Mono<Long> deleteAll() {
        var sql = "DELETE  FROM customers";
        return Mono.from(
                r2dbcOperations.withTransaction(status ->
                        Mono.just(status.getConnection())
                                .flatMap(connection -> Mono
                                        .from(connection.createStatement(sql).execute())
                                        .flatMap(result -> Mono.from(result.getRowsUpdated()))
                                )
                )
        );
}

Check my Micronaut R2dbc example here, hope it is helpful.