Using Cloud SQL

This page shows how to connect to a Cloud SQL instance from an App Engine application, and best practices for using Cloud SQL with App Engine.

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. To learn more about Cloud SQL, see the Cloud SQL documentation.

Setting up Cloud SQL

App Engine supports Cloud SQL for MySQL and Cloud SQL for PostgreSQL instances. See the instructions below to set up a new Cloud SQL instance:

Connecting to Cloud SQL

App Engine features a local UNIX socket interface for accessing your Cloud SQL instance with automatic authorization using the App Engine service account. The interface works for both MySQL and PostgreSQL instances. The socket interface is available at the /cloudsql/CLOUDSQL-CONNECTION-NAME file system path.

If your Cloud SQL instance is in a different region than your App Engine instance, follow the instructions at the end of this guide to grant your application access to your Cloud SQL instance.

For security reasons, you should not use standard TCP sockets to connect your App Engine app to Cloud SQL instances. App Engine supports connectors to Cloud SQL instances. The following example uses package PyMySQL with SQLAlchemy (for MySQL) and psycopg2 (for PostgresSQL) :

MySQL

import os

from flask import Flask
import pymysql

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')

app = Flask(__name__)


@app.route('/')
def main():
    # When deployed to App Engine, the `GAE_ENV` environment variable will be
    # set to `standard`
    if os.environ.get('GAE_ENV') == 'standard':
        # If deployed, use the local socket interface for accessing Cloud SQL
        unix_socket = '/cloudsql/{}'.format(db_connection_name)
        cnx = pymysql.connect(user=db_user, password=db_password,
                              unix_socket=unix_socket, db=db_name)
    else:
        # If running locally, use the TCP connections instead
        # Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
        # so that your application can use 127.0.0.1:3306 to connect to your
        # Cloud SQL instance
        host = '127.0.0.1'
        cnx = pymysql.connect(user=db_user, password=db_password,
                              host=host, db=db_name)

    with cnx.cursor() as cursor:
        cursor.execute('SELECT NOW() as now;')
        result = cursor.fetchall()
        current_time = result[0][0]
    cnx.close()

    return str(current_time)

PostgreSQL

import os

from flask import Flask
import psycopg2

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')

app = Flask(__name__)


@app.route('/')
def main():
    # When deployed to App Engine, the `GAE_ENV` environment variable will be
    # set to `standard`
    if os.environ.get('GAE_ENV') == 'standard':
        # If deployed, use the local socket interface for accessing Cloud SQL
        host = '/cloudsql/{}'.format(db_connection_name)
    else:
        # If running locally, use the TCP connections instead
        # Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
        # so that your application can use 127.0.0.1:3306 to connect to your
        # Cloud SQL instance
        host = '127.0.0.1'

    cnx = psycopg2.connect(dbname=db_name, user=db_user,
                           password=db_password, host=host)
    with cnx.cursor() as cursor:
        cursor.execute('SELECT NOW() as now;')
        result = cursor.fetchall()
    current_time = result[0][0]
    cnx.commit()
    cnx.close()

    return str(current_time)

The example uses the following environment variables:

  • CLOUD_SQL_USERNAME
  • CLOUD_SQL_PASSWORD
  • CLOUD_SQL_DATABASE_NAME
  • CLOUD_SQL_CONNECTION_NAME

You can set the environment variables in your app's app.yaml file. To find the values of these variables:

  • Go to the Cloud SQL Dashboard.
  • Click on the instance that you would like to connect to.
  • The instance connection name is listed in the Connect to this instance card.
  • The user credentials and databases are displayed in the USERS and DATABASES tabs.

The Cloud SQL instance connection name is of the following format: YOUR-PROJECT-ID:YOUR-REGION:YOUR-INSTANCE-ID. You can also find its value using the gcloud command line tool:

gcloud sql instances describe YOUR-INSTANCE-ID

For PostgreSQL, you need to add the .s.PGSQL.5432 suffix to the local socket interface path. Some connector libraries such as psycopg2 add this suffix automatically, for other connectors the full local socket interface path is /cloudsql/YOUR-INSTANCE-CONNECTION/.s.PGSQL.5432.

Best practices for using Cloud SQL in App Engine

Connection pooling

For best performance, it is recommended that you use a connector library that supports connection pooling. Using connection pooling will allow your app to reuse existing database connections instead of having to open and close a new one every time, and broken connections can be resumed automatically.

You should set a maximum connection count that is appropriate for your scaling settings and Cloud SQL Quotas and Limits so that your App Engine app does not exhaust connection resources during rapid scale up.

