Groovy / Maven

Access a database with Micronaut Data R2DBC

Learn how to access a database with Micronaut R2DBC repositories.

Graeme Rocher
On this guide
In this section

Getting Started

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

The application exposes some REST endpoints and stores data in a MySQL database using Micronaut Data R2DBC.

What is R2DBC?

The Reactive Relational Database Connectivity (R2DBC) project brings reactive programming APIs to relational databases.

What you will need

To complete this guide, you will need the following:

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.

Writing the Application

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

mn create-app example.micronaut.micronautguide \
    --features=data-r2dbc,flyway,mysql,test-resources,jdbc-hikari,serialization-jackson \
    --build=maven \
    --lang=groovy \
    --test=spock
Note
 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-r2dbc, flyway, mysql, test-resources, jdbc-hikari, and serialization-jackson features.

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

Data Source configuration

Define the R2DBC and the JDBC datasource in src/main/resources/application.properties (the latter is needed for Flyway migrations).

src/main/resources/application.properties
r2dbc.datasources.default.dialect=MYSQL
datasources.default.dialect=MYSQL
Note
 Only the dialect is defined. The remainder of the values (including the database URL etc.) will automatically be populated by the Test Resources integration, which uses Testcontainers.

When deploying to production, the datasource connection properties and r2dbc connection properties can be specified externally (using environment variables for example).

Database Migration with Flyway

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

Flyway automates schema changes, significantly simplifying schema management tasks, such as migrating, rolling back, and reproducing in multiple environments.

Add the following snippet to include the necessary dependencies:

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

We will enable Flyway in the Micronaut configuration file and configure it to perform migrations on one of the defined data sources.

src/main/resources/application.properties
flyway.datasources.default.enabled=true
1 Enable Flyway for the default datasource.
Note
 Configuring multiple data sources is as simple as enabling Flyway for each one. You can also specify directories that will be used for migrating each data source. Review the Micronaut Flyway documentation for additional details.

Flyway migration will be automatically triggered before your Micronaut application starts. Flyway will read migration commands in the resources/db/migration/ directory, execute them if necessary, and verify that the configured data source is consistent with them.

Create the following migration files with the database schema creation:

src/main/resources/db/migration/V1__schema.sql
DROP TABLE IF EXISTS genre;

CREATE TABLE genre (
    id   BIGINT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
   name  VARCHAR(255) NOT NULL UNIQUE
);

During application startup, Flyway will execute the SQL file and create the schema needed for the application.

Domain

Create the domain entity:

groovy/src/main/groovy/example/micronaut/domain/Genre.groovy
package example.micronaut.domain

import groovy.transform.CompileStatic
import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.serde.annotation.Serdeable
import jakarta.validation.constraints.NotBlank

@Serdeable
@CompileStatic
@MappedEntity
class Genre {

    @Id
    @GeneratedValue(GeneratedValue.Type.AUTO)
    Long id

    @NotBlank
    String name

    String toString() {
        "Genre{id=$id, name='$name'}"
    }
}
Tip
 You could use a subset of supported JPA annotations instead by including the following compileOnly scoped dependency: jakarta.persistence:jakarta.persistence-api.

Repository Access

Next, create a repository interface to define the operations to access the database. Micronaut Data will implement the interface at compilation time:

groovy/src/main/groovy/example/micronaut/GenreRepository.groovy
package example.micronaut

import jakarta.transaction.Transactional
import jakarta.validation.constraints.NotBlank

import example.micronaut.domain.Genre
import io.micronaut.data.annotation.Id
import io.micronaut.data.exceptions.DataAccessException
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.r2dbc.annotation.R2dbcRepository
import io.micronaut.data.repository.reactive.ReactorPageableRepository
import reactor.core.publisher.Mono

@R2dbcRepository(dialect = Dialect.MYSQL) // 
interface GenreRepository extends ReactorPageableRepository<Genre, Long> { // 

    Mono<Genre> save(@NotBlank String name);

    @Transactional
    default Mono<Genre> saveWithException(@NotBlank String name) {
        return save(name)
                .then(Mono.error(new DataAccessException("test exception")))
    }

    Mono<Long> update(@Id long id, @NotBlank String name);
}
1 @R2dbcRepository with a specific dialect.
2 Genre, the entity to treat as the root entity for the purposes of querying, is established either from the method signature or from the generic type parameter specified to the GenericRepository interface.

