Replace H2 with a real database for testing

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 .

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.

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;
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) 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 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= 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()); 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. 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); By extending JpaRepository, you enable automatic generation of CRUD (Create, Read, Update, Delete) operations and JPA specific methods like merge and flush. You can use the @Query annotation to specify an explicit query. 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> <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()); 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. Annotate the class with @Property to supply the driver class name configuration to the test. Annotate the class with @Property to supply the datasource url which we supply via the Testcontainers special JDBC URL. 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()); 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. Disable test if Docker not present. Classes that implement TestPropertyProvider must use this annotation to create a single class instance for all tests (not necessary in Spock tests). Seed the database using the @Sql annotation from micronaut-test. 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. 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()); 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. Seed the database using the @Sql annotation from micronaut-test. 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 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); By extending CrudRepository you enable automatic generation of CRUD (Create, Read, Update, Delete) operations. You can use the @Query annotation to specify an explicit query. 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.