How to use @FlywayTest + custom PostgreSQL database name/user with Zonkyio?

[rkrisztian]

My project uses Flyway migration scripts where one such script grants privileges to certain users:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_user_name TO my_user_name;
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA my_user_name TO my_other_user_name;
-- (...)

And that's not the only migration script where this specific schema name is used (as wel as those user names). And this is what causes the challenge to me in my tests, how I can make this work with Zonkyio.

With the pure TestContainers approach (without using Zonkyio yet), I enabled Flyway migration the same way as in production in application-test.properties (for now let's not worry about code duplication, this is just for illustrating the problem):

spring:
    datasource:
        username: my_other_user_name
        password: my_password
    flyway:
        enabled: true

flyway:
    datasource:
        username: my_user_name
        password: my_password
        schema: my_user_name

Plus some other settings set dynamically using a context initializer (via annotation @ContextConfiguration):

public class SharedPostgresqlContainer {

    private static final PostgreSQLContainer<?> postgresqlContainer = new PostgreSQLContainer<>("postgres:12.9")
            .withReuse(true)
           // This SQL script will create the aforementioned schema, and the users, just like in production.
            .withInitScript("db/create_db_and_users.sql");

    public static class DockerPostgreDataSourceInitializer
            implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(final @NotNull ConfigurableApplicationContext applicationContext) {
            if (!postgresqlContainer.isRunning()) {
                postgresqlContainer.start();
            }

            TestPropertySourceUtils.addInlinedPropertiesToEnvironment(applicationContext,
                    "spring.datasource.url=" + postgresqlContainer.getJdbcUrl(),
                    "spring.datasource.driver-class-name=" + postgresqlContainer.getDriverClassName(),
                    "flyway.datasource.jdbc-url=" + postgresqlContainer.getJdbcUrl(),
                    "flyway.datasource.driver-class-name=" + postgresqlContainer.getDriverClassName()
            );
        }
    }

}

Now I need Zonkyio too so I can reset the database between tests with the least overhead. However, I've run into various problems that stop me from having a similar configuration when using Zonkyio:

Firstly, the following customizer does not work:

@Configuration
public class EmbeddedPostgresConfiguration {

    @Bean
    public PostgreSQLContainerCustomizer postgresContainerCustomizer() {
        return container -> container
                .withUsername("my_user_name")
                .withPassword("my_password")
                .withDatabaseName("my_user_name")
                // Later I tried only setting this:
                //.withInitScript("db/create_db_and_users.sql")
                ;
    }
}

I get an error that the role "postgres" does not exist, because unfortunately Zonkyio hardcodes that role: https://github.com/zonkyio/embedded-database-spring-test/blob/b173315acedfc2e3d490837ceef3aa3383fd021c/embedded-database-spring-test/src/main/java/io/zonky/test/db/provider/postgres/DockerPostgresDatabaseProvider.java#L203-L209

