Quickstart for Cloud SQL using Cloud Run

This page shows you how to connect to Cloud SQL using Cloud Run (fully managed).

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. In the Cloud Console, on the project selector page, select or create a Cloud project.

    Go to the project selector page

  3. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.

Project setup requirements:

  • The name for your project must be between 4 and 30 characters.
  • When you type the name, the form suggests a project ID, which you can edit.
  • The project ID must be between 6 and 30 characters, with a lowercase letter as the first character.
  • You can use a dash, lowercase letter, or digit for the remaining characters, but the last character cannot be a dash.

Create an instance

In this procedure, you use the Cloud Console. To use gcloud or cURL, see Creating instances.

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select your project and click Continue.

  3. Click Create Instance.

  4. Click PostgreSQL.

  5. Enter myinstance for Instance ID.

  6. Enter a password for the postgres user.

  7. Use the default values for the other fields.

  8. Click Create.

    You are returned to the instances list; you can click into the new instance right away to see the details, but it won't be available until it initializes and starts.

Connect to your instance using the psql client in Cloud Shell

  1. In the Google Cloud Console, click the Cloud Shell icon (Cloud Shell icon) in the upper right corner.

    When Cloud Shell finishes initializing, you should see:

    Welcome to Cloud Shell! Type "help" to get started.
    username@example-id:~$
    

  2. At the Cloud Shell prompt, connect to your Cloud SQL instance:

    gcloud sql connect myinstance --user=postgres
    
  3. Enter your postgres password.

    You should see the psql prompt.

Create a database and upload data

  1. Create a SQL database on your Cloud SQL instance:
    CREATE DATABASE guestbook;
    
  2. Insert sample data into the postgres database:
    CREATE TABLE guestbook (guestName VARCHAR(255), content VARCHAR(255),
                            entryID SERIAL PRIMARY KEY);
    INSERT INTO guestbook (guestName, content) values ('first guest', 'I got here!');
    INSERT INTO guestbook (guestName, content) values ('second guest', 'Me too!');
    
  3. Retrieve the data:
    SELECT * FROM guestbook;
    
    You should see:
      guestname   |   content   | entryid
    --------------+-------------+---------
     first guest  | I got here! |       1
     second guest | Me too!     |       2
    (2 rows)
    postgres=>
    

Get the instance connection name

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Click the instance name to open its Instance details page.

  3. Under Connect to this instance, note the Instance connection name.

Enable the Cloud SQL Admin API

Enable the API

Configuring service accounts for Cloud Run (fully managed)

Like any configuration change, setting a new configuration for the Cloud SQL connection leads to the creation of a new Cloud Run revision. Subsequent revisions will also automatically get this Cloud SQL connection unless you make explicit updates to change it.

Console

  1. Go to Cloud Run

  2. Configure the service:

    • If you are adding a Cloud SQL connection to a new service:

      • You need to have your service containerized and uploaded to the Container Registry. If you don't already have one, see these instructions about building and deploying a container image.

      • Click CREATE SERVICE.

    • If you are adding Cloud SQL connections to an existing service:

      • Click on the service name.

      • Click DEPLOY NEW REVISION.

  3. Enable connecting to a Cloud SQL:

    • Click SHOW OPTIONAL SETTINGS:

    Add Cloud SQL connection

    • If you are adding a connection to a Cloud SQL instance in your project, select the desired Cloud SQL instance from the dropdown menu.
    • If you are using a Cloud SQL instance from another project, select custom connection string in the dropdown and then enter the full instance connection name in the format PROJECT-ID:REGION:INSTANCE-ID.

    • If you are deleting a connection, hover your cursor to the right of the connection to display the Trash icon, and click it.

  4. Click Create or Deploy.

Command line

Before using any of the commands below, make the following replacements:

  • IMAGE with the image you are deploying
  • SERVICE-NAME with the name of your Cloud Run service
  • INSTANCE-CONNECTION-NAME with the instance connection name of your Cloud SQL instance, or a comma delimited list of connection names.

If you are deploying a new container, use the following command:

