Table of Contents

Upsert PostgreSQL

An upsert is a database operation that will update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn’t already exist.

Authors: Sergio del Amo

Micronaut Version: 5.2.3

1. Getting Started

In this guide, we will create a Micronaut application written in Java.

An upsert is a native database operation that inserts a row when no matching row exists, or updates the existing row when a match is found. Doing this in a single statement, instead of a "find, then insert or update" sequence, avoids a race condition: two concurrent requests cannot both decide the row is missing and both attempt an insert.

In this guide, you will declare an upsert method in a Micronaut Data JDBC repository and use it from an HTTP controller backed by PostgreSQL.

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. Writing the Application

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

mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,liquibase,postgres,serialization-jackson,testcontainers \
    --build=maven \
    --lang=java \
    --test=junit
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 data-jdbc, liquibase, postgres, serialization-jackson, and testcontainers 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.

4.1. Micronaut Data JDBC

Add Micronaut Data JDBC dependencies to the project:

pom.xml
<!-- Add the following to your annotationProcessorPaths element -->
<path>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-processor</artifactId>
</path>
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
    <scope>compile</scope>
</dependency>
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-hikari</artifactId>
    <scope>compile</scope>
</dependency>

4.2. PostgreSQL Driver

Add also the PostgreSQL Driver

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

4.3. Data Source configuration

Define the datasource in src/main/resources/application.properties.

src/main/resources/application.properties
(1)
datasources.default.driver-class-name=org.postgresql.Driver
(2)
datasources.default.db-type=postgres
(3)
datasources.default.dialect=POSTGRES
1 Use the PostgreSQL driver.
2 Set the database type.
3 Configure the PostgreSQL dialect.

4.4. Database Migration with Liquibase

We need a way to create the database schema. For that, we use Micronaut integration with Liquibase.

Add the following snippet to include the necessary dependencies:

pom.xml
<dependency>
    <groupId>io.micronaut.liquibase</groupId>
    <artifactId>micronaut-liquibase</artifactId>
    <scope>compile</scope>
</dependency>

Configure the database migrations directory for Liquibase in application.properties.

src/main/resources/application.properties
liquibase.datasources.default.change-log=classpath\:db/liquibase-changelog.xml

Create the following files with the database schema creation:

src/main/resources/db/liquibase-changelog.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <include file="changelog/01-schema.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
src/main/resources/db/changelog/01-schema.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <changeSet id="01" author="username">
    <createTable tableName="book">
      <column name="id" type="BIGINT">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="title" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="pages" type="INT">
        <constraints nullable="true"/>
      </column>
    </createTable>
  </changeSet>
</databaseChangeLog>

The id column is the table’s primary key. PostgreSQL requires a primary key or unique constraint that matches the conflict target of an upsert.

4.5. Entity

Create a Book entity:

src/main/java/example/micronaut/Book.java
package example.micronaut;

import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.serde.annotation.Serdeable;
import org.jspecify.annotations.Nullable;

@Serdeable (1)
@MappedEntity (2)
public record Book(
        @Id @Nullable Long id,  (3) (4)
        String title,
        @Nullable Integer pages) {
}
1 Declare the @Serdeable annotation at the type level in your source code to allow the type to be serialized or deserialized.
2 Annotate the class with @MappedEntity to map the class to the table defined in the schema.
3 Specifies the ID of an entity
4 Since Records have immutable constructor arguments, those arguments need to be marked as @Nullable, and you should pass null for those arguments.
The id is not annotated with @GeneratedValue. By default, Micronaut Data uses the entity identity as the upsert conflict target, so the caller must supply the identity value. Upserts that rely on the default identity conflict target reject entities with generated identities.

4.6. Repository

Create a repository and declare an upsert method:

src/main/java/example/micronaut/BookRepository.java
package example.micronaut;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;
import org.jspecify.annotations.Nullable;

@JdbcRepository(dialect = Dialect.POSTGRES) (1)
public interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Nullable
    Book findByTitle(String title);

    void upsert(Book book); (3)
}
1 @JdbcRepository with a specific dialect.
2 By extending CrudRepository you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.
3 Upsert methods are opt-in; they are not inherited from CrudRepository. Micronaut Data implements a method named exactly upsert (or upsertAll for an Iterable of entities) as a native upsert.

For the POSTGRES dialect, Micronaut Data generates an INSERT …​ ON CONFLICT …​ DO UPDATE statement at compilation time. For the Book entity, the generated SQL is similar to:

INSERT INTO "book" ("id","title","pages") VALUES (?,?,?)
ON CONFLICT ("id") DO UPDATE SET "title"=EXCLUDED."title","pages"=EXCLUDED."pages"

If no row with the given id exists, PostgreSQL inserts a new row. Otherwise, it updates the title and pages columns of the existing row with the values proposed for insertion, which PostgreSQL exposes through the special EXCLUDED table.

Use the @Upsert annotation to give an upsert method a custom name, or its conflictsOn member to use another unique property, such as an ISBN, as the conflict target instead of the identity.

4.7. Controller

Create a controller which uses the upsert method both to create and to update a book:

src/main/java/example/micronaut/BookController.java
package example.micronaut;

import io.micronaut.http.HttpStatus;
import io.micronaut.http.annotation.Body;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Post;
import io.micronaut.http.annotation.Put;
import io.micronaut.http.annotation.Status;

