Spring Boot. @DataJpaTest H2 embedded database create schema

Question

I have couple of entities in my data layer stored in particular schema. For example:

@Entity
@Table(name = "FOO", schema = "DUMMY")
public class Foo {}

I'm trying to setup H2 embedded database for integration testing of my data layer. I'm using @DataJpaTest annotation for my tests to get H2 embedded database configured automatically. However, the creation of tables fails because schema DUMMY is not created at DB initialization.

Any ideas on how to create schema before creation of tables in test cases?

I've tried to use @Sql(statements="CREATE SCHEMA IF NOT EXISTS DUMMY") but didn't succeed.

Also, I've tried to set spring.datasource.url = jdbc:h2:mem:test;INIT=CREATE SCHEMA IF NOT EXISTS DUMMY in my test.properties file together with TestPropertySource("classpath:test.properties"), but that didn't work too.

Gorgias

Answer 1

I had the same issue, I managed to resolve by creating schema.sql (in resources folder) with the content

CREATE SCHEMA IF NOT EXISTS <yourschema>

Documentation can be found here but imho the lack of real examples make it very complex. Warning: this script is also executed within the normal (not test) environment.

Not mandatory, but good practice, add h2 dependency only in test scope

<dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
   <scope>test</scope>
</dependency>

Breathed

thanks for reply. It works! BTW, nice catch about scope of dependency. – Gorgias

Also, I've found another solution for this problem, just 've forgotten to place it here. – Gorgias

it turned out that you can put schema.sql under src/test/resources to apply it only for tests – Gorgias

Answer 2

I think you are looking for this annotation:

@AutoConfigureTestDatabase(replace=Replace.NONE)

example:

@DataJpaTest
@AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE)
class UserRepoTest {...}

Eiffel

I would expect this to delete/drop you productive Database instead of using H2. – Hyperbole

Answer 3

After couple hours of struggling I've found a workaround.

You can define spring.jpa.properties.hibernate.default_schema = DUMMY in your application.properties.

And then set spring.jpa.properties.hibernate.default_schema = in your test.properties and use together with @TestPropertySource("classpath:test.properties")

So, in this way the schema DUMMY won't be created and the entities will be created in default schema.

Gorgias

You can also use @TestPropertySource(properties = "spring.jpa.properties.hibernate.default_schema=...") – Oskar

Answer 4

In my case schema.sql under test/resources din't worked.

The following configuration in test/resources/application.yml file worked.

spring:
  datasource:
    username: sa
    password: sa
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:usrmgmt;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL;INIT=CREATE SCHEMA IF NOT EXISTS DUMMY;
  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.xml

In the above configuration, provided the below additional configuration

1. INIT=CREATE SCHEMA IF NOT EXISTS DUMMY extension to the existing DB url. In the absence of this, faced the exception Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "DUMMY" not found;.
2. spring.liquibase.change-log property. In the absence of this, faced the exception Caused by: liquibase.exception.ChangeLogParseException: classpath:/db/changelog/db.changelog-master.yaml does not exist.

Saltine