Spring Boot Embedded PostgreSQL Database for Testing

In this story, we’ll learn how to implement Embedded PostgreSQL for Spring Boot Integration testing.

· Prerequisites · Abstract · Zonky Embedded Database · Usage ∘ Maven Dependencies ∘ Writing Testing · Conclusion · References

Prerequisites

This is the list of all the prerequisites:

Spring Boot 3+
Maven 3.6.3
Java 21

Abstract

Because of its lightweight nature, an embedded database can be useful during a project's development phase. Benefits include ease of configuration, quick startup time, testability, and the ability to rapidly evolve SQL during development.

Many Spring Boot projects use an H2 database or Docker container with TestContainers for integration testing. So, there are instances in which the project team desires to use an identical production database in testing without the need to execute a Docker container for some reason. This approach can have some advantages, such as speed of testing, performance, and the ability to reproduce production issues.

Zonky Embedded Database

Zonky Embedded Database was created as a fork of OpenTable Embedded PostgreSQL Component due to the inactivity of maintainers, and it continues to support options for a test database without Docker.

It allows embedding PostgreSQL into Java application code with no external dependencies. Excellent for allowing you to unit test with a “real” Postgres without requiring end users to install and set up a database cluster.

It supports different database providers:

Usage

Here is a simple project structure of a typical Spring Boot REST API with PostgreSQL.

Maven Dependencies

Before using the Zonky provider, we need to add the following Maven dependencies to the pom.xml file:

<dependency>
    <groupId>io.zonky.test</groupId>
    <artifactId>embedded-postgres</artifactId>
    <version>2.0.7</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>io.zonky.test</groupId>
    <artifactId>embedded-database-spring-test</artifactId>
    <version>2.5.1</version>
    <scope>test</scope>
</dependency>

Now we are ready to write test classes.

Writing Testing

Repository integration testing

Let’s write unit tests to verify the functionality of the BookRepository.

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

}

@DataJpaTest
@AutoConfigureEmbeddedDatabase(provider = ZONKY)
@Sql({"/sql/init.sql"})
class BookRepositoryIT {

    @Autowired
    private BookRepository bookRepository;

    @Test
    void testFindById() {
        Optional<Book> bookOptional = bookRepository.findById(1L);

        assertThat(bookOptional).hasValueSatisfying(book -> {
            assertThat(book.getId()).isNotNull();
            assertThat(book.getDescription()).isEqualTo("netus et malesuada");
            assertThat(book.getIsbn()).isEqualTo("X4J 5H8");
            assertThat(book.getPage()).isEqualTo(62);
            assertThat(book.getPrice()).isEqualTo(529);
            assertThat(book.getTitle()).isEqualTo("arcu. Vestibulum ut");
        });
    }

    @Test
    void testFindAll() {
        assertThat(bookRepository.findAll())
                .isNotEmpty()
                .hasSize(10)
                .extracting(Book::getTitle)
                .contains("arcu. Vestibulum ut", "et ipsum cursus", "lorem semper");
    }

    @Test
    void testSave() {
        Book b = Book.builder()
                .title("title")
                .isbn("20351LOPf")
                .page(100)
                .price(250)
                .description("my description")
                .build();

        var book = bookRepository.save(b);
        assertThat(book.getId()).isNotNull();
        assertThat(book.getDescription()).isEqualTo("my description");
        assertThat(book.getIsbn()).isEqualTo("20351LOPf");
        assertThat(book.getPage()).isEqualTo(100);
        assertThat(book.getPrice()).isEqualTo(250);
        assertThat(book.getTitle()).isEqualTo("title");
    }
}

We used the @AutoConfigureEmbeddedDatabase annotation with the DatabaseProvider.ZONKY provider because it allows using the embedded Postgres database without the need for Docker.

Controller integration testing

@Slf4j
@RestController
@RequestMapping("/api/book")
public class BookController {

    private final BookService entityService;

    public BookController(BookService entityService) {
        this.entityService = entityService;
    }

    /**
     * {@code POST  /book} : Create a new book.
     *
     * @param book the book to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new book.
     */
    @PostMapping()
    public ResponseEntity<Book> createBook(@RequestBody @Valid Book book) {
        log.debug("REST request to save Book : {}", book);
        return new ResponseEntity<>(entityService.create(book), HttpStatus.CREATED);
    }

    /**
     * {@code PUT  /book/:id} : Updates an existing book.
     *
     * @param book the book to update.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the updated book,
     * or with status {@code 400 (Bad Request)} if the book is not valid,
     * or with status {@code 500 (Internal Server Error)} if the book couldn't be updated.
     */
    @PutMapping(value = "/{id}")
    public ResponseEntity<Book> updateBook(@Valid @RequestBody Book book, @PathVariable("id") Long id) {
        log.debug("REST request to update Book : {}", book);
        Book result = entityService.update(book, id);
        return ResponseEntity.ok().body(result);
    }

    /**
     * {@code GET  /book} : get all the books.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of book in body.
     */

    @GetMapping()
    public ResponseEntity<List<Book>> getAllBook() {
        log.debug("REST request to get all books");
        List<Book> lst = entityService.getAll();

        return new ResponseEntity<>(lst, HttpStatus.OK);
    }

    /**
     * {@code GET  /book/:id} : get the "id" book.
     *
     * @param id the id of the book to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the book, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Book> getOneBook(@PathVariable("id") Long id) {
        log.debug("REST request to get Book : {}", id);
        Book e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

    /**
     * {@code DELETE  /book/:id} : delete the "id" book.
     *
     * @param id the id of the book to delete.
     * @return the {@link ResponseEntity} with status {@code 204 (NO_CONTENT)}.
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteBook(@PathVariable("id") Long id) {
        log.debug("REST request to delete Book : {}", id);
        entityService.delete(id);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

}

We’ll create an abstract class to be used by all integration test classes.

/**
 * Base composite class for integration tests.
 */
@SpringBootTest(classes = {SpringBootEmbeddedPostgresqlApplication.class})
@AutoConfigureMockMvc
@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD)
@AutoConfigureEmbeddedDatabase(provider = ZONKY)
public abstract class AbstractIntegrationTest {

