Table of Contents

Access a database with Micronaut Data JDBC

Learn how to access a database with Micronaut JDBC repositories.

1. Getting Started

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

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

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

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,flyway,jdbc-hikari,mysql,serialization-jackson,validation

4.1. Data Source configuration

Define the datasource in config/application.toml.

config/application.toml
[datasources.default]
dialect = "MYSQL"
driver-class-name = "com.mysql.cj.jdbc.Driver"
db-type = "mysql"
This way of defining the datasource properties enables us to externalize the configuration, for example for production environment, and also provide a default value for development. If the environment variables are not defined, the Micronaut framework will use the default values.

4.2. 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:

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

config/application.toml
[flyway.datasources.default]
(1)
enabled = true
1 Enable Flyway for the default datasource.
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 Pyronaut 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:

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

4.3. Domain

Create the domain entities:

src/example/micronaut/domain/genre.py
from dataclasses import dataclass
from typing import Annotated

from jakarta.validation.constraints import NotBlank
from micronaut.data.annotation import GeneratedValue, Id, MappedEntity
from micronaut.serde.annotation import Serdeable


@dataclass
@Serdeable
@MappedEntity  (1)
class Genre:
    id: Annotated[int | None, Id, GeneratedValue] = None
    name: Annotated[str | None, NotBlank] = None
You could use a subset of supported JPA annotations instead by including the following compileOnly scoped dependency: jakarta.persistence:jakarta.persistence-api.

4.4. Repository Access

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

src/example/micronaut/genre_repository.py
from typing import List

import java
from micronaut.data.jdbc.annotation import JdbcRepository
from micronaut.data.repository import CrudRepository

from .domain.genre import Genre

Pageable = java.type("io.micronaut.data.model.Pageable")


@JdbcRepository(dialect="MYSQL")  (1)
class GenreRepository(CrudRepository[Genre, int]):  (2)
    def findAll(self, pageable: Pageable) -> List[Genre]: ...
1 @JdbcRepository 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 CrudRepository and adds a findAll(Pageable) method for pagination.

Repository Description

PageableRepository

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

CrudRepository

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.

Create a small service to keep the rollback example inside a transactional boundary:

src/example/micronaut/genre_service.py
from jakarta.inject import Singleton
from jakarta.transaction import Transactional
from micronaut.data.exceptions import DataAccessException

from .domain.genre import Genre
from .genre_repository import GenreRepository


@Singleton  (1)
class GenreService:
    def __init__(self, genre_repository: GenreRepository):
        self.genre_repository = genre_repository

    @Transactional  (2)
    def save_with_exception(self, genre: Genre) -> Genre:
        self.genre_repository.save(genre)
        raise DataAccessException("test exception")  (3)
1 Use jakarta.inject.Singleton to designate a class as a singleton.
2 The @Transactional annotation starts a transaction around the method.
3 Throwing a DataAccessException marks the transaction for rollback.

4.5. 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:

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

Create a dataclass to encapsulate the Update operations:

src/example/micronaut/genre_update_command.py
from dataclasses import dataclass
from typing import Annotated

from jakarta.validation.constraints import NotBlank, NotNull
from micronaut.serde.annotation import Serdeable


@Serdeable  (1)
@dataclass
class GenreUpdateCommand:
    id: Annotated[int, NotNull]
    name: Annotated[str, NotBlank]
1 Declare the @Serdeable annotation at the type level in your source code to allow the type to be serialized or deserialized.

Create route functions that expose a resource with the common CRUD operations:

src/example/micronaut/genre_controller.py
from typing import Annotated

import java
from jakarta.inject import Inject
from jakarta.validation import Valid
from micronaut.data.exceptions import DataAccessException
from micronaut.http import HttpHeaders, HttpResponse, HttpStatus
from micronaut.http.annotation import Body, Delete, Get, Post, Put, Status
from micronaut.scheduling import TaskExecutors
from micronaut.scheduling.annotation import ExecuteOn

from .domain.genre import Genre
from .genre_repository import GenreRepository
from .genre_service import GenreService
from .genre_update_command import GenreUpdateCommand

Pageable = java.type("io.micronaut.data.model.Pageable")

genre_repository: Annotated[GenreRepository, Inject]  (3)
genre_service: Annotated[GenreService, Inject]


@ExecuteOn(TaskExecutors.BLOCKING)  (1)
@Get("/genres/{id}")   (2) (4)
def show(id: int) -> Genre | None:
    return genre_repository.findById(id).orElse(None)  (5)


@ExecuteOn(TaskExecutors.BLOCKING)
@Put("/genres")  (6)
def update(command: Annotated[GenreUpdateCommand, Body, Valid]) -> HttpResponse:  (7)
    genre_repository.update(Genre(command.id, command.name))
    return (
        HttpResponse.noContent()
        .header(HttpHeaders.LOCATION, location(command.id))  (8)
    )


@ExecuteOn(TaskExecutors.BLOCKING)
@Get("/genres/list")  (9)
def list(pageable: Annotated[Pageable, Valid]) -> list[Genre]:  (10)
    return genre_repository.findAll(pageable)


@ExecuteOn(TaskExecutors.BLOCKING)
@Post("/genres")  (11)
def save(genre: Annotated[Genre, Body, Valid]) -> HttpResponse[Genre]:
    saved = genre_repository.save(genre)
    return (
        HttpResponse.created(saved)
        .header(HttpHeaders.LOCATION, location(saved.id))
    )