The repository extends from ReactorPageableRepository. It inherits the hierarchy ReactorPageableRepository → ReactorCrudRepository → ReactiveStreamsCrudRepository → GenericRepository.

Repository Description

ReactorPageableRepository

A repository that supports pagination. It provides findAll(Pageable) and findAll(Sort).

ReactorCrudRepository

A repository interface for performing CRUD (Create, Read, Update, Delete). It provides methods such as findAll(), save(Genre), deleteById(Long), and findById(Long).

GenericRepository

A root interface that features no methods but defines the entity type and ID type as generic arguments.

Controller

Micronaut validation is built on the standard framework – JSR 380, also known as Bean Validation 2.0. Micronaut Validation has built-in support for validation of beans that are annotated with jakarta.validation annotations.

To use Micronaut Validation, you need the following dependencies:

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

Alternatively, you can use Micronaut Hibernate Validator, which uses Hibernate Validator; a reference implementation of the validation API.

Create a class to encapsulate the update operations:

groovy/src/main/groovy/example/micronaut/GenreUpdateCommand.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.serde.annotation.Serdeable

import jakarta.validation.constraints.NotBlank

@CompileStatic
@Serdeable // 
class GenreUpdateCommand {

    final long id

    @NotBlank
    final String name

    GenreUpdateCommand(long id, String name) {
        this.id = id
        this.name = name
    }
}
1 Declare the @Serdeable annotation at the type level in your source code to allow the type to be serialized or deserialized.

Create GenreController, a controller that exposes a resource with the common CRUD operations:

groovy/src/main/groovy/example/micronaut/GenreController.groovy
package example.micronaut

import io.micronaut.core.annotation.NonNull
import example.micronaut.domain.Genre
import io.micronaut.data.model.Page
import io.micronaut.data.model.Pageable
import io.micronaut.http.HttpHeaders
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import io.micronaut.http.MutableHttpResponse
import io.micronaut.http.annotation.Body
import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Delete
import io.micronaut.http.annotation.Get
import io.micronaut.http.annotation.Post
import io.micronaut.http.annotation.Put
import io.micronaut.http.annotation.Status
import reactor.core.publisher.Mono

import jakarta.validation.Valid
import jakarta.validation.constraints.NotBlank

@Controller("/genres")  // 
class GenreController {

    protected final GenreRepository genreRepository

    GenreController(GenreRepository genreRepository) { // 
        this.genreRepository = genreRepository
    }

    @Get("/{id}") // 
    Mono<Genre> show(long id) {
        return genreRepository
                .findById(id) // 
    }

    @Put // 
    Mono<HttpResponse<?>> update(@Body @Valid GenreUpdateCommand command) { // 
        return genreRepository.update(command.id, command.name)
                .thenReturn(HttpResponse
                        .noContent()
                        .header(HttpHeaders.LOCATION, location(command.id).path))  // 
    }

    @Get("/list") // 
    Mono<List<Genre>> list(@Valid Pageable pageable) { // 
        return genreRepository.findAll(pageable)
                .map(Page::getContent)
    }

    @Post // 
    Mono<HttpResponse<Genre>> save(@Body("name") @NotBlank String name) {
        return genreRepository.save(name)
                .map(GenreController::createdGenre)
    }

    @Post("/ex") // 
    Mono<MutableHttpResponse<Genre>> saveExceptions(@Body @NotBlank String name) {
        return genreRepository.saveWithException(name)
                .map(GenreController::createdGenre)
                .onErrorReturn(HttpResponse.noContent())
    }

    @Delete("/{id}") // 
    @Status(HttpStatus.NO_CONTENT)
    Mono<Void> delete(long id) {
        return genreRepository.deleteById(id)
                .then()
    }

    @NonNull
    private static MutableHttpResponse<Genre> createdGenre(@NonNull Genre genre) {
        return HttpResponse
                .created(genre)
                .headers(headers -> headers.location(location(genre.getId())));
    }

    protected static URI location(Long id) {
        return URI.create("/genres/$id")
    }

