Connecting to Private CloudSQL from Cloud Run

1. Overview

In this lab, you will create the Cymbal Eats menu service, exposing RESTful APIs to add, update, delete, and list menu items. You will create a Cloud SQL database as the backend database for the menu service, which will run in Cloud Run. Because Cloud Run does not reside in the same VPC as the Cloud SQL database, you will need to configure a Serverless VPC Access connector to allow Cloud Run to communicate with Cloud SQL via a private IP address.

What you will learn

In this lab, you will learn how to do the following:

• Configure Private VPC Network
• Create Private Postgres Cloud SQL Database
• Connect CloudRun to Private VPC
• Deploy a Service on Cloud Run that connects to the Cloud SQL database

2. Setup and Requirements

Self-paced environment setup

1. Sign-in to the Google Cloud Console and create a new project or reuse an existing one. If you don't already have a Gmail or Google Workspace account, you must create one.

• The Project name is the display name for this project's participants. It is a character string not used by Google APIs. You can update it at any time.
• The Project ID is unique across all Google Cloud projects and is immutable (cannot be changed after it has been set). The Cloud Console auto-generates a unique string; usually you don't care what it is. In most codelabs, you'll need to reference the Project ID (it is typically identified as PROJECT_ID). If you don't like the generated ID, you may generate another random one. Alternatively, you can try your own and see if it's available. It cannot be changed after this step and will remain for the duration of the project.
• For your information, there is a third value, a Project Number which some APIs use. Learn more about all three of these values in the documentation.

Caution: A project ID is globally unique and cannot be used by anyone else after you've selected it. You are the only user of that ID. Even if a project is deleted, the ID can never be used again

Note: If you're using a Gmail account, you can leave the default location set to No organization. If you're using a Google Workspace account, then choose a location that makes sense for your organization.

2. Next, you'll need to enable billing in the Cloud Console to use Cloud resources/APIs. Running through this codelab shouldn't cost much, if anything at all. To shut down resources so you don't incur billing beyond this tutorial, you can delete the resources you created or delete the whole project. New users of Google Cloud are eligible for the $300 USD Free Trial program.

Environment Setup

1. Create project and resource related environment variables

export PROJECT_ID=$(gcloud config get-value project)
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format='value(projectNumber)')
export PROJECT_NAME=$(gcloud projects describe $PROJECT_ID --format='value(name)')
export REGION=us-east1
export MENU_SERVICE_NAME=menu-service

export SERVERLESS_VPC_CONNECTOR=cymbalconnector
export DB_INSTANCE_NAME=menu-catalog
export DB_INSTANCE_PASSWORD=password123
export DB_DATABASE=menu-db
export DB_USER=menu-user
export DB_PASSWORD=menupassword123

2. Clone the repository and navigate to the directory

git clone https://github.com/GoogleCloudPlatform/cymbal-eats.git && cd cymbal-eats/menu-service

3. Enable the services

gcloud services enable \
    sqladmin.googleapis.com \
    run.googleapis.com \
    vpcaccess.googleapis.com \
    servicenetworking.googleapis.com

3. Configure Private Access

Private services access is provided as a VPC peering link between your VPC network and the underlying Google Cloud VPC network where your Cloud SQL instance is located. The private connection allows VM instances in your VPC network and the services you use to communicate solely through internal IP addresses. To access services that are available through private services access, VM instances do not require Internet connectivity or external IP addresses.

4. Allocate an IP address range

gcloud compute addresses create google-managed-services-default \
    --global \
    --purpose=VPC_PEERING \
    --prefix-length=20 \
    --network=projects/$PROJECT_ID/global/networks/default

Example output