@ExecuteOn(TaskExecutors.BLOCKING)
@Post("/genres/ex")  (12)
def save_exceptions(genre: Annotated[Genre, Body, Valid]) -> HttpResponse[Genre]:
    try:
        saved = genre_service.save_with_exception(genre)
        return (
            HttpResponse.created(saved)
            .header(HttpHeaders.LOCATION, location(saved.id))
        )
    except DataAccessException:
        return HttpResponse.noContent()


@ExecuteOn(TaskExecutors.BLOCKING)
@Delete("/genres/{id}")  (13)
@Status(HttpStatus.NO_CONTENT)
def delete(id: int) -> None:
    genre_repository.deleteById(id)


def location(id: int) -> str:
    return f"/genres/{id}"
1 It is critical that any blocking I/O operations (such as fetching the data from the database) are offloaded to a separate thread pool that does not block the Event loop.
2 The route decorators map each function directly to a /genres URI.
3 Inject beans into annotated module variables.
4 Maps a GET request to /genres/{id}, which attempts to show a genre. This illustrates the use of a URL path variable.
5 Returning None when the genre doesn’t exist makes Micronaut Framework respond with 404 (not found).
6 Maps a PUT request to /genres, which attempts to update a genre.
7 Adds @Valid to any method parameter that requires validation. Use a POJO supplied as a JSON payload in the request to populate the command.
8 It is easy to add custom headers to the response.
9 Maps a GET request to /genres/list, which returns a list of genres. This mapping illustrates URL parameters being mapped to a single POJO.
10 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.
11 Maps a POST request to /genres, which attempts to save a genre.
12 Maps a POST request to /genres/ex, which generates an exception.
13 Maps a DELETE request to /genres/{id}, which attempts to remove a genre. This illustrates the use of a URL path variable.

4.6. Writing Tests

Create a test to verify the CRUD operations:

tests/example/micronaut/test_genre_controller.py
import pytest
import requests

from pyronaut.test import MicronautTest, micronaut_test_fixture


@pytest.fixture
def my_context(request):
    fixture = micronaut_test_fixture(
        request,
        MicronautTest(environments=["test"], transactional=False),  (1)
    )
    yield fixture
    fixture.stop()


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


def entity_id(response):
    path = "/genres/"
    value = response.headers.get("Location")
    assert value is not None
    return int(value[value.index(path) + len(path):])


def test_find_non_existing_genre_returns_404(client):
    response = client.get("/genres/99")

    assert response.status_code == 404


def test_genre_crud_operations(client):
    genre_ids = []

    response = client.post("/genres", json={"name": "DevOps"})  (3)
    assert response.status_code == 201
    genre_ids.append(entity_id(response))

    response = client.post("/genres", json={"name": "Microservices"})  (3)
    assert response.status_code == 201
    genre_id = entity_id(response)
    genre_ids.append(genre_id)

    response = client.get(f"/genres/{genre_id}")  (4)
    assert response.status_code == 200
    genre = response.json()
    assert genre["name"] == "Microservices"

    response = client.put(
        "/genres",
        json={"id": genre_id, "name": "Micro-services"},
    )  (5)
    assert response.status_code == 204

    response = client.get(f"/genres/{genre_id}")
    assert response.status_code == 200
    genre = response.json()
    assert genre["name"] == "Micro-services"

    response = client.get("/genres/list")
    assert response.status_code == 200
    genres = response.json()
    assert len(genres) == 2

    response = client.post("/genres/ex", json={"name": "Microservices"})  (3)
    assert response.status_code == 204

    response = client.get("/genres/list")
    assert response.status_code == 200
    genres = response.json()
    assert len(genres) == 2

    response = client.get("/genres/list?size=1")
    assert response.status_code == 200
    genres = response.json()
    assert len(genres) == 1
    assert genres[0]["name"] == "DevOps"

    response = client.get("/genres/list?size=1&sort=name,desc")
    assert response.status_code == 200
    genres = response.json()
    assert len(genres) == 1
    assert genres[0]["name"] == "Micro-services"

    response = client.get("/genres/list?size=1&page=2")
    assert response.status_code == 200
    genres = response.json()
    assert len(genres) == 0

    for genre_id in genre_ids:
        response = client.delete(f"/genres/{genre_id}")
        assert response.status_code == 204
1 Create a Micronaut test fixture for the application under test.
2 Use requests.with_context(my_context) to call the application under test with a context-bound HTTP client.
3 Send JSON requests with the requests library.
4 Decode the JSON response body into a Python dictionary.
5 Use the response object when you need HTTP status or header information.

5. Testing the Application

To run the tests:

pyronaut install
pyronaut validate-config
pyronaut test

6. Running the Application

To run the application, use the pyronaut dev command, which starts the application on port 8080.

7. Testing the 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" }'

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

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

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.

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 datasources.default.* properties.

Before running the native executable, start a MySQL database and define the JDBC URL, username, and password with the environment variables described in the section Connecting to a MySQL database.

You can execute the genres endpoints exposed by the native executable, for example:

curl localhost:8080/genres/list

10. Next Steps

Read more about Micronaut Data.

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