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.

1. Getting Started

In this guide, we will create a Python application built with Pyronaut.

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:

Some time on your hands
GraalPy installed and the Pyronaut CLI available locally
Docker installed to run PostgreSQL and to run tests using Testcontainers.

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.

Download and unzip the source

4. Writing the Application

Create an application using the Pyronaut CLI (Command Line Interface) or Pyronaut Launch

pyronaut create example.micronaut.micronautguide --features=data-jdbc,liquibase,postgres,serialization-jackson,testcontainers

The data-jdbc and postgres features add Micronaut Data JDBC and the PostgreSQL JDBC driver to the dependencies in pyproject.toml.

4.1. Data Source configuration

Define the datasource in config/application.toml.

config/application.toml

[datasources.default]
(1)
driver-class-name = "org.postgresql.Driver"
(2)
db-type = "postgres"
(3)
dialect = "POSTGRES"

1	Use the PostgreSQL driver.
2	Set the database type.
3	Configure the PostgreSQL dialect.

4.2. Database Migration with Liquibase

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

The liquibase feature adds Micronaut Liquibase to the dependencies in pyproject.toml.

Configure the database migrations directory for Liquibase in config/application.toml:

config/application.toml

[liquibase.datasources.default]
change-log = "classpath:db/liquibase-changelog.xml"

Create the following files with the database schema creation:

config/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>

config/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.3. Entity

Create a Book entity:

src/example/micronaut/book.py

from dataclasses import dataclass
from typing import Annotated

from micronaut.data.annotation import Id, MappedEntity
from micronaut.serde.annotation import Serdeable


@Serdeable  (1)
@MappedEntity  (2)
@dataclass
class Book:
    id: Annotated[int, Id]  (3)
    title: str
    pages: int | None = None

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

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.4. Repository

Create a repository and declare an upsert method:

src/example/micronaut/book_repository.py

from micronaut.data.jdbc.annotation import JdbcRepository
from micronaut.data.model.query.builder.sql import Dialect
from micronaut.data.repository import CrudRepository

from .book import Book


@JdbcRepository(dialect=Dialect.POSTGRES)  (1)
class BookRepository(CrudRepository[Book, int]):  (2)

    def findByTitle(self, title: str) -> Book | None: ...

    def upsert(self, book: Book) -> None: ...  (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.5. Controller

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

src/example/micronaut/book_controller.py

from typing import Annotated

from micronaut.http import HttpStatus
from micronaut.http.annotation import Body, Controller, Post, Put, Status
from micronaut.scheduling import TaskExecutors
from micronaut.scheduling.annotation import ExecuteOn

from .book import Book
from .book_repository import BookRepository


@ExecuteOn(TaskExecutors.BLOCKING)
@Controller("/books")  (1)
class BookController:

    def __init__(self, book_repository: BookRepository):  (2)
        self.book_repository = book_repository

    @Post  (3)
    @Status(HttpStatus.CREATED)  (4)
    def save(self, book: Annotated[Book, Body]) -> None:  (5)
        self.book_repository.upsert(book)  (6)

    @Put  (7)
    @Status(HttpStatus.OK)
    def update(self, book: Annotated[Book, Body]) -> None:
        self.book_repository.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`.

The controller is annotated with @ExecuteOn(TaskExecutors.BLOCKING) so the blocking database calls run on the blocking executor instead of the event loop.

4.6. Tests

Create tests/conftest.py with a fixture that starts a PostgreSQL container with Testcontainers and exposes the datasource properties:

tests/conftest.py

import pytest
from org.testcontainers.containers import PostgreSQLContainer


@pytest.fixture(scope="session")
def datasource():
    container = PostgreSQLContainer("postgres:15.3-alpine")
    container.start()  (1)
    try:
        yield {  (2)
            "datasources.default.driver-class-name": "org.postgresql.Driver",
            "datasources.default.url": str(container.getJdbcUrl()),
            "datasources.default.username": str(container.getUsername()),
            "datasources.default.password": str(container.getPassword()),
        }
    finally:
        container.stop()

1	Start the container once; it is shared by every test.
2	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:

tests/example/micronaut/test_book_controller.py

import pytest
import requests

from pyronaut.test import MicronautTest, micronaut_test_fixture

PATH = "/books"
TITLE = "Netty in Action"


@pytest.fixture
def my_context(request, datasource):
    fixture = micronaut_test_fixture(
        request,
        MicronautTest(transactional=False, properties=datasource),  (1)
    )
    yield fixture
    fixture.stop()


@pytest.fixture
def client(my_context):
    return requests.with_context(my_context)  (2)


@pytest.fixture
def repository(my_context):
    return my_context["example.micronaut.BookRepository"]  (3)


def test_book_upsert(client, repository):
    assert repository.findByTitle(TITLE) is None  (4)

    response = client.post(PATH, json={"id": 123, "title": TITLE})
    assert response.status_code == 201  (5)
    assert repository.findByTitle(TITLE) is not None
    assert repository.findByTitle(TITLE).pages is None

    response = client.put(PATH, json={"id": 123, "title": TITLE, "pages": 265})
    assert response.status_code == 200  (6)
    assert repository.findByTitle(TITLE).pages == 265  (7)

1	Start the application with the datasource properties of the database started by the `datasource` fixture.
2	Send HTTP requests to the application started by the fixture.
3	Retrieve the `BookRepository` bean to verify the database state.
4	Initially, the book does not exist.
5	The first upsert inserts the book without pages.
6	The second upsert, with the same `id`, updates the existing row instead of failing with a duplicate key error.
7	The book now has pages.

5. Testing the Application

To run the tests:

pyronaut install
pyronaut validate-config
pyronaut test

6. Next Steps

Explore more features with Pyronaut Guides.

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

7. 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).

Table of Contents

Upsert PostgreSQL

1. Getting Started

2. What you will need

3. Solution

4. Writing the Application

4.1. Data Source configuration

4.2. Database Migration with Liquibase

4.3. Entity

4.4. Repository

4.5. Controller

4.6. Tests

5. Testing the Application

6. Next Steps

7. License