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.

Seems to be a problem with using blocking JDBC calls in Reactor reactive calls. Using R2DBC and all code reactive, it works. — macmillancodes, Oct 31 '22 at 18:13

Hantsy · Answer 1 · 2022-11-12T13:14:24.617

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.

Since I use test-resources and this plugin forces me to use the same name for both datasources, this won't work. I now have two options: Using two different names, removing test-resources amd configure the testcontainers myself (as I did earlier). Or I strip flyway completly out of the project and run it as a sidecar in my k8s setup. — macmillancodes, Nov 21 '22 at 16:57
@macmillancodes According to test-resource doc, it just add test dependencies and simplify test framework config, I do not think it will affect your dataources in the application. — Hantsy, Nov 22 '22 at 09:44

score 0 · Accepted Answer · edited Nov 28 '22 at 02:09

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 {}.

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

2 Answers2