How to route R2DBC calls to different data sources?

[danilodeLuca]

i'm trying to use 2 different data sources in the same project: readWrite(RW) and readOnly(RO), each one having its own instance host.
To do that I have configured RW data source as default, using spring-r2dbc properties and dependency, and it works fine.
But when trying to configure a RO it does not have any property support, so I manually configured the connectionFactory and DatabaseClient with RO properties, like the following code.

Configuring this new connection and databaseClient (RO) for some reason the connection pool is using this config instead of just using the default configurable in the application (the pool is composed by RW and RO connection). Is there some way to force pool to ignore it? Or a way to do that?

Versions:
io.r2dbc:r2dbc-postgresql:0.8.11.RELEASE
io.r2dbc:r2dbc-pool:0.8.8.RELEASE
org.springframework.boot:spring-boot-starter-data-r2dbc:2.6.6

Spring application.properties:

[hantsy]

if possible to select the two datasources automatically and implements read write operations separate in one method. Eg, perform query on the read-only connections, and do modifications on another connections.

[mp911de]

You can use AbstractRoutingConnectionFactory along with Reactor's context feature to set up routing. Here's a snippet that shows the setup. The code will print the database schema (using H2 here) to show which database you're using.

	@Bean
	CloseableConnectionFactory rw() {
		return H2ConnectionFactory.inMemory("rw");
	}

	@Bean
	CloseableConnectionFactory ro() {
		return H2ConnectionFactory.inMemory("ro");
	}

	@Bean
	CloseableConnectionFactory def() {
		return H2ConnectionFactory.inMemory("def");
	}

	@Bean
	AbstractRoutingConnectionFactory router(CloseableConnectionFactory rw, CloseableConnectionFactory ro,
			CloseableConnectionFactory def) {

		AbstractRoutingConnectionFactory router = new AbstractRoutingConnectionFactory() {
			@Override
			protected Mono<Object> determineCurrentLookupKey() {
				return Mono.deferContextual(contextView -> {

					if (contextView.hasKey("TYPE")) {
						return Mono.just(contextView.get("TYPE"));
					}

					return Mono.empty();
				});
			}
		};

		router.setTargetConnectionFactories(Map.of("RW", rw, "RO", ro));
		router.setDefaultTargetConnectionFactory(def);

		return router;
	}

	@Bean
	CommandLineRunner commandLineRunner(AbstractRoutingConnectionFactory router) {

		R2dbcTransactionManager tm = new R2dbcTransactionManager(router);

		return args -> {

			TransactionalOperator transactionalOperator = TransactionalOperator.create(tm);

			DatabaseClient.create(router)
					.sql("CALL CURRENT_CATALOG")
					.fetch()
					.all()
					.doOnNext(System.out::println)
					.as(transactionalOperator::transactional)
					.contextWrite(Context.of("TYPE", "RO"))
					.blockLast();
		};
	}

[danilodeLuca]

Thats amazing! I didn't know about it, I will try to implement it soon
Thank you @mp911de