Create and Connect a Micronaut Application to an Amazon Web Services RDS MySQL Database

This guide describes how to use GCN to create a Micronaut database application. The application presents REST endpoints and stores data in an Amazon Web Services (AWS) Relational Database Service (RDS) for MySQL database using Micronaut Data JDBC.

AWS RDS for MySQL is a service for MySQL database to set up, operate, and scale MySQL deployments in the cloud. AWS RDS supports MySQL Community Edition versions 5.7 and 8.0.

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

Prerequisites #

• JDK 17 or higher. See Setting up Your Desktop.
• An Amazon Web Services (AWS) account with enough user permissions to create and manage DB instances in RDS. See Setting up Your Cloud Accounts.
• The AWS CLI. Follow the AWS documentation for installing or updating the latest version of the AWS CLI.
• The MySQL client CLI.
• The GCN 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 in Java:

A note regarding your development environment

Consider using Visual Studio Code that provides native support for developing applications with the Graal Cloud Native Tools extension.

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

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

1. Create the Application #

Create an application using the GCN Launcher.

1. Open the GCN Launcher in advanced mode.

2. Create a new project using the following selections.
   • Project Type: Application (Default)
   • Project Name: aws-db-demo
   • Base Package: com.example (Default)
   • Clouds: AWS
   • Language: Java (Default)
   • Build Tool: Gradle (Groovy) or Maven
   • 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. GCN Launcher creates a Java project with the default package com.example in a directory named aws-db-demo. The application ZIP file will be downloaded in your default downloads directory. Unzip it, open in your code editor, and proceed to the next steps.

Alternatively, use the GCN CLI as follows:

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

gcn create-app com.example.aws-db-demo \
    --clouds=aws \
    --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, gcn-aws-cloud-app, gcn-aws-database, gcn-bom, gcn-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 GCN Launcher creates a multimodule project with two subprojects: aws for Amazon Web Services, and lib for common code and configuration shared across cloud platforms. You develop the application logic in the lib subproject, and keep the Amazon Web Services-specific configurations in the aws subproject. If you enable sample code generation, the GCN 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 aws/src/main/resources/application.properties file. The GCN 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 aws/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 GCN 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 a MySQL Database Instance Using AWS RDS #

You will create a MySQL Database instance with the AWS CLI. See the AWS CLI rds Command Reference to learn more about AWS RDS.

2.1. Create an Administrator Account #

Instead of using your AWS root account, use an administrator account. If you do not have one already, see Setting up Your Cloud Accounts.

2.2. Create VPC, Security Group, Subnets, and Subnet Group (Optional) #

To allow connections to the database from your local computer, create a VPC, and a security group that allows access to the MySQL default port from your current public IP address.

Note: Exposing a database port to the internet is a security risk. This should be done only for development purposes.

You will also create two subnets in different availability zones and a subnet group to associate them.

Some of the following commands use jq, which is a lightweight and flexible command-line JSON processor.

# VPC, internet gateway and route table
export VPC_ID=$(aws ec2 create-vpc --cidr-block 10.0.0.0/16 | jq -r '.Vpc.VpcId')
export IG_ID=$(aws ec2 create-internet-gateway | jq -r '.InternetGateway.InternetGatewayId')
aws ec2 attach-internet-gateway --internet-gateway-id $IG_ID --vpc-id $VPC_ID
aws ec2 modify-vpc-attribute --enable-dns-hostnames --vpc-id $VPC_ID
export RT_ID=$(aws ec2 describe-route-tables --filters "Name=vpc-id,Values=$VPC_ID" --query "RouteTables[].RouteTableId" --output text)
aws ec2 create-route --route-table-id $RT_ID --destination-cidr-block 0.0.0.0/0 --gateway-id $IG_ID

# Security group
aws ec2 create-security-group --group-name micronaut-guides-mysql-sg --description "Security Group for the Micronaut MySQL guide" --vpc-id $VPC_ID
export SG_ID=$(aws ec2 describe-security-groups --query 'SecurityGroups[?GroupName==`micronaut-guides-mysql-sg`].GroupId' --output text)
aws ec2 authorize-security-group-ingress --group-id $SG_ID --protocol tcp --port 3306 --cidr $(curl ifconfig.me)/32

# Subnets and subnet group
export AZ_0=$(aws ec2 describe-availability-zones --filters "Name=state,Values=available" --query "AvailabilityZones[0].ZoneName" --output text)
export AZ_1=$(aws ec2 describe-availability-zones --filters "Name=state,Values=available" --query "AvailabilityZones[1].ZoneName" --output text)
export SN0_ID=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.0.0/20 --availability-zone $AZ_0 | jq -r '.Subnet.SubnetId')
export SN1_ID=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.16.0/20 --availability-zone $AZ_1 | jq -r '.Subnet.SubnetId')
aws ec2 modify-subnet-attribute --subnet-id $SN0_ID --map-public-ip-on-launch
aws ec2 modify-subnet-attribute --subnet-id $SN1_ID --map-public-ip-on-launch
aws rds create-db-subnet-group --db-subnet-group-name micronaut-guides-mysql-sng --db-subnet-group-description "DB subnet group for the Micronaut MySQL guide" --subnet-ids "$SN0_ID" "$SN1_ID"

2.3. Create a MySQL Instance #

1. Run this command to create a MySQL instance in AWS RDS:

    aws rds create-db-instance \
        --db-instance-identifier micronaut-guides-mysql \
        --db-instance-class db.t3.micro \
        --engine mysql \
        --master-username admin \
        --master-user-password secret99 \
        --allocated-storage 20 \
        --db-subnet-group-name micronaut-guides-mysql-sng \
        --vpc-security-group-ids $SG_ID \
        --publicly-accessible
   

2. Wait for the instance to become available:

    aws rds wait db-instance-available --db-instance-identifier micronaut-guides-mysql
   

3. Once the instance is available, configure an environment variable for its hostname:

    export MYSQL_HOST=$(aws rds describe-db-instances --query 'DBInstances[?DBInstanceIdentifier==`micronaut-guides-mysql`].Endpoint.Address' --output text)
   

4. Finally, test connectivity to the database using the MySQL client CLI:

    mysql -u admin -p
   

2.4. Create a Database and a Database User #

1. Connect to the database using the MySQL client CLI (as shown earlier).

2. Create the database. You can use any valid database name (for example, awsdb):

    CREATE DATABASE awsdb;
   

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

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

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

    GRANT ALL ON awsdb.* TO 'awsdb_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 aws/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/awsdb
    export DATASOURCES_DEFAULT_USERNAME=awsdb_user
    export DATASOURCES_DEFAULT_PASSWORD=M1cr0n4ut!
   

   where <MySQL_IP_Address> is the private IP address of your MySQL database instance.

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

3. Run the tests:

   • Gradle
   • Maven

   ./gradlew :aws:test

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

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 #

GCN supports compiling Java applications ahead-of-time into native executables using GraalVM Native Image and Native Build Tools. Packaged as a native executable, it significantly reduces the application startup time and memory footprint.

Prerequisites: GraalVM Native Image is required to build native executables. Install GraalVM JDK with Native Image if you have not done that yet.

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

   • Gradle
   • Maven

   ./gradlew :aws:nativeCompile

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

   ./mvnw install -pl lib -am
   ./mvnw package -pl aws -Dpackaging=native-image

   The native executable will be created in the aws/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

   aws/build/native/nativeCompile/aws

   aws/target/aws

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. Stop Database Instance and Clean Up #

Once you are done with this guide, you can stop and/or delete the AWS resources created to avoid incurring unnecessary charges. Run this command:

aws rds delete-db-instance --db-instance-identifier micronaut-guides-mysql --skip-final-snapshot
aws rds wait db-instance-deleted --db-instance-identifier micronaut-guides-mysql
aws ec2 delete-subnet --subnet-id $SN0_ID
aws ec2 delete-subnet --subnet-id $SN1_ID
aws rds delete-db-subnet-group --db-subnet-group-name micronaut-guides-mysql-sng
aws ec2 delete-security-group --group-id $SG_ID
aws ec2 detach-internet-gateway --internet-gateway-id $IG_ID --vpc-id $VPC_ID
aws ec2 delete-internet-gateway --internet-gateway-id $IG_ID
aws ec2 delete-vpc --vpc-id $VPC_ID

Summary #

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

Related Documentation #

• Micronaut Data
• Micronaut AWS Support
• Amazon Relational Database Service (RDS)
• Native Build Tools
• GraalVM Native Image