Created [https://www.googleapis.com/compute/v1/projects/cymbal-eats-2-348215/global/addresses/google-managed-services-default].

You can also provide address range with --addresses option in the command above. When you omit the address range, Google Cloud automatically selects an unused address range in your VPC network.

5. Create a private connection.

gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=google-managed-services-default \
    --network=default \
    --project=$PROJECT_ID

The command initiates a long-running Cloud SQL instance operation, returning an operation ID.

Example output

Operation "operations/pssn.p24-528514492617-2f2b507f-e4e5-4d53-a4de-9ddaceb4e92f" finished successfully.

4. Setting up Cloud SQL

Cloud SQL is a fully-managed database service that makes it simple to set up, maintain, manage, and administer your PostgreSQL and MySQL relational databases in the cloud. Each Cloud SQL instance is powered by a virtual machine (VM) that runs on a Google Cloud host server. The high availability option also includes a standby VM in another zone with the same setup as the primary VM. The database is kept on a scalable, long-lasting network storage device known as a persistent disk, which is connected to the VM. A static IP address is assigned to each VM to ensure that the IP address to which an application connects remains constant during the life of the Cloud SQL instance.

You will create a Postgres Cloud SQL database with a private IP address.

Create a database and user

6. Create a Postgres Cloud SQL instance to use a private IP

gcloud sql instances create $DB_INSTANCE_NAME \
    --project=$PROJECT_ID \
    --network=projects/$PROJECT_ID/global/networks/default \
    --no-assign-ip \
    --database-version=POSTGRES_12 \
    --cpu=2 \
    --memory=4GB \
    --region=$REGION \
    --root-password=${DB_INSTANCE_PASSWORD}

Example output

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/cymbal1/instances/menu-instance].
NAME: menu-instance
DATABASE_VERSION: POSTGRES_12
LOCATION: us-east1-a
TIER: db-custom-2-4096
PRIMARY_ADDRESS: -
PRIVATE_ADDRESS: 10.8.80.5
STATUS: RUNNABLE

The –no-assign-ip flag is used to disable the instance's public IP.

7. Add a database to the database instance

gcloud sql databases create $DB_DATABASE --instance=$DB_INSTANCE_NAME

Example output

Created database [menu-db].
instance: menu-catalog
name: menu-db
project: cymbal1

8. Create a SQL user

gcloud sql users create ${DB_USER} \
    --password=$DB_PASSWORD \
    --instance=$DB_INSTANCE_NAME

Example output

Created user [menu-user].

9. Store the database IP address

export DB_INSTANCE_IP=$(gcloud sql instances describe $DB_INSTANCE_NAME \
    --format=json | jq \
    --raw-output ".ipAddresses[].ipAddress")

10. Add the Cloud SQL Client role to Compute Engine service account

gcloud projects add-iam-policy-binding $PROJECT_ID \
--member="serviceAccount:$[e-mail]" \
--role="roles/cloudsql.client"

Example output

Updated IAM policy for project [cymbal1].
[...]

5. Serverless VPC

Serverless VPC Access allows you to connect directly to your Virtual Private Cloud network from serverless environments such as Cloud Run, App Engine, or Cloud Functions. Configuring Serverless VPC Access allows your serverless environment to send requests to your VPC network using internal DNS and internal IP addresses (as defined by RFC 1918 and RFC 6598). The responses to these requests also use your internal network.

You will create a Serverless VPC Access connector for the Cloud Run service to connect to Cloud SQL.

1. Create a Serverless VPC Access connector in the same VPC network as your Cloud SQL instance.

gcloud compute networks vpc-access connectors create ${SERVERLESS_VPC_CONNECTOR} \
    --region=${REGION} \
    --range=10.8.0.0/28

Example output

Created connector [cymbalconnector].

The –range flag must not overlap with any existing IP address reservations in your VPC network. For example, 10.8.0.0/28 works in most new projects.

6. Deploying to Cloud Run

You will build and deploy a Docker image to Cloud Run and connect the Cloud Run to the Serverless VPC connector to access the Cloud SQL database.

1. Compile the application using maven

./mvnw package -DskipTests

Example output

[...]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  42.864 s
[INFO] Finished at: 2022-04-28T16:15:33Z
[INFO] ------------------------------------------------------------------------

2. Build docker image:

docker build -f src/main/docker/Dockerfile.jvm \
    --tag gcr.io/$PROJECT_NAME/menu-service .

Example output

[...]
Successfully built 4ef5d7a3befc
Successfully tagged gcr.io/cymbal1/menu-service:latest