    protected static URI location(Genre genre) {
        return location(genre.id)
    }
}
1 The class is defined as a controller with the @Controller annotation mapped to the path /genres.
2 Use constructor injection to inject a bean of type GenreRepository.
3 Maps a GET request to /genres/{id}, which attempts to show a genre. This illustrates the use of a URL path variable.
4 Returning an empty Reactor Mono when the genre doesn’t exist makes the Micronaut framework respond with 404 (not found).
5 Maps a PUT request to /genres, which attempts to update a genre.
6 Adds @Valid to any method parameter that requires validation. Use a POJO supplied as a JSON payload in the request to populate the command.
7 The Mono.thenReturn(..) method is used to return a NO_CONTENT response only if the update was successful. Headers are easily added by the HttpResponse API.
8 Maps a GET request to /genres/list, which returns a list of genres.
9 You can bind Pageable as a controller method argument. Check the examples in the following test section and read the Pageable configuration options. For example, you can configure the default page size with the configuration property micronaut.data.pageable.default-page-size.
10 Maps a POST request to /genres, which attempts to save a genre. This example uses the Mono.map(..) method to obtain the result of the call to save(String name) and map it to a HTTP CREATED response if the save operation was successful.
11 Maps a POST request to /ex, which generates an exception. This example demonstrates how to handle exceptions with Mono.onErrorReturn(..), which simply returns a NO_CONTENT response if an error occurs. More complex use cases (like logging the exception) can be handled with Mono.onErrorResume(..).
12 Maps a DELETE request to /genres/{id}, which attempts to remove a genre. This illustrates the use of a URL path variable. The Mono.then() method is called to discard the result and return an empty Mono only if the call to deleteById is successful.

Writing Tests

Create a test to verify the CRUD operations:

groovy/src/test/groovy/example/micronaut/GenreControllerSpec.groovy
package example.micronaut

import example.micronaut.domain.Genre
import io.micronaut.core.type.Argument
import io.micronaut.http.HttpHeaders
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import io.micronaut.http.client.HttpClient
import io.micronaut.http.client.annotation.Client
import io.micronaut.http.client.exceptions.HttpClientResponseException
import io.micronaut.http.uri.UriBuilder
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import jakarta.inject.Inject
import spock.lang.Specification
import spock.lang.Unroll

@MicronautTest(transactional = false) //
class GenreControllerSpec extends Specification {

    @Inject
    @Client("/")
    HttpClient client; // 

    void "find non-existing genre should return 404"() {
        when:
        client.toBlocking().exchange(HttpRequest.GET('/genres/99'))

        then:
        HttpClientResponseException exception = thrown()

        exception.response
        exception.status == HttpStatus.NOT_FOUND
    }

