Multiple DataSource configuration

[mcherb]

I have multiple datasources, each points to a different schema in the same database.

dsx.datasource.jdbc-url=jdbc:postgresql://localhost:5432/db?currentSchema=x
dsx.datasource.hikari.data-source-properties.currentSchema=x

dsy.datasource.jdbc-url=jdbc:postgresql://localhost:5432/db?currentSchema=y
dsy.datasource.hikari.data-source-properties.currentSchema=y

dsz.datasource.jdbc-url=jdbc:postgresql://localhost:5432/db?currentSchema=z
dsz.datasource.hikari.data-source-properties.currentSchema=z

I'm using the AutoConfigureEmbeddedDatabase annotation like so:

@AutoConfigureEmbeddedDatabase(provider = ZONKY, beanName = "xDataSource")
@AutoConfigureEmbeddedDatabase(provider = ZONKY, beanName = "yDataSource")
@AutoConfigureEmbeddedDatabase(provider = ZONKY, beanName = "zDataSource")

So far, so good

Beside, I have a sql query which is joining multiple schema together; something like :

select * from x.table x join y.table y on ...

EmbeddedDatabase will replace the original datasources whithout taking the original configuration (like the schema name).

From the documentation I've found that I can set currentSchema with some additional configuration :

zonky.test.database.postgres.client.properties.currentSchema=x

But his configuration will affect all the replaced dataSources. I'm I right ?

Is there a way to use the same configuration with the datasource bean name, for instance:

zonky.test.database.postgres.client.xDataSource.properties.currentSchema=x

[tomix26]

The problem is that each @AutoConfigureEmbeddedDatabase annotation creates a new, completely independent postgres database. But if I understood you correctly, your goal is to have only one database, but with several schemas to be able to make select and join queries among these schemas. In this case, using a single @AutoConfigureEmbeddedDatabase annotation should be sufficient.

The annotation creates a common data source through which you can initialize the database and create the required schemas. However, the other data sources must be created manually, according to your needs. These data sources actually refer to the same database, but with different connection parameters. Since the target database and the data source parameters may change during the tests (to achieve the desired test isolation), the best approach is to create a data source wrapper that applies these parameters at the moment of creating a new connection.

You can either extend org.springframework.jdbc.datasource.DelegatingDataSource and make your own custom wrapper by overriding getConnection methods, or you can use the existing UserCredentialsDataSourceAdapter that allows, among other things, to change the scheme of the underlying data source. Check the example below.

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureEmbeddedDatabase(beanName = "commonDataSource")
public class DatabaseWithMultipleSchemasTest {

    @TestConfiguration // or @Configuration
    static class Config {

        @Bean
        @Primary
        public DataSource xDataSource(DataSource commonDataSource) {
            UserCredentialsDataSourceAdapter adapter = new UserCredentialsDataSourceAdapter();
            adapter.setTargetDataSource(commonDataSource);
            adapter.setSchema("schemaX");
            return adapter;
        }

        @Bean
        public DataSource yDataSource(DataSource commonDataSource) {
            UserCredentialsDataSourceAdapter adapter = new UserCredentialsDataSourceAdapter();
            adapter.setTargetDataSource(commonDataSource);
            adapter.setSchema("schemaY");
            return adapter;
        }

        @Bean
        public DataSource zDataSource(DataSource commonDataSource) {
            UserCredentialsDataSourceAdapter adapter = new UserCredentialsDataSourceAdapter();
            adapter.setTargetDataSource(commonDataSource);
            adapter.setSchema("schemaZ");
            return adapter;
        }
    }

    @Autowired
    @Qualifier("xDataSource")
    private DataSource xDataSource;

    @Autowired
    @Qualifier("yDataSource")
    private DataSource yDataSource;

    @Autowired
    @Qualifier("zDataSource")
    private DataSource zDataSource;

    // class body...
}

[tomix26]

In the future, I will try to come up with some easier way to handle these cases.

[mcherb]

@tomix26 thank you for your quick replay. I let you know if your solution works for me. I didn't try it yet

[mcherb]

Hello @tomix26 ,

Your solution with the commonDataSource works fine. But yes, it requires more configuration. If we have another simple solution it will be great.

Thanks again

[mcherb]

Hi @tomix26,
Is there any plan to do this feature ?

[tomix26]

Hi @mcherb, I would like to implement all the tasks, but unfortunately I don't have enough time for it right now. Also, this task will likely require changes to the internal API, so delivering it won't be possible until the next major version.