Using Cloud SQL

This page shows how to connect to a Cloud SQL Second Generation instance from an App Engine application in the flexible environment, and how to read and write to Cloud SQL. Cloud SQL is a MySQL database that lives in Google's cloud.

To learn more about Google Cloud SQL, see the Cloud SQL documentation. For information on Cloud SQL billing plans and limits, see the Cloud SQL Pricing page. App Engine applications are also subject to the App Engine quotas.

Before you begin

  1. Select or create a Cloud Platform project.

    Go to the Projects page

  2. Enable billing for your project.

    Enable billing

  3. Enable the Cloud SQL Administration API.

    Enable the API

  4. Install and initialize the Cloud SDK.

Creating an instance and setting the password

To create and configure a Cloud SQL instance:

  1. Create a Cloud SQL Second Generation instance.

  2. If you haven't already, set the password for the MySQL root user on your Cloud SQL instance:

    gcloud sql instances set-root-password [INSTANCE_NAME] --password [INSTANCE_ROOT_PASSWORD]
    
  3. Get the connection name for the instance:

    gcloud sql instances describe [INSTANCE_NAME]
    

    Record the connectionName for later use. You can also get this value from the Instance details page in the Google Cloud Platform Console.

Granting access to App Engine

If your App Engine application and Cloud SQL instance are in different Cloud Platform Console 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 Cloud Platform Console project.

  1. If your App Engine application is in the same Google Cloud Platform project as your Cloud SQL instance, proceed to Setting up your local environment. 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 Cloud Platform Console 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 Editor role, you can proceed to Setting up your local environment.
  7. Otherwise, add the service account by clicking Add member.
  8. In the Add members dialog, provide the name of the service account and select Project > Editor for the role.
  9. Click Add.

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

Setting up your local environment

Once deployed, your application uses the Cloud SQL Proxy that is built in to the App Engine flexible environment to communicate with your Cloud SQL instance. However, to test your application locally, you must install and use a local copy of the Cloud SQL Proxy in your development environment.

To perform basic administrative tasks on your Cloud SQL instance, you can use the MySQL Client.

  1. Install the Cloud SQL proxy:

    Linux 64-bit

    1. Download the proxy:
      wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
      
    2. Rename the proxy to use the standard filename:
      mv cloud_sql_proxy.linux.amd64 cloud_sql_proxy
      
    3. 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
      
    2. Rename the proxy to use the standard filename:
      mv cloud_sql_proxy.linux.386 cloud_sql_proxy
      
    3. 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.

  2. Run the proxy:

    Depending on your language and environment, you can start the proxy using either TCP sockets or Unix sockets. Unix sockets are not supported for applications written in the Java programming language or for the Windows environment.

    TCP sockets

    1. Copy your instance connection name from the Instance details page.
    2. If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
    3. Start the proxy.

      Some possible proxy invocation strings:

      • Using Cloud SDK authentication:
        ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306
      • Using a service account and explicit instance specification (recommended for production environments):
        ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \
                          -credential_file=<PATH_TO_KEY_FILE> &

      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. If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
    4. Open a new terminal window and start the proxy.

      Some possible proxy invocation strings:

      • Using a service account and explicit instance specification (recommended for production environments):
        ./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \
                          -credential_file=<PATH_TO_KEY_FILE> &
      • Using Cloud SDK authentication and 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. To use MySQL Client, you can install a local copy of MySQL Client and connect either by using the proxy or IP Addresses.

    For more information, see Connecting MySQL Client Using the Cloud SQL Proxy and Connecting MySQL Client Using IP Addresses.

Creating a user and managing user access

Use the MySQL Client command line tools or the MySQL Client built into Cloud Shell to create a user.

  1. Create a user:

    CREATE USER '[USER_NAME]'@'%' IDENTIFIED BY '[PASSWORD]';
    
  2. Grant the user access to the database:

    GRANT ALL ON *.* TO '[USER_NAME]'@'%';
    

