Table of Contents

Replace H2 with a real database for testing

This guide shows how to replace H2 with a real database for testing in a Micronaut application.

Authors: Sergio del Amo

Micronaut Version: 4.6.3

1. Getting Started

In this guide you will learn how to:

2. What you will need

To complete this guide, you will need the following:

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. What we are going to achieve in this guide

We are going to create a sample Micronaut application using Micronaut Data JPA and PostgreSQL.

We will take a look at the common practice of using an H2 in-memory database for testing and understand the downsides of this approach. Then we will learn how we can replace this approach by testing with the same type of database (PostgreSQL in our case) that we use in production using Testcontainers JDBC URL. Finally, we will look into how we can simplify testing with Micronaut Test Resources.

5. Getting Started

6. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide \
    --features=data-jpa,h2 \
    --build=maven \
    --lang=java
If you don’t specify the --build argument, Gradle with the Kotlin DSL is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.
If you don’t specify the --test argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

If you use Micronaut Launch, select Micronaut Application as application type and add null features.

If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Diff

Setup IntelliJ IDEA to develop Micronaut Applications.

7. Create Entity

First let us start with creating a JPA entity Product.java.

h2/src/main/java/example/micronaut/Product.java
package example.micronaut;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity (1)
@Table(name = "products") (2)
public class Product {

    @Id (3)
    private Long id;

    @Column(nullable = false, unique = true)  (4)
    private String code;

    @Column(nullable = false) (4)
    private String name;

    public Product() {}

    public Product(Long id, String code, String name) {
        this.id = id;
        this.code = code;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}
1 Annotate the class with @Table to map the class to the table defined in the schema.
2 Annotate the class with @Entity to specify the class as JPA entity.
3 It specifies the primary key of an entity.
4 Specifies the mapped column for a persistent property or field.

8. Micronaut Data Repository

Let us create a Micronaut Data @Repository interface for the Product entity.

h2/src/main/java/example/micronaut/ProductRepository.java
import io.micronaut.data.annotation.Query;
import io.micronaut.data.annotation.Repository;
import io.micronaut.data.jpa.repository.JpaRepository;

@Repository (1)
interface ProductRepository extends JpaRepository<Product, Long> { (2)

}
1 Annotate with @Repository to allow compile time implementations to be added.
2 By extending JpaRepository, you enable automatic generation of CRUD (Create, Read, Update, Delete) operations and JPA specific methods like merge and flush.

9. Testing with H2 in-memory database

One of the approaches for testing database repositories is using lightweight databases such as H2 or HSQL as in-memory databases while using a different database like PostgreSQL, MySQL or Oracle in production.

The drawbacks of using a different database for testing are:

  • The test database might not support all the features of your production database

  • The SQL query syntax might not be compatible with both in-memory database and your production database.

  • Testing with a different database than what you use for production will not give you complete confidence in your test suite.

But still, in-memory databases like H2 are being predominantly used for testing because of their ease of use.

9.1. Datasource Configuration

To use H2, add the following configuration to define your datasource.

h2/src/main/resources/application.properties
jpa.default.properties.hibernate.hbm2ddl.auto=update
datasources.default.dialect=H2
datasources.default.driver-class-name=org.h2.Driver
datasources.default.url=jdbc\:h2\:mem\:devDb;LOCK_TIMEOUT\=10000;DB_CLOSE_ON_EXIT\=FALSE
datasources.default.username=sa
datasources.default.password=

9.2. H2 Dependency

Add the H2 driver dependency:

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

9.3. Seed Data

We will load the seed data in the tests:

h2/src/test/resources/sql/seed-data.sql
insert into products(id, code, name) values(1, 'p101', 'Apple MacBook Pro');
insert into products(id, code, name) values(2, 'p102', 'Sony TV');

9.4. ProductRepository Test

Let us see how we can write tests for our ProductRepository using H2.

h2/src/test/java/example/micronaut/ProductRepositoryTest.java
package example.micronaut;

import io.micronaut.core.io.ResourceLoader;
import io.micronaut.test.annotation.Sql;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;

@MicronautTest
@Sql(scripts = "classpath:sql/seed-data.sql", phase = Sql.Phase.BEFORE_EACH) (1)
class ProductRepositoryTest {

    @Inject
    Connection connection;

    @Inject
    ResourceLoader resourceLoader;

