How to update the list of configured dataSources when a new tenant registers? SpringBoot with mysql connector

Question

I am developing a multitenant application (separate database for each tenant) using spring-boot and mysql for database. Here are some important points about my application:

• There is a master database where all the tenants details are stored
• When the tenant registers a new database is created and the necessary tables are added to it
• I have implemented dynamic routing using AbstractRoutingDataSource

public class TenantRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContext.getCurrentTenant() != null ? TenantContext.getCurrentTenant() : "master";
    }
}

This is my DataSource configuration bean

@Configuration
public class DataSourceConfig {


    @Bean
    public DataSource dataSource() {
        TenantRoutingDataSource routingDataSource = new TenantRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>();
        DriverManagerDataSource masterDataSource=new DriverManagerDataSource();
        masterDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        masterDataSource.setUrl("jdbc:mysql://localhost:3306/test3");
        masterDataSource.setUsername("root");
        masterDataSource.setPassword("your_new_password");
        dataSourceMap.put("master", masterDataSource);

        // Populate the map with DataSource for each tenant
        try{
            List<String> tenantNames = getTenantDatabases(masterDataSource);

            tenantNames.forEach((tenant) -> {
                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
                dataSource.setUrl("jdbc:mysql://localhost:3306/" + tenant);
                dataSource.setUsername("root");
                dataSource.setPassword("password");
                dataSourceMap.put(tenant, dataSource);
            });
            routingDataSource.setTargetDataSources(dataSourceMap);
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        return routingDataSource;
    }
    public List<String> getTenantDatabases(DataSource dataSource) throws Exception {
        List<String> tenantDatabases = new ArrayList<>();
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT name FROM tenant_info")) {

            while (resultSet.next()) {
                tenantDatabases.add(resultSet.getString("name"));
            }
        }
        return tenantDatabases;
    }
}

PROBLEM My problem is that the datasource bean is configured during the start of the application and when a new tenant registers during the runtime they are not added to the configured datasource. I have to restart the application to make this happen. Is there a solution where the new datasorce can be add to the existing list of datasources?

What I considered trying

• I was planning to update the ApplicationContext. Is this possible/advisable?

If you have any other strategy please suggest it.

Answer 1

Found answer to my question here. Also refer this repo

@Slf4j
@Configuration
public class MultiTenantManager {

    private final Map<Object, Object> tenantDataSources = new ConcurrentHashMap<>();
    private final DataSourceProperties properties;

    private Function<String, DataSourceProperties> tenantResolver;

    private AbstractRoutingDataSource multiTenantDataSource;

    public MultiTenantManager(DataSourceProperties properties) {
        this.properties = properties;
    }

    @Bean
    public DataSource dataSource() {

        multiTenantDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                return currentTenant.get();
            }
        };
        multiTenantDataSource.setTargetDataSources(tenantDataSources);
        multiTenantDataSource.setDefaultTargetDataSource(defaultDataSource());
        multiTenantDataSource.afterPropertiesSet();
        return multiTenantDataSource;
    }

    public void addTenant(String tenantId, String url, String username, String password) throws SQLException {

        DataSource dataSource = DataSourceBuilder.create()
                .driverClassName(properties.getDriverClassName())
                .url(url)
                .username(username)
                .password(password)
                .build();

        // Check that new connection is 'live'. If not - throw exception
        try(Connection c = dataSource.getConnection()) {
            tenantDataSources.put(tenantId, dataSource);
            multiTenantDataSource.afterPropertiesSet();
            log.debug("[d] Tenant '{}' added.", tenantId);
        }
    }
}

In the addTenat method after adding a new datasource the

multitenantDatasource.afterPropertiesSet()

method is called to perform validation of the beans overall configuration and final initialization. Using this setup you can add new dataSources dynamically.

Thank's to @Cepr0