Create and Connect a Micronaut Application to an Azure Database for MySQL

This guide describes how to create a database application using the Graal Development Kit for Micronaut (GDK). The application presents REST endpoints and stores data in an Azure MySQL database using Micronaut^® Data JDBC.

Azure Database for MySQL is a relational database service powered by the MySQL Community Edition.

Micronaut Data is a database access toolkit that uses ahead-of-time compilation to precompute queries for repository interfaces that are then executed by a thin, lightweight runtime layer. Micronaut Data supports the following back ends: JPA (Hibernate and Hibernate Reactive); SQL (JDBC, R2DBC); and MongoDB.

Prerequisites #

• JDK 17 or higher. See Setting up Your Desktop.
• An Azure account with enough user permissions to create and manage MySQL instances. See Setting up Your Cloud Accounts.
• The Azure CLI. Follow the Azure documentation for installing or updating the latest version of the Azure CLI.
• The MySQL client CLI.
• The GDK CLI. See Setting up Your Desktop. (Optional.)

Follow the steps below to create the application from scratch. However, you can also download the completed example:

A note regarding your development environment

Consider using Visual Studio Code, which provides native support for developing applications with the Graal Development Kit extension.

Note: If you use IntelliJ IDEA, enable annotation processing.

Windows platform: The GDK guides are compatible with Gradle only. Maven support is coming soon.

1. Create the Application #

Create an application using the GDK Launcher.

1. Open the GDK Launcher in advanced mode.

2. Create a new project using the following selections.
   • Project Type: Application (Default)
   • Project Name: azure-db-demo
   • Base Package: com.example (Default)
   • Clouds: Azure
   • Build Tool: Gradle (Groovy) or Maven
   • Language: Java (Default)
   • Test Framework: JUnit (Default)
   • Java Version: 17 (Default)
   • Micronaut Version: (Default)
   • Cloud Services: Database
   • Features: GraalVM Native Image (Default)
   • Sample Code: Yes (Default)
3. Click Generate Project, then click Download Zip. The GDK Launcher creates a Java project with the default package com.example in a directory named azure-db-demo. The application ZIP file will be downloaded to your default downloads directory. Unzip it, open it in your code editor, and proceed to the next steps.

Alternatively, use the GDK CLI as follows:

gdk create-app com.example.azure-db-demo \
    --clouds=azure \
    --services=database \
    --features=graalvm \
    --build=gradle \
    --jdk=17 \
    --lang=java

gdk create-app com.example.azure-db-demo \
    --clouds=azure \
    --services=database \
    --features=graalvm \
    --build=maven \
    --jdk=17 \
    --lang=java

Open the micronaut-cli.yml file, you can see what features are packaged with the application:

features: [app-name, data, data-jdbc, flyway, gdk-azure-cloud-app, gdk-azure-database, gdk-bom, gdk-license, graalvm, http-client-test, java, java-application, jdbc-hikari, junit, logback, maven, maven-enforcer-plugin, micronaut-aot, mysql, netty-server, properties, readme, serialization-jackson, shade, test-resources, validation]

The GDK Launcher creates a multi-module project with two subprojects: azure for Azure, and lib for common code and configuration shared across cloud platforms. You develop the application logic in the lib subproject, and keep the Azure-specific configurations in the azure subproject. If you enable sample code generation, the GDK Launcher creates the main controller, repository interface, entity, service classes, and tests for you. Consider checking this guide where each sample class is closely examined.

1.1. Configure Datasources #

The datasources are defined in the azure/src/main/resources/application.properties file. The GDK Launcher also included and enabled Flyway to perform migrations on the default datasources. It uses the Micronaut integration with Flyway that automates schema changes, significantly simplifies schema management tasks, such as migrating, rolling back, and reproducing in multiple environments:

flyway.datasources.default.enabled=true

Note: Flyway migrations are not compatible with the default automatic schema generation. So, in the azure/src/main/resources/application.properties file, either delete the datasources.default.schema-generate=CREATE_DROP line or change that line to datasources.default.schema-generate=NONE to ensure that only Flyway manages your schema.

Flyway migration is automatically triggered before your application starts. Flyway reads migration file(s) in the lib/src/main/resources/db/migration/ directory. The migration file with the database schema, lib/src/main/resources/db/migration/V1__schema.sql, was also created for you by the GDK Launcher.

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 runs the commands in the SQL file and creates the schema needed for the application.

2. Create an Azure MySQL Database Instance #

You will create a MySQL Database instance with the Azure CLI.

2.1. Create a Resource Group #

We recommend that you create a new resource group for this guide, but you can use an existing resource group instead.

Run the az group create command to create a resource group named gdkguides in the eastus region:

az group create --location eastus --name gdkguides

If you prefer using the region geographically closer to you, run az account list-locations to list all available regions.

2.2. Create a MySQL Database Instance #

Run the az mysql flexible-server create command to create a MySQL flexible server and a database named azuredb:

az mysql flexible-server create \
   --admin-password Secret99! \
   --admin-user gdkadmin \
   --database-name azuredb \
   --location eastus \
   --name gdkmysql \
   --public-access All \
   --resource-group gdkguides \
   --version 8.0.21

