Expand or collapse sidebar Expand or collapse sidebar
  • Database
  • Create and Connect a Micronaut Application to a Google MySQL Database

    • Create and Connect a Micronaut Application to a Google MySQL Database

    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 a Google Cloud Platform (GCP) MySQL database using Micronaut® Data.

    GCP Cloud SQL for MySQL is a service for MySQL database to set up, manage, and administer MySQL deployments in the cloud. GCP Cloud SQL supports MySQL Community Edition versions 5.6, 5.7, and 8.0.

    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

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

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

    Gradle GCP Database Example Download completed example

    The application ZIP file will be downloaded in your default downloads directory. Unzip it and proceed to the next steps.

    A note regarding your development environment

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

    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: gcp-db-demo
      • Base Package: com.example (Default)
      • Clouds: GCP
      • 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 an application with the package com.example in a directory named gcp-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.gcp-db-demo \
 --clouds=gcp \
 --services=database \
 --features=graalvm \
 --build=gradle \
 --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-bom, gdk-gcp-cloud-app, gdk-gcp-database, gdk-license, graalvm, http-client, java, java-application, jdbc-hikari, junit, logback, maven, maven-enforcer-plugin, micronaut-http-validation, mysql, netty-server, properties, readme, serialization-jackson, shade, static-resources, test-resources, validation]

    The GDK Launcher creates a multi-module project with two subprojects: gcp for Google Cloud Platform, and lib for common code and configuration shared across cloud platforms. You develop the application logic in the lib subproject, and keep the Google Cloud Platform-specific configurations in the gcp 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.

    The GDK Launcher included Flyway for database migrations. 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. The GDK Launcher enables Flyway in the gcp/src/main/resources/application.properties file and configures it to perform migrations on the default datasources.

    flyway.datasources.default.enabled=true

    If you specified Flyway as a project feature in the GDK Launcher the build file includes it as a dependency:

    build.gradle

    implementation("io.micronaut.flyway:micronaut-flyway")
implementation("org.flywaydb:flyway-mysql")

    Note: Flyway migrations are not compatible with the default automatic schema generation that is configured in gcp/src/main/resources/application.properties. If schema-generate is active, it will conflict with Flyway. So edit gcp/src/main/resources/application.properties and 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.

    Configuring multiple datasources is as simple as enabling Flyway for each one. You can also specify directories that will be used for migrating each datasource. For more information, see Micronaut integration with Flyway.

    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 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 Google Cloud Platform Project

    Create a new Google Cloud Platform project and name it "gdk-guides".

    Google Cloud Platform Project

    2.1. Install the Cloud SDK

    The Cloud SDK includes the gcloud command-line tool.

    1. Initialize the Cloud SDK:

      gcloud init

    2. Log in to the Google Cloud Platform:

      gcloud auth login

    3. Select your project:

      gcloud config set project gdk-guides

    You can get the IDs of your projects by running the command gcloud projects list.

    3. Create a MySQL Database Instance in Google Cloud

    You will create a MySQL database instance with the GCP CLI. See the MySQL CLI command reference for more information.

    First define the environment variables:

    export DB_INSTANCE_NAME=gdk-guides-mysql
export LOCAL_IP=<local IP address>

    Run this command to create a MySQL instance:

    MYSQL_HOST=$(gcloud sql instances create $DB_INSTANCE_NAME \
    --database-version=MYSQL_8_0 \
    --tier=db-f1-micro \
    --region=us-east1 \
    --root-password=Passw0rd1234! \
    --authorized-networks=$LOCAL_IP --format='value(ipAddresses.ipAddress)')

echo "MYSQL_HOST=$MYSQL_HOST"

    • --database-version Specify 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 options.)

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

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

    • $LOCAL_IP This is an environment variable that you should have previously defined and corresponds to the local IP address where the 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
gdk-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, run the following command:

    gcloud sql instances patch $DB_INSTANCE_NAME \
    --authorized-networks=<DIFFERENT_IP>

    3.1. Create a Database User

    Instead of using the root DB User account, create one for the application to use, as follows. First define a user password:

    export DATASOURCES_DEFAULT_PASSWORD=<user password>

    Then create the user:

    gcloud sql users create guide_user \
    --instance=$DB_INSTANCE_NAME \
    --password=$DATASOURCES_DEFAULT_PASSWORD

    3.2. Create the Database

    Now that you have your MySQL instance running, create a database as follows:

    gcloud sql databases create gcpdb \
    --instance=$DB_INSTANCE_NAME

    4. Run the Application

    1. Return to your local IDE where you have opened the Micronaut database application. With almost everything in place, you can start the application and try it out.

    2. Define the database driver URL, username, and password to configure the application datasources. It should look like this:

      datasources.default.driver-class-name=com.mysql.cj.jdbc.Driver
datasources.default.db-type=mysql
datasources.default.schema-generate=CREATE_DROP
datasources.default.dialect=MYSQL
datasources.default.url=jdbc:mysql://<MySQL IP address>:3306/demo
datasources.default.username=guide_user
datasources.default.password=<user password>

      Where <MySQL_IP_Address> is the public IP address of your MySQL database instance.

      Alternatively, you can export those via environment variables. From the terminal connected to the virtual machine, run:

      export DATASOURCES_DEFAULT_URL=jdbc:mysql://<MySQL IP address>:3306/demo
export DATASOURCES_DEFAULT_USERNAME=guide_user

    3. Build and test the application running:

      ./gradlew :gcp:run

      It builds the project and starts the application on port 8080.

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

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

echo "Result: $TEST_RES"

    5. Then list all genres:

      TEST_RES=$(curl -s localhost:8080/genres/list)
echo "Result: $TEST_RES"

    This server application presents REST endpoints and stores data in the Google MySQL database using Micronaut Data JDBC.

    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 a remote machine and run easily.

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

      ./gradlew :gcp:nativeCompile

      The native executable is created in the gcp/build/native/nativeCompile/ directory

    2. You can then run the native executable with the following command:

      gcp/build/native/nativeCompile/gcp-db-demo-gcp

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

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

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

echo "Result: $TEST_RES"

    4. Then list all genres:

      TEST_RES=$(curl -s localhost:8080/genres/list)
echo "Result: $TEST_RES"

    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.

    6. Stop the Instance

    Use the following command to stop the instance:

    gcloud sql instances patch $DB_INSTANCE_NAMEl \
    --activation-policy=NEVER

    7. Clean Up

    When you have completed the guide, you can clean up the resources you created on Google Cloud Platform so you will not be billed for them in the future.

    7.1. Delete the Project

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

    Deleting a project has the following consequences:

    • If you used an existing project, you will also delete any other work you have done in the project.

    • You cannot 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 guides, reusing projects instead of deleting them prevents you from exceeding project quota limits.

    7.1.1. Via the CLI

    To delete the project using the Google Cloud CLI, run the following command:

    gcloud projects delete gdk-guides

    7.1.2. Via the Cloud Platform Console

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

    2. In the project list, select the project you want to delete and click Delete project. Select the check box next to the project name and click Delete project.

    3. In the dialog box, enter the project ID, and then click Shut down to delete the project.

    Summary

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