Connecting to Cloud SQL with Cloud Functions

1. Introduction

Last Updated: 2021-05-11

What is Cloud SQL?

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform.

What is Cloud Functions?

Cloud Functions is a lightweight compute solution for developers to create single-purpose, stand-alone functions that respond to Cloud events without needing to manage a server or runtime environment.

What you'll build

In this codelab, you'll write a Cloud Function in Python. The function:

Connects to a Cloud SQL Database instance.
Sends an insert statement to a table in the database.

What you'll learn

How to access the Cloud Functions web UI in the Google Cloud Console.
How to create a Cloud Function.
How to test a Cloud Function.
How to connect to a Cloud SQL database instance (either MySQL or PostgreSQL) using Python.
How to write to a Cloud SQL database using Python.

2. Requirements

A browser, such as Chrome or Firefox.
A Google Cloud Platform project that contains your Cloud SQL instance.
If you don't already have one, you can follow the steps in the Quickstart for MySQL or PostgreSQL. Do all the steps except for the clean up.
Your instance contains a MySQL or PostgreSQL database with a table.
Your instance connection name, the database and table names, the database user name and the user's password.

A Service account with the Cloud SQL Client role.

3. Prepare the code and create the function

Prepare the code

The Cloud Function code for connecting to a Cloud SQL database is right here. Some of the variable values depend on whether your Cloud SQL database is MySQL or PostgreSQL, and depend on your own database information.

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.

requirements.txt

# This file tells Python which modules it needs to import
SQLAlchemy==1.3.12      
# If your database is MySQL, uncomment the following line:
#PyMySQL==0.9.3
# If your database is PostgreSQL, uncomment the following line:
#pg8000==1.13.2

main.py

# This file contains all the code used in the codelab. 
import sqlalchemy

# Depending on which database you are using, you'll set some variables differently. 
# In this code we are inserting only one field with one value. 
# Feel free to change the insert statement as needed for your own table's requirements.

# Uncomment and set the following variables depending on your specific instance and database:
#connection_name = ""
#table_name = ""
#table_field = ""
#table_field_value = ""
#db_name = ""
#db_user = ""
#db_password = ""

# If your database is MySQL, uncomment the following two lines:
#driver_name = 'mysql+pymysql'
#query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

# If your database is PostgreSQL, uncomment the following two lines:
#driver_name = 'postgres+pg8000'
#query_string =  dict({"unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(connection_name)})

# If the type of your table_field value is a string, surround it with double quotes.

def insert(request):
    request_json = request.get_json()
    stmt = sqlalchemy.text('insert into {} ({}) values ({})'.format(table_name, table_field, table_field_value))
    
    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

In a browser, go to the Google Cloud Platform Console UI.
Select Cloud Functions from the Navigation menu.
Click CREATE FUNCTION on the button bar.
Enter a name for the function.
Select the HTTP trigger. (Make a note of the URL displayed beneath the trigger item. It will be in this format: https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME)
Under Authentication, select Allow unauthenticated invocations to make the function public.
Expand the Runtime, Build and Connections Settings In Runtime service account, select a service account that has the Cloud SQL Client role.
Click the NEXT button.
Select Python 3.7 for the runtime option.
Select Inline editor for the source code option.
SelIn the source 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.
Enter insert as the name of the Entry point.
Click Deploy and wait while the function is created. The spinner stops spinning and a green check appears on the subsequent page when the function is ready to use.

4. Test the function

In a browser, go to the Google Cloud Platform Console UI.
Select Cloud Functions from the Navigation menu.
Click on the name of the function you created earlier.
Select the TESTING link in the middle of the page.
Select TEST THE FUNCTION.
The result should appear: ok (If the test fails, you'll see a stack trace to help with debugging.)
In a browser, go to the URL that you saved earlier, when you created the function. If you forgot to save the URL, you can get it from the TRIGGER link.
The ok result should appear in the browser as well.

5. Clean up

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

Delete the Cloud SQL instance

Go to the Cloud SQL Instances page in the Google Cloud Console.
Select the instance you created to open the Instance details page.
In the icon bar at the top of the page, click Delete.
In the Delete instance window, type the name of your instance, then click Delete to delete the instance. You cannot reuse an instance name for about 7 days after an instance is deleted.

Delete the Cloud Function

Go to the Cloud Functions page in the Google Cloud Console.
Select the three dots under Actions for your function and choose Delete.
Confirm deletion by clicking the DELETE button.

6. Congratulations

Congratulations, you've successfully built a Cloud Function that works with Cloud SQL.

Specifically, you've created a Cloud Function that connects and writes to a Cloud SQL database instance.