@Controller("/books") (1)
class BookController {
    private final BookRepository bookRepository;

    BookController(BookRepository bookRepository) { (2)
        this.bookRepository = bookRepository;
    }

    @Post (3)
    @Status(HttpStatus.CREATED) (4)
    void save(@Body Book book) { (5)
        bookRepository.upsert(book); (6)
    }

    @Put (7)
    @Status(HttpStatus.OK)
    void update(@Body Book book) {
        bookRepository.upsert(book); (6)
    }
}
1 The class is defined as a controller with the @Controller annotation mapped to the path /books.
2 Use constructor injection to inject a bean of type BookRepository.
3 The @Post annotation maps the save method to an HTTP POST request on /books.
4 You can return void in your controller’s method and specify the HTTP status code via the @Status annotation.
5 The @Body annotation indicates that the request body should be parsed by Micronaut Framework and bound to the annotated parameter.
6 The same repository method inserts or updates the row, depending on whether a book with the supplied id already exists.
7 The @Put annotation maps the update method to an HTTP PUT request on /books.

4.8. Tests

Create an abstract test class that starts a PostgreSQL container with Testcontainers and points the default datasource to it:

src/test/java/example/micronaut/AbstractTest.java
package example.micronaut;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.test.support.TestPropertyProvider;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;

import java.util.Map;

public abstract class AbstractTest implements TestPropertyProvider { (1)

    @Container (2)
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15.3-alpine");

    @Override
    public @NonNull Map<String, String> getProperties() {
        if (!postgres.isRunning()) {
            postgres.start(); (3)
        }
        return Map.of("datasources.default.driver-class-name", "org.postgresql.Driver", (4)
                "datasources.default.url", postgres.getJdbcUrl(),
                "datasources.default.username", postgres.getUsername(),
                "datasources.default.password", postgres.getPassword());
    }
}
1 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.
2 Shared containers are defined as static fields in a top level test class and have to be annotated with @Container.
3 Start the container if it is not running yet.
4 Use the PostgreSQL JDBC driver and the container’s JDBC URL and credentials.

Create a test which sends a POST request to create a book and then a PUT request with the same id to update it:

src/test/java/example/micronaut/BookControllerTest.java
package example.micronaut;

import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.client.BlockingHttpClient;
import io.micronaut.http.client.HttpClient;
import io.micronaut.http.client.annotation.Client;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;

import java.util.Map;

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

@TestInstance(TestInstance.Lifecycle.PER_CLASS) (1)
@MicronautTest (2)
class BookControllerTest extends AbstractTest { (3)

    @Test
    void bookUpsert(BookRepository repository, (4)
                    @Client("/") HttpClient httpClient) { (5)
        BlockingHttpClient client = httpClient.toBlocking();
        String path = "/books";
        String title = "Netty in Action";
        assertNull(repository.findByTitle(title)); (6)
        HttpRequest<?> postRequest = HttpRequest.POST(path, Map.of("id", 123L, "title", title));
        HttpResponse<?> postResponse = assertDoesNotThrow(() -> client.exchange(postRequest));
        assertEquals(HttpStatus.CREATED, postResponse.getStatus()); (7)
        assertNotNull(repository.findByTitle(title));
        assertNull(repository.findByTitle(title).pages());

        HttpRequest<?> putRequest = HttpRequest.PUT(path, Map.of("id", 123L, "title", title, "pages", 265));
        HttpResponse<?> putResponse = assertDoesNotThrow(() -> client.exchange(putRequest));
        assertEquals(HttpStatus.OK, putResponse.getStatus()); (8)
        assertNotNull(repository.findByTitle(title).pages()); (9)
    }

}
1 Classes that implement TestPropertyProvider must use this annotation to create a single class instance for all tests (not necessary in Spock tests).
2 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. More info.
3 Extend AbstractTest to run the test against a database started by Testcontainers.
4 By default, with JUnit 5 the test method parameters will be resolved to beans if possible.
5 Inject the HttpClient bean and point it to the embedded server.
6 Initially, the book does not exist.
7 The first upsert inserts the book without pages.
8 The second upsert, with the same id, updates the existing row instead of failing with a duplicate key error.
9 The book now has pages.

5. Testing the Application

To run the tests:

./mvnw test

6. Native Tests

The Maven plugin for GraalVM Native Image building allows you to run native tests.

This plugin supports running tests on the JUnit Platform as native images. This means that tests will be compiled and executed as native code.

First, add the following profile to pom.xml:

 <profile>
      <id>native</id>
      <build>
        <plugins>
          <plugin>
            <groupId>org.graalvm.buildtools</groupId>
            <artifactId>native-maven-plugin</artifactId>
            <extensions>true</extensions>
            <executions>
              <execution>
                <id>test-native</id>
                <goals>
                  <goal>test</goal>
                </goals>
                <phase>test</phase>
              </execution>
            </executions>
          </plugin>
        </plugins>
      </build>
    </profile>

Then, to execute the native tests, execute:

./mvnw -Pnative test

INFO: A test may be disabled within a GraalVM native image via the @DisabledInNativeImage annotation.

7. Next Steps

Explore more features with Micronaut Guides.

Read more about Upserts in the Micronaut Data documentation, including conflict targets, batch upserts, supported dialects, and lifecycle event behavior.

8. License

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