Table of Contents

Connect a Micronaut Data JDBC Application to a Google MySQL Database

Learn how to connect to a Google MySQL Database

Authors: John Shingler

Micronaut Version: 4.6.3

1. Getting Started

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

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

This guide uses paid services; you may need to enable Billing in Google Cloud to complete some steps in this guide.

4. Google Cloud Platform

Signup for the Google Cloud Platform

4.1. Cloud SDK

Install the Cloud SDK CLI for your operating system.

Cloud SDK includes the gcloud command-line tool. Run the init command in your terminal:

gcloud init

Log in to your Google Cloud Platform:

gcloud auth login

4.2. Google Cloud Platform Project

Create a new project with a unique name (replace xxxxxx with alphanumeric characters of your choice):

gcloud projects create micronaut-guides-xxxxxx
In GCP, project ids are globally unique, so the id you used above is the one you should use in the rest of this guide.

Change your project:

gcloud config set project micronaut-guides-xxxxxx

If you forget the project id, you can list all projects:

gcloud projects list

5. Creating a MySQL Database instance in the Google Cloud

We will create the database with the Google Cloud CLI. See the MySQL CLI command reference for more information.

5.1. Create the MySQL instance

gcloud sql instances create micronaut-guides-mysql \
 --database-version=MYSQL_8_0 \
 --tier=db-f1-micro \
 --region=us-east1 \
 --root-password=<YOUR_ROOT_PASSWORD> \
 --authorized-networks=<LOCAL_IP_ADDRESS>

  • micronaut-guides-mysql - Change if you would like to use a different instance name

  • --database-version - Specifying MySQL v8.0 (See SQL DB Versions for other options)

  • --tier - Change to match your needs. (Instead of using tiers, you can specify the CPU and memory, with the --cpu and --memory flags)

  • --region - Defaults to us-central1 if omitted

  • --root-password - Default to password123 if omitted

  • --authorized-networks - A single IP or a range of IP Addresses in the CIDR notation

  • LOCAL_IP_ADDRESS - This is the local IP address where the Micronaut application is running. You can find out the value your local machine by visiting http://whatismyip.akamai.com/.

You might be prompted to enable the Google SQL Admin API

API [sqladmin.googleapis.com] not enabled on project [<project-id>].

Would you like to enable and retry (this will take a few minutes)? (y/N)?

You will see the following output:

NAME                    DATABASE_VERSION  LOCATION    TIER         PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
micronaut-guides-mysql  MYSQL_8_0         us-east1-c  db-f1-micro  34.xxx.xxx.65    -                RUNNABLE

Make note of the "Primary Address"; you will need this later.

If you need to change the authorized-networks, use:

gcloud sql instances patch micronaut-guides-mysql \
  --authorized-networks=<DIFFERENT_IP>

5.1.1. Create a User

Instead of using the root DB User account, create one for the application to use:

gcloud sql users create <USER_NAME> \
  --instance=micronaut-guides-mysql \
  --password=<USER_PASSWORD>

5.2. Create the Database

Now that we have our MySQL instance running, we need to create a database.

gcloud sql databases create demo \
  --instance=micronaut-guides-mysql

  • demo - Change if you would like to use a different 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, we can start the application and try it out. First, we need to set environment variables to configure the application datasource. Then we 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://<DB_INSTANCE_IP>:3306/demo
export DATASOURCES_DEFAULT_USERNAME=<USER_NAME>
export DATASOURCES_DEFAULT_PASSWORD=<USER_PASSWORD>

  • DB_INSTANCE_IP - Is your MySQL instance’s primary address

  • demo - Change if you used a different database name

  • DATASOURCES_DEFAULT_USERNAME - Not needed if you are using the root user

Window System
Command Prompt

Change 'export' to 'set'

Example: set DATASOURCES_DEFAULT_USERNAME=<USER_NAME>

PowerShell

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

Example: $DATASOURCES_DEFAULT_USERNAME="<USER_NAME>"

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

7.1. Stopping the Instance

gcloud sql instances patch micronaut-guides-mysql \
  --activation-policy=NEVER

8. Cleaning Up

After you’ve finished this guide, you can clean up the resources you created on Google Cloud Platform so you won’t be billed for them in the future. The following sections describe how to delete or turn off these resources.

8.1. Deleting the project

The easiest way to eliminate billing is to delete the project you created for the tutorial.

Deleting a project has the following consequences:

  • If you used an existing project, you’ll also delete any other work you’ve done in the project.

  • You can’t reuse the project ID of a deleted project. If you created a custom project ID that you plan to use in the future, you should delete the resources inside the project instead. This ensures that URLs that use the project ID, such as an appspot.com URL, remain available.

  • If you are exploring multiple tutorials and quickstarts, reusing projects instead of deleting them prevents you from exceeding project quota limits.

8.1.1. Via the CLI

To delete the project using the Cloud SDK, run the following command, replacing YOUR_PROJECT_ID with the project ID:

gcloud projects delete YOUR_PROJECT_ID

8.1.2. Via the Cloud Platform Console

In the Cloud Platform Console, go to the Projects page.

In the project list, select the project you want to delete and click Delete project. After selecting the checkbox next to the project name, click Delete project

In the dialog, type the project ID, and then click Shut down to delete the project.

Deleting or turning off specific resources

You can individually delete or turn off some of the resources that you created during the tutorial.

9. Next Steps

Read more about Micronaut Data.

10. 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…​).