gcloud run deploy --image IMAGE 
--add-cloudsql-instances INSTANCE-CONNECTION-NAME
--update-env-vars INSTANCE_CONNECTION_NAME="INSTANCE-CONNECTION-NAME"
` If you are updating an existing service, use the following command:
gcloud run services update SERVICE-NAME 
--add-cloudsql-instances INSTANCE-CONNECTION-NAME
--update-env-vars INSTANCE_CONNECTION_NAME="INSTANCE-CONNECTION-NAME"

Cloud Run (fully managed) uses a service account to authorize your connections to Cloud SQL. This service account must have the correct IAM permissions to successfully connect. Unless otherwise configured, the default service account is in the format PROJECT_NUMBER-compute@developer.gserviceaccount.com.

You can control whether other applications or clients can connect to your Cloud SQL instance. For more information, see Authentication options to determine who is allowed to connect to your instance and from where.

When connecting resources in two different projects, make sure that both projects have enabled the correct IAM roles and have given the service account the correct permissions.

Ensure that the service account for your service has one of the following IAM roles:

  • Cloud SQL Client (preferred)
  • Cloud SQL Editor
  • Cloud SQL Admin

Or, you can manually assign the following IAM permissions:

  • cloudsql.instances.connect
  • cloudsql.instances.get

For detailed instructions on adding IAM roles to a service account, see Granting Roles to Service Accounts.

Prepare the code

All of the Cloud Run (fully managed) code you need for connecting to a Cloud SQL database is below. Some of the variable values depend on your own database information.

This Quickstart uses Python 3.7+.

The settings in requirements.txt tells Python which modules it needs to import:

SQLAlchemy==1.3.12

pg8000==1.13.2

Dockerfile uses the official lightweight Python image:

# https://hub.docker.com/_/python
FROM python:3.7-slim

COPY requirements.txt ./

# Install production dependencies.
RUN set -ex; \
    pip install -r requirements.txt; \
    pip install gunicorn

# Copy local code to the container image.
ENV APP_HOME /app
WORKDIR $APP_HOME
COPY . ./

# Install production dependencies.
RUN pip install Flask gunicorn

# Run the web service on container startup. Here we use the gunicorn
# webserver, with one worker process and 8 threads.
# For environments with multiple CPU cores, increase the number of workers
# to be equal to the cores available.
CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 main:app

The main.py file contains all the code used in this Quickstart:

import os
import sqlalchemy

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello_world():

  // Set the following variables depending on your specific
  // connection name and root password from the earlier steps:
  connection_name = "[INSTANCE_CONNECTION_NAME]"
  db_password = "[DATABASE_USER_PASSWORD]"
  db_name = "guestbook"

  db_user = "postgres"
  driver_name = 'postgres+pg8000'
  query_string =  '"unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(connection_name)'


  db = sqlalchemy.create_engine(
    sqlalchemy.engine.url.URL(
      drivername=driver_name,
      username=db_user,
      password=db_password,
      database=db_name,
      query={query_string},
    ),
    pool_size=5,
    max_overflow=2,
    pool_timeout=30,
    pool_recycle=1800
  )

  stmt = sqlalchemy.text('INSERT INTO entries (guestName, content) values ("another guest", "Also this one");')
  try:
    with db.connect() as conn:
      conn.execute(stmt)
  except Exception as e:
    return 'Error: {}'.format(str(e))
    return 'Hello! You just wrote something into your database!\n'.format(target)

if __name__ == "__main__":
    app.run(debug=True,host='0.0.0.0',port=int(os.environ.get('PORT', 8080)))

The service.yaml file defines the application name and docker container:

apiVersion: serving.knative.dev/v1
kind: Service
metadata:
  name: APP_NAME
  namespace: default
spec:
  template:
    spec:
      containers:
      - image: gcr.io/USER_NAME/APP_NAME
        env:
        - name: TARGET
          value: "Python Sample v1"

The .dockerignore file tells docker which files to ignore:

Dockerfile
README.md
*.pyc
*.pyo
*.pyd
__pycache__

Build, submit and deploy the container

  1. Create a new directory.
  2. Copy the following files from the steps above into the new directory:
    • requirements.txt
    • Dockerfile
    • main.py
    • service.yaml
    • .dockerfile
  3. Replace the variables USER_NAME and APP_NAME in the system.yaml file and set your database-specific values in main.py.
  4. Build the container image:
       
    gcloud builds submit --tag gcr.io/[PROJECT_ID]/run-sql
       
       
  5. Deploy the service to Cloud Run (fully managed):
       
    gcloud run deploy run-sql --image gcr.io/[PROJECT_ID]/run-sql
       
       

    Note the URL output at the end of the deployment process.

  6. Configure the service for use with Cloud Run (fully managed):
       
    gcloud run services update run-sql \
      --add-cloudsql-instances [INSTANCE_CONNECTION_NAME] \
      --set-env-vars CLOUD_SQL_CONNECTION_NAME=[INSTANCE_CONNECTION_NAME],\
      DB_USER=postgres,DB_PASS=[DATABASE_USER_PASSWORD],DB_NAME=guestbook
       
       

    Replace the variables with the correct values for your Cloud SQL configuration.

Test the application

  1. In a browser, navigate to the URL noted in Step 2 above.
  2. Verify that you see this message: Hello! You just wrote something into your database.
  3. Connect to your database and enter select * from guestbook to see the new entry.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this quickstart, follow these steps.

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
    Go to the Cloud SQL Instances page
  2. Select the myinstance instance to open the Instance details page.
  3. In the icon bar at the top of the page, click Delete.
  4. In the Delete instance window, type myinstance, then click Delete to delete the instance.

    You cannot reuse an instance name for about 7 days after an instance is deleted.

What's next