Allow autoconfigured DataSource beans to be wrapped in TransactionAwareDataSourceProxy

[jmaniquet]

Hi

I would like to propose a new feature for the autoconfigured DataSource.

The problem I want to solve is an app in which the DB-related tests have the following setup :

• Sliced test with either @JdbcTest or @DataJdbcTest - could be any DB-related sliced-test
• Flyway to initialize the database schema
• @Sql is used when the test needs some data
• I do assertions on the state of the database using the AssertJ-DB library

The AssertJ-DB library is (obviously) an AssertJ-like library providing assertions on the database.
It needs a DataSource as an input.
The problem is, since it is not a Spring library, it is not aware of the Spring transaction.

I currently solve it by wrapping the DataSource with TransactionAwareDataSourceProxy for every call to the library; and then perform the DB assertions.

It looks like that :

AssertDbConnection connection = AssertDbConnectionFactory.of(new TransactionAwareDataSourceProxy(dataSource))
    .create();

Request request = connection
   .request("select name, firstname from members where id = 2")
   .build();

assertThat(request)
  .hasNumberOfRows(1)
  .row()
    .column("name").value().isEqualTo(...)
    .column("firstname").value().isEqualTo(...);

Though the AssertDbConnection verbosity can be fix by wrapping it in some utility method, I was looking for a way to do the wrapping only once, instead of on each call of the library.
Following the fix for spring-projects/spring-framework#36611, it can now be done with a BeanPostProcessor, something like this :

@SpringBootApplication
public class DsTxAwareApp {

    public static void main(String[] args) {
        SpringApplication.run(DsTxAwareApp.class, args);
    }

    @Bean
    static TxAwareDataSourceBeanPostProcessor txAwareDataSourceBeanPostProcessor() {
        return new TxAwareDataSourceBeanPostProcessor();
    }

    static class TxAwareDataSourceBeanPostProcessor implements BeanPostProcessor {

        @Override
        public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
            if (bean instanceof DataSource targetDataSource) {
                return new TransactionAwareDataSourceProxy(targetDataSource);
            }

            return bean;
        }
    }
}

It works, and I can do that for all my future projects which have this need.
But my wish is for this feature to be directly in Spring Boot, enabled by some condition / property if it needs to be opt-in.

I believe it could be beneficial to any use cases where non-spring stuff needs to interact with the DataSource bean in a transactional way.
I don't know if there are many of them (probably not much pure JDBC code left), but I believe AssertJ-DB complements so well the DB-related test starters that better/easier integration for it could be warranted.

I am willing to try a PR for this.

[philwebb]

I want to discuss this with the rest of the team to see if we think this is something we should offer at the datasource bean level, or if we should try and target it to something only tests should use.

A quick scan of the current code shows that org.springframework.boot.jooq.autoconfigure.JooqAutoConfiguration.dataSourceConnectionProvider(DataSource) is currently the only place we use TransactionAwareDataSourceProxy. If we do add support for all datasource beans, we need to change this code to not wrap things twice.

[philwebb]

We discussed this today on our team call and we don't think we should directly configure TransactionAwareDataSourceProxy in Spring Boot. We're not yet convinced that enough applications will need to use it in their main code, and so far jOOQ has been the only technology we directly support that's needed it.

We're also reluctant to use it just for tests since it has the potential to change the way your application works.

We did consider supporting injection of an AssertDbConnection, but we don't currently depend on assertj-db.

Our current feeling is that it's best if applications make use of TransactionAwareDataSourceProxy directly if they need it.

Thanks anyway for the offer of a PR and for raising the issue so that others can find and comment on it.