Connecting to Cloud SQL with Cloud Run

Learn how to connect to Cloud SQL using Cloud Run (fully managed).

You will find this tutorial helpful if you:

  • Run applications on Cloud Run (fully managed).
  • Use Cloud SQL as your database.

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.

Create an instance

In this procedure, you use the Cloud Console. To use the gcloud command-line tool, cURL, or PowerShell, 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 MySQL.

  5. Enter myinstance for Instance ID.

  6. Enter a password for the root 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 mysql 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, the following appears:

    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=root
    
  3. Enter your root password.

    The mysql prompt appears.

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 guestbook database:
    USE guestbook;
    CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),
        entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
        INSERT INTO entries (guestName, content) values ("first guest", "I got here!");
        INSERT INTO entries (guestName, content) values ("second guest", "Me too!");
    
  3. Retrieve the data:
    SELECT * FROM entries;
    
    The result is:
    +--------------+-------------------+---------+
    | guestName    | content           | entryID |
    +--------------+-------------------+---------+
    | first guest  | I got here!       |       1 |
    | second guest | Me too!           |       2 |
    +--------------+-------------------+---------+
    2 rows in set (0.00 sec)
    mysql>
    

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)

  • Make sure that the instance created earlier has a public IP address. You can verify this on the Overview page for your instance in the Google Cloud Console. If you need to add one, see the Configuring public IP page for instructions.
  • Get the INSTANCE_CONNECTION_NAME for your instance. This can be found on the Overview page for your instance in the Google Cloud Console. or by running the following command:

    gcloud sql instances describe [INSTANCE_NAME].

  • Ensure that the service account your service is using to authenticate calls to Cloud SQL has the appropriate Cloud SQL roles and permissions.
    • The service account for your service needs one of the following IAM roles:
      • Cloud SQL Client (preferred)
      • Cloud SQL Editor
      • Cloud SQL Admin
      . or by running the following command:

      gcloud sql instances describe [INSTANCE_NAME]

    • 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.

    By default, your app will authorize your connections using the Cloud Run (fully managed) service account. The service account is in the format PROJECT_NUMBER-compute@developer.gserviceaccount.com.

    If the authorizing service account belongs to a different project than the Cloud SQL instance, the Cloud SQL Admin API and IAM permissions will need to be added for both projects.

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 tutorial uses Python 3.7+.

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

SQLAlchemy==1.3.12

PyMySQL==0.9.3

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 tutorial:

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 = "root"
  driver_name = 'mysql+pymysql'
  query_string = dict({"unix_socket": "/cloudsql/{}".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 wrote something into your database!\n'

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: run-sql
  namespace: default
spec:
  template:
    spec:
      containers:
      - image: gcr.io/PROJECT_ID/run-sql
        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 variable PROJECT_ID in the service.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-mysql \
      --add-cloudsql-instances [INSTANCE_CONNECTION_NAME] \
      --set-env-vars CLOUD_SQL_CONNECTION_NAME=[INSTANCE_CONNECTION_NAME],\
      DB_USER=root,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 5 above.
  2. Verify that you see this message: Hello! You 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 tutorial, 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