Connecting to Cloud SQL with Cloud Functions

Last Updated: 2020-06-17

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.
  • 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, we have a tutorial for that. Do all the steps except for deleting the instance.
  • 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.

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 table_name (table_field) values (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'
  1. In a browser, go to the Google Cloud Platform 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. (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)
  6. Select Inline editor for the source code option.
  7. Select Python 3.7 for the runtime option.
  8. In 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.
  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.
  1. In a browser, go to the Google Cloud Platform 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 result should appear: ok (If the test fails, you'll 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.
  8. The ok result should appear in the browser as well.

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.

Check out some of these codelabs...

Further reading

Reference docs