@DataJpaTest & @SpringBootTest querying src/main/resources database instead of src/test/resources database

Question

Im trying to thoroughly understand Spring Boot, especially testing.

I want all tests to run against the database in application.properties inside src/test/resources regardless of the active profile. But for some reason it only queries src/main/resources databases.

I created the following profiles for now (Maybe the names don't make sense):

• tdd
• stage
• production

I have created two Databases with the same schema and functions through script files:

• MythicalLearn
• MythicalLearnTest

I also have a separate Data.sql file inside src/test/resources/db/Data.sql

I have a local Postgres database running & since I am using Postgres, I don't feel the need for any test containers or H2, I just created another database on the same instance for testing locally, Is this acceptable ?

As far as I know, everything in src/test/resources should be used for tests but somehow all tests are running against whatever database is in the active profile.

For some reason, all @DataJpaTests and @SpringBootTests are finding the Data.sql script inside src/test/resources/db/Data.sql, But for some reason, it is running tests against the database defined in the active profile inside src/main/resources file.

I have only added the code I feel is necessary for this question, but will happily edit this and provide more details.

Here is the code:

SQL

TRUNCATE serverappsplayground.DummyEntity RESTART IDENTITY CASCADE ;
INSERT INTO serverappsplayground.DummyEntity (name, key, value, x, y, z)
VALUES
('DE-Name 1', 'key-1', 'value-1', 65, 33, FLOOR(RANDOM() * 1000)),
('DE-Name 2', 'key-2', 'value-2', 47, 96, FLOOR(RANDOM() * 1000)),
('DE-Name 3', 'key-3', 'value-3', 22, 34, FLOOR(RANDOM() * 1000)),
('DE-Name 4', 'key-4', 'value-4', 77, 56, FLOOR(RANDOM() * 1000)),
('DE-Name 5', 'key-5', 'value-5', 89, 17, FLOOR(RANDOM() * 1000)),
('DE-Name 6', 'key-6', 'value-1', 4, 78, FLOOR(RANDOM() * 1000)),
('DE-Name 7', 'key-7', 'value-2', 13, 8, FLOOR(RANDOM() * 1000)),
('DE-Name 8', 'key-8', 'value-8', 67, 10, FLOOR(RANDOM() * 1000)),
('DE-Name 9', 'key-9', 'value-9', 56, 2, FLOOR(RANDOM() * 1000)),
('DE-Name 10', 'key-10', 'value-10', 99, 43, FLOOR(RANDOM() * 1000)),
('DE-Name 11', 'key-11', 'value-11', 65, 33, FLOOR(RANDOM() * 1000)),
('DE-Name 12', 'key-12', 'value-12', 47, 96, FLOOR(RANDOM() * 1000)),
('DE-Name 13', 'key-13', 'value-13', 22, 34, FLOOR(RANDOM() * 1000)),
('DE-Name 14', 'key-14', 'value-14', 77, 56, FLOOR(RANDOM() * 1000)),
('DE-Name 15', 'key-15', 'value-15', 89, 17, FLOOR(RANDOM() * 1000)),
('DE-Name 16', 'key-16', 'value-16', 4, 78, FLOOR(RANDOM() * 1000)),
('DE-Name 17', 'key-17', 'value-17', 13, 8, FLOOR(RANDOM() * 1000)),
('DE-Name 18', 'key-18', 'value-18', 67, 10, FLOOR(RANDOM() * 1000)),
('DE-Name 19', 'key-19', 'value-19', 56, 2, FLOOR(RANDOM() * 1000)),
('DE-Name 20', 'key-20', 'value-20', 99, 43, FLOOR(RANDOM() * 1000));

Configuration:

application.yml

spring:

    # Set the application name and description:
    application:
      name: "SpringBootWebMvcJavaFoundation"
      description: "This is the fundamental foundation project for Spring Boot"
    main:
      keep-alive: true

  # Setting the profile
    profiles.active: stage

    # Enabling virtual threads
    # This is extremely important in order to maximise performance and make the most of Java 21
    threads.virtual.enabled: true

    # Data access layer

    jpa:
      hibernate:
        # This property is extremely important as I want to remove Spring Boot making queries with those underscores by default:
        naming.physical-strategy: "org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl"

        # Here we make sure Spring Data JPA does not mess around with my database as I don't want it too,
        # It makes sure there are no ddl statements in any profile
        ddl-auto: none

    test:
      database:
        replace: none

application-tdd.yml:

spring:
  sql:
    init:
      mode: always
      # Adding the classpath prefix to all paths,
      # and then following the path as normal works for tests + when app context is loaded
      # Note: Schema generation does not  need to happen everytime, I can also manually load the schema,
      # This makes tests slow, so I must simply aware, anytime changes are made too Schema or functions,
      # Either uncomment this to be safe or remember and manually modify the schema with psql,

      #schema-locations: classpath:db/Schema.sql,classpath:db/Functions.sql
      data-locations: classpath:db/Data.sql
  datasource:
    url: jdbc:postgresql://localhost:5432/MythicalLearnTest
  jpa:
    show-sql: 'true'
testpropertyexists: 'yes'

application-stage.yml

spring:
  sql:
    init:
      mode: always
      #schema-locations: classpath:db/Schema.sql,classpath:db/Functions.sql
      #data-locations: classpath:db/Data.sql
  datasource:
    url: "jdbc:postgresql://localhost:5432/MythicalLearn"

application.properties in src/test/resources:

spring.sql.init.mode=always
spring.datasource.url=jdbc:postgresql://localhost:5432/MythicalLearnTest
spring.sql.init.data-locations=classpath:db/Data.sql

Test Code

@DataJpaTest base class:

@org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class DataJpaTest {
    
}

class DummyJpaServiceDbTest extends DataJpaTest {

    private DummyJpaService dummyJpaService;

    @Autowired
    private DummyJpaRepo dummyJpaRepo;

    @BeforeEach
    void setUp() {
        dummyJpaService = new DummyJpaService(dummyJpaRepo);
    }

    @Test
    void works() {
        assertTrue(true);
    }

    @Test
    void getAllDummies() {
        assertEquals(
                20,
                dummyJpaService.getAllDummies().size()
        );
    }

    @Test
    void getDummyById() {
        assertEquals(
                "key-7",
                dummyJpaService.getDummyById(7).get().getKey()
        );
    }

    @Test
    void createDummy() {
        DummyEntity entity = new DummyEntity();
        entity.setKey("key-222");
        dummyJpaService.createDummy(entity);
        assertEquals(21, dummyJpaService.getAllDummies().size());
    }

    @Test
    void updateDummy() {
        DummyEntity entity = dummyJpaService.getDummyById(7).get();
        entity.setName("UPDATED DE7");
        dummyJpaService.updateDummy(entity);
        assertEquals("UPDATED DE7", dummyJpaService.getDummyById(7).get().getName());
    }

    @Test
    void deleteDummy() {

        DummyEntity entity = dummyJpaService.getDummyById(20).get();
        dummyJpaService.deleteDummy(entity);
        assertEquals(19, dummyJpaService.getAllDummies().size());
    }

    @Test
    void deleteDummyById() {
        DummyEntity entity = dummyJpaService.getDummyById(20).get();
        dummyJpaService.deleteDummyById(entity.getId());
        assertEquals(19, dummyJpaService.getAllDummies().size());
    }
}

@SpringBootTest

@SpringBootTest
@AutoConfigureMockMvc
@Sql(scripts = "classpath:db/Data.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class SpringBootJavaApplicationTests {

    @Test
    void contextLoads() {
    }

}

    class DummyApiServiceIntegrationTest extends SpringBootJavaApplicationTests {

    @Autowired
    private MockMvc mvc;

    private String basePath = "/dummy-entity-api";

    @Autowired
    private ObjectMapper mapper;

    @BeforeEach
    void setUp() {

    }

    @Test
    @DisplayName("Checking and hoping the real database is loaded for tests")
    void getAllDummies() throws Exception {
        MvcResult result = mvc.perform(
            get(basePath + "/dummies")
        ).andReturn();

        assertThat(result.getResponse().getContentAsString()).isNotBlank();

    }

    @Test
    @DisplayName("Testing the response returns the 20 lists that are in the dummy data")
    void getAllDummiesObjects() throws Exception {
        MvcResult result = mvc.perform(
            get(basePath + "/dummies")
        ).andReturn();

        //Use the arrrays.asList because mapper returns an array:
        List<DummyEntity> dummyEntityList = getDummyListFromJson(
            result.getResponse().getContentAsString()
        );

        assertThat(dummyEntityList.size()).isEqualTo(20);
    }

    @Test
    @DisplayName("When id not found then we get 404")
    void getByIdNotFound() throws Exception {
        mvc.perform(
            get(basePath + "/{25}", 25)

        ).andExpect(status().isNotFound());
    }

    @Test
    @DisplayName("When id found then it is 200")
    void getByIdFound() throws Exception {
        mvc.perform(
            get(basePath + "/{1}", 1)

        ).andExpect(status().isOk());
    }

    @Test
    @DisplayName("Testing that it creates a new entity")
    void testCreates() throws Exception {
        DummyEntity dummyEntity = new DummyEntity();
        dummyEntity.setName("DE-Created By Spring Boot test");
        dummyEntity.setKey("key-21");
        dummyEntity.setValue("value-21");
        dummyEntity.setXYZ(1,2,3);

        MvcResult result = mvc.perform(
                post(basePath + "/create")
                    .contentType(MediaType.APPLICATION_JSON)
                    .content(mapper.writeValueAsString(dummyEntity))
            )
            .andReturn();

        assertThat(result.getResponse().getStatus()).isEqualTo(201);

    }

    @Test
    @DisplayName("Here i am testing that the extra entity is added, but then on each load it is reset")
    void testExtraEnttityIsAdded() throws Exception {
        DummyEntity dummyEntity = new DummyEntity();
        dummyEntity.setName("DE-Created By Spring Boot test");
        dummyEntity.setKey("key-21");
        dummyEntity.setValue("value-21");
        dummyEntity.setXYZ(1,2,3);

        mvc.perform(
                post(basePath + "/create")
                    .contentType(MediaType.APPLICATION_JSON)
                    .content(mapper.writeValueAsString(dummyEntity))
            )
            .andReturn();

        //Not a very readable expression, but later I can learn
        assertThat(
            getDummyListFromJson(
                mvc.perform(
                        get(basePath + "/dummies")
                    )
                    .andReturn()
                    .getResponse()
                    .getContentAsString()
            ).size()
        ).isEqualTo(21);



        //Getting the list so I can see it should be 21:

    }

    /**
     * Keeping a private function that gets a List from JSON String,
     * So I do not have to repeat myself multiple times:
     */
    private List<DummyEntity> getDummyListFromJson(String jsonList) throws Exception {
        return Arrays.asList(
            mapper.readValue(
                jsonList,
                DummyEntity[].class
            )
        );
    }



}

Now, I have two separate databases, but when I run the tests while in the staging profile, it finds the Data.sql script in test/resources, but for some reason, does not run the tests against the test database:

I have truncated the tables many times and the same thing happens

How do I get all tests to only run against the test database regardless of the profile ? Is this even possible ? Or perhaps, what I am trying to do is silly, and there is a better work flow ?

I can't help but think, this is one of those things that is so straight forward that its not even mentioned, but I am not getting it. I will appreciate any advice

Answer 1

What happens now is that the keys from both files (src/test/resources/application.properties and src/main/resources/application.yml) are merged, so the spring.profiles.active key is present in your tests.

Rename your src/test/resources/application.properties to src/test/resources/application.yml. This will override the src/main/resources/application.yml. So now only the keys from src/test/resources/application.yml will be present in the tests.