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

    Enable the API

  3. To deploy a Java app to App Engine, you must first setup your environment, see Using Apache Maven and the App Engine Plugin for details.

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

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 the Cloud SQL instance. 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 Editor role, you can proceed to Setting up the Cloud SQL instance.
  7. Otherwise, add the service account by clicking Add.
  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 the Cloud SQL instance

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]'@'%';
    
  3. Create a database on your Cloud SQL instance named sqldemo.

    For more information, see Creating a database.

Setting connection strings and adding a library

Use the Cloud SQL MySQL Socket Factory socket library to connect to your Cloud SQL Second Generation instance for local testing and in deployment.

  1. Update pom.xml with your connection name, user name, and password:

      <properties>
    <!-- INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details > Properties
         or `gcloud sql instances describe <instance> | grep connectionName`
         project:region:instance for Cloud SQL 2nd Generation
    -->
        <INSTANCE_CONNECTION_NAME></INSTANCE_CONNECTION_NAME>
        <user>root</user>
        <password></password>
        <database>sqldemo</database>
    <!-- ...
        <sqlURL>jdbc:mysql://google/${database}?cloudSqlInstance=${INSTANCE_CONNECTION_NAME}&amp;socketFactory=com.google.cloud.sql.mysql.SocketFactory&amp;user=${user}&amp;password=${password}&amp;useSSL=false</sqlURL>
      </properties>

  2. Add a JDBC library and the Cloud SQL MySQL Socket Factory to your application. For example, if you use Maven, you can add the dependency to the project's pom.xml:

    <dependency>                        <!-- http://dev.mysql.com/doc/connector-j/en/ -->
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.5</version>
    </dependency>
    <dependency>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>mysql-socket-factory-connector-j-6</artifactId>
      <version>1.0.2</version>
    </dependency>

Running the sample code

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

@SuppressWarnings("serial")
@WebServlet(name = "cloudsql", value = "")
public class CloudSqlServlet extends HttpServlet {
  String url;

  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {
    // store only the first two octets of a users ip address
    String userIp = req.getRemoteAddr();
    InetAddress address = InetAddress.getByName(userIp);
    if (address instanceof Inet6Address) {
      // nest indexOf calls to find the second occurrence of a character in a
      // string
      // an alternative is to use Apache Commons Lang:
      // StringUtils.ordinalIndexOf()
      userIp = userIp.substring(0, userIp.indexOf(":", userIp.indexOf(":") + 1)) + ":*:*:*:*:*:*";
    } else if (address instanceof Inet4Address) {
      userIp = userIp.substring(0, userIp.indexOf(".", userIp.indexOf(".") + 1)) + ".*.*";
    }

    final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id INT NOT NULL "
        + "AUTO_INCREMENT, user_ip VARCHAR(46) NOT NULL, timestamp DATETIME NOT NULL, "
        + "PRIMARY KEY (visit_id) )";
    final String createVisitSql = "INSERT INTO visits (user_ip, timestamp) VALUES (?, ?)";
    final String selectSql = "SELECT user_ip, timestamp FROM visits ORDER BY timestamp DESC "
        + "LIMIT 10";
    PrintWriter out = resp.getWriter();
    resp.setContentType("text/plain");

    try (Connection conn = DriverManager.getConnection(url);
        PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {
      conn.createStatement().executeUpdate(createTableSql);
      statementCreateVisit.setString(1, userIp);
      statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));
      statementCreateVisit.executeUpdate();

      try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
        out.print("Last 10 visits:\n");
        while (rs.next()) {
          String savedIp = rs.getString("user_ip");
          String timeStamp = rs.getString("timestamp");
          out.print("Time: " + timeStamp + " Addr: " + savedIp + "\n");
        }
      }
    } catch (SQLException e) {
      throw new ServletException("SQL error", e);
    }
  }

  @Override
  public void init() {
    try {
      Properties properties = new Properties();
      properties.load(
          getServletContext().getResourceAsStream("/WEB-INF/classes/config.properties"));
      url = properties.getProperty("sqlUrl");
    } catch (IOException e) {
      log("no property", e);  // Servlet Init should never fail.
    }
  }
}

Testing and Deploying

  1. To test your application locally:

    mvn clean jetty:run
    

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

    mvn clean appengine:deploy
    

Send feedback about...

App Engine flexible environment for Java docs