Connect your application to Cloud SQL data and deploy on Cloud Run

1. Overview

With this codelab, we will demonstrate a simple, easy-to-do method for setting up Cloud SQL and connecting our application to it. It is a quick L100 level learning to upgrade the developer experience for integrating advanced AI applications to sophisticated data features that Cloud SQL has to offer.

What you'll build

A simple web application.

As part of this, you will:

1. Create a Cloud SQL for PostgreSQL instance in one click installation
2. Create a sample application to connect to this instance and set up data

Requirements

• A browser, such as Chrome or Firefox
• A Google Cloud project with billing enabled.

2. Before you begin

Create a project

1. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
2. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

For trying this codelab out, you can use the GCP Trial Billing Credit Link: https://bit.ly/ez-cloudsql1

How to redeem credits using this link? Refer.

3. You'll use Cloud Shell, a command-line environment running in Google Cloud. Click Activate Cloud Shell at the top of the Google Cloud console.

4. Once connected to Cloud Shell, you check that you're already authenticated and that the project is set to your project ID using the following command:

gcloud auth list

5. Run the following command in Cloud Shell to confirm that the gcloud command knows about your project.

gcloud config list project

6. If your project is not set, use the following command to set it:

gcloud config set project <YOUR_PROJECT_ID>

7. Enable the required APIs: Follow the link and enable the APIs.

Alternatively you can use the gcloud command for this. Refer documentation for gcloud commands and usage.

3. Database setup

In this lab we'll use Cloud SQL for PostgreSQL as the database for the test data.

Let's create a Cloud SQL instance where the test dataset will be loaded.

1. Click the button or Copy the link below to your browser where you have the Google Cloud Console user logged in.

One Click & Run

2. Once this step is complete the repo will be cloned to your local cloud shell editor and you will be able to run the command below from with the project folder (important to make sure you are in the project directory):

sh run.sh

3. Now use the UI (clicking the link in the terminal or clicking the "preview on web" link in the terminal.
4. Enter your details for project id and instance name to get started.
5. Go grab a coffee while the logs scroll & you can read about how it's doing this behind the scenes here.

4. Create the sample application to test the connection

In the Cloud Shell Terminal, run the following commands one by one:

git clone https://github.com/flazer99/verify-easy-cloud-sql-connection

cd verify-easy-cloud-sql-connection

Make changes to the verify_connection.py file for your Cloud SQL setup you just configured in the last section:

# Replace this with the Private IP of your Cloud SQL Instance
DB_HOST = <<>> 
DB_PORT = "5432"
DB_NAME = "postgres"
DB_USER = "postgres"
# Replace this with your actual Cloud SQL password    
DB_PASS = <<>>

If you want to test it locally or from anywhere, go to the Cloud SQL instance, click "EDIT" and click "Enable Public IP" or "Public IP Connectivity" (not the outbound one) and enter "0.0.0.0/0" in "Authorized External Networks" for development purposes but once done, remove & disable public ip connectivity.

5. Deploy to Cloud Run

In the Cloud Shell Terminal make sure you are inside your project folder.

Once you are sure you are in the project folder, run the following command:

gcloud run deploy verify-cloudsql \
    --source . \
    --region=us-central1 \
    --network=easy-cloudsql-vpc \
    --subnet=easy-cloudsql-subnet \
    --vpc-egress=all-traffic \
    --allow-unauthenticated

Once deployed, you should receive a deployed Cloud Run Endpoint that looks like this:

https://verify-cloudsql-**********-uc.a.run.app/

6. Demo

To confirm it worked, you can see the result on the browser or the logs explorer.

You can navigate to Cloud SQL on Google Cloud Console and open the newly created instance. Click Cloud SQL Studio from the navigation pane on the left and connect with your credentials.

In the studio, on the left pane, refresh the database objects and you should see the newly created table:

You can quickly query it to verify that the insert is successfully done:

7. Cleanup

Once this trial lab is done, do not forget to delete the Cloud SQL instance.

Go to https://console.cloud.google.com/sql/instances. Select the instance you want to delete by clicking the vertical ellipsis next to it and click DELETE.

It should clean up the cluster along with its instance(s).

8. Conclusion

Get started with setting up your data with Cloud SQL quick & easy!!!