Using Google Cloud SQL with App Engine

Python | Java | PHP | Go

This document describes how to use Google Cloud SQL instances with the App Engine PHP SDK.

To learn more about Google Cloud SQL, see the Google Cloud SQL documentation.

If you haven't already created a Google Cloud SQL instance, the first thing you need to do is create one.

Creating a Cloud SQL Instance

A Cloud SQL instance is equivalent to a server. A single Cloud SQL instance can contain multiple databases. Follow these steps to create a Google Cloud SQL instance:

Sign into the Google Developers Console .
Create a new project, or open an existing project.
From within a project, select Cloud SQL to open the Cloud SQL control panel for that project.
Click New Instance to create a new Cloud SQL instance in your project, and configure your size, billing and replication options.
- More information on Cloud SQL billing options and instance sizes
- More information on Cloud SQL replication options
You'll notice that the App Engine application associated with your current project is already authorized to access this new instance. For more information on app authorization see the Access Control topic in the Cloud SQL docs.

That's it! You can now connect to your Google Cloud SQL instance from within your app, or any of these other methods .

MySQL case sensitivity

When you are creating or using databases and tables, keep in mind that all identifiers in Google Cloud SQL are case-sensitive. This means that all tables and databases are stored with the same name and case that was specified at creation time. When you try to access your databases and tables, make sure that you are using the exact database or table name.

For example, if you create a database named PersonsDatabase, you will not be able to reference the database using any other variation of that name, such as personsDatabase or personsdatabase. For more information about identifier case sensitivity, see the MySQL documentation.

Build a starter application and database

The easiest way to build an App Engine application that accesses Google Cloud SQL is to create a starter application then modify it. This section leads you through the steps of building an application that displays a web form that lets users read and write entries to a guestbook database. The sample application demonstrates how to read and write to a Google Cloud SQL instance.

Step 1: Create your App Engine sample application

Follow the instructions for the Hello World! chapter of the PHP Getting Started guide to create a simple App Engine application.

Step 2: Grant your App Engine application access to the Google Cloud SQL instance

You can grant individual Google App Engine applications access to a Google Cloud SQL instance. One application can be granted access to multiple instances, and multiple applications can be granted access to a particular instance. To grant access to a Google App Engine application, you need its application ID which can be found at the Google App Engine administration console under the Applications column.

Note : An App Engine application must be in the same region (either EU or US) as a Google Cloud SQL instance to be authorized to access that Google Cloud SQL instance.

To grant an App Engine application access to a Google Cloud SQL instance:

Go to the Google Developers Console .
Select a project by clicking the project name.
In the left sidebar of your project, click Cloud SQL .
Click the name of the instance to which you want to grant access.
In the instance dashboard, click Edit .
In the Instance settings window, enter your Google App Engine application ID in the Authorized App Engine applications section. You can grant access to multiple applications, by entering them one at a time.

Note : In order to improve performance, the Google Cloud SQL instance will be kept as close as possible to the first App Engine application on the list, so this should be the application whose performance is most important.
Click Confirm to apply your changes.

After you have added authorized applications to your Google Cloud SQL instance, you can view a list of these applications in the instance dashboard, in the section titled Applications .

Step 3: Create your database and table

For example, you can use MySQL Client to run the following commands:

Create a new database called guestbook using the following SQL statement:
```
CREATE DATABASE guestbook;
```
Inside the guestbook database create a table called entries with columns for the guest name, the message content, and a random ID, using the following statement:
```
CREATE TABLE guestbook.entries (
  entryID INT NOT NULL AUTO_INCREMENT,
  guestName VARCHAR(255),
  content VARCHAR(255),
  PRIMARY KEY(entryID)
);
```

After you have set up a bare-bones application, you can modify it and deploy it.

Connect to your database

Working with different connection interfaces

Google Cloud SQL supports connections from PHP using common connection methods, including PDO_MySQL , mysqli , and MySQL API . PDO_MySQL, mysqli, and MySQL API are enabled by default in App Engine, in development (locally) or in your production (deployed App Engine application).