The response should look like this:

{
   "connectionString": "mysql azuredb --host gdkmysql.mysql.database.azure.com --user gdkadmin --password=Secret99!",
   "databaseName": "azuredb",
   "firewallName": "AllowAll_2024-6-25_13-1-38",
   "host": "gdkmysql.mysql.database.azure.com",
   "id": "/subscriptions/fe053d95...",
   "location": "East US",
   "password": "...",
   "resourceGroup": "MySQL",
   "skuname": "Standard_B1ms",
   "username": "gdkadmin",
   "version": "8.0.21"
}

Set the value of an environment variable (named MYSQL_HOST) to represent the hostname of the MySQL server, using the value of the host attribute from the response:

export MYSQL_HOST=gdkmysql.mysql.database.azure.com

2.3. Create a Database User #

1. Connect to the database using the MySQL client CLI:

    mysql -h $MYSQL_HOST -u gdkadmin -p
   

2. Select the database you created when you created the server with the USE statement:

    USE azuredb;
   

3. Create a database user. You can use any valid MySQL username (for example, azuredb_user) and any valid password:

    CREATE USER 'azuredb_user' IDENTIFIED BY 'M1cr0n4ut!';
   

4. Grant access to the database for the new user:

    GRANT ALL ON azuredb.* TO 'azuredb_user';
   

5. Exit the MySQL console by entering “exit”.

3. Test the Application #

With almost everything in place, you can run the tests.

1. Return to your local IDE where you have opened the Micronaut database application, and open the azure/src/main/resources/application.properties file.

2. The settings configure the datasource for MySQL, specifying the database type, dialect, and driver class:

    datasources.default.db-type=mysql
    datasources.default.dialect=MYSQL
    datasources.default.driver-class-name=com.mysql.cj.jdbc.Driver
   

   Set values for the missing datasources.default.url, datasources.default.username, and datasources.default.password properties by exporting them as environment variables as follows:

    export DATASOURCES_DEFAULT_URL=jdbc:mysql://$MYSQL_HOST:3306/azuredb
    export DATASOURCES_DEFAULT_USERNAME=azuredb_user
    export DATASOURCES_DEFAULT_PASSWORD=M1cr0n4ut!
   

   Note: If you use Windows Command Prompt, replace export with set, for example, set DATASOURCES_DEFAULT_USERNAME=azuredb_user. If you use Windows PowerShell, replace export with $ and surround the value with double quote marks, for example, $DATASOURCES_DEFAULT_USERNAME="azuredb_user".

3. Run the tests:

   • Gradle
   • Maven

   ./gradlew :azure:test

   ./mvnw install -pl lib -am
   ./mvnw test -pl azure

Next, you can package this application as a native executable and deploy from the virtual machine, connected to the MySQL database. Deploying as a native executable does not require a Java VM to run, so you can transfer it to another Linux host and run easily.

4. Generate a Native Executable Using GraalVM #

The GDK supports compiling Java applications ahead-of-time into native executables using GraalVM Native Image. You can use the Gradle plugin for GraalVM Native Image building/Maven plugin for GraalVM Native Image building. Packaged as a native executable, it significantly reduces application startup time and memory footprint.

Prerequisites: Make sure you have installed a GraalVM JDK. The easiest way to get started is with SDKMAN!. For other installation options, visit the Downloads section.

1. To generate a native executable, use the following command:

   • Gradle
   • Maven

   ./gradlew :azure:nativeCompile

   The native executable will be created in the azure/build/native/nativeCompile/ directory.

   ./mvnw install -pl lib -am
   ./mvnw clean package -pl azure -Dpackaging=native-image -DskipTests

   The native executable will be created in the azure/target/ directory.

   You can customize the name of the resulting binary by updating the Maven/Gradle plugin for GraalVM Native Image configuration.

2. Run the native executable:

   • Gradle
   • Maven

   azure/build/native/nativeCompile/azure-db-demo-azure

   azure/target/azure-db-demo-azure

3. Run this command to create a new Genre entry in the database table:

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

   Then list all genres:

    curl localhost:8080/genres/list
   

As a reminder, you do not need to install a Java VM on the virtual machine to run the application. The native executable is a self-contained binary. Deploying from a native executable significantly reduces application startup time and memory footprint.

5. Clean up Cloud Resources #

Once you are done with this guide, you can delete the Azure resources created to avoid incurring unnecessary charges.

Delete the resource group and all of its resources with:

az group delete --name gdkguides

Alternatively, run these commands to delete resources individually:

az mysql flexible-server delete --resource-group gdkguides --name gdkmysql

az group delete --name gdkguides

Summary #

This guide demonstrated how to use the GDK to create and access a database application that stores data in an Azure MySQL database using Micronaut Data JDBC. You also learned how to package this application into a native executable.

Related Documentation #

• Micronaut Data
• Micronaut Azure Support
• Azure Database for MySQL
• Native Build Tools
• GraalVM Native Image