Using Google Cloud SQL

Google Cloud SQL provides a relational database that you can use with your App Engine application. Cloud SQL is a MySQL database that lives in Google's cloud. To learn more about Google Cloud SQL, see the Google Cloud SQL documentation.

For information on pricing and restrictions imposed by both Cloud SQL and App Engine, see Pricing and Access Limits.

Before you begin

  1. Create or select a Cloud Platform project in the Cloud Platform Console and then ensure that project includes an App Engine application:
    Go to App Engine

    The Dashboard opens if an App Engine application already exists in your project. Otherwise, you are prompted to choose the region where you want your App Engine application located.

  2. To deploy your app with the gcloud tool, you must download, install, and initialize the Google Cloud SDK:
    Download the SDK

    If you already have the gcloud tool installed and want to configure it to use a Cloud Platform project ID other than the one that you initialized it to, see Managing Cloud SDK Configurations.

Configuring your local environment

You can either use a local MySQL server to test your application or you can connect to Cloud SQL.

  1. If you want to test your application with a local MySQL server, install it now:
  2. Alternatively, if you want to connect to Cloud SQL to test your application, install the Cloud SQL Proxy:

    Linux 64-bit

    1. Download the proxy:
      wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
      
    2. Make the proxy executable:
      chmod +x cloud_sql_proxy
      

    Linux 32-bit

    1. Download the proxy:
      wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
      
    2. Make the proxy executable:
      chmod +x cloud_sql_proxy
      

    OS X 64-bit

    1. Download the proxy:
      curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
      
    2. Make the proxy executable:
      chmod +x cloud_sql_proxy
      

    OS X 32-bit

    1. Download the proxy:
      curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
      
    2. Make the proxy executable:
      chmod +x cloud_sql_proxy
      

    Windows 64-bit

    Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe and select "Save link as..." to download the proxy, renaming it to cloud_sql_proxy.exe.

    Windows 32-bit

    Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe and select "Save link as..." to download the proxy, renaming it to cloud_sql_proxy.exe.
    If your operating system is not included here, you can also compile the proxy from source.

  3. Install the MySQLdb library locally to test in your development environment:

Setting up the Cloud SQL instance

  1. Create a Second Generation Cloud SQL instance and configure the root user.
  2. If you don't want to use the root user to connect, create a user.
  3. Using the Cloud SDK, get the Cloud SQL instance connection name to use as a connection string in your application code:
    gcloud sql instances describe [INSTANCE_NAME]
    Record the value returned for connectionName. You can also find this value in the Instance details page of the Google Cloud Platform Console. For example, in the Cloud SDK output:
    gcloud sql instances describe instance1
      connectionName: project1:us-central1:instance1

Granting access to App Engine

If your App Engine application and Cloud SQL instance are in different Google Cloud Platform projects, you must use a service account to allow your App Engine application access to Cloud SQL.

This service account represents your App Engine application and is created by default when you create a Google Cloud Platform project.

  1. If your App Engine application is in the same project as your Cloud SQL instance, proceed to Setting up. Otherwise, proceed to the next step.
  2. 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

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

    Go to the IAM & Admin Projects page

  4. Select the project that contains the Cloud SQL instance.
  5. Search for the service account name.
  6. If the service account is already there with the Cloud SQL Client or Editor role, you can proceed to Setting up.
  7. Otherwise, add the service account by clicking Add.
  8. In the Add members dialog, provide the name of the service account and select Cloud SQL > Cloud SQL Client for the role.

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

  9. Click Add.

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

Setting up

  1. Add the Cloud SQL instance connection name, user, and password to the environment variables in app.yaml.
    env_variables:
        CLOUDSQL_CONNECTION_NAME: your-connection-name
        CLOUDSQL_USER: root
        CLOUDSQL_PASSWORD: your-cloudsql-user-password

  2. Add the MySQLdb client library to your application's app.yaml:
    libraries:
    - name: MySQLdb
      version: "latest"
    
    
    The App Engine Python runtime includes the MySQLdb library, so you must add it to the runtime environment.

Code sample overview

The following code sample connects to Google Cloud SQL using App Engine's native UNIX socket, then uses the SHOW VARIABLES command to display values of MySQL flags:

import os

import MySQLdb
import webapp2


# These environment variables are configured in app.yaml.
CLOUDSQL_CONNECTION_NAME = os.environ.get('CLOUDSQL_CONNECTION_NAME')
CLOUDSQL_USER = os.environ.get('CLOUDSQL_USER')
CLOUDSQL_PASSWORD = os.environ.get('CLOUDSQL_PASSWORD')


def connect_to_cloudsql():
    # When deployed to App Engine, the `SERVER_SOFTWARE` environment variable
    # will be set to 'Google App Engine/version'.
    if os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
        # Connect using the unix socket located at
        # /cloudsql/cloudsql-connection-name.
        cloudsql_unix_socket = os.path.join(
            '/cloudsql', CLOUDSQL_CONNECTION_NAME)

        db = MySQLdb.connect(
            unix_socket=cloudsql_unix_socket,
            user=CLOUDSQL_USER,
            passwd=CLOUDSQL_PASSWORD)

    # If the unix socket is unavailable, then try to connect using TCP. This
    # will work if you're running a local MySQL server or using the Cloud SQL
    # proxy, for example:
    #
    #   $ cloud_sql_proxy -instances=your-connection-name=tcp:3306
    #
    else:
        db = MySQLdb.connect(
            host='127.0.0.1', user=CLOUDSQL_USER, passwd=CLOUDSQL_PASSWORD)

    return db


class MainPage(webapp2.RequestHandler):
    def get(self):
        """Simple request handler that shows all of the MySQL variables."""
        self.response.headers['Content-Type'] = 'text/plain'

        db = connect_to_cloudsql()
        cursor = db.cursor()
        cursor.execute('SHOW VARIABLES')

        for r in cursor.fetchall():
            self.response.write('{}\n'.format(r))


app = webapp2.WSGIApplication([
    ('/', MainPage),
], debug=True)

Testing in your development environment

To test your app with the local development server:

  1. If you are using a local MySQL server, start the MySQL server in your development environment.
  2. Otherwise (you are connecting to Cloud SQL from your local environment), start the Cloud SQL Proxy.

    Depending on your language and environment, you can start the proxy using either TCP sockets or Unix sockets.

    TCP sockets

    1. Copy your instance connection name from the Instance details page.
    2. Open a new terminal window and start the proxy.
      ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306
      
      The specified port must not already be in use, for example, by a local database server.
    3. For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.

    Unix sockets

    1. If you are using explicit instance specification, copy your instance connection name from the Instance details page.
    2. Create the directory where the proxy sockets will live:
      sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
    3. Open a new terminal window and start the proxy.
      • Using explicit instance specification (recommended for production environments) with Unix sockets:
        ./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> &
      • Using automatic instance discovery:
        ./cloud_sql_proxy -dir=/cloudsql &

      It is best to start the proxy in its own terminal so you can monitor its output without it mixing with the output from other programs.

      For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.

  3. Start the development server from the application directory:

    dev_appserver.py .

  4. The web server is now running and listening for requests on port 8080. To view, visit the following URL:

    http://localhost:8080/

Something go wrong? See Using the Local Development Server for more information.

Deploying your app

  1. To upload your app to App Engine, run the gcloud app deploy command:

     gcloud app deploy
    

    For details about deploying to App Engine, see Deploying a Python App.

  2. To launch your browser and view the app at http://[YOUR_PROJECT_ID].appspot.com, run the following command:

    gcloud app browse
    

Send feedback about...

App Engine standard environment for Python