How to make an API which interacts with Google Cloud SQL using Choreo — Pt 1

In this two series blog, we would jump into implementing a REST API using Choreo that interacts with Google Cloud SQL. If you’re interested in getting familiar and playing around with Choreo then this is for you.

In part 1 (aka in a few moments), we’ll be setting up Google Cloud SQL where your database will be and in part 2 we’ll be using Choreo to develop the API to interact with our Cloud SQL hosted database.

For those wondering what Choreo is, you can get a thorough understanding by visiting: https://wso2.com/choreo/ which includes video tutorials to architecture diagrams on how it works.

Choreo Site

For the ones who didn’t visit the Choreo site, here’s a small intro on Choreo:

“What is choreo? The site actually covers everything ;)” — Areeb Niyas

So let’s cut to the chase and focus on developing the API.

Setting up Google Cloud SQL

1. Register and sign into the Google Cloud SQL at https://cloud.google.com/sql

Note — Don’t worry about entering your card details, shut down the project after the end of this interactive blog, so everything will be under the free tier. Google won’t charge you without notifying.

“I did not get charged on my card, so you won’t too” — Areeb Niyas

2. You will be redirected into the google cloud console. If not visit: https://console.cloud.google.com/getting-started and click on Cloud SQL or type in SQL on the search bar and click on SQL under products and pages.

Google Cloud Console

3. You will land on the page shown below where you can click on create project and set the project name to ‘sample’ or whatever you wish.

Create project

4. Then you will be redirected to the Cloud SQL Instances page where you need to click on create instance. For this instance we will be using MySQL for convenience so go ahead and choose your database engine as MySQL.

5. Enable the compute engine API if you already haven’t and lets get into creating a MySQL instance,

6. Where you only have to add in 3 things,

• Instance-ID- sample-instance
• Password- sample123
• Region- asia-southeast1(singapore) or whichever region is closest to you.

Creating a MySQL Instance

• Scroll to the bottom and click on Create Instance. It may take upto 5 minutes for the instance to be created so till then lets create a user, database and configure IPs using the dashboard.

Creating user, database and configuring connections via dashboard

• Since Choreo will make connections to our database via IPs we need to white list these IPs and allow traffic only from these IPs. (https://wso2.com/choreo/resources/access-your-rdbms-data-using-choreo/). So lets click on the connections tab which will redirect you to:

Click on add network and add the shown IPs

• You can choose whatever name, but make sure to add the two shown IPs (20.22.170.144/28) and (20.22.170.176/28) as mentioned in the prerequisites section in this documentation & in this alternate documentation as well.
• Then go to the users tab to your left and click on add user account. Add username and password as root and keep the rest of the default configurations.

‘Don’t mess around with default configurations ’ — Areeb Niyas

• Then go to the databases tab to your left and click on create database. Let’s set the database name to sampledb, leave the other default configurations because we don’t want to tamper with google because of the previous quote and voila we’re good to go and check if our instance is ready.

7. After the instance has been created, a public IP address will appear. Make sure to note it down as we will be using this to access our database from Choreo. Now we will get into creating our tables and add some sample data so go ahead and open the cloud shell.

Open cloud shell

8. The terminal should popup and you should be able to see gcloud sql connect sample-instance -user=root -quietalready typed in for you so just press enter to access your SQL shell.

• It will ask you to authorize the cloud shell, click on authorize.
• Enter root as password
• Also, if you run into an error trying to access your instance. Check if Cloud SQL Admin API is enabled.

9. Let’s create our table in the previously created database (sampledb) using MySQL commands.

USE sampledb;
CREATE TABLE persons (
    nic varchar(255) NOT NULL PRIMARY KEY,
    name varchar(255)
);
INSERT INTO persons
VALUES (200055110036,"John Doe"), (200044221234,"Jane Doe")

Cloud Shell Terminal

Boom simple as that, now our database and tables with sample data are ready! Now all we have left is to make the API using Choreo’s cool features.

See you on the next part!