Provide support for auto-configuring multiple beans

[wilkinsona]

Auto-configuration of a single DataSource works well for the vast majority of users, but when a subsequent DataSource is required things get harder (see #7652 for one example) as all of the data sources then need to be manually configured. We'd like to make things easier by providing support for auto-configuring multiple DataSources. In terms of properties, this could look something like this:

spring.datasource.primary.url=…
spring.datasource.primary.username=…
spring.datasource.primary.password=…

spring.datasource.secondary.url=…
spring.datasource.secondary.username=…
spring.datasource.secondary.password=…

Some more design work is needed, but primary could be used as a "special" name that results in the auto-configured bean being marked as @Primary. We'd also need similar functionality for components that consume a DataSource such as JPA, transaction management, Flyway and Liquibase.

[mbhave]

We might also need @AutoConfigureTestDatabase#replace to support replacing all auto-configured databases.

[OrangeDog]

With such a property design, I would like the current spring.datasource properties, including vendor-specific ones, to become the defaults for the other ones.

[membersound]

Please note that spring.datasource.hikari.* (min/max idle, timeout, etc) and spring.datasource.hikari.data-source-properties.* should then also automatically be applied to any secondary auto-configured datasource. As it's likely the user wants the same config for all databases in the application.

[wilkinsona]

We should keep AbstractRoutingDataSource in mind while working on this. It may be useful to group together two or more auto-configured DataSources into an AbstractRoutingDataSource, and perhaps not even expose the underlying DataSources as beans.

We should also ensure that whatever we come up with isn't just applicable to DataSources. #25369 raised the possibility of configuring multiple RabbitMQ connection factories and we need something that's consistent across all sorts of different data stores, message brokers, etc.

[muhmud]

Completely concur with that last point, I have just recently had the same requirement for kafka.

[OrangeDog]

If AbstractRoutingDataSource is being looked at, is there any possibility of rearranging things so it can see the target transaction properties? I did have a look at routing based on read-only status, but the routing happens too early and searching the call stack for the @Transactional was too messy.

Edit: there's already an issue: https://github.com/spring-projects/spring-framework/issues/21415

[wilkinsona]

You'd need to raise that with the Framework team, @OrangeDog.

[OrangeDog]

It may be useful to group together two or more auto-configured DataSources into an AbstractRoutingDataSource, and perhaps not even expose the underlying DataSources as beans.

I think that's only useful right now for e.g. multi-tenant systems where you route on e.g. SecurityContext. It's not going to work for e.g. when you have completely different entities in different databases needed at the same time.

In the former case, if there is auto-configuration of multiple DataSources beans, it should be easy to manually gather them into a @Primary AbstractRoutingDataSource with the needed routing logic. The reverse is not true.

[wilkinsona]

To be clear, I wasn't proposing that we'd always automatically create the AbstractRoutingDataSource but that we would provide a property or similar convenience that allows someone to opt in to two or more of the auto-configured DataSources being combined into a routing DataSource. If those DataSources are only ever used via an AbstractRoutingDataSource, this would remove the need for them to be beans.

[OrangeDog]

I'm not sure there's any opinionated way to do that. You could tag each DS with a routing value in its properties, but you still need an implementation to determine what the selected value should be when creating a connection.

[wilkinsona]

Indeed. You'd need something that provides the logic for at least determineCurrentLookupKey(). We could provide a strategy interface for that, but it may not be worth doing so. At this stage, it's really just something to bear in mind as we have some longer-term interest in minimising beans in the context or somehow getting rid of beans that have served their purpose.

[wilkinsona]

I've change the issue's title to reflect the fact that this should work for any type of bean and not just for data sources. Once we've figured out the general approach, we can then open additional issues as needed to tackle anything that's DataSource-specific.

When we start working on this, https://github.com/spring-projects/spring-boot/issues/32194 contains some interesting ideas that we should evaluate and discuss with the Framework team.

[ilkou]

Please note that spring.datasource.hikari.* (min/max idle, timeout, etc) and spring.datasource.hikari.data-source-properties.* should then also automatically be applied to any secondary auto-configured datasource. As it's likely the user wants the same config for all databases in the application.

Hi! I'm doing this for my dynamic datasources configuration

myapp.config:
  # generic configuration
  datasouce:
    hikari:
      minimum-idle: 2
      maximum-pool-size: 10
      idle-timeout: 30000
      max-lifetime: 60000
  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.xml
  tenants:
    - name: master
      datasource:
        url: ${DATABASE_URL}
        username: ${DATABASE_USERNAME}
        password: ${DATABASE_PASSWORD}
        # other configs are inherited from the generic configuration
    - name: client1
      datasource:
        url: ${DATABASE_URL_CLIENT1}
        username: ${DATABASE_USERNAME}
        password: ${DATABASE_PASSWORD}
        # overrides the generic configuration
        hikari:
          minimum-idle: 4
          maximum-pool-size: 10
          idle-timeout: 30000
          max-lifetime: 60000
      liquibase:
        change-log: classpath:db/changelog/db.changelog-client1.xml

it supports both cases that u have mentioned for hikari with a simple

        HikariConfig hikariConfig = customHikariConfig != null ? customHikariConfig : genericHikariConfig;

Still figuring out how to apply liquibase migrations for all datasources, as for now spring boot doesn't support migration of a bean of type AbstractRoutingDataSource Or multi datasources in general

[kanakharaharsh]

Please refer to my solution here https://github.com/spring-projects/spring-framework/issues/21415

[bekoenig]

Hi @wilkinsona,

what's the status of this topic? There are a lot of ideas and codesnips to support multiple beans, but all are declined or the discussion has stopped.

For flyway we need multiple bean support for different schemas with the same or with different databases. The usecases are motivated by providing shared libs with migrations in classpath, which are fired against isolated environments.

I have two different drafts:

• dynamical from property with support for annotated beans (javamigration, callback,...)
• explicit with builder

Both designs are fully compatible with the current behaviour, but after reading the other issues (like https://github.com/spring-projects/spring-boot/pull/25369) I'am a bit disillusioned to finish the test and the documentation, just for it to get declined as well.

What's the decision from the framework team with this design topic?

Greetings, Ben

[wilkinsona]

This is something that we'd like to support but we do not yet know how to do so. Some design work is required and, unfortunately, until that has happened, we're unlikely to be in a position to accept a contribution. Perhaps you could share your drafts in their current form by linking to your fork so that we can take what you're trying to do into consideration?

[nightswimmings]

@wilkinsona I know we already discussed the multitenancy datasource feature in https://github.com/spring-projects/spring-boot/issues/28812. But really, 3 years later, I do encourage the lead team to reassess providing such a feature. I think it is the most recurrent most complex feature we need to build over the near perfect Boot, and almost everyone have same needs (you even have a blog post about it https://spring.io/blog/2022/07/31/how-to-integrate-hibernates-multitenant-feature-with-spring-data-jpa-in-a-spring-boot-application).

AbstractRoutingDataSource does not make it trivial to modify the list of datasources in runtime in a thread-safe way, nor has a built-in integration with Hibernate multitenancy gimmicks (so you have to deal with very low-level stuff for the L2 cache to work fine), it does not provide efficient initialization facilities (like async init if you have 2000 datasources, and the fact that the hibernate-recommended use_jdbc_metadata_defaults settings makes you having to manage the datasource map in order to provide the lenientFallback/defaultDatasource arbitrarily), it does not integrate with Flyway. And besides, integrating the determineCurrentLookupKey() with jwt resource-server oauth would be really easy.

I don't think such arch would require a massive rework and it's certainly a killer feature. There's dozens of resources in internet trying to approach this need and a standard way would be amazing. The abstraction of the datsourceconfig source, and the one that deals with tenant mapping to datasource (schema,table,database) would be more time-consuming, but I think it's feasible