InitialScript failed Postgres container startup

[hantsy]

I have an initial script like this.

DO $$ BEGIN
    CREATE TYPE post_status AS ENUM( 'DRAFT', 'PENDING_MODERATION', 'PUBLISHED');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

It worked well when using docker compose command.

But when I create a Postgres docker container using testcontainers like this.

 var container = new PostgreSQLContainer("postgres:12")
                    .withInitScript("init.sql");

It will fail with the following exception.

Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 3 in SQL DO $$ BEGIN CREATE TYPE post_status AS ENUM( 'DRAFT', 'PENDING_MODERATION', 'PUBLISHED'). Expected terminating $$

The example project to reproduce this issue: https://github.com/hantsy/spring6-sandbox/blob/master/jdbc/src/test/java/com/example/demo/testcontainers/PostRepositoryTestWithTestcontainers.java

[kiview]

Hey @hantsy, this is related to this issue:
#4441

The best solution is to delegate the script execution to Postgres itself. This can be achieved by copying the file into a special location in the container (a feature of the Postgres image):

new PostgreSQLContainer("postgres:12")
                    .withCopyFileToContainer(
                        MountableFile.forClasspathResource("init.sql"), 
                        "/docker-entrypoint-initdb.d/init.sql"
                     );

[seanf]

@kiview How can I do this with org.testcontainers.jdbc.ContainerDatabaseDriver please?

I want to provide jooq-codegen-maven with a pre-initialised database. This was working until the functions in the schema started using $$.

[seanf]

@kiview How can I do this with org.testcontainers.jdbc.ContainerDatabaseDriver please?

I want to provide jooq-codegen-maven with a pre-initialised database. This was working until the functions in the schema started using $$.

I found a workaround for Maven/jOOQ, but it's got a lot of moving parts.

It requires an extra Maven module. This module holds one class to implement an init function. The init function reads in the init.sql file as one big string, then executes it via JDBC's Statement.execute(). That module is declared as a dependency of the jooq-codegen-maven plugin, and the JDBC URL uses the TC_INITFUNCTION parameter to point to the init function. Fiddly, but it works.

[kiview]

I get the complexity @seanf. For this to become simpler using the special JDBC URL integration, we probably would need to have a feature like a URL query variable, that delegates to withCopyFileToContainer. Since the location will be specific to the db image, this could be somehow implemented in the specific container implementation.