Setting connection strings and adding a library

  1. Set up the local environment to support connections for local testing.

    For example, for the provided code sample:

    export MYSQL_CONNECTION="[USER_NAME]:[PASSWORD]@tcp(127.0.0.1:3306)/[DATABASE_NAME]"
    

  2. To allow your app to connect to your Cloud SQL Second Generation instance when the app is deployed, add the user, password, database, and instance connection name variables from Cloud SQL to the related environment variables in the app.yaml file:

    env_variables:
      # See https://github.com/go-sql-driver/mysql
      #
      # Replace INSTANCE_CONNECTION_NAME with the same value as in the
      # beta_settings section below.
      MYSQL_CONNECTION: user:password@unix(/cloudsql/INSTANCE_CONNECTION_NAME)/dbname
      #
      # If you're testing locally using the Cloud SQL proxy with TCP,
      # instead use the "tcp" dialer by setting the environment variable:
      # MYSQL_CONNECTION=user:password@tcp(127.0.0.1:3306)/dbname

  3. Add the beta_settings section to your app.yaml, using your Cloud SQL instance connection name.
    beta_settings:
      cloud_sql_instances: INSTANCE_CONNECTION_NAME

  4. Download a MySQL package to your local machine using the command line. For example:

    go get -u github.com/go-sql-driver/mysql
    

Running the sample code

The following sample writes visit information to Cloud SQL and then reads and returns the last ten visits:

// Copyright 2015 Google Inc. All rights reserved.
// Use of this source code is governed by the Apache 2.0
// license that can be found in the LICENSE file.

// Sample cloudsql demonstrates usage of Cloud SQL from App Engine flexible environment.
package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"os"
	"time"

	"google.golang.org/appengine"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {
	// Set this in app.yaml when running in production.
	datastoreName := os.Getenv("MYSQL_CONNECTION")

	var err error
	db, err = sql.Open("mysql", datastoreName)
	if err != nil {
		log.Fatal(err)
	}

	// Ensure the table exists.
	// Running an SQL query also checks the connection to the MySQL server
	// is authenticated and valid.
	if err := createTable(); err != nil {
		log.Fatal(err)
	}

	http.HandleFunc("/", handle)
	appengine.Main()
}

func createTable() error {
	stmt := `CREATE TABLE IF NOT EXISTS visits (
			timestamp  BIGINT,
			userip     VARCHAR(255)
		)`
	_, err := db.Exec(stmt)
	return err
}

func handle(w http.ResponseWriter, r *http.Request) {
	if r.URL.Path != "/" {
		http.NotFound(w, r)
		return
	}

	// Get a list of the most recent visits.
	visits, err := queryVisits(10)
	if err != nil {
		msg := fmt.Sprintf("Could not get recent visits: %v", err)
		http.Error(w, msg, http.StatusInternalServerError)
		return
	}

	// Record this visit.
	if err := recordVisit(time.Now().UnixNano(), r.RemoteAddr); err != nil {
		msg := fmt.Sprintf("Could not save visit: %v", err)
		http.Error(w, msg, http.StatusInternalServerError)
		return
	}

	fmt.Fprintln(w, "Previous visits:")
	for _, v := range visits {
		fmt.Fprintf(w, "[%s] %s\n", time.Unix(0, v.timestamp), v.userIP)
	}
	fmt.Fprintln(w, "\nSuccessfully stored an entry of the current request.")
}

type visit struct {
	timestamp int64
	userIP    string
}

func recordVisit(timestamp int64, userIP string) error {
	stmt := "INSERT INTO visits (timestamp, userip) VALUES (?, ?)"
	_, err := db.Exec(stmt, timestamp, userIP)
	return err
}

func queryVisits(limit int64) ([]visit, error) {
	rows, err := db.Query("SELECT timestamp, userip FROM visits ORDER BY timestamp DESC LIMIT ?", limit)
	if err != nil {
		return nil, fmt.Errorf("Could not get recent visits: %v", err)
	}
	defer rows.Close()

	var visits []visit

	for rows.Next() {
		var v visit
		if err := rows.Scan(&v.timestamp, &v.userIP); err != nil {
			return nil, fmt.Errorf("Could not get timestamp/user IP out of row: %v", err)
		}
		visits = append(visits, v)
	}

	return visits, rows.Err()
}

Testing and Deploying

  1. To test your application locally:

    go run cloudsql.go
    

  2. After you test your application, deploy your project to App Engine:

    aedeploy gcloud app deploy
    

Send feedback about...

App Engine flexible environment for Go