    @Test
    void shouldGetAllProducts(ProductRepository productRepository) {
        List<Product> products = productRepository.findAll();
        assertEquals(2, products.size());
    }
}
1 Seed the database using the @Sql annotation from micronaut-test.

If you run this test, the application uses the H2 in-memory database.

The test loads the sample data required for our test, calls the repository method, and asserts the expected result.

But the challenge comes when we want to use features supported only by our production database, but not by H2 database.

For example, let us imagine we want to implement a feature where we want to create a new product if a product with a given code does not already exist; otherwise, don’t create a new product.

In PostgreSQL we can implement this using the following query:

INSERT INTO products(id, code, name) VALUES(?,?,?) ON CONFLICT DO NOTHING;

But the same query doesn’t work with H2 by default. When you execute the above query with H2 then you will get the following exception:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO products (id, code, name) VALUES (?, ?, ?) ON[*] CONFLICT DO NOTHING";"

You can run H2 with PostgreSQL compatibility mode to support PostgreSQL syntax but still not all the features are supported by H2.

The inverse scenario is also possible where some query works fine with H2 but not in PostgreSQL. For example, H2 supports the ROWNUM() function where PostgreSQL doesn’t. So even if you write tests for repositories using H2 database there is no guarantee that your code works in the same way with the production database, and you will need to verify after deploying your application which defeats the whole purpose of writing automated tests.

Now, let us see how simple it is to replace the H2 database with a real Postgres database for testing using Testcontainers.

10. ProductRepository

Modify the repository to add a method which uses ON CONFLICT DO NOTHING:

postgresql/src/main/java/example/micronaut/ProductRepository.java
import io.micronaut.data.annotation.Query;
import io.micronaut.data.annotation.Repository;
import io.micronaut.data.jpa.repository.JpaRepository;

@Repository (1)
interface ProductRepository extends JpaRepository<Product, Long> { (2)

    default void createProductIfNotExists(Product product) {
        createProductIfNotExists(product.getId(), product.getCode(), product.getName());
    }

    @Query(
            value = "insert into products(id, code, name) values(:id, :code, :name) ON CONFLICT DO NOTHING",
            nativeQuery = true
    ) (3)
    void createProductIfNotExists(Long id, String code, String name);

}
1 Annotate with @Repository to allow compile time implementations to be added.
2 By extending JpaRepository, you enable automatic generation of CRUD (Create, Read, Update, Delete) operations and JPA specific methods like merge and flush.
3 You can use the @Query annotation to specify an explicit query.

11. Testing using Testcontainers

11.1. PostgreSQL Configuration

Replace the Datasource configuration with the PostgreSQL configuration.

postgresql/src/main/resources/application.properties
jpa.default.properties.hibernate.hbm2ddl.auto=none
datasources.default.db-type=postgres
datasources.default.dialect=POSTGRES
datasources.default.driver-class-name=org.postgresql.Driver

We disable schema generation with jpa.default.properties.hibernate.hbm2ddl.auto=none. We will use a classpath init script with Testcontainers instead to load the following SQL file.

postgresql/src/test/resources/sql/init-db.sql
CREATE TABLE IF NOT EXISTS products
(
    id   int          not null,
    code varchar(255) not null,
    name varchar(255) not null,
    primary key (id),
    unique (code)
    );

11.2. PostgreSQL Driver dependency

Remove H2 Dependency and add the PostgreSQL driver dependency instead:

pom.xml
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

11.3. Testcontainers Dependencies

Add the Testcontainers dependencies:

pom.xml
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers</artifactId>
    <scope>test</scope>
</dependency>
pom.xml
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

11.4. Testcontainers JDBC URL

Use Testcontainers special JDBC URL as the data source URL in the test.

postgresql/src/test/java/example/micronaut/ProductRepositoryWithJdbcUrlTest.java
package example.micronaut;

import io.micronaut.context.annotation.Property;
import io.micronaut.core.io.ResourceLoader;
import io.micronaut.test.annotation.Sql;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;

@MicronautTest(startApplication = false) (1)
@Property(name = "datasources.default.driver-class-name",
        value = "org.testcontainers.jdbc.ContainerDatabaseDriver") (2)
@Property(name = "datasources.default.url",
        value = "jdbc:tc:postgresql:15.2-alpine:///db?TC_INITSCRIPT=sql/init-db.sql") (3)
@Sql(scripts = "classpath:sql/seed-data.sql", phase = Sql.Phase.BEFORE_EACH) (4)
class ProductRepositoryWithJdbcUrlTest {

    @Inject
    Connection connection;

    @Inject
    ResourceLoader resourceLoader;

