These tutorials target Micronaut Framework 3. Read, Guides for Micronaut Framework 4.

Table of Contents

Connect a Micronaut Data JDBC Application to Azure Database for MySQL

Learn how to connect a Micronaut Data JDBC application to a Microsoft Azure Database for MySQL

Authors: Graeme Rocher

Micronaut Version: 3.9.2

1. Getting Started

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

The application uses Micronaut Data JDBC and a MySQL database.

2. What you will need

To complete this guide, you will need the following:

We will then connect the application to Azure Database for MySQL.

Paid services are used as part of this guide. To follow the tutorial, you will need a paid Azure account or a trial account with credits available.

3. Create an Azure Database for MySQL instance

Now, you’ll create a new Azure Database for MySQL instance. This is accomplished in three steps, using the Azure CLI:

  1. First, you’ll create a resource group that the database will form part of. It is recommended that you create a new resource group just for this guide rather than reusing an existing one.

  2. Next, you’ll create the Azure Database for MySQL instance using the CLI.

  3. Finally, you will configure the Azure firewall ingress rules to allow a connection from your local machine to the Azure Database instance.

4. Configure Environment Variables

Before getting started, configure the following environment variables. For example, using bash:

Required Environment Variables
export AZURE_RESOURCE_GROUP=micronaut-azure-database-group \
export AZURE_DATABASE_NAME=micronaut-azure-database \
export AZURE_REGION=eastus \
export LOCAL_IP_ADDRESS=<YOUR_LOCAL_IP_ADDRESS> \
export AZURE_DATABASE_USER=guide_user \
export AZURE_DATABASE_PASSWORD=<YOUR_MYSQL_PASSWORD>

The remainder of the guide will use the values of these environments variables, which are described below:

  • AZURE_RESOURCE_GROUP - The name of the Azure resource group. You can change this value if you prefer another name.

  • AZURE_DATABASE_NAME - The name of the database that will be created within the MySQL instance. You can change this value if you prefer another name.

  • AZURE_REGION - The Azure region. This guide uses the U.S. east region by default. However, you can change this to a closer region. You can list the regions by running az account list-locations.

  • LOCAL_IP_ADDRESS - Your local IP address, which will be required to configure the Azure firewall. You will need to replace this value with your local IP address. You can find your local IP address by visiting http://whatismyip.akamai.com/.

  • AZURE_DATABASE_USER - The database username to establish the connection

  • AZURE_DATABASE_PASSWORD - The database password to establish the connection. Replace this value with a password of your choice.

4.1. Create a Resource Group

First, create a resource group using the Azure CLI, specifying the AZURE_RESOURCE_GROUP and AZURE_REGION environment variables you configured in the previous section:

az group create \
    --name $AZURE_RESOURCE_GROUP \
    --location $AZURE_REGION

The above command should result in a response like:

{
  "id": "/subscriptions/18ce9a15-3ebb-4f30-ad7d-bcc0e39329a5/resourceGroups/micronaut-azure-database-group",
  "location": "eastus",
  "managedBy": null,
  "name": "micronaut-azure-database-group",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null,
  "type": "Microsoft.Resources/resourceGroups"
}

4.2. Create the Azure Database Instance

Now create the Azure Database for MySQL instance using the following command:

az mysql flexible-server create \
    --version 8.0.21 \
    --resource-group $AZURE_RESOURCE_GROUP \
    --name $AZURE_DATABASE_NAME \
    --location $AZURE_REGION \
    --sku-name Standard_B1ms \
    --tier Burstable \
    --storage-size 32 \
    --admin-user $AZURE_DATABASE_USER \
    --admin-password $AZURE_DATABASE_PASSWORD \
    --public-access $LOCAL_IP_ADDRESS
The above command uses the --version flag to create a MySQL 8 instance; see the Azure documentation for supported MySQL versions.

Creating the Azure Database for MySQL instance may take several minutes to complete. Once the operation has completed, you should see output like the following (shortened for brevity):

{
   ...,
  "databaseName": "flexibleserverdb",
  "host": "micronaut-azure-database.mysql.database.azure.com",
   ...,
  "location": "East US",
   ...,
  "resourceGroup": "micronaut-azure-database-group",
  "skuname": "Standard_B1ms",
   ...,
  "username": "guide_user",
  "version": "8.0.21"
}

5. Configure MySQL

Flyway will create the database tables the first time the application starts, but you must create the actual database first.

You can once again use the Azure CLI to achieve this. The following command creates a database called demo within the Azure Database for MySQL instance:

Create the MySQL database
az mysql flexible-server db create \
    --resource-group $AZURE_RESOURCE_GROUP \
    --database-name demo \
    --server-name $AZURE_DATABASE_NAME

6. Creating the Application

Download the complete solution of the Access a database with Micronaut Data JDBC guide. You will use the sample application as a starting point.

7. Running the Application

With almost everything in place, you can start the application and try it out. First, set environment variables to configure the application datasource, then start the app.

Create environment variables for DATASOURCES_DEFAULT_URL, DATASOURCES_DEFAULT_USERNAME, and DATASOURCES_DEFAULT_PASSWORD, which will be used in the Micronaut app’s application.yml datasource:

export DATASOURCES_DEFAULT_URL=jdbc:mysql://$AZURE_DATABASE_NAME.mysql.database.azure.com:3306/demo?serverTimezone=UTC
export DATASOURCES_DEFAULT_USERNAME=guide_user
export DATASOURCES_DEFAULT_PASSWORD=$AZURE_DATABASE_PASSWORD
Window System
Command Prompt

Change 'export' to 'set'

Example: set DATASOURCES_DEFAULT_USERNAME=guide_user

PowerShell

Change 'export ' to '$' and use quotes around the value

Example: $DATASOURCES_DEFAULT_USERNAME="guide_user"

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.

To run the application, use the ./gradlew run command, which starts the application on port 8080.

You can test the application in a web browser or with cURL.

Run from a terminal window to create a Genre:

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

and run this to list the genres:

curl http://localhost:8080/genres/list

8. Cleanup

When you are finished using the database, you can delete the resource group, which will delete all resources associated with the group, including the database. To do this, run

az group delete -n $AZURE_RESOURCE_GROUP

9. Next steps

10. Next Steps

Read more about Micronaut Data.

Learn more about Azure Flexible Server