Using Cloud SQL for MySQL

This page shows how to connect to a 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 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 GCP project in the GCP 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 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 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 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 Cloud SQL Client or 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 Cloud SQL > Cloud SQL Client for the role.

    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.

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.

  1. Create a user:
    gcloud sql users create [USER_NAME] [HOST] --instance=[INSTANCE_NAME] --password=[PASSWORD]
    
  2. Create a database on your Cloud SQL instance named `sqldemo`.
    gcloud sql databases create sqldemo --instance=[INSTANCE_NAME]
    

Setting connection strings and adding a library

Use the Cloud SQL JDBC Socket Factory socket library to connect to your Cloud SQL 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`
    -->
        <INSTANCE_CONNECTION_NAME>Project:Region:Instance</INSTANCE_CONNECTION_NAME>
        <user>root</user>
        <password>myPassword</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 JDBC 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.4</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 {
  Connection conn;

  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {
    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";

    String path = req.getRequestURI();
    if (path.startsWith("/favicon.ico")) {
      return; // ignore the request for favicon.ico
    }

    PrintWriter out = resp.getWriter();
    resp.setContentType("text/plain");

    // 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)) + ".*.*";
    }

    Stopwatch stopwatch = Stopwatch.createStarted();
    try (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()) {
        stopwatch.stop();
        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");
        }
        out.println("Elapsed: " + stopwatch.elapsed(TimeUnit.MILLISECONDS));
      }
    } catch (SQLException e) {
      throw new ServletException("SQL error", e);
    }
  }

  @Override
  public void init() throws ServletException {
    try {
      String url;

      Properties properties = new Properties();
      try {
        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.
        return;
      }

      log("connecting to: " + url);
      try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url);
      } catch (ClassNotFoundException e) {
        throw new ServletException("Error loading JDBC Driver", e);
      } catch (SQLException e) {
        throw new ServletException("Unable to connect to PostGre", e);
      }

    } finally {
      // Nothing really to do here.
    }
  }
}

Testing and deploying

  1. To test your application locally:

    mvn clean jetty:run
    

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

    mvn clean appengine: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 Java docs