    @Test
    void shouldGetAllProducts(ProductRepository productRepository) {
        List<Product> products = productRepository.findAll();
        assertEquals(2, products.size());
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. By default, each @Test method will be wrapped in a transaction that will be rolled back when the test finishes. This behaviour is is changed by setting transaction to false.
2 Annotate the class with @Property to supply the driver class name configuration to the test.
3 Annotate the class with @Property to supply the datasource url which we supply via the Testcontainers special JDBC URL.
4 Seed the database using the @Sql annotation from micronaut-test.

Now if you run the test, you can see in the console logs that our test is using a PostgreSQL database instead of the H2 in-memory database. It is as simple as that!

Let us understand how this test works.

If we have Testcontainers and the appropriate JDBC driver on the classpath, we can simply use the special JDBC connection URLs to get a fresh containerized instance of the database each time the application starts up.

The actual PostgreSQL JDBC URL looks like: jdbc:postgresql://localhost:5432/postgres

To get the special JDBC URL, insert tc: after jdbc: as follows. (Note that the hostname, port and database name will be ignored; so you can leave these as-is or set them to any value.)

jdbc:tc:postgresql:///db

We can also indicate which version of PostgreSQL database to use by specifying the Docker image tag after postgresql as follows:

jdbc:tc:postgresql:15.2-alpine:///db

Here we have appended the tag 15.2-alpine to postgresql so that our test will use a PostgreSQL container created from postgres:15.2-alpine image.

You can also initialize the database using a SQL script by passing TC_INITSCRIPT parameter as follows:

jdbc:tc:postgresql:15.2-alpine:///db?TC_INITSCRIPT=sql/init-db.sql

Testcontainers will automatically execute the SQL script that was specified using the TC_INITSCRIPT parameter. However, ideally you should be using a proper database migration tool like Flyway or Liquibase.

The special JDBC URL also works for other databases such as MySQL, PostGIS, YugabyteDB, CockroachDB etc.

11.5. Initializing the database container using Testcontainers and JUnit

If using special JDBC URL doesn’t meet your needs, or you need more control over the container creation, then you can use the JUnit 5 Testcontainers Extension as follows:

postgresql/src/test/java/example/micronaut/ProductRepositoryTest.java
package example.micronaut;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.io.ResourceLoader;
import io.micronaut.test.annotation.Sql;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import io.micronaut.test.support.TestPropertyProvider;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.utility.MountableFile;

import java.sql.Connection;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

@MicronautTest(startApplication = false) (1)
@Testcontainers(disabledWithoutDocker = true) (2)
@TestInstance(TestInstance.Lifecycle.PER_CLASS) (3)
@Sql(scripts = "classpath:sql/seed-data.sql", phase = Sql.Phase.BEFORE_EACH) (4)
class ProductRepositoryTest implements TestPropertyProvider {  (5)

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(
            "postgres:15.2-alpine"
    ).withCopyFileToContainer(MountableFile.forClasspathResource("sql/init-db.sql"), "/docker-entrypoint-initdb.d/init-db.sql");

    @Override
    public @NonNull Map<String, String> getProperties() { (5)
        if (!postgres.isRunning()) {
            postgres.start();
        }
        return Map.of("datasources.default.driver-class-name", "org.postgresql.Driver",
                "datasources.default.url", postgres.getJdbcUrl(),
                "datasources.default.username", postgres.getUsername(),
                "datasources.default.password", postgres.getPassword());
    }

    @Inject
    Connection connection;

    @Inject
    ResourceLoader resourceLoader;

    @Inject
    ProductRepository productRepository;

    @Test
    void shouldGetAllProducts() {
        List<Product> products = productRepository.findAll();
        assertEquals(2, products.size());
    }

    @Test
    void shouldNotCreateAProductWithDuplicateCode() {
        Product product = new Product(3L, "p101", "Test Product");
        productRepository.createProductIfNotExists(product);
        Optional<Product> optionalProduct = productRepository.findById(product.getId());
        assertTrue(optionalProduct.isEmpty());
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. By default, each @Test method will be wrapped in a transaction that will be rolled back when the test finishes. This behaviour is is changed by setting transaction to false.
2 Disable test if Docker not present.
3 Classes that implement TestPropertyProvider must use this annotation to create a single class instance for all tests (not necessary in Spock tests).
4 Seed the database using the @Sql annotation from micronaut-test.
5 When you need dynamic properties definition, implement the TestPropertyProvider interface. Override the method .getProperties() and return the properties you want to expose to the application.

We have used the Testcontainers JUnit 5 extension annotations @Testcontainers and @Container to start PostgreSQLContainer and register the data source properties for the Test using the dynamic property registration through the TestPropertyProvider API.

12. Testing with Test Resources

We are going to simplify testing with Micronaut Test Resources:

Micronaut Test Resources adds support for managing external resources which are required during development or testing.

12.1. Removing Testcontainers Dependencies

Remove the Testcontainers dependencies from your build files.

12.2. Configure Test Resources

Integerate your Maven Build with Test Resources

You can enable test resources support simply by setting the property micronaut.test.resources.enabled (either in your POM or via the command-line).

13. Test Resources

When the application is started locally — either under test or by running the application — resolution of the datasource URL is detected and the Test Resources service will start a local PostgreSQL docker container, and inject the properties required to use this as the datasource.

For more information, see the JDBC section of the Test Resources documentation.

13.1. Simpler Test with Test Resources

Thanks to Test Resources, we can simplify the test as follows:

postgresqltestresources/src/test/java/example/micronaut/ProductRepositoryTest.java
package example.micronaut;

import io.micronaut.core.io.ResourceLoader;
import io.micronaut.test.annotation.Sql;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.util.List;
import java.util.Optional;

import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

@MicronautTest(startApplication = false) (1)
@Sql(scripts = {"classpath:sql/init-db.sql", "classpath:sql/seed-data.sql"},
        phase = Sql.Phase.BEFORE_EACH) (2)
class ProductRepositoryTest {

    @Inject
    Connection connection;

    @Inject
    ResourceLoader resourceLoader;

    @Inject
    ProductRepository productRepository;

    @Test
    void shouldGetAllProducts() {
        List<Product> products = productRepository.findAll();
        assertEquals(2, products.size());
    }

    @Test
    void shouldNotCreateAProductWithDuplicateCode() {
        Product product = new Product(3L, "p101", "Test Product");
        assertDoesNotThrow(() -> productRepository.createProductIfNotExists(product));
        Optional<Product> optionalProduct = productRepository.findById(product.getId());
        assertTrue(optionalProduct.isEmpty());
    }

}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. By default, each @Test method will be wrapped in a transaction that will be rolled back when the test finishes. This behaviour is is changed by setting transaction to false.
2 Seed the database using the @Sql annotation from micronaut-test.

If you run the test, you will see a PostgreSQL container being started by Test Resources through integration with Testcontainers to provide throwaway containers for testing.

13.2. Micronaut Test Resources Goals

Micronaut Test Resources aims to achieve the following goals:

  • zero-configuration: without adding any configuration, test resources should be spawned and the application configured to use them. Configuration is only required for advanced use cases.

  • classpath isolation: use of test resources shouldn’t leak into your application classpath, nor your test classpath

  • compatible with GraalVM native: if you build a native binary, or run tests in native mode, test resources should be available

  • easy to use: the Micronaut build plugins for Gradle and Maven should handle the complexity of figuring out the dependencies for you

  • extensible: you can implement your own test resources, in case the built-in ones do not cover your use case

  • technology agnostic: while lots of test resources use Testcontainers under the hood, you can use any other technology to create resources

14. Micronaut Data JDBC

Micronaut Data JDBC goes one step further, you have to specify the dialect in the JdbcRepository annotation. Micronaut Data JDBC pre-computes native SQL queries for the specified dialect, providing a repository implementation that is a simple data mapper between a native result set and an entity.

A Micronaut JDBC repository for this sample application would look like:

jdbc/src/main/java/example/micronaut/ProductRepository.java
package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

@JdbcRepository(dialect = Dialect.POSTGRES) (1)
interface ProductRepository extends CrudRepository<Product, Long> { (2)

    default void createProductIfNotExists(Product product) {
        createProductIfNotExists(product.getId(), product.getCode(), product.getName());
    }

    @Query(
            value = "insert into products(id, code, name) values(:id, :code, :name) ON CONFLICT DO NOTHING",
            nativeQuery = true
    ) (3)
    void createProductIfNotExists(Long id, String code, String name);
}
1 @JdbcRepository with a specific dialect.
2 By extending CrudRepository you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.
3 You can use the @Query annotation to specify an explicit query.

15. Summary

We have looked into how to test Micronaut Data JPA repositories using H2 in-memory database and talked about the drawbacks of using different (in-memory) databases for testing while using a different type of database in production.

Then we learned about how simply we can replace H2 database with a real database for testing using Testcontainers special JDBC URL. We also looked at using Testcontainers JUnit 5 extension annotations to spin up the database for testing which gives more control over the lifecycle of the database container.

We learned that Micronaut Test Resources streamlines testing with throwaway containers through its integration with Testcontainers.

16. Further Reading

17. Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

18. License

All guides are released with an Apache license 2.0 license for the code and a Creative Commons Attribution 4.0 license for the writing and media (images…​).