How to one-off run @DataJpaTest against real database instead of in-memory with Spring Boot

Question

I am using Spring Boot 1.4.3 and have a whole bunch of tests that are annotated with @DataJpaTest. By default, they run against an in-memory database. I would like to be able to run all of them against a local MySQL temporarily. How can I do this in an easy way?

I have found that I can make it work for one by adding @ActiveProfiles("local") where I have an application-local.properties that points to my local MySQL, but it is just too much work to add that everywhere, run the tests and then remove it again (since I only want to run this manually against MySQL, the CI environment will run against the in memory db).

I am using Maven if that would matter.

UPDATE:

So I have an application-local.properties which contains the db properties to connect to my local MySQL database (Which I use already to run my application against the local MySQL)

Then I right-click in IntelliJ on a package and select "Run all tests in package". In the settings of that run configuration, I add -Dspring.profiles.active=local to the "VM options" field.

I would have thought that this would activate the local profile during the tests, but it does not. If I stop the local MySQL, the tests still run fine.

I suggest renaming this to "How to one-off run @DataJpaTest against real database instead of in-memory" (or something like that) so it sounds less subjective. — Captain Man, Jan 12 '17 at 15:54
The system property does not override `@ActiveProfiles`. See [SPR-8982](https://jira.spring.io/browse/SPR-8982). For a custom work-around, see http://stackoverflow.com/questions/20551681/spring-integration-tests-with-profile/33044283#33044283. — Sam Brannen, Jan 12 '17 at 17:41
@SamBrannen Seems `@ActiveProfies` itself does not work either. The embedded db gets used anyway. — Wim Deblauwe, Jan 12 '17 at 20:10
Check this for a solution to this kind of problem: https://stackoverflow.com/questions/53002232/spring-boot-datajpatest-unit-test-reverting-to-h2-instead-of-mysql. To exclude the autoconfiguration of H2 for tests use `@AutoConfigureTestDatabase(replace=Replace.NONE)` — lainatnavi, May 26 '21 at 16:27

score 3 · Answer 1 · answered Sep 04 '20 at 09:22

3

In the docs it states that you are able to remove the autoconfigured h2 datasource with @AutoConfigureTestDatabase(replace= Replace.NONE) on the test class https://docs.spring.io/spring-boot/docs/1.4.2.RELEASE/reference/html/boot-features-testing.html#boot-features-testing-spring-boot-applications-testing-autoconfigured-jpa-test.

Also you then need to provide your db setup in properties, so that it does not use your app properties e.g.:

# Database
spring.datasource.url=jdbc:mariadb://localhost:3303/test
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

i put this in application.properties in the test package

answered Sep 04 '20 at 09:22

Bruno

89
4

1

But this does not allow easy switching between H2 and MySQL. (Side note: I am now no longer using H2. I just use Testcontainers with MySQL for all tests) – Wim Deblauwe Sep 04 '20 at 09:24
Unfortunately this does not work or the answer doesn't have enough details to make it work. Where _exactly_ does the application.properties go? It is possible the spring boot docs are incorrect as well because putting @AutoConfigureTestDatabase(replace= Replace.NONE) on the test class still results in H2 being configured and used. – Michael May 07 '21 at 14:41

score 1 · Answer 2 · answered Jan 12 '17 at 14:58

You can add the profile with the MySQL datasource properties in the same application.properties (or .yml) as:

application.yml

# Existing properties

---
spring:
  profiles: h2
# More h2-related properties

---
spring:
  profiles: postgres
  database:
    driverClassName: org.postgresql.Driver
  datasource:
    url: jdbc:postgresql://localhost:5432/db_dvdrental
    username: user_dvdrental
    password: changeit
  jpa:
    database: POSTGRESQL
    generate-ddl: false
# More postgres-related properties

and either use @ActiveProfiles("postgres") in an integration test class or start teh container using VM argument as:

java -Dspring.profiles.active=h2 ...

It still uses the embedded in momory DB , regardless of the DB url provided — Clyde D'Cruz, Apr 24 '20 at 18:29

score 1 · Answer 3 · answered Jul 09 '22 at 19:25

Add application.yml(properties) with jdbc connection into src/test/resources

Run your JPA test with @AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE) - it disables using embedded database (h2), otherwise :

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource': Invocation of init method failed; nested exception is java.lang.IllegalStateException: Failed to replace DataSource with an embedded database for tests. If you want an embedded database please put a supported one on the classpath or tune the replace attribute of @AutoConfigureTestDatabase.

score 0 · Answer 4 · answered Jun 04 '23 at 18:52

My problem was that I had multiple application.properties in the project and DataJpaTest collected all of them and overwrote my properties meant for testing with production ones.

The best thing I came up with is to instruct the test to use a specific application.properties by annotating the class with

@TestPropertySource(properties = "spring.config.location=classpath:/application.properties")
@DataJpaTest
class MyRepositoryTest {
  // test methods
}

How to one-off run @DataJpaTest against real database instead of in-memory with Spring Boot

4 Answers4