Where possible, allocate connection objects in global scope. This is both a performance optimization, and a less error-prone approach as it is often the case that connection closure is missed.

Closing connections

When you use a connection pool:

  1. Request a connection from the pool.
  2. Submit a query.
  3. Return the connection to the pool. There is no need to close or destroy a connection.

If you do not use connection pools or create individual connections, you should always close the connection when the query is completed. All the connections will be closed naturally when the App Engine instance is scaled down automatically.

The following code sample is an example of connection pooling:

MySQL

import os

from flask import Flask
import sqlalchemy

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')

# When deployed to App Engine, the `GAE_ENV` environment variable will be
# set to `standard`
if os.environ.get('GAE_ENV') == 'standard':
    # If deployed, use the local socket interface for accessing Cloud SQL
    unix_socket = '/cloudsql/{}'.format(db_connection_name)
    engine_url = 'mysql+pymysql://{}:{}@/{}?unix_socket={}'.format(
        db_user, db_password, db_name, unix_socket)
else:
    # If running locally, use the TCP connections instead
    # Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
    # so that your application can use 127.0.0.1:3306 to connect to your
    # Cloud SQL instance
    host = '127.0.0.1'
    engine_url = 'mysql+pymysql://{}:{}@{}/{}'.format(
        db_user, db_password, host, db_name)

# The Engine object returned by create_engine() has a QueuePool integrated
# See https://docs.sqlalchemy.org/en/latest/core/pooling.html for more
# information
engine = sqlalchemy.create_engine(engine_url, pool_size=3)

app = Flask(__name__)


@app.route('/')
def main():
    cnx = engine.connect()
    cursor = cnx.execute('SELECT NOW() as now;')
    result = cursor.fetchall()
    current_time = result[0][0]
    # If the connection comes from a pool, close() will send the connection
    # back to the pool instead of closing it
    cnx.close()

    return str(current_time)

PostgreSQL

import os

from flask import Flask
import psycopg2.pool

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')

# When deployed to App Engine, the `GAE_ENV` environment variable will be
# set to `standard`
if os.environ.get('GAE_ENV') == 'standard':
    # If deployed, use the local socket interface for accessing Cloud SQL
    host = '/cloudsql/{}'.format(db_connection_name)
else:
    # If running locally, use the TCP connections instead
    # Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
    # so that your application can use 127.0.0.1:3306 to connect to your
    # Cloud SQL instance
    host = '127.0.0.1'

db_config = {
    'user': db_user,
    'password': db_password,
    'database': db_name,
    'host': host
}

cnxpool = psycopg2.pool.ThreadedConnectionPool(minconn=1, maxconn=3,
                                               **db_config)

app = Flask(__name__)


@app.route('/')
def main():
    cnx = cnxpool.getconn()
    with cnx.cursor() as cursor:
        cursor.execute('SELECT NOW() as now;')
        result = cursor.fetchall()
    current_time = result[0][0]
    cnx.commit()
    cnxpool.putconn(cnx)

    return str(current_time)

Running Cloud SQL and App Engine apps in separate projects

For App Engine apps and Cloud SQL instances in Google Cloud Platform projects, you must grant the service account representing your App Engine app access to the Cloud SQL instance.

  1. Identify the service account associated with your App Engine application. The default App Engine service account is named [PROJECT-ID]@appspot.gserviceaccount.com.

    You can verify the App Engine service account on the IAM Permissions page. Ensure that you select the project for your App Engine application, not your Cloud SQL instance.

    Go to the IAM Permissions page

  2. Go to the IAM & Admin Projects page in the Google Cloud Platform Console.

    Go to the IAM & Admin Projects page

  3. Select the project that contains the Cloud SQL instance.
  4. Search for the service account name.
  5. If the service account is already there, and it has a role that includes the cloudsql.instances.connect permission, you can skip the rest of this section.

    The Cloud SQL Client, Cloud SQL Editor and Cloud SQL Admin roles all provide the necessary permission, as do the legacy Editor and Owner project roles.

  6. Otherwise, add the service account by clicking Add.
  7. In the Add members dialog, provide the name of the service account and select a role that include the cloudsql.instances.connect permission (any Cloud SQL predefined role other than Viewer will work).

    Alternatively, you can use the primitive Editor role by selecting Project > Editor, but the Editor role includes permissions across Google Cloud Platform.

    If you do not see these roles, your Google Cloud Platform user might not have the resourcemanager.projects.setIamPolicy permission. You can check your permissions by going to the IAM page in the Google Cloud Platform Console and searching for your user id.

  8. Click Add.

    You should now see the service account listed with the specified role.

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

App Engine standard environment for Python 3