Using Cloud SQL

This page shows how to connect to a Google Cloud SQL for MySQL 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 SQL database that lives in Google's cloud.

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

Before you begin

  1. Create or select a Cloud Platform project in the Cloud Platform Console and then ensure that project includes an App Engine application and billing is enabled:
    Go to App Engine

    The Dashboard opens if an App Engine application already exists in your project and billing is enabled. Otherwise, follow the prompts for choosing a region and enabling billing.

  2. Enable the Cloud SQL API.

    Enable the API

  3. To deploy your app with the gcloud tool, you must download, install, and initialize the Google Cloud SDK:
    Download the 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 default user on your Cloud SQL instance:
    gcloud sql users set-password root % --instance [INSTANCE_NAME] --password [PASSWORD]
    
  3. Record the connection name for the instance:
    gcloud sql instances describe [INSTANCE_NAME]
    

    For example:

    connectionName: project1:us-central1:instance1
    

    You can also find this value in the Instance details page of the Google Cloud Platform Console.

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, you can skip this section and go 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 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 Cloud SQL Client or Editor role, you can proceed to Setting up your local environment.
  7. Otherwise, add the service account by clicking Add.
  8. In the Add members dialog, provide the name of the service account and select Cloud SQL > Cloud SQL Client for the role.

    Alternatively, you can use the primitive Editor role by selecting Roles > Editor, but the Editor role includes permissions across Google Cloud Platform.

  9. Click Add.

    You should now see the service account listed with the specified 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 administration client for your database or the Cloud Platform Console.

  1. Install the Cloud SQL proxy:

    Linux 64-bit

    1. Download the proxy:
      wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
      
    2. 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 -O cloud_sql_proxy
      
    2. 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.

    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
        
        The specified port must not already be in use, for example, by a local database server.
      • 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 the administration client, you can install a local copy 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.

Setting up the Cloud SQL instance

The following instructions use the gcloud command-line tool to set up the instance. You can also use the administration client on your local machine or the administration client built into Cloud Shell.

Note: The sample code below creates the databases needed in the sample, but for more information, see Creating and Managing Databases.
  1. Create a user:

    gcloud sql users create [USER_NAME] [HOST] --instance=[INSTANCE_NAME] --password=[PASSWORD]
    

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_USER=[YOUR_USER]
    export MYSQL_PASSWORD=[YOUR_PASSWORD]
    export MYSQL_DATABASE=[YOUR_DATABASE]
    npm install
    

    By default, when you run the app locally it tries to connect using TCP sockets. If you configured the proxy to use Unix sockets, set this additional environment variable:

    export INSTANCE_CONNECTION_NAME=[YOUR_INSTANCE_CONNECTION_NAME]
    

    These environment variables are used to create the connection:

    const config = {
      user: process.env.SQL_USER,
      password: process.env.SQL_PASSWORD,
      database: process.env.SQL_DATABASE
    };
    
    if (process.env.INSTANCE_CONNECTION_NAME && process.env.NODE_ENV === 'production') {
      if (process.env.SQL_CLIENT === 'mysql') {
        config.socketPath = `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`;
      } else if (process.env.SQL_CLIENT === 'pg') {
        config.host = `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`;
      }
    }
    
    // Connect to the database
    const knex = Knex({
      client: process.env.SQL_CLIENT,
      connection: config
    });

  2. To allow your app to connect to your Cloud SQL 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: The deployed application will connect via unix sockets.

    env_variables:
      SQL_USER: YOUR_USER
      SQL_PASSWORD: YOUR_PASSWORD
      SQL_DATABASE: YOUR_DATABASE
      # e.g. my-awesome-project:us-central1:my-cloud-sql-instance
      INSTANCE_CONNECTION_NAME: YOUR_INSTANCE_CONNECTION_NAME
      SQL_CLIENT: YOUR_SQL_CLIENT # either 'pg' or 'mysql' (all lowercase)
  3. Add the beta_settings section to your app.yaml, using your Cloud SQL instance connection name.

    beta_settings:
      # The connection name of your instance, available by using
      # 'gcloud beta sql instances describe [INSTANCE_NAME]' or from
      # the Instance details page in the Google Cloud Platform Console.
      cloud_sql_instances: YOUR_INSTANCE_CONNECTION_NAME

  4. Add a MySQL library to your application's package.json, for example:

    "dependencies": {
      "express": "4.15.3",
      "knex": "^0.13.0",
      "mysql": "2.13.0",
      "pg": "^6.2.3",
      "prompt": "1.0.0"
    },