I also tried to leave the container settings on default (which I'm not sure is right with Zonkyio) and instead initialize the context as before, but I don't have access to the container anymore, and I can't use the @FlywayTest annotation because I can't configure it in any way that works: I get the error java.lang.IllegalArgumentException: Annotation class com.sun.proxy.$Proxy10 was set, but no Flyway configuration was given.

So any help is appreciated.

[Allight7]

+, have same problem

[tomix26]

At this moment, the library does not offer support for managing multiple user accounts. Its primary purpose is to test the database structure rather than user permissions. However, it's a great idea for the next development.

Regarding the initialization of the database, the easiest approach is the use of Flyway. So for example, if you have a directory db/migration that contains production migrations V0001_1__xxx.sql, V0002_1__yyy.sql, ..., you can add another directory db/test-migration to contains testing-only migrations. When the migrations are being executed, the content of both of those directories are combined together and executed in the order based on migration's versions. So if you add a migration V0000_1__init_test_database.sql, it should be called first and may prepare the state of the database for the following migrations.

If you don't want to use Flyway for this purpose, there are some other techniques to initialize the database. For instance, baking the data directly into a docker image, creating a custom org.springframework.boot.sql.init.dependency.DatabaseInitializerDetector to call some initialization logic, or using a bean preprocessor to call sql initialization logic before Flyway#migrate is called.

But I agree that all of these steps are unnecessarily complicated, so it would probably be a good idea to add some configuration properties to simplify this process. It would also be great to fix some of the issues you've come across. So I will take a look at the hardcoded postgres user in the provider and also investigate why calling the withInitScript method in the container customizer didn't work as expected. Because for global objects it should work now.

And finally, regarding the java.lang.IllegalArgumentException error, please check that you have correctly configured Flyway and you are using compatible versions of flyway-core and flyway-test dependencies. Because this error usually occurs if there is no Flyway bean present. There is a similar issue here: https://github.com/zonkyio/embedded-database-spring-test/issues/129

[Allight7]

It would be useful to be able to configure distinct users for applying migrations and for regular connection to datasource, as in OP config example.

I tried zonky.test.database.postgres.client.properties.user=my_user_name, but it's used both for migrations and for regular connection.

[rkrisztian]

Thanks, you were right, @tomix26 :

• Test-only migrations are a posible workaround for creating users that are used in Flyway migration scripts but not created by them. This means we can transparently go back to complete auto-configuration where the tests don't need to deal with password so much anymore. It's not 100% production-like, but we should be able to accept this trade-off for our integration tests.
• @ContextConfiguration was indeed overriding all auto-configurations, making the Flyway bean unavailable.

I've tried to reproduce the remaining issues I've been having with Zonky in a simple PoC project, but I may have overly simplified it, because now everything just works fine there, "unfortunately": :)

https://github.com/rkrisztian/testcontainers-zonky-poc

So I think I'll be back with more experiments done later. Thanks again for the help so far.

[tomix26]

@rkrisztian Thank you very much for the reproducer, I will take a look at it when I have a little more time.

[rkrisztian]

@tomix26 please be warned, currently it may be too useless yet. I also need time to update it to actually reproduce issues.

[tomix26]

@rkrisztian Ok, sure, thanks for letting me know 🙂

[rkrisztian]

In the meantime I solved the remaining problems. One trap is when the production code uses a custom data source, because then Zonky's auto configuration for the data source won't be picked up anymore, and you may see errors like JDBC URL is not defined. Turning such @Configuration classes off in tests is not the simplest (AFAIK you can add a @Profile("!test"), or a @ConditionalOnProperty, or define a default profile for production, or use profile groups and make whatever active by default). Not sure if possible but If Zonky could override these configs in any way, that would be even nicer...

Update: A later comment links example code.

[rkrisztian]

@tomix26, I've added 1 more commit to my demo repository, which shows a problem when a custom data source is used but bound to properties spring.datasource.*. You can reproduce the error by deleting TestDataSourceConfiguration. It's related to the originally reported problem of using a different Flyway data source. Could you please check if this is Zonky's bug or rather Flyway specific? In any case, if you know a better solution to the problem than the ones I found, I'd appreciate if you could tell. :) Thanks in advance.

Update 2022-03-29: Sorry, I don't know why, but lately I can't reproduce this issue anymore. And I was going to try out @AutoConfigureTestDatabase(replace = NONE) as well... I'll get back to you when I have time to recheck this.

[tomix26]

@rkrisztian I took a look at the demo project you sent and found a few potential issues. The first one is that DemoDataSourceConfiguration contains two beans with the same name. Since only one of those beans can win and is put into the context, it may make the tests unpredictable and unreliable.

The second issue is that even if those two beans were named differently, the library couldn't handle it. That's because both beans inherently reference the same database (but with different users/permissions) and the library is not able to simulate this behavior at the moment. Which is consistent with what I wrote earlier:

At this moment, the library does not offer support for managing multiple user accounts

So you can for example manage multiple independent databases, but not multiple data source beans referencing the same database. So in your case there should be only one data source, which should be applied to the whole application. And in this situation the library will be able to find the primary data source and replace it with the embedded one, and everything should be working fine. If you need two separated data sources in production environment, then you need to put those beans to a separate config class and annotate it with @Profile("!testcontainers") as you did in your example. Right now there is no better way how to do it.

[tomix26]

For now I'm closing the issue, feel free to reopen it if the problem persists.