    @Unroll
    void "genre CRUD operations"() {
        given: "verify post/create operation - adding #entries.value"
        HttpRequest<?> request = HttpRequest.POST('/genres', [name: 'DevOps']) // 

        when:
        HttpResponse<?> response = client.toBlocking().exchange(request)

        then:
        response.status == HttpStatus.CREATED

        when:
        String location = response.header(HttpHeaders.LOCATION)

        then:
        location.startsWith("/genres/")

        when:
        Long devOpsId = Long.valueOf(location.substring("/genres/".length()))

        then:
        noExceptionThrown()

        when:
        request = HttpRequest.POST('/genres', [name: 'Microservices']) // 
        response = client.toBlocking().exchange(request)

        then:
        response.status == HttpStatus.CREATED

        when:
        location = response.header(HttpHeaders.LOCATION)

        then:
        location.startsWith("/genres/")

        when:
        Long microservicesId = Long.valueOf(location.substring("/genres/".length()))

        then:
        noExceptionThrown()

        when: "verify get/read operation"
        URI microservicesUri = UriBuilder.of("/genres").path(microservicesId as String).build()
        Genre genre = client.toBlocking().retrieve(microservicesUri.toString(), Genre) //

        then:
        genre.name == 'Microservices'

        when: "verify put/update operation"
        request = HttpRequest.PUT('/genres', new GenreUpdateCommand(microservicesId, 'Micro-services'))
        response = client.toBlocking().exchange(request) //

        then:
        response.status == HttpStatus.NO_CONTENT

        when:
        genre = client.toBlocking().retrieve(microservicesUri.toString(), Genre)

        then:
        genre.name == 'Micro-services'

        when: "verify list operation"
        request = HttpRequest.GET('/genres/list')
        List<Genre> genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))

        then:
        genres.size() == 2

        when: "verify transaction execption does not add genre"
        request = HttpRequest.POST('/genres/ex', [name: 'Microservices']) //
        response = client.toBlocking().exchange(request)

        then:
        response.status == HttpStatus.NO_CONTENT

        and:
        client.toBlocking().retrieve(HttpRequest.GET('/genres/list'), Argument.listOf(Genre)).size() == 2

        when: "verify pageable operation"
        request = HttpRequest.GET(UriBuilder.of("/genres")
                .path("list")
                .queryParam("size", 1)
                .queryParam("sort", "name")
                .build())
        genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))

        then:
        genres.size() == 1
        genres[0].name == 'DevOps'

        when: "verify pagable sort operation"
        request =  HttpRequest.GET(UriBuilder.of("/genres")
                .path("list")
                .queryParam("size", 1)
                .queryParam("sort", "name,desc")
                .build())
        genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))

        then:
        genres.size() == 1
        genres[0].name == 'Micro-services'

        when: "verify pageable empty page"
        request =  HttpRequest.GET(UriBuilder.of("/genres")
                .path("list")
                .queryParam("size", 1)
                .queryParam("page", 2)
                .queryParam("sort", "name")
                .build())
        genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))

        then:
        genres.size() == 0

        when: "verify delete operation - id: #id"
        request = HttpRequest.DELETE(UriBuilder.of("/genres").path(microservicesId as String).build().toString())
        response = client.toBlocking().exchange(request)

        then:
        response.status == HttpStatus.NO_CONTENT

        when:
        request = HttpRequest.DELETE(UriBuilder.of("/genres").path(devOpsId as String).build().toString())
        response = client.toBlocking().exchange(request)

        then:
        response.status == HttpStatus.NO_CONTENT
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. More info.
2 Inject the HttpClient bean and point it to the embedded server.
3 Creating HTTP Requests is easy thanks to the Micronaut framework fluid API.
4 If you care just about the object in the response, use retrieve.
5 Sometimes, receiving just the object is not enough, and you need information about the response. In this case, instead of retrieve, use the exchange method.

Testing the Application

To run the tests:

./mvnw test

Running the Application

To run the application, use the ./mvnw mn:run command, which starts the application on port 8080.

Testing Running API

Save one genre, and your genre table will now contain an entry.

curl -X "POST" "http://localhost:8080/genres" \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d $'{ "name": "music" }'

Test Resources

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

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

Connecting to a MySQL database

Previously, we connected to a MySQL database, which Micronaut Test Resources started for us.

However, it is easy to connect to an already existing database. Let’s start a database and connect to it.

Execute the following command to run a MySQL container:

docker run -it --rm \
    -p 3306:3306 \
    -e MYSQL_DATABASE=db \
    -e MYSQL_USER=sherlock \
    -e MYSQL_PASSWORD=elementary \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=true \
    mysql:8
Tip
 If you are using macOS on Apple Silicon – e.g. M1, M1 Pro, etc. – Docker might fail to pull an image for mysql:8. In that case substitute mysql:oracle.

Database Migrations tools, such Flyway need a configured JDBC datasource. Export several environment variables:

export DATASOURCES_DEFAULT_URL=jdbc:mysql://localhost:3306/db
export DATASOURCES_DEFAULT_USERNAME=sherlock
export DATASOURCES_DEFAULT_PASSWORD=elementary

Micronaut Framework populates the properties datasources.default.url, datasources.default.username and datasources.default.password with those environment variables' values. Learn more about JDBC Connection Pools.

For R2DBC, export serveral environment variables:

export R2DBC_DATASOURCES_DEFAULT_URL=jdbc:mysql://localhost:3306/db
export R2DBC_DATASOURCES_DEFAULT_USERNAME=sherlock
export R2DBC_DATASOURCES_DEFAULT_PASSWORD=elementary

Micronaut Framework populates the properties r2dbc.datasources.default.url, r2dbc.datasources.default.username and r2dbc.datasources.default.password with those environment variables' values.

You can run the application and test the API as it was described in the previous sections. However, when you run the application, Micronaut Test Resources does not start a MySQL container because you have provided values for r2dbc.datasources.default. and datasources.default. properties.

Next Steps

Read more about:

Help with the Micronaut Framework

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

License

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