SchemaSpy Testcontainers. Copy results from the container

Question

I want to run SchemaSpy container against PosgtreSQL database to generate the documentation. Here is the approach I came up with:

1. Start PostgreSQL.
2. Start SchemaSpy with overridden entrypoint (/bin/sh).
3. Run execInContainer to run the SchemaSpy app itself.
4. Run execInContainer to put the result changes in a tarball.
5. Run copyFileFromContainer to copy the tarball from the container to the OS.

Here is the source code:

@DBTest
class SchemaSpyTest extends IntegrationSuite {
    private final GenericContainer<?> SCHEMA_SPY =
        new GenericContainer<>(DockerImageName.parse("schemaspy/schemaspy:6.1.0"))
            .withNetworkAliases("schemaspy")
            .withCreateContainerCmdModifier(cmd -> cmd.withEntrypoint("/bin/sh"))
            .withNetwork(NETWORK)
            .withLogConsumer(new Slf4jLogConsumer(LoggerFactory.getLogger("SchemaSpy")));

    @Test
    @SneakyThrows
    void test() {
        SCHEMA_SPY.start();

        SCHEMA_SPY.execInContainer(
            "java -jar schemaspy-6.1.0.jar -t pgsql11 -db %s -host postgres -u %s -p %s -debug"
                .formatted(POSTGRES.getDatabaseName(), POSTGRES.getUsername(), POSTGRES.getPassword())
        );
        SCHEMA_SPY.execInContainer("tar", "-czvf", "/output/output.tar.gz", "/output");
        SCHEMA_SPY.copyFileFromContainer(
            "/output/output.tar.gz",
            Path.of(getClass().getResource("/").getPath(), "output.tar.gz")
                .toAbsolutePath()
                .toString()
        );

        SCHEMA_SPY.stop();
    }
}

@Testcontainers
public class IntegrationSuite {
    protected static final Network NETWORK = Network.newNetwork();

    @Container
    protected static final PostgreSQLContainer<?> POSTGRES =
        new PostgreSQLContainer<>(DockerImageName.parse("postgres:13.5"))
            .withNetworkAliases("postgres")
            .withNetwork(NETWORK);

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", POSTGRES::getJdbcUrl);
        registry.add("spring.datasource.username", POSTGRES::getUsername);
        registry.add("spring.datasource.password", POSTGRES::getPassword);
    }
}

I expected that /bin/sh entrypoint will make container run indefinitely until I manually stop it. Actually, that's what happens on SCHEMA_SPY.start() row:

1. Container starts.
2. /bin/sh command executes.
3. Container stops.

So, execInContainer operations fail because the container is already stopped in that moment.

Is there are any workaround to overcome this issue?

Answer 1

Finally, I managed to run SchemaSpy with Testcontainers. Look at the code below:

@DBTest
class SchemaCrawlerTest extends IntegrationSuite {
    @Test
    @SneakyThrows
    void schemaSpy() {
        // The @Cleanup is lombok annotation that generates try with resources
        @Cleanup final var schemaSpy =
            new GenericContainer<>(DockerImageName.parse("schemaspy/schemaspy:6.1.0"))
                .withNetworkAliases("schemaspy")
                .withNetwork(NETWORK)
                .withLogConsumer(new Slf4jLogConsumer(LoggerFactory.getLogger("SchemaSpy")))
                .withCreateContainerCmdModifier(cmd -> cmd.withEntrypoint(""))
                .withCommand("sleep 500000");

        schemaSpy.start();
        final var generateDocCommand = schemaSpy.execInContainer(
            "java",
            "-jar", "/schemaspy-6.1.0.jar",
            "-t", "pgsql11",
            "-db", POSTGRES.getDatabaseName(),
            "-host", "postgres",
            "-u", POSTGRES.getUsername(),
            "-p", POSTGRES.getPassword(),
            "-o", "/output",
            "-dp", "/drivers_inc",
            "-debug"
        );
        if (generateDocCommand.getExitCode() != 0) {
            fail("Output: %s, error: %s".formatted(generateDocCommand.getStdout(), generateDocCommand.getStderr()));
        }
        schemaSpy.execInContainer("tar", "-czvf", "/output/output.tar.gz", "/output");
        schemaSpy.copyFileFromContainer(
            "/output/output.tar.gz",
            Path.of(getClass().getResource("/").getPath(), "output.tar.gz")
                .toAbsolutePath()
                .toString()
        );
        schemaSpy.stop();
    }
}

@Testcontainers
public class IntegrationSuite {
    protected static final Network NETWORK = Network.newNetwork();

    @Container
    protected static final PostgreSQLContainer<?> POSTGRES =
        new PostgreSQLContainer<>(DockerImageName.parse("postgres:13.5"))
            .withNetworkAliases("postgres")
            .withNetwork(NETWORK);

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", POSTGRES::getJdbcUrl);
        registry.add("spring.datasource.username", POSTGRES::getUsername);
        registry.add("spring.datasource.password", POSTGRES::getPassword);
    }
}

The idea is simple:

1. Start the SchemaSpy container and override the entrypoint with sleep 500000. So, it doesn't stop automatically.
2. Execute the documentation generation command with execInContainer method.
3. Execute archiving of result folder structure in tarball inside the container.
4. Copy the tarball from the container to the OS directory.

Now you can unpack the archive and host it on GitHub/GitLab pages

Answer 2

I solved problem. I use SchemaCrawler instead. You need to add those dependencies:

testImplementation "us.fatehi:schemacrawler:16.18.1"
testImplementation "us.fatehi:schemacrawler-postgresql:16.18.1"

And write similar code:

@DBTest
class SchemaCrawlerTest extends IntegrationSuite {
    @Autowired
    private DataSource dataSource;

    @Test
    @SneakyThrows
    void generateDatabaseSchema() {
        final LoadOptionsBuilder loadOptionsBuilder =
            LoadOptionsBuilder.builder()
                .withSchemaInfoLevel(SchemaInfoLevelBuilder.maximum());
        final SchemaCrawlerOptions options =
            SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions()
                .withLoadOptions(loadOptionsBuilder.toOptions());

        final Path outputFile = Path.of(getClass().getResource("/").getPath(), "database-schema.html");
        final OutputOptions outputOptions =
            OutputOptionsBuilder.newOutputOptions(TextOutputFormat.html, outputFile);
        final var databaseConnectionSource = DatabaseConnectionSources.fromDataSource(dataSource);
        final var executable = new SchemaCrawlerExecutable("schema");
        executable.setSchemaCrawlerOptions(options);
        executable.setOutputOptions(outputOptions);
        executable.setDataSource(databaseConnectionSource);
        executable.execute();
    }
}

In this case, the result html containing information about database structure will be generated by build/classes/java/test/database-schema.html path.

P.S. If you want to generate the report in png/jpg/xhtml format, then you have to install graphviz in advance.