    @Autowired
    protected MockMvc mockMvc;

    protected final static String BASE_CONTROLLER_ENDPOINT = "/api";

}

/**
 * Integration tests for the {@link BookController} REST controller.
 */
class BookControllerIT extends AbstractIntegrationTest {

    @Autowired
    private BookRepository repository;

    private Book book;

    @BeforeEach
    public void initTest() {
        repository.deleteAll();
        book = Book.builder()
                .title("testing")
                .isbn("20351LOPf")
                .page(110)
                .price(214)
                .description("controller IT")
                .build();
    }

    @Test
    void createBook() throws Exception {
        int databaseSizeBeforeCreate = repository.findAll().size();

        mockMvc
                .perform(post(MessageFormat.format("{0}/book", BASE_CONTROLLER_ENDPOINT)).contentType(MediaType.APPLICATION_JSON)
                        .content(TestUtil.convertObjectToJsonBytes(book)))
                .andExpect(status().isCreated());


        var books = repository.findAll();
        assertThat(books).hasSize(databaseSizeBeforeCreate + 1);
        Optional<Book> bookOptional = books.stream().filter(b -> b.getIsbn().equals("20351LOPf")).findFirst();
        assertThat(bookOptional).hasValueSatisfying(testBook -> {
            assertThat(testBook.getId()).isNotNull();
            assertThat(testBook.getDescription()).isEqualTo("controller IT");
            assertThat(testBook.getIsbn()).isEqualTo("20351LOPf");
            assertThat(testBook.getPage()).isEqualTo(110);
            assertThat(testBook.getPrice()).isEqualTo(214);
            assertThat(testBook.getTitle()).isEqualTo("testing");
        });
    }

    @Test
    void updateBook() throws Exception {
        repository.save(book);
        int databaseSizeBeforeUpdate = repository.findAll().size();

        book.setTitle("update title");
        book.setIsbn("00125689");
        book.setPage(50);
        mockMvc
                .perform(put(MessageFormat.format("{0}/book/1", BASE_CONTROLLER_ENDPOINT)).contentType(MediaType.APPLICATION_JSON)
                        .content(TestUtil.convertObjectToJsonBytes(book)))
                .andExpect(status().isOk());


        var books = repository.findAll();
        assertThat(books).hasSize(databaseSizeBeforeUpdate);
        Optional<Book> bookOptional = books.stream().filter(b -> b.getIsbn().equals("00125689")).findFirst();
        assertThat(bookOptional).hasValueSatisfying(testBook -> {
            assertThat(testBook.getId()).isNotNull();
            assertThat(testBook.getDescription()).isEqualTo("controller IT");
            assertThat(testBook.getIsbn()).isEqualTo("00125689");
            assertThat(testBook.getPage()).isEqualTo(50);
            assertThat(testBook.getPrice()).isEqualTo(214);
            assertThat(testBook.getTitle()).isEqualTo("update title");
        });
    }

    @Test
    void getAllBook() throws Exception {
        repository.save(book);

        mockMvc.perform(get(MessageFormat.format("{0}/book", BASE_CONTROLLER_ENDPOINT))
                        .accept(MediaType.APPLICATION_JSON))
                .andDo(print())
                .andExpect(status().isOk())
                .andExpect(MockMvcResultMatchers.jsonPath("$.[*]").exists())
                .andExpect(MockMvcResultMatchers.jsonPath("$.[*].title").isNotEmpty())
                .andExpect(MockMvcResultMatchers.jsonPath("$.[*].isbn").value(book.getIsbn()));
    }

    @Test
    void getOneBook() throws Exception {
        repository.save(book);


        var books = repository.findAll();

        mockMvc.perform(get(MessageFormat.format("{0}/book/1", BASE_CONTROLLER_ENDPOINT))
                        .accept(MediaType.APPLICATION_JSON))
                .andDo(print())
                .andExpect(status().isOk())
                .andExpect(MockMvcResultMatchers.jsonPath("$.id").value(1))
                .andExpect(MockMvcResultMatchers.jsonPath("$.isbn").value(book.getIsbn()))
                .andExpect(MockMvcResultMatchers.jsonPath("$.description").value(book.getDescription()))
                .andExpect(MockMvcResultMatchers.jsonPath("$.page").value(book.getPage()));
    }

    @Test
    void getOneBookNotFound() throws Exception {
        mockMvc.perform(get(MessageFormat.format("{0}/book/1", BASE_CONTROLLER_ENDPOINT))
                        .accept(MediaType.APPLICATION_JSON))
                .andDo(print())
                .andExpect(status().isBadRequest());
    }
    @Test
    void deleteBook() throws Exception {
        repository.save(book);

        mockMvc.perform(delete(MessageFormat.format("{0}/book/1", BASE_CONTROLLER_ENDPOINT))
                        .accept(MediaType.APPLICATION_JSON))
                .andDo(print())
                .andExpect(status().isNoContent());
    }

}

When we run the above tests, we get the test results below.

Conclusion

Well done !!. In this story, we learned to write integration tests using Embedded PostgreSQL for Spring Boot Integration testing.

The complete source code is available on GitHub.

If you loved reading the story, don’t forget to clap 👏. You can reach out to me and follow me on Medium, Twitter, GitHub, Linkedln

Support me through GitHub Sponsors.

Thank you for Reading !! See you in the next story.