ERROR: type "geometry" does not exist when creating schema using PostGIS

[cfecherolle]

Hi!

I'm a regular user of your library and it helps me a lot for testing repositories so, thank you for creating and maintaining it. 😄

I just changed the Docker image used for my embedded database Spring tests like so:

zonky:
  test:
    database:
      postgres:
        docker:
          image: postgis/postgis:11-3.1-alpine

The goal here is to use Postgis in my tests, which is not included nor installed in the standard PostgreSQL images.

This Docker image, through initdb, creates the required Postgis extensions. My problem is, it creates them in template_postgis and $POSTGRES_DB (see https://github.com/postgis/docker-postgis/blob/master/initdb-postgis.sh) while the test library uses another separate database with a randomly generated name, as I've seen in the DockerPostgresDatabaseProvider code:

String databaseName = RandomStringUtils.randomAlphabetic(12).toLowerCase(Locale.ENGLISH);

Using your embedded database test lib and this Postgis Docker image, my tests fail. I think it's because the Postgis extension is installed in another database so my table creations fail.

With ddl-auto set to create, I get this kind of exception: Caused by: org.postgresql.util.PSQLException: ERROR: type "geometry" does not exist

To make this work, I would like to either:

• Set a fixed name for the embedded test database, if that is possible, or
• Pass the randomly generated name as an environment variable to my Docker image (but that seems unlikely)

What would you advise me to get Postgis working in my test config? Thanks a lot.

[tomix26]

Hi @cfecherolle. Try creating the following bean:

@Bean
public PostgreSQLContainerCustomizer postgresContainerCustomizer() {
    return container -> container.withDatabaseName("template1");
}

It ses the $POSTGRES_DB property to the template1 database, which is used as a template for creating all new databases. Maybe I could set this setting as default, but I need to check the possible drawbacks first.

[tomix26]

By the way, you could also add extension registration to flyway or liquibase scripts, that would work too.

[cfecherolle]

Hi @tomix26,

Thanks for the help! Actually, I forgot to post here but I found a solution to my issue last week. As you said, this can work by using Flyway to execute the CREATE EXTENSION query in a migration script before tests. I intend to use Flyway soon in my app anyway, so the added weight from the new dependencies was not a deal breaker to me.

If it can help others, here is what I did:

1. Added these dependencies to my pom.xml:

   <dependency>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-core</artifactId>
   <version>6.5.7</version>
   </dependency>
   <dependency>
   <groupId>org.flywaydb.flyway-test-extensions</groupId>
   <artifactId>flyway-spring-test</artifactId>
   <version>6.4.0</version>
   <scope>test</scope>
   </dependency>

2. Added @FlywayTest annotation to the tests which require PostGIS to create their database schema.

3. Added a migration file V1__create-postgis-extension.sql containing CREATE EXTENSION IF NOT EXISTS postgis;

4. Adapted my Spring Boot configuration to enable Flyway and set the correct location to my migration file:

   spring:
   # Flyway migrations are enabled for this profile in order to create (= enable) the PostGIS extension
   # Without this hack to enable extension creation, the database schema cannot be created with DDL auto because of missing data types (such as geometry)
   flyway:
   enabled: true
   locations: classpath:/db/migration/<your_migrations_directory>

The database name fix looks good too and would be lighter, so I'm going to try it too.

[cfecherolle]

... And the PostgreSQLContainerCustomizer bean solution also works, without Flyway 🎉 Thank you!