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.

  1. Before you begin
  2. Setting up
  3. Code sample overview
  4. Testing and deploying

Before you begin

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. For more information about Cloud SQL Second Generation, see Second Generation Capabilities. Note that Cloud SQL Second Generation is in beta.

  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.

    For example, replace the values in MYSQL_DSN, MYSQL_USER, and MYSQL_PASSWORD. Note that this sample uses App Engine's native UNIX socket with the prefix /cloudsql/ to connect with Cloud SQL:

    env_variables:
      # Replace project, instance, database, user and password with the values obtained
      # when configuring your Cloud SQL instance.
      MYSQL_DSN: mysql:unix_socket=/cloudsql/INSTANCE_CONNECTION_NAME;dbname=DATABASE
      MYSQL_USER: root
      MYSQL_PASSWORD: ''
  7. To install dependencies using Composer, run:

    composer install
    

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:
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;
};
<html>
    <body>
        

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

    For example, you can set the MySQL connection parameters with the following commands:

    cd php-docs-samples/appengine/standard/cloudsql
    
    export MYSQL_DSN="mysql:host=127.0.0.1;port=3306;dbname=guestbook"
    export MYSQL_USERNAME=root
    export MYSQL_PASSWORD=
    
    php -S localhost:8080
    

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