Polling Application with CloudSQL & CloudRun

Configure the environment

1. Enable Cloud Run API
   gcloud services enable run.googleapis.com
2. Set the compute region
   gcloud config set compute/region us-central1
3. Create a LOCATION environment variable
   LOCATION=”us-central1″

Create a Cloud SQL instance

1. Create a PostgreSQL instance with the following values
   Instance ID: poll-database
   Password: secretpassword
   Database version: PostgreSQL 13
   Region: us-central1
   Zone: Single Zone
2. Click Create Instance
   

Populate the Cloud SQL instance

1. Connect to the Cloud SQL instance (Allowlist will be created for Cloud Shell IP)

   gcloud sql connect poll-database --user=postgres

2. Enter the Cloud SQL password when requested (i.e. “secretpassword”)
   Connect to the database

   \connect postgres;

3. Create the votes table

   CREATE TABLE IF NOT EXISTS votes
   ( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL,
   candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );

4. Create the totals table

   CREATE TABLE IF NOT EXISTS totals
   ( total_id SERIAL NOT NULL, candidate VARCHAR(6) NOT NULL,
   num_votes INT DEFAULT 0, PRIMARY KEY (total_id) );

5. Initialise the data for Tabs

   INSERT INTO totals (candidate, num_votes) VALUES ('TABS', 0);

6. Initialise the data for Spaces

   INSERT INTO totals (candidate, num_votes) VALUES ('SPACES', 0);

Deploy a public service

1. Set the environment variables for the Cloud SQL connection

   CLOUD_SQL_CONNECTION_NAME=$(gcloud sql instances describe poll-database --format='value(connectionName)')

2. Deploy poll Cloud RUN service

   gcloud beta run deploy poll-service \
   --image gcr.io/qwiklabs-resources/gsp737-tabspaces \
   --region $LOCATION \
   --allow-unauthenticated \
   --add-cloudsql-instances=$CLOUD_SQL_CONNECTION_NAME \
   --set-env-vars "DB_USER=postgres" \
   --set-env-vars "DB_PASS=secretpassword" \
   --set-env-vars "DB_NAME=postgres" \
   --set-env-vars "CLOUD_SQL_CONNECTION_NAME=$CLOUD_SQL_CONNECTION_NAME"

3. The Cloud Run service endpoint can be accessed as per below:

   POLL_APP_URL=$(gcloud run services describe poll-service --platform managed --region us-central1 --format="value(status.address.url)")

Testing the application

The Poll service should look similar to below: