Spring Cloud Using Google Cloud SQL and Google Cloud Storage

By amikblogger30 06/13/20239 Comments

Spring Cloud GCP makes the Spring Boot application a First Class citizen of Google Cloud Platform (GCP). GCP provides a cloud computing service which helps to run a Spring Boot application. Spring Cloud GCP, has a wide variety of features as mentioned below:

Features

Spring Data Cloud SQL
Spring Data Cloud Datastore
Spring Data Cloud Spanner
Spring Data Reactive Repositories for Cloud Firestore
Spring Cloud GCP Pub/Sub
Google Cloud Storage
Google Cloud Logging, Tracing, and Monitoring
Google Cloud Vision API Template
Google Cloud BigQuery
Spring Security from Google Cloud IAP or Firebase Headers, etc.

In this blog, we will use 2 components of GCP, one is Cloud SQL (a MySQL instance) and Cloud Storage. Cloud SQL will be used to store the transactional data and Cloud Storage will be used to store media files and those media URL as provided by Cloud Storage will also be captured in Cloud SQL.

Initial Setup

Before you start using the Google Cloud Platform, you have to register first. So let’s follow with the steps as mentioned below:

Open Google Cloud Console in the browser
Register with your personal Gmail ID or even you can register with your Official Mail ID. But here I have registered with my personal Gmail ID.

So the black marks represent the Email ID, your name, and in the top right corner the profile pics, etc.
Once you accepted the Terms and Agreement, now you are eligible to access different components of GCP.

N.B. Remember one thing that for the 1st time Google Cloud provides a $300 credit to use the services. At the top of the screen, you will be able to see that notification.

After you complete the initial steps you are now inside GCP. The screenshot of the GCP Interface is provided below:

In the above screenshot, some numbers are mentioned. A one-liner for all numbers are provided below.

Notify the user about $300 free credit
Navigation Menu from where the user will select the services/components and will use
Dashboard page which will account for the recent activities as performed by the user

GCP Components

In this blog, I have used 2 components of GCP, which are as follows:

Cloud SQL
Cloud Storage

Steps to Configure GCP Components

Configure Cloud SQL (MySQL) instance

First, you need to create a project in GCP.
- At the top left corner, you can able to see a dropdown as Select a Project
- Click on that
- It will open a page where you have to input the project name. Here I have defined the project name as spring-gcp-sql-storage.
- It will take a little bit of time to create the project. Once it is created, the dashboard will look like the below screenshot.

The highlighted yellow color is the name of the project.

N.B. The project name should be within 4–30 characters. You can’t exceed 30 characters.

After that from the Left navigation panel click on SQL. As SQL is a billing service, so for that first you have to create a Billing Account.
- Once you are done creating a Billing Account now that account will be tied up with your Project.
- Once this is paired now you can use the GCP Components which are paid service after your free trial expire

Here the project (spring-gcp-sql-storage) is connected with Billing Account ID (01F06A-412D98-86631C)

Now from the left navigation panel Click on SQL
- Click on Create Instance With Your Free Credits button
- After that, choose the Database Engine. For this blog, we will be using MySQL DB Engine.
- It will tell you that before creating a Database Engine, Enable the API
Click on Enable API button
- It will enable the API once you click the Enable API button
After that, you can create a MySQL Instance
- Provide the name of the instance
- If you want to keep a password to connect to an instance, you can keep it
- Set the configuration as Development
- Select the region, I have chosen the asia-south1 (Mumbai) region
- And select Single Zone from the Zonal Availability option
- Now click on the CREATE INSTANCE button below. It will take a little bit of time to create the instance.
The MySQL instance got created with configurations such as vCPUs (2), Memory (8 GB), and SSD Storage (100 GB). And the version of MySQL is 8.0.26 and the region is specified.
- Even you can see the Connection Name. For me the connection name is spring-gcp-sql-storage:asia-south1:gcp-sql-storage

Once the instance is created. From the left navigation panel click on the Database option
- Provide the name of the Database. I have provided the name sql-storage
- Click on CREATE button
After you successfully create the Database now you can use the Cloud Shell to connect to the database.

While connecting if you face any error like as mentioned in the below screenshot.

Then in that error statement it is mentioned to Enable the Cloud SQL Admin API. The link is provided in the error description as well. Click on that link and click on the ENABLE button. You can also navigate from the left panel APIs and Services. And check the Cloud SQL Admin API from the list of APIs enabled.

After that close the terminal and again click the Cloud Shell it will connect.

Command is : gcloud sql connect gcp-sql-storage –user=root –quiet

After that it will opt for password. If you have not selected any password then don’t need to provide. Click Enter and you are in the MySQL console.

Use the command as use sql-storage;. It will connect with the database. Here ‘sql-storage‘ is the name of the database.
You need to add IAM Roles to your Google account, by which you are able to connect from your Spring Boot Application to access the Google Cloud components. IAM Roles to access Google Cloud SQL are as follows:
- Cloud SQL Admin
- Cloud SQL Client
- Cloud SQL Editor
- Cloud SQL Instance User

Configure Cloud Storage

Click Cloud Storage from the left navigation panel
Then click Bucket
Click CREATE button at the top.
- Provide the name of the bucket. I have provided the name as gcp-sql-storage
- Choose the region. I have selected the option Region and choose the region as asia-south1 (Mumbai)
- Choose a storage class. I choose the Standard option
- Choose how to Control Access to Objects. I have selected the option Uniform.
- Choose the Protection Policy. I have chosen the None option
- Lastly, click the CREATE button below. And don’t provide Public access. Google by default will prevent that option.
- The bucket is created successfully.

To access Google Cloud Storage from your Spring Boot Application, you need to create IAM Roles as follows:
- Storage Object Admin
- Storage Object Creator
- Storage Object Viewer

Steps to install Google Cloud SDK

Download the Google Cloud SDK.
Install the SDK following the instructions on that same page.
After successful installation, it will run the gcloud init command
It will authenticate with your Google account that you are using in the Google Cloud Platform
Once authenticated, it will ask to select the Project, provide the no of the project.
Then it will ask for the Zone and Region. I have chosen the asia-south1-c region, which is available at option no 35.

Once completed, the Google Cloud CLI will exit.

N.B. If in the future you need to change the zone then the command is gcloud config set compute/zone NAME and if you want to change the region then the command is gcloud config set compute/region NAME for your project.

Steps to use the same Google account to connect/configure to various GCP Services from local

Open the Google Cloud SDK from your machine and run a command

gcloud auth application-default login

The above command will store the credentials of your Google account that you use in GCP in your local machine in a json file format. By default, it stores in the location as C:/Users/<UserName>/AppData/Roaming/gcloud/application_default_credentials.json.

The advantage is with the help of this json file you can connect to various GCP Services like Cloud SQL, Cloud Storage, etc.

Steps to install Cloud SQL Proxy

Whenever you want to connect to Cloud SQL from your local machine, you need a Proxy. Google itself provides a Proxy known as Cloud SQL Proxy by which one can connect to Google Cloud SQL locally.

Download the Google Cloud SQL Proxy from the official site of Google
Copy and paste the executable from the download folder to your suitable folder
Rename the file to cloud-sql-proxy.exe
Open the folder location from Command Prompt and run the command as

cloud-sql-proxy --port 3307 spring-gcp-sql-storage:asia-south1:gcp-sql-storage

It will run the Cloud SQL Proxy, and now you can connect to your Cloud SQL instance using any SQL Client. I have used DBeaver to connect to Cloud SQL.

Steps to configure Cloud SQL in SQL Client locally

I have used DBeaver as the SQL Client
I have used the MySQL Driver and then mention the required parameters to configure
Hostname – localhost
Port – 3307 (as Cloud SQL Proxy is running in port no 3307
Username and Password – As you have provided while creating the Cloud SQL instance in the GCP console.
Test the connection, if it connects successfully then you will be able to see as provided in the below screenshot

Now you can use the localhost-based connection string in your Spring Boot application.

N.B. Remember that whenever you finished your day’s work, you shut down the Cloud SQL Instance. And again before connecting check whether your Cloud SQL instance is running or not, after that try to connect to Cloud SQL Proxy and then use any SQL Client to connect to Cloud SQL DB.

A brief idea about Google Cloud Storage

Google Cloud Storage is a type of service offered by Google where one can store media files. It can store .pdf, .doc, .jpg, .jpeg, .png, .mp4, etc. types of media files. To use this service, one has to create a bucket, provide an understandable name for the bucket, and provide the storage capacity of the bucket. PFB, the screenshot is attached.

I have created a bucket and named gcp-sql-storage to store the media files. Inside this bucket, you can create folders based on your needs.

For my requirement, I have created 2 folders named sqlddls and sqlstorage. sqlddls folder is used to store the DDLs of tables used for this application. Cloud SQL used Cloud Storage to upload the DDLs of the required tables. And sqlstorage folder is used to store media files.

Discussion on spring-cloud-gcp-sql-storage-masterclass application

As we know that the introduction of spring-data makes Developer’s life very easier. While spring even undoubtedly makes strong bonding with Cloud Frameworks like GCP, AWS, Azure, etc. In this article, I have showcased one of the functionalities of Spring to use a cloud framework (GCP) to persist transactional data in Google Cloud SQL and persist media files in Google Cloud Storage. I have created a Demo application named spring-cloud-gcp-sql-storage-masterclass and uploaded it to my personal GitHub account. One can clone the project and test it locally. All the steps that are required to clone the project from GitHub and run it locally by installing the software are mentioned in the README.md file of the application.

Brief Description – In this application, I have used Java, Spring Boot, Maven, Spring-REST, Spring-Data, Google Cloud SQL, and Google Cloud Storage. Google Cloud SQL is used to persist the transactional data and Google Cloud Storage is used to store the media files like .jpg, .jpeg, .png, .mp4, .doc, .pdf, etc. And a reference is captured in Google Cloud SQL as well.
Software Used – Software required to develop this project.
- Spring Tool Suite-4.7.0-RELEASE – If the latest version is available then download that one
- Apache Maven 3.6.3 – If the latest version is available then download that one
- Java 8 – Not less than Java8
- Git 2.27.0 – Latest version as available
- MySQL Workbench 8.0 CE – MySQL DB Server – Community Edition
- SQLYog/DBeaver – One can use SQLYog/DBeaver in replacement of MySQL Workbench
- Google Cloud SDK – To configure the Google account as used in Google Cloud Console to configure the required services
- Cloud-SQL-Proxy – It is used to connect with Google Cloud SQL instance locally
- Postman v8.3.0 – To test the REST Service
- The list of the software used in this application and their required download links with the installation steps are briefly described in the README.md file of the spring-cloud-gcp-sql-storage-masterclass repository.
Project Components – Maven is used as the building tool and to provide the dependencies for this demo application. This application connects with one of the Cloud frameworks named Google Cloud Platform. For this, I define BOM (Bills of Materials) in pom.xml file. Let’s discuss this in brief.

<dependencyManagement>
	<dependencies>
		<dependency>
			<groupId>com.google.cloud</groupId>
			<artifactId>spring-cloud-gcp-dependencies</artifactId>
			<version>3.3.0</version>
			<type>pom</type>
			<scope>import</scope>
		</dependency>
		<dependency>
			<groupId>com.google.cloud</groupId>
			<artifactId>libraries-bom</artifactId>
			<version>26.12.0</version>
			<type>pom</type>
			<scope>import</scope>
		</dependency>
	</dependencies>
</dependencyManagement>

The above 2 dependencies spring-cloud-gcp-dependencies and libraries-bom of com.google.cloud groupId is used to load all the required google-cloud dependencies in support to develop the application.

After that, I defined some specific dependencies to connect with the necessary services of Google. The services that are being used: Google Cloud SQL and Google Cloud Storage. But here for Google Cloud SQL, we don’t need any dependencies as by using Cloud SQL Proxy we connect with the Cloud SQL interface of Google.

I have used spring-cloud-gcp, spring-cloud-gcp-starter dependencies to load all the spring-cloud-gcp dependencies. And I have used google-cloud-storage dependency to connect with Google Cloud Storage service.

<!-- Cloud GCP Dependencies -->
<dependency>
	<groupId>org.springframework.cloud</groupId>
	<artifactId>spring-cloud-gcp</artifactId>
	<version>1.0.0.RELEASE</version>
	<type>pom</type>
</dependency>
<dependency>
	<groupId>org.springframework.cloud</groupId>
	<artifactId>spring-cloud-gcp-starter</artifactId>
	<version>1.1.1.RELEASE</version>
</dependency>
<dependency>
	<groupId>com.google.cloud</groupId>
	<artifactId>google-cloud-storage</artifactId>
</dependency>

N.B. This is for those who are not able to connect to Google Cloud SQL using Cloud SQL proxy locally from the machine. During that time, use one dependency named spring-cloud-gcp-starter-sql-mysql.

<dependency>
	<groupId>org.springframework.cloud</groupId>
	<artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
	<version>1.1.1.RELEASE</version>
</dependency>

This dependency will let you allow to direct connect with Google Cloud SQL without installing Cloud SQL Proxy on your local machine. But the disadvantage of using this dependency is that you will never be able to run your application in your local system. For this, you need to run the application in Google Environment using EC2 instance concept, either using GKE (Google Kubernetes Engine) Cluster or Google Cloud Run.

In the next step, I have configured some properties in application.properties file. You can even use application.yml file to include those properties.

spring.cloud.gcp.project-id=spring-gcp-sql-storage

spring.cloud.gcp.credentials.location=file:<Folder location>/application_default_credentials.json

spring.cloud.gcp.storage.bucket-name=gcp-sql-storage
spring.cloud.gcp.bucket.dir=sqlstorage

spring.datasource.url=jdbc:mysql://localhost:3307/sql-storage
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

I would like to discuss a little bit on some properties as mentioned above:

spring.cloud.gcp.project-id=spring-gcp-sql-storage ⇾ This is the name of the project as created in Google Cloud Console using your own Google Account or Company Account.
spring.cloud.gcp.credentials.location=file:<Folder location>/application_default_credentials.json ⇾ If you want to connect your application from a local machine with Google Cloud Services, you need to provide the credentials of that account that you have used in Google Cloud Console to configure various services. This is a JSON file, and it contains the credentials in token format. Here, the red highlighted <Folder location>is the place in your hard drive where you keep this file. A detailed description is provided in the README.md file of the spring-cloud-gcp-sql-storage-masterclass repository.
spring.cloud.gcp.storage.bucket-name=gcp-sql-storage ⇾ gcp-sql-storage is the name of the bucket created in Google Cloud Storage service.
spring.cloud.gcp.bucket.dir=sqlstorage -> sqlstorage is the name of the folder where I am storing the media files used in the application and a reference in Cloud SQL.
spring.datasource.url=jdbc:mysql://localhost:3307/sql-storage ⇾ I have used a normal MySQL JDBC connection as I have connected Cloud SQL from my local machine using Cloud SQL Proxy.

N.B. If you are not able to connect with Google Cloud SQL locally by using Cloud SQL Proxy, then you are using one of the dependencies named spring-cloud-gcp-starter-sql-mysql. For this, dependency you have to define 2 new properties in your application.properties file as

spring.cloud.gcp.sql.database-name=sql-storage
spring.cloud.gcp.sql.instance-connection-name=spring-gcp-sql-storage:asia-south1:gcp-sql-storage

A brief about the 2 properties:

spring.cloud.gcp.sql.database-name=sql-storage ⇾ sql-storage is the name of the Database created in the Google Cloud SQL instance.
spring.cloud.gcp.sql.instance-connection-name=spring-gcp-sql-storage:asia-south1:gcp-sql-storage ⇾ This is the name of the instance spring-gcp-sql-storage:asia-south1:gcp-sql-storage. There is a nomenclature to define the name of the instance as <project-name>:<region-name>:<instance-name>. Here the project-name is spring-gcp-sql-storage, region-name is asia-south1 and instance-name is gcp-sql-storage.

Structure of the Project – If you are using Maven as the building tool, then the project structure is always the same. PFB the screenshot.

Spring-cloud integration with Cloud SQL and Cloud Storage – This application is a Spring Boot application and I have used google-cloud dependencies to interact with the Google Cloud components. But, as I have used Cloud SQL Proxy to connect with Google Cloud SQL locally, so there is no change in the spring-data implementation concept. But to connect to Google Cloud Storage I have used google-cloud-storage dependency which provides me certain files like StorageOptions, Storage, Blob, and Bucket.

A code snippet of the use of the above classes:

StorageOptions options = StorageOptions.newBuilder().setProjectId(gcpProjectId).build();
Storage storage = options.getService();
Bucket bucket = storage.get(gcpBucketId, Storage.BucketGetOption.fields());
Blob blob = bucket.create(gcpBucketDirectoryName + "/" + fileName, fileData, contentType);

A brief description:

StorageOptions ⇾ com.google.cloud.storage.StorageOptions class uses the Builder pattern to attach the Google project ID created in the Google console.
Storage ⇾ com.google.cloud.storage.Storage is an interface that gets the service from StorageOptions by calling the getService().
Bucket ⇾ com.google.cloud.storage.Bucket is a class that gets the information about the bucket created in the Google console by passing the name of the bucket ID.
Blob ⇾ com.google.cloud.storage.Blob is a class that deals with the blob data of the required file which will be uploaded in the Google Cloud Storage. It uses the Bucket’s class create() to create the required file in the Google Cloud Storage in the required bucket and in the required folder. This create() method also takes the name of the file and the content type of the file to upload. Once the required media file is uploaded to Google Cloud Storage, it returns the name and mediaLink. Here, the name is the name of the file that gets uploaded in Google Cloud Storage and the mediaLink is the media URL of the uploaded file.

Testing the application – In this application there is no UI component. So, I have used Postman to test the REST endpoint as defined.
1. You clone the application from GitHub and set up the application locally in any one of the IDEs like Spring Tool Suite (STS) or Eclipse.
2. Right-click on the application
3. Click the Option Run As
4. Select the option Spring Boot App.
5. It will start the application in port no 7120.

Below, I have provided the list of endpoints as available in this application.

Upload Files in GCP Cloud Storage – localhost:7120/uploadFile
Save a User in GCP Cloud SQL – localhost:7120/saveUser
Update a User in GCP Cloud SQL – localhost:7120/updateUser/{id}
Delete a User from GCP Cloud SQL – localhost:7120/deleteUser/{id}
Get user by Id from Cloud SQL – localhost:7120/getUser/{id}
Get all users from Cloud SQL – localhost:7120/allUsers

Sample JSON Data – I have provided 3 sample JSON data in support to upload files in GCP Cloud Storage, inserting a record in Cloud SQL DB, and update a record in Cloud SQL DB.

Upload File in Cloud Storage

“file” : “<Upload a file from your local system>”
“name” : “<Name of the file without extension>”
“fileName” : “<Name of the file with extension>”

Insert Request – JSON Data

{
“name” : “Test Name”,
“mobileNo” : “1234567890”,
“profilePhoto” : “sqlstorage/test.png”,
“profileUrl” : “https://storage.googleapis.com/download/storage/v1/b/gcp-sql-storage/o/sqlstorage%2Ftest.png?generation=1687665501945010&alt=media”
}

Update Request – JSON Data

{
“userId” : 1,
“name” : “Test Name”,
“mobileNo” : “1234567891”,
“profilePhoto” : “sqlstorage/test2.png”,
“profileUrl” : “https://storage.googleapis.com/download/storage/v1/b/gcp-sql-storage/o/sqlstorage%2Ftest2.png?generation=1687665501945010&alt=media”
}

Sample Data in Database

In this application, I first uploaded the file to Google Cloud Storage and in response, I got the name and mediaLink which I used to store the User data object in Google Cloud SQL. A sample data representation of the Cloud SQL is provided below:

Even you can verify the same data from the Google Cloud Console as well by connecting to the Cloud SQL instance from the console. Commands are as follows:

Click on the OPEN CLOUD SHELL link from the Cloud SQL instance page
It will open the terminal in Google Cud Console
Run the command as `gcloud sql connect gcp-sql-storage –user=root –quiet`
It will authorize and opt for a password.
Provide the password you have set while creating the database under the instance.
Once you provide the password, you are inside the mysql terminal now.
Run the command `show databases`. It will list all the active databases present.
Run the command `use <DBName>`.
Run the command `show tables`. It will list all the tables present inside that selected database.
Now run the select query on the desired table as `select * from <table_name>`

Last updated on 07/14/2024

amikblogger30

View All Posts

Features

Initial Setup

GCP Components

Steps to Configure GCP Components

Configure Cloud SQL (MySQL) instance

Configure Cloud Storage

Steps to install Google Cloud SDK

Steps to use the same Google account to connect/configure to various GCP Services from local

Steps to install Cloud SQL Proxy

Steps to configure Cloud SQL in SQL Client locally

A brief idea about Google Cloud Storage

Discussion on spring-cloud-gcp-sql-storage-masterclass application

Sample Data in Database

GitHub Code Link

Common Faced Problems

Interview FAQs

Other Useful Links of Spring Cloud using Google

Other Useful Links

9 Comments