In these connection examples, an App Engine app that belongs to a Google Cloud Platform project called <your-project-id> is connecting to a Cloud SQL instance named <your-instance-name> . These following examples show how to connect from a deployed App Engine application using a socket or named pipe that specifies the Cloud SQL instance. When you connect from App Engine, you can use the root user and no password (as shown here), or you can use a specific database user and password .

PDO_MySQL (connecting from App Engine)

$db = new pdo('mysql:unix_socket=/cloudsql/<your-project-id>:<your-instance-name>;dbname=<database-name>',
  'root',  // username
  ''       // password
  );

mysqli (connecting from App Engine)

$sql = new mysqli(null,
  'root', // username
  '',     // password
  <database-name>,
  null,
  '/cloudsql/<your-project-id>:<your-instance-name>'
  );

MySQL API (connecting from App Engine)

$conn = mysql_connect(':/cloudsql/<your-project-id>:<your-instance-name>',
  'root', // username
  ''      // password
  );
mysql_select_db('<database-name'>);

These connection examples cannot be used from your development environment. To connect from your development environment to either a local MySQL instance or a Cloud SQL instance, use the following connection code:

PDO_MySQL (connecting using an IP address)

$db = new pdo('mysql:host=127.0.0.1:3306;dbname=<database-name>',
  '<username>',
  '<password>'
);

mysqli (connecting using an IP address)

$sql = new mysqli('127.0.0.1:3306',
  '<username>',
  '<password>',
  <database-name>
  );

MySQL API (connecting using an IP address)

$conn = mysql_connect('127.0.0.1:3306',
  '<username>',
  '<password>'
  );
mysql_select_db('<database-name>');

To connect to a Cloud SQL instance from your development environment, substitute "127.0.0.1" with the instance IP address. You do not use the "/cloudsql/"-based connection string to connect to a Cloud SQL instance if your App Engine app is running locally in the Development Server .

If you want to use the same code locally and deployed, you can use a Special $_SERVER keys variable ( SERVER_SOFTWARE ) to determine where your code is running. This approach is shown below.

Connect, post, and get from your database

Create two files guestbook.php and sign.php . guestbook.php displays the current entries in the guestbook and accepts input to add a new entry. When you click the Sign Guestbook button on the guestbook.php page, the sign.php page is invoked, which adds the entry and then redirects back to the guestbook.php page.

In both files, you access a Cloud SQL instance by using a Unix socket with the prefix /cloudsql/ . In the connection code in both files, replace <your-project-id> and <your-instance-name> with the project ID of your Google Cloud Project, and the name of your Cloud SQL instance.

Here is guestbook.php :

<html>
 <body>
  <?php
  use google\appengine\api\users\User;
  use google\appengine\api\users\UserService;

  $user = UserService::getCurrentUser();

  if ($user) {
    echo 'Hello, ' . htmlspecialchars($user->getNickname());
  }
  else {
    header('Location: ' . UserService::createLoginURL($_SERVER['REQUEST_URI']));
  }
  ?>

  <h2>Guestbook Entries</h2>
  <?php
  // Create a connection.

  $db = null;
  if (isset($_SERVER['SERVER_SOFTWARE']) &&
  strpos($_SERVER['SERVER_SOFTWARE'],'Google App Engine') !== false) {
    // Connect from App Engine.
    try{
       $db = new pdo('mysql:unix_socket=/cloudsql/<your-project-id>:<your-instance-name>;dbname=guestbook', 'root', '');
    }catch(PDOException $ex){
        die(json_encode(
            array('outcome' => false, 'message' => 'Unable to connect.')
            )
        );
    }
  } else {
    // Connect from a development environment.
    try{
       $db = new pdo('mysql:host=127.0.0.1:3306;dbname=guestbook', 'root', '<password>');
    }catch(PDOException $ex){
        die(json_encode(
            array('outcome' => false, 'message' => 'Unable to connect')
            )
        );
    }
  }
  try {
    // Show existing guestbook entries.
    foreach($db->query('SELECT * from entries') as $row) {
            echo "<div><strong>" . $row['guestName'] . "</strong> wrote <br> " . $row['content'] . "</div>";
     }
  } catch (PDOException $ex) {
    echo "An error occurred in reading or writing to guestbook.";
  }
  $db = null;
  ?>

  <h2>Sign the Guestbook</h2>
  <form action="/sign" method="post">
    <div><textarea name="content" rows="3" cols="60"></textarea></div>
    <div><input type="submit" value="Sign Guestbook"></div>
  </form>
  </body>
