Quickstart for Cloud SQL using Cloud Functions

This page shows you how to connect to Cloud SQL using Cloud Functions.

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 Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to the project selector page

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that 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, 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=postgres
    
  3. Enter your postgres password.

    The psql prompt appears.

Create a database and upload data

  1. Create a SQL database on your Cloud SQL instance:
    CREATE DATABASE guestbook;
    
  2. Connect to the database:
    \connect guestbook;
    
  3. Insert sample data into the database:
    CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),
                            entryID SERIAL PRIMARY KEY);
    INSERT INTO entries (guestName, content) values ('first guest', 'I got here!');
    INSERT INTO entries (guestName, content) values ('second guest', 'Me too!');
    
  4. 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)
    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 Functions

To configure Cloud Functions to enable connections to a Cloud SQL instance:

  • Make sure that the instance created above 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 function 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, 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 Functions service account. The service account is in the format service-PROJECT_NUMBER@gcf-admin-robot.iam.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.

Cloud Functions use 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 service-PROJECT_NUMBER@gcf-admin-robot.iam.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 Functions 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 Cloud Functions UI in the Cloud Console includes a text editor. You can copy/paste and edit the code there, or edit the code locally first, and then copy/paste it into the UI.

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

SQLAlchemy==1.3.12

pg8000==1.13.2

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

import sqlalchemy

# 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 = dict({"unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(connection_name)})


def insert(request):
    request_json = request.get_json()
    stmt = sqlalchemy.text("INSERT INTO entries (guestName, content) values ('third guest', 'Also this one');")
    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
    )
    try:
        with db.connect() as conn:
            conn.execute(stmt)
    except Exception as e:
        return 'Error: {}'.format(str(e))
    return 'ok'

Create the function

  1. In a browser, go to the Console UI.

  2. Select Cloud Functions from the Navigation menu.

  3. Click CREATE FUNCTION on the button bar.

  4. Enter a name for the function.

  5. Select the HTTP trigger. (Note the URL displayed beneath the trigger item. It will be in this format: https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME)

  6. Select Inline editor for the source code option.

  7. Select Python 3.7 for the runtime option.

  8. In the code editor windows, delete the existing content for both requirements.txt and main.py, and replace them with your edited versions of the code above.

  9. Enter insert as the name of the Function to execute.

  10. In the Advanced options, select a Service account that has the Cloud SQL Client role.

  11. Click Create and wait for the spinner to stop. A green check appears when the function is ready to use.

Test the function

  1. In a browser, go to the Console UI.

  2. Select Cloud Functions from the Navigation menu.

  3. Click on the name of the function you created earlier.

  4. Select the TESTING link in the middle of the page.

  5. Select TEST THE FUNCTION.

  6. The following result appears: ok. If the test fails, you see a stack trace to help with debugging.

  7. In a browser, go to the URL that you saved earlier, when you created the function. The ok result appears in the browser as well.

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