Spring Boot - dynamically choose datasource with different SQL flavours based on a feature toggle

I have a number of existing Spring Boot applications that use either Crud or Jpa repositories. I'm working on migrating them from SQL Server to Postgres. I'd like to be able to dynamically swap the used datasource at runtime based on a value of a feature toggle. I've been looking at AbstractRoutingDatasource. It seems that the lookup key could be a value provided by my feature toggle system. It would be different from the usual case, but feels it would work. I'm still not sure though how would I use it with the repositories and if it allowed me to swap the dialect of the generated SQL

First you need to provide an implementation of AbstractRoutingDataSource

public class DialectSpecificSourceRouting extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return Features.currentDialect();
    }
}

This class will be taken into account when trying to lookup the specific lookup key. The Features is just a static class which give access to the configured dialect. You can also implement it another way.

Next you need to build your datasources. For this you need some kind of configuration class where you can set each dialect to a specific data source configuration. Following is a minimal example:

@Configuration
@RequiredArgsConstructor
@ConfigurationProperties(prefix = "foo")
public class MultiDialectConfiguration {

    public Set<String> dialects;

    public void setDialects(Set<String> dialects) {
       this.dialects = dialects;
    }
    private Map<String, DataSource> dataSourceMap;

    public Map<String, DataSource> getDataSourceMap() {
        return dataSourceMap;
    }

    @PostConstruct
    public void postConstruct() {
        this.dataSourceMap = this.dialects.stream()
                .collect(Collectors.toMap(Function.identity(), this::createDialectSpecificDataSource);
    }

    private DataSource createDialectSpecificDataSource(String dialect) {

        HikariConfig dialectSpecificConfig = new HikariConfig();
        dialectSpecificConfig.setJdbcUrl("urlfromConfig");
        dialectSpecificConfig.setUsername("usernamefromConfig");
        dialectSpecificConfig.setPassword("passwordfromconfig");
        dialectSpecificConfig.setPoolName(String.format("%s-db-pool", dialect);
        dialectSpecificConfig.setDriverClassName("YOUR");
        dialectSpecificConfig.setMaximumPoolSize(10);
        dialectSpecificConfig.setLeakDetectionThreshold(10);


        return new HikariDataSource(dialectSpecificConfig);
    }


}

This class contains a map which holds the dialect as Key and the specific DataSource as value. Sure, you need to provide the configuration properties from e.g. the application.properties file using own prefixes.

Last step we need to provide our DataSource Bean using the previously configured DataSource map.

@Configuration
@RequiredArgsConstructor
public class DialectSpecificDataSource {
    private final MultiDialectConfiguration multiDialectConfiguration;

    @Bean
    public DataSource dataSource() {
        final DialectSpecificSourceRouting dialectSpecificDataSourceRouting = new DialectSpecificSourceRouting();
        final Map<Object, Object> targetDataSources = multiDialectConfiguration.getDataSourceMap()
                .entrySet()
                .stream()
                .collect(Collectors.toUnmodifiableMap(Map.Entry::getKey, Map.Entry::getValue));

        dialectSpecificDataSourceRouting.setTargetDataSources(targetDataSources);
        return dialectSpecificDataSourceRouting;
    }
}

That should be a way to implement your requirements.

This way round it is also possible to implement a multi tenancy application using different databases in the background.