</html>

Here is sign.php :

<?php
header('Location: '."/guestbook");

use google\appengine\api\users\User;
use google\appengine\api\users\UserService;

$user = UserService::getCurrentUser();

$db = null;
if (isset($_SERVER['SERVER_SOFTWARE']) &&
strpos($_SERVER['SERVER_SOFTWARE'],'Google App Engine') !== false) {
  // Connect from App Engine.
  try{
     $db = new pdo('mysql:unix_socket=/cloudsql/<your-project-id>:<your-instance-name>;dbname=guestbook', 'root', '');
  }catch(PDOException $ex){
      die(json_encode(
          array('outcome' => false, 'message' => 'Unable to connect.')
          )
      );
  }
} else {
  // Connect from a development environment.
  try{
     $db = new pdo('mysql:host=127.0.0.1:3306;dbname=guestbook', 'root', '<password>');
  }catch(PDOException $ex){
      die(json_encode(
          array('outcome' => false, 'message' => 'Unable to connect')
          )
      );
  }
}
try {
  if (array_key_exists('content', $_POST)) {
    $stmt = $db->prepare('INSERT INTO entries (guestName, content) VALUES (:name, :content)');
    $stmt->execute(array(':name' => htmlspecialchars($user->getNickname()), ':content' => htmlspecialchars($_POST['content'])));
    $affected_rows = $stmt->rowCount();
    // Log $affected_rows.
  }
} catch (PDOException $ex) {
  // Log error.
}
$db = null;
?>

The connection code shown above connects to the Google Cloud SQL instance as the root user but you can connect to the instance as a specific database user. For more information, see Working with different connection interfaces .

For information about creating MySQL users, see Adding Users in the MySQL documentation.

Managing connections

An App Engine application is made up of one or more modules . Each module consists of source code and configuration files. An instance instantiates the code which is included in an App Engine module, and a particular version of module will have one or more instances running. The number of instances running depends on the number of incoming requests. You can configure App Engine to scale the number of instances automatically in response to processing volume (see Instance scaling and class ).

When App Engine instances talk to Google Cloud SQL, each App Engine instance cannot have more than 12 concurrent connections to a Cloud SQL instance. Always close any established connections before finishing processing a request. Not closing a connection will cause it to leak and may eventually cause new connections to fail. You can exit this state by shutting down the affected App Engine instance.

You should also keep in mind that there is also a maximum number of concurrent connections and queries for each Cloud SQL instance, depending on the tier (see Cloud SQL pricing ). For guidance on managing connections, see How should I manage connections? in the "Google Cloud SQL FAQ" document.

Update your configuration file

In your app.yaml file, make sure you have script handlers for guestbook.php and sign.php .

application: <your-application-name>
version: 1
runtime: php
api_version: 1

handlers:
- url: /
  script: guestbook.php
- url: /sign
  script: sign.php

That's it! Now you can deploy your application and try it out!

Using a local MySQL instance during development

The above example shows how to connect to a Cloud SQL instance when the code runs in App Engine and how to connect to a local MySQL server when the code runs in the Development Server . We encourage this pattern to minimize confusion and also maximize flexibility.

Size and access limits

The following limits apply to Google Cloud SQL:

Google App Engine Limits

Google App Engine applications are also subject to additional Google App Engine quotas and limits. Requests from Google App Engine applications to Google Cloud SQL are subject to the following time limits:

All database requests must finish within the HTTP request timer , around 60 seconds .
Offline requests like cron tasks have a time limit of 10 minutes.
Backend requests to Google Cloud SQL have a time limit of 10 minutes.
Each App Engine instance cannot have more than 12 concurrent connections to a Cloud SQL instance.

App Engine-specific quotas and access limits are discussed on the Quotas page.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .

Last updated July 1, 2014.

PHP