Using Google Cloud SQL

Python |Java |PHP |Go

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.

You can connect to Cloud SQL First Generation or Second Generation instances.

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

Before you begin

  • Select or create a Cloud Platform Console project.

    Go to the Projects page

  • To test in your development environment, you need to connect to a local MySQL server. Install the MySQL Community Server.

    If you use Linux on a distribution with apt-get, you can run:

    sudo apt-get install mysql-server
    

  • While the App Engine Python runtime includes the MySQLdb library, you need to install MySQLdb locally to test in your development environment.

    If you use Linux on a distribution with apt-get, you can install MySQLdb by running:

    sudo apt-get install python-mysqldb

    For instructions for other operating systems, see MySQLdb installation instructions at SourceForge.

Setting up

  1. In the same project as your App Engine application, create a Cloud SQL instance and configure the root user.

    You can use a Cloud SQL First Generation or a Second Generation instance. Second Generation instances provide increased performance at a lower cost than First Generation instances. Note that Cloud SQL Second Generation is in beta, and that network latency between Second Generation instances and App Engine standard environment is approximately double that for First Generation instances.

    For more information about Cloud SQL Second Generation, see Second Generation Capabilities.

  2. For the Cloud SQL instance, create a database.
  3. The default user for App Engine applications is root@localhost. If you don't want to use the default user, create a user.
  4. If you are using a Cloud SQL First Generation instance, in the Cloud Platform Console, grant your App Engine application access to the Google Cloud SQL instance.

    If you are using a Cloud SQL Second Generation instance and your application is in the same project as the SQL instance, then your application should already have access.

  5. Get the Cloud SQL instance connection name to use as a connection string in your application code. To find the instance connection name, go to the Cloud SQL Instances page in the Cloud Platform Console.
    Go to the Cloud SQL Instances page
    1. Click on the Instance ID for the Cloud SQL instance you are using.
    2. Under Properties, find the Instance connection name.
  6. Add the Cloud SQL instance connection name, database name, user, and password to the environment variables in app.yaml.
  7. The App Engine Python runtime includes the MySQLdb library. To request the library so that it is added to the runtime environment when your application is deployed, add the MySQLdb client library to your application's app.yaml:
    runtime: python27
    api_version: 1
    threadsafe: yes
    
    handlers:
    - url: /
      script: main.app
    
    libraries:
    - name: MySQLdb
      version: "latest"
    

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:
# Copyright 2013 Google Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#         http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

"""
Sample App Engine application demonstrating how to connect to Google Cloud SQL
using App Engine's native unix socket.

For more information, see the README.md.
"""

# [START all]

import os

import MySQLdb
import webapp2


CLOUDSQL_PROJECT = '<your-project-id>'
CLOUDSQL_INSTANCE = '<your-cloud-sql-instance>'


class MainPage(webapp2.RequestHandler):
    def get(self):
        self.response.headers['Content-Type'] = 'text/plain'

        # When running on Google App Engine, use the special unix socket
        # to connect to Cloud SQL.
        if os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
            db = MySQLdb.connect(
                unix_socket='/cloudsql/{}:{}'.format(
                    CLOUDSQL_PROJECT,
                    CLOUDSQL_INSTANCE),
                user='root')
        # When running locally, you can either connect to a local running
        # MySQL instance, or connect to your Cloud SQL instance over TCP.
        else:
            db = MySQLdb.connect(host='localhost', user='root')

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

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


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

# [END all]

Testing and deploying

The development server in the Google App Engine SDK can use a locally installed MySQL server instance to closely mirror the Google Cloud SQL environment during development. To test in your development environment:
  1. Update the connection string in your application code to connect to a local instance of MySQL server.
  2. Start the MySQL server in your development environment.
  3. Start the development server, including a path to the application directory. For example, if your application's main.py file is in a directory named cloudsql:

    dev_appserver.py cloudsql/

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

    http://localhost:8080/

  5. When you are ready, deploy your project to App Engine:

    appcfg.py update cloudsql/

Send feedback about...