Running the sample code

The sample of server.js below creates a visitor log in a Cloud SQL instance.

Before running the sample, run createTables.js to create the tables you need and to ensure that the database is properly configured:

const Knex = require('knex');
const prompt = require('prompt');

/**
 * Create the "visits" table.
 *
 * @param {object} knex A Knex client object.
 */
function createTable (knex) {
  return knex.schema.createTable('visits', (table) => {
    table.increments();
    table.timestamp('timestamp');
    table.string('userIp');
  })
  .then(() => {
    console.log(`Successfully created 'visits' table.`);
    return knex;
  })
  .catch((err) => {
    console.error(`Failed to create 'visits' table:`, err);
    return knex;
  });
}

/**
 * Ask the user for connection configuration and create a new connection.
 */
function getConnection () {
  const FIELDS = ['user', 'password', 'database'];
  return new Promise((resolve, reject) => {
    prompt.start();
    prompt.get(FIELDS, (err, config) => {
      if (err) {
        return reject(err);
      }

      // Connect to the database
      return resolve(Knex({
        client: process.env.SQL_CLIENT,
        connection: config
      }));
    });
  });
}

exports.main = function () {
  getConnection()
    .then((knex) => {
      return createTable(knex);
    })
    .then((knex) => {
      return knex.destroy();
    })
    .catch((err, knex) => {
      console.error(`Failed to create database connection:`, err);
      if (knex) {
        knex.destroy();
      }
    });
};

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

const express = require('express');
const Knex = require('knex');
const crypto = require('crypto');

const app = express();
app.enable('trust proxy');
let knex;

function connect () {
  const config = {
    user: process.env.SQL_USER,
    password: process.env.SQL_PASSWORD,
    database: process.env.SQL_DATABASE
  };

  if (process.env.INSTANCE_CONNECTION_NAME && process.env.NODE_ENV === 'production') {
    if (process.env.SQL_CLIENT === 'mysql') {
      config.socketPath = `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`;
    } else if (process.env.SQL_CLIENT === 'pg') {
      config.host = `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`;
    }
  }

  // Connect to the database
  const knex = Knex({
    client: process.env.SQL_CLIENT,
    connection: config
  });

  return knex;
}

/**
 * Insert a visit record into the database.
 *
 * @param {object} visit The visit record to insert.
 */
function insertVisit (visit) {
  return knex('visits').insert(visit);
}

/**
 * Retrieve the latest 10 visit records from the database.
 */
function getVisits () {
  return knex.select('timestamp', 'userIp')
    .from('visits')
    .orderBy('timestamp', 'desc')
    .limit(10)
    .then((results) => {
      return results.map((visit) => `Time: ${visit.timestamp}, AddrHash: ${visit.userIp}`);
    });
}

app.get('/', (req, res, next) => {
  // Create a visit record to be stored in the database
  const visit = {
    timestamp: new Date(),
    // Store a hash of the visitor's ip address
    userIp: crypto.createHash('sha256').update(req.ip).digest('hex').substr(0, 7)
  };

  insertVisit(visit)
    .then(() => {
      // Query the last 10 visits from the database.
      return getVisits();
    })
    .then((visits) => {
      res
        .status(200)
        .set('Content-Type', 'text/plain')
        .send(`Last 10 visits:\n${visits.join('\n')}`)
        .end();
    })
    .catch((err) => {
      next(err);
    });
});

// Get type of SQL client to use
const sqlClient = process.env.SQL_CLIENT;
if (sqlClient === 'pg' || sqlClient === 'mysql') {
  knex = connect();
} else {
  throw new Error(`The SQL_CLIENT environment variable must be set to lowercase 'pg' or 'mysql'.`);
}

const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`App listening on port ${PORT}`);
  console.log('Press Ctrl+C to quit.');
});

Testing and deploying

  1. To test your application locally:

    npm start
    

  2. After local testing, deploy your app to App Engine:

    gcloud app deploy
    
  3. To launch your browser and view the app at http://[YOUR_PROJECT_ID].appspot.com, run the following command:

    gcloud app browse
    

Send feedback about...

App Engine flexible environment for Node.js docs