3. Push docker image to container registry:

docker push gcr.io/$PROJECT_NAME/menu-service

Example output

Using default tag: latest
The push refers to repository [gcr.io/cymbalsql/menu-service]
17b374963800: Pushed
d9a51c06430d: Pushed
fff5d2a2cfc9: Pushed
f21fceb558c6: Pushed
5ffbbbf218dd: Pushed
60609ec85f86: Layer already exists
f2c4302f03b8: Layer already exists
latest: digest: sha256:f64cb7c288dbf4ad9b12bd210c23c5aec1048dee040450ff2d9dbdf96e83a426 size: 1789

4. Deploy Menu service:

gcloud run deploy $MENU_SERVICE_NAME \
    --image=gcr.io/$PROJECT_NAME/menu-service:latest \
    --region $REGION \
    --allow-unauthenticated \
    --set-env-vars DB_USER=$DB_USER \
    --set-env-vars DB_PASS=$DB_PASSWORD \
    --set-env-vars DB_DATABASE=$DB_DATABASE \
    --set-env-vars DB_HOST=$DB_INSTANCE_IP \
    --vpc-connector $SERVERLESS_VPC_CONNECTOR \
    --project=$PROJECT_ID \
    --quiet

Example output

[...]
Done.
Service [menu-service] revision [menu-service-00002-xox] has been deployed and is serving 100 percent of traffic.
Service URL: https://menu-service-g2mfphytdq-uk.a.run.app

Google recommends that you use Secret Manager to store sensitive information such as SQL credentials. You can pass secrets as environment variables or mount as a volume with Cloud Run.

5. Store Menu service URL:

MENU_SERVICE_URL=$(gcloud run services describe menu-service \
  --platform managed \
  --region $REGION \
  --format=json | jq \
  --raw-output ".status.url")

6. Verify the menu service URL

echo $MENU_SERVICE_URL

Example output

https://menu-service-g2mfphytdq-uk.a.run.app

7. Testing the service

1. Create new menu item by sending POST request:

curl -X POST "${MENU_SERVICE_URL}/menu" \
  -H 'Content-Type: application/json' \
  -d '{
       "itemImageURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
       "itemName": "Curry Plate",
       "itemPrice": 12.5,
       "itemThumbnailURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
       "spiceLevel": 3,
       "status": "Ready",
       "tagLine": "Spicy touch for your taste buds!!"
   }'

Example output

{
    "id": 16,
    "createDateTime": "2022-04-28T18:14:04.17225",
    "itemImageURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
    "itemName": "Curry Plate",
    "itemPrice": 12.5,
    "itemThumbnailURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
    "spiceLevel": 3,
    "status": "Processing",
    "tagLine": "Spicy touch for your taste buds!!",
    "updateDateTime": "2022-04-28T18:14:04.172298"
}

2. Change status for menu item by sending PUT request:

curl -X PUT "${MENU_SERVICE_URL}/menu/1" \
  -H 'Content-Type: application/json' \
  -d '{"status": "Ready"}'

Example output

{
    "id": 1,
    "createDateTime": "2022-04-28T17:21:02.369093",
    "itemImageURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
    "itemName": "Curry Plate",
    "itemPrice": 12.50,
    "itemThumbnailURL": "https://images.unsplash.com/photo-1631452180519-c014fe946bc7?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1587&q=80",
    "spiceLevel": 0,
    "status": "Ready",
    "tagLine": "Spicy touch for your taste buds!!",
    "updateDateTime": "2022-04-28T17:21:02.657636"
}

8. Congratulations!

Congratulations, you finished the codelab!

What's next:

Explore other Cymbal Eats codelabs:

• Triggering Cloud Workflows with Eventarc
• Triggering Event Processing from Cloud Storage
• Connecting to Fully Managed Databases from Cloud Run
• Secure Serverless Application with Identity Aware Proxy (IAP)
• Triggering Cloud Run Jobs with Cloud Scheduler
• Securely Deploying to Cloud Run
• Securing Cloud Run Ingress Traffic
• Connecting to private AlloyDB from GKE Autopilot

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

Deleting the project

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