Using Cloud SQL for MySQL

Python |Java |PHP |Go |Node.js

Cloud SQL provides a relational database that lives in Google's cloud that you can use with your App Engine application. To learn more about Cloud SQL, see the 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 GCP project in the GCP 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. Download, install, and initialize Cloud SDK:
   Download the SDK

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

Setting up the Cloud SQL instance

1. Create a Second Generation Cloud SQL instance.
2. If you haven't already, set the password for the default user on your Cloud SQL instance:

     gcloud sql users set-password root % --instance [INSTANCE_NAME] --password [PASSWORD]
     

3. If you don't want to use the default user to connect, create a user.
4. Using 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

5. Install Composer, a tool for dependency management for PHP.
6. Install dependencies using Composer:

   composer install

7. Add the Cloud SQL instance connection name, database, user, and password to the environment variables in app.yaml.
   appengine/php55/cloudsql/app.yaml

   View on GitHub

   env_variables:
     # Replace USER, PASSWORD, DATABASE, and CONNECTION_NAME with the
     # values obtained when configuring your Cloud SQL instance.
     MYSQL_DSN: mysql:unix_socket=/cloudsql/INSTANCE_CONNECTION_NAME;dbname=DATABASE
     MYSQL_USER: USER
     MYSQL_PASSWORD: PASSWORD

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, and it has a role that includes the cloudsql.instances.connect permission, you can proceed to Setting up.

   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.

7. Otherwise, add the service account by clicking Add.

8. 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.

9. Click Add.

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

Code sample overview

The following sample reads and displays guestbook entries from Cloud SQL. When you click to sign the guestbook, the application adds the entry and writes the results back to Cloud SQL:

appengine/php55/cloudsql/app.php

View on GitHub

use Silex\Application;
use Silex\Provider\TwigServiceProvider;
use Symfony\Component\HttpFoundation\Request;

// create the Silex application
$app = new Application();
$app->register(new TwigServiceProvider());
$app['twig.path'] = [ __DIR__ ];

$app->get('/', function () use ($app) {
    /** @var PDO $db */
    $db = $app['database'];
    /** @var Twig_Environment $twig */
    $twig = $app['twig'];

    // Show existing guestbook entries.
    $results = $db->query('SELECT * from entries');

    return $twig->render('cloudsql.html.twig', [
        'results' => $results,
    ]);
});

$app->post('/', function (Request $request) use ($app) {
    /** @var PDO $db */
    $db = $app['database'];

    $name = $request->request->get('name');
    $content = $request->request->get('content');

    if ($name && $content) {
        $stmt = $db->prepare('INSERT INTO entries (guestName, content) VALUES (:name, :content)');
        $stmt->execute([
            ':name' => $name,
            ':content' => $content,
        ]);
    }

    return $app->redirect('/');
});

// function to return the PDO instance
$app['database'] = function () use ($app) {
    // Connect to CloudSQL from App Engine.
    $dsn = getenv('MYSQL_DSN');
    $user = getenv('MYSQL_USER');
    $password = getenv('MYSQL_PASSWORD');
    if (!isset($dsn, $user) || false === $password) {
        throw new Exception('Set MYSQL_DSN, MYSQL_USER, and MYSQL_PASSWORD environment variables');
    }

    $db = new PDO($dsn, $user, $password);

    return $db;
};

appengine/php55/cloudsql/cloudsql.html.twig

View on GitHub

<html>
    <body>
        {% if results %}
            <h2>Guestbook Entries</h2>

            {% for row in results %}
                <div><strong> {{ row.guestName }} </strong> wrote <br> {{ row.content }}</div>
            {% endfor %}
        {% endif %}

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

If you want to use this sample, follow the steps below to test and deploy the application. After the application is running, go to /create_tables to create the tables needed.

Testing in your development environment

To test your app with the local development server:

1. To connect to Cloud SQL from your local test environment, install a local Cloud SQL proxy client, using the instructions provided in Quickstart for Using the Proxy for Local Testing.

2. Start the Cloud SQL Proxy, if you didn't already.

   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.

   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

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

gcloud app deploy

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

That's it! To launch your browser and view the app at http://[YOUR_PROJECT_ID].appspot.com, run the following command:

gcloud app browse