How to create 2nd schema on db initialization in unit tests?

[maadddog]

Hi there! :)

I am currently using Spring Boot with liquibase & the embedded-database-spring-test dependency to start up my PostgreSQL DB for my unit tests & everything was working great with very little work on my end until this past week when I had to refactor my database structure & I now have a 2nd schema that needs to be created when the database is initialized during unit tests; so that when my liquibase scripts run, they are able to find schema B and execute the scripts I have. However, I get an error telling me that "schema B does not exist" so my tests all fail (which makes sense since only one schema gets created normally).

I was hoping to find a way to do this without having to use TestContainers dependency directly. Everything I have tried seems to occur too late in the lifecycle.

• I attempted adding @Sql("classpath:db/createSchemas.sql") as well as other variations of this with execution phases, but all of them would occur too late.
• I have also attempted adding a CREATE SCHEMA IF NOT EXISTS B; into my liquibase scripts with a context of "test" so that the code only gets executed during my unit tests
• I saw something online about trying zonky.test.database.postgres.initdb.database-defaults=CREATE SCHEMA A; CREATE SCHEMA B;

There are quite a few other things I have attempted, but none of them seem to work. Sometimes I debate if they didn't work because I was just missing a small piece of the set up that I needed.
To note: my code works when I run my application, so I am pretty confident my Liquibase scripts are set up properly in general. However, when I start up my application I have a docker-compose.yml that contains this & the init-db.sql contains CREATE SCHEMA IF NOT EXISTS A; CREATE SCHEMA IF NOT EXISTS B;

services:
  db:
    image: postgis/postgis:latest
    ports:
      - '5432:5432'
    container_name: my-postgres
    environment:
      - POSTGRES_USER=myuser
      - POSTGRES_PASSWORD=mypassword
      - POSTGRES_DB=MYDB
    volumes:
      - postgis_data:/var/lib/postgresql/data
      - ./dbInitScripts/init-db.sql:/docker-entrypoint-initdb.d/init.sql

Thank you for your time replying. I really appreciate any help that can be provided.

[tomix26]

Hi @maadddog, thanks for your question and sorry for the delayed response.

Based on your description, when you run the application through docker-compose.yml, the init-db.sql script creates the required schemas before Liquibase migrations are executed. However, when running tests, this script is missing, which causes the problems. I recommend checking out the following article: https://www.baeldung.com/java-postgresql-create-schema-before-liquibase, which describes various ways to create the schemas.

Personally, I would recommend either adding the CREATE SCHEMA IF NOT EXISTS statement directly to the Liquibase changeset (solution 5.1), or using a data source post processor (solution 5.2). The post processor has the advantage that it can be activated by a test profile, so you can ensure it won't affect the production environment.

Regarding the other mentioned solutions, the @Sql annotation indeed executes too late because it's applied just before running the test class or test method. And as for the zonky.test.database.postgres.initdb.database-defaults property, based on the prefix it would have to be part of the Zonky library, and I'm not aware of any such property.