Improve usability of SQL init scripts with multiple file support

[mroche89]

Context

Issue originally raised on stackoverflow as a question.

It would be useful to be able to initialize a database with multiple startup scripts. In most projects that I've worked on, there are multiple onboarding scripts (database, schema, data, test-data etc). As such, when provisioning a new database using testcontainers for an integration test, it'd be helpful to be able to execute all of these scripts on startup without needing to merge them into a single script.

Suggestion

An additional API in JdbcDatabaseContainer

/**
  * Execute each script in the order of which they're provided
  */
public SELF withInitScript(String... initScriptPaths) {
    // ...
}

An additional consideration for this API might be the result of 2127

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If you believe this is a mistake, please reply to this comment to keep it open. If there isn't one already, a PR to fix or at least reproduce the problem in a test case will always help us get back on track to tackle this.

[stale]

This issue has been automatically closed due to inactivity. We apologise if this is still an active problem for you, and would ask you to re-open the issue if this is the case.

[bjmi]

Please consider reopening this issue. It should be possible to execute multiple init scripts.
My use-case: first script contains definitions of tables (DDL) and another script that is full of testdata.

[bsideup]

@bjmi unless somebody wants to work on a PR to support it, keeping an issue open makes little sense. Would you like to work on such PR?

Also, have you considered using Flyway or Spring Boot's built-in support for data/schema SQL files?

[Jorge2M]

@bjmi, can be achieved by using inheritance in OracleContainer class:

`
public class MyOracleContainer extends OracleContainer {

private List<String> listInitScriptPaths;

public MyOracleContainer(String dockerImageName) {
	super(dockerImageName);
}

public MyOracleContainer withInitScripts(List<String> listInitScriptPaths) {
    this.listInitScriptPaths = listInitScriptPaths;
    return this;
}

@Override
public void runInitScriptIfRequired() {
	super.runInitScriptIfRequired();
	if (listInitScriptPaths != null) {
		for (String initScriptPath : listInitScriptPaths) {
			ScriptUtils.runInitScript(getDatabaseDelegate(), initScriptPath);
		}
	}
}

}
`

[bjmi]

I end up using ScriptUtils.runInitScript(databaseDelegate, initScriptPath) directly where needed and don't use JdbcDatabaseContainer.withInitScript(String) at all.
E.g.

@BeforeEach
private void setup() {
    ScriptUtils.runInitScript(new JdbcDatabaseDelegate(CONTAINER, ""), "sql-files/Unittest-ddl.sql");
    ScriptUtils.runInitScript(new JdbcDatabaseDelegate(CONTAINER, ""), "sql-files/Unittest-data.sql");
}

[ShayanAhmad]

The solution by @bjmi still works.

[sbhuju61]

Hopefully it will help others. If you are using Spring and testcontainers, below worked for me in Spring Boot 3.2.1

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/init/DataSourceInitializer.html can be used to use multiple sql files.

import org.springframework.boot.SpringApplication;
import org.springframework.boot.test.context.TestConfiguration;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.testcontainers.containers.MSSQLServerContainer;
import org.testcontainers.utility.DockerImageName;

import javax.sql.DataSource;

@TestConfiguration(proxyBeanMethods = false)
public class TestDemoApplication {

    @Bean
    @ServiceConnection
    MSSQLServerContainer<?> sqlServerContainer() {
        return new MSSQLServerContainer<>(DockerImageName.parse("mcr.microsoft.com/mssql/server:latest"));
    }

    @Bean
    public DataSourceInitializer initializer(DataSource dataSource) {

        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(dataSource);
        // schema.sql and data.sql should be in resources folder under sql folder
        dataSourceInitializer.setDatabasePopulator(new ResourceDatabasePopulator(
                new ClassPathResource("sql/schema.sql")
                , new ClassPathResource("sql/data.sql")));

        return dataSourceInitializer;
    }

    public static void main(String[] args) {
        SpringApplication.from(DemoApplication::main).with(TestDemoApplication.class).run(args);
    }

}