SQL syntax error while testing in Spring Boot

Question

I have a Spring Boot project that uses PostgreSQL and Flyway. I want to test a UserRepository so I added an H2 database. Here is an application.properties file for tests:

spring.datasource.url=jdbc:h2://mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa

spring.datasource.driver-class-name=org.h2.Driver

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql=true

Here is UserRepository code:

@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long> {

    Optional<UserEntity> findByEmail(String email);

    Optional<UserEntity> findByUsername(String username);
}

I created such test for it:

@DataJpaTest
class UserRepositoryTest {

    @Autowired
    private UserRepository underTest;

    @Test
    void itShouldCheckThatUserExistsByEmail() {
        // given
        String email = "[email protected]";
        UserEntity user = new UserEntity(
                email,
                "12345678",
                "BobIsMe"
        );
        underTest.save(user);

        // when
        boolean expected = underTest.findByEmail(email).isPresent();

        // then
        assertTrue(expected);
    }

And when I start the test it fails with this error:

SQL State  : 42001
Error Code : 42001
Message    : Syntax error in expression SQL "CREATE TABLE IF NOT EXISTS customer\000d\000a(\000d\000a    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 [*]START 1),\000d\000a    email VARCHAR(50) NOT NULL,\000d\000a    password VARCHAR(20) NOT NULL,\000d\000a    username VARCHAR(30) NOT NULL\000d\000a)"; ожидалось "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, START WITH, RESTART, INCREMENT, MINVALUE, MAXVALUE, CYCLE, NO, EXHAUSTED, CACHE, )"
Syntax error in SQL statement "CREATE TABLE IF NOT EXISTS customer\000d\000a(\000d\000a    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 [*]START 1),\000d\000a    email VARCHAR(50) NOT NULL,\000d\000a    password VARCHAR(20) NOT NULL,\000d\000a    username VARCHAR(30) NOT NULL\000d\000a)"; expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, START WITH, RESTART, INCREMENT, MINVALUE, MAXVALUE, CYCLE, NO, EXHAUSTED, CACHE, )"; SQL statement:
CREATE TABLE IF NOT EXISTS customer
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1),
    email VARCHAR(50) NOT NULL,
    password VARCHAR(20) NOT NULL,
    username VARCHAR(30) NOT NULL
) [42001-224]
Location   : db/migration/V00001__Create_tables.sql (D:\university\SkyCast\skycast-repo\skycast_spring\target\classes\db\migration\V00001__Create_tables.sql)
Line       : 1
Statement  : CREATE TABLE IF NOT EXISTS customer
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1),
    email VARCHAR(50) NOT NULL,
    password VARCHAR(20) NOT NULL,
    username VARCHAR(30) NOT NULL
)

Here is SQL script creating tables in my PostgreSQL database:

CREATE TABLE IF NOT EXISTS customer
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1),
    email VARCHAR(50) NOT NULL,
    password VARCHAR(20) NOT NULL,
    username VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS customerCity
(
    customerId BIGINT NOT NULL,
    cityId BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS customerHistory
(
    customerId BIGINT NOT NULL,
    requestId BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS city
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1),
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS weather
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1),
    dateTime TIMESTAMPTZ NOT NULL,
    cityId INTEGER NOT NULL,
    climateData json NOT NULL
);

Answer 1

The syntax of your SQL script is specific to the PostgreSQL database. To test with H2 database you must start it in a PostgreSQL compatibility mode. Like this:

spring.datasource.url=jdbc:h2://mem:db;MODE=PostgreSQL

Based on: http://www.h2database.com/html/features.html#compatibility