Postgresql – Spring Boot integration testing with H2 database not working due to schema not found

I try to develop integration tests with H2 database but it always fails to find schema even though it is present.

Spring Boot 3.3.3 with Java 22. As main database I use PostgreSQL 16.

I also have src/test/resources/schema.sql where the script is: "CREATE SCHEMA IF NOT EXISTS cc;" but it fails to run it.

How to deal with schema creation when trying to mock a PostgreSQL database?

Here is my setup:

Proof that properties from src/test/resources/application.properties are loaded:

The test:

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import ro.championsclub.entity.JwtToken;
import ro.championsclub.entity.User;
import ro.championsclub.repository.JwtTokenRepository;
import java.util.List;
import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
public class JwtTokenServiceIT {

    @Autowired
    private JwtTokenService jwtTokenService;

    @Autowired
    private JwtTokenRepository jwtTokenRepository;

    @BeforeEach
    void setUp() {
        jwtTokenRepository.deleteAll();
    }

    @Test
    void saveJwtTokenTest() {
        var user = User.builder()
                .email("[email protected]")
                .password("test")
                .firstName("First")
                .lastName("Last")
                .isEnabled(true)
                .build();

        var token = "random-string";

        jwtTokenService.saveJwtToken(user, token);

        List<JwtToken> tokens = jwtTokenRepository.findAll().stream()
                .filter(tkn -> tkn.getToken().equals(token))
                .toList();

        assertThat(tokens).isNotEmpty();

        var jwtToken = tokens.getFirst();

        assertThat(jwtToken.getToken()).isEqualTo(token);
        assertThat(jwtToken.getUser().getEmail()).isEqualTo(user.getEmail());
    }
    
}

application.properties from src/test/resources

spring.datasource.url=jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.hikari.schema=cc

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.security</groupId>
    <artifactId>spring-security-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

The root exception is:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "cc" not found [90079-224]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.message.DbException.get(DbException.java:199)
    at org.h2.engine.Database.getSchema(Database.java:1578)
    at org.h2.engine.SessionLocal.setCurrentSchemaName(SessionLocal.java:1317)
    at org.h2.jdbc.JdbcConnection.setSchema(JdbcConnection.java:1808)
    at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:423)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:550)
    ... 67 more

While the schema exists: