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

In this part 2 of 2, we will be using Choreo to develop an API that does basic CRUD operations to our previously setup google cloud SQL database.

If you missed part 1, make sure to head over to part 1 to setup your database first.

Developing the API using Choreo

1. Register and sign into the choreo console by clicking Try Choreo Now at: https://wso2.com/choreo/
2. You will be redirected into the Choreo console where you you will have to click on REST API under Get started with a component.

Choreo Console

3. We will be making the API from scratch, so click on ‘Start from scratch’ when the popup appears after clicking on REST API.

Then lets call our API idcheck and click on next to choose the source repository.

REST API Component Details

Choose ‘Choreo Managed Repository’ and click on create and watch Choreo do the magic!

4. You will be redirected into the repository Choreo created for you where you should click on Edit Code to start developing our requests.

Choreo Repository

5. This will launch the web editor which will show you the low code diagram.

“VS Code on Chrome? Yes sir that’s Choreo for you:D” — You know who

Lets delete the existing GET request which is there by default and start with configuring our record. So click on the delete icon on the get request and click on the top most ‘+’ icon on the low code diagram.

Choreo Web Editor

6. There will be a popup to your right, click Record from that under constructs which will redirect you to the record configuration,

• change record_name -> Person
• Add two fields:
  -String nic
  -String name

Record configuration

Click on Save on the bottom right to save the record.

“Why do we need this record? I had the same question. It will make sense when we write the SQL queries. Hang in there!”- Me again

7. Now we will add in configurables so that we don’t expose any of our cloud SQL credentials in the code.

“Cool kids use best practices :)” — a cool kid

Now lets have a taste of manually editing the source code. Best of both worlds. Click on the show source code icon to your top right and add following lines of code below record:

// MySQL configuration parameters
configurable string host = ?;
configurable string user = ?;
configurable string password = ?;
configurable string database = ?;

Adding lines 10–14 in source code

You will notice changes in your diagram too as soon as you paste the code as well. How cool is that! So now you’re starting to get the idea of Ballerina. Low code diagram is source code, source code is low code diagram.

Lets close the source code and now configure our connector using the diagram to talk to our database.

8. Click on the ‘+’ icon right below the string database configurable and click on Connector. This will popup the connectors supported by Choreo and in our mini exercise we’ll be using the mysql/client by ballerinax so search it up and click on it.

MySQL Client connector by ballerinax

This will take you the client connector configurations where you configure your parameters for the connector. Click on show optional fields under the parameters tab and click the check boxes for:

• host
• user
• password
• database

Also, it will show you the source code being added on the top section where we can make changes to that as well. Lets double tap on each parameter and set it to our configurables instead of just quotes. For example:

• host = "" — > host = host
• user = "" — > user = user

and continue for password and database as well like shown below:

mysql client connector configurations

Click on save and voila now we’re ready to make our HTTP requests to interact with our database!

9. Now lets add a GET request to our HTTP service by clicking on the ‘+’ icon inside the HTTP service and configure our request.

Configuring our HTTP request

• change Resource Path -> persons
• add a Parameter -> Type string& Param Name nic -> click save
• change Return Type -> Person|error? -> click save

10. Then we add an Action construct to the new request by expanding the GET request in the diagram and clicking on the ‘+’ icon in between start and end.

• Click the mysqlEp from the next tab.
• Select queryRow as the action.
• Click save and lets make some changes to the code directly -> so go to show source code.

Lets replace the get persons function with the code given below:

isolated resource function get persons(string nic) returns Person|error? {
Person|error queryRowResponse = mysqlEp->queryRow(sqlQuery = `select * from persons where nic=${nic}`);
return queryRowResponse;
}

• Also, add final before the mysql client (like shown in line no 16 below). This is done because it is recommended to use isolated functions, without the final keyword it'll throw an error.

So your final source code should look like this:

Final source code (try not to just copy paste this:D)

Voila, simple as that!

Now let’s run our API and test it using the TryIt button.

Running our API

• Click on the 3 dots to expand the menu on the right hand side of the HTTP service
• Click on run
• Set out configurables according the cloud instance we created in part 1 host -> set this to the public ip of your cloud sql
username -> root
password -> root
database -> sampledb
• Click on run

Then after the service is running, lets go TryIt out and see if it works!

Trying out our HTTP request

Clicking the TryIt button will open up the swagger tab where you can test your API.

• Expand the GET request and click on Try it out
• Enter one of the values which we added previously to our database - 200044221234 and click on execute.

There we go! You will be able to successfully see the retrieved data. Hope you enjoyed the interactive tutorial on how to make an API using Choreo.

Hoping to cover the deployment aspect in a future blog till then you can refer Step 2: Deploy in the following link to deploy your API and test it out using the Choreo console too!

• https://wso2.com/choreo/docs/tutorials/create-your-first-rest-api/

Stay tuned for more interesting and interactive blogs!