Table of Contents

Connect a Micronaut Data JDBC Application to an AWS RDS MySQL Database

Learn how to connect to an AWS MySQL Database

Authors: Álvaro Sánchez-Mariscal

Micronaut Version: 4.6.3

1. Getting Started

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

The application uses Micronaut Data JDBC and a MySQL database.

2. What you will need

To complete this guide, you will need the following:

3. Amazon Web Services (AWS)

If you don’t have one already, create an AWS Account.

3.1. AWS CLI

Follow the AWS documentation for installing or updating the latest version of the AWS CLI.

3.2. Administrator IAM user

Instead of using your AWS root account, it is recommended that you use an IAM administrative user. If you don’t have one already, follow the steps below to create one:

aws iam create-group --group-name Administrators
aws iam create-user --user-name Administrator
aws iam add-user-to-group --user-name Administrator --group-name Administrators
aws iam attach-group-policy --group-name Administrators --policy-arn $(aws iam list-policies --query 'Policies[?PolicyName==`AdministratorAccess`].{ARN:Arn}' --output text)
aws iam create-access-key --user-name Administrator

Then, run aws configure to configure your AWS CLI to use the Administrator IAM user just created.

4. Creating a MySQL Database instance in Amazon Relational Database Service (Amazon RDS)

You will create the database with the AWS CLI. See the AWS CLI rds command for more information.

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

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

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

Some of the following commands use jq

jq 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"

4.2. Create the MySQL instance

aws rds create-db-instance \
    --db-instance-identifier micronaut-guides-mysql \
    --db-instance-class db.t2.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

You then need to wait for the instance to be available:

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

Once the instance is available, you can configure environment variables for its hostname and port:

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

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

mysql -u admin -p

4.3. Create a database and user

Create the database. You can use any valid database name (e.g., micronaut):

CREATE DATABASE micronaut;

Create a database user. You can use any valid MySQL username (e.g., guide_user) and any valid password:

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

Grant access to the database for the new user:

GRANT ALL ON micronaut.* TO 'guide_user';

Exit the MySQL console:

exit

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

6. 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 you can 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://${MYSQL_HOST}:3306/micronaut
export DATASOURCES_DEFAULT_USERNAME=guide_user
export DATASOURCES_DEFAULT_PASSWORD=M1cr0n4ut!
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

6.1. Stopping the Instance and cleaning up

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

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

6.2. Next Steps

7. Next Steps

Read more about Micronaut Data.

Discover Amazon Relational Database Service (RDS).

8. License

All guides are released with an Apache license 2.0 license for the code and a Creative Commons Attribution 4.0 license for the writing and media (images…​).