Using Cloud SQL

This page shows how to connect to a Cloud SQL 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 Google 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. Select or create a Cloud Platform project.

    Go to the Projects page

  2. Enable the Cloud SQL Administration API.

    Enable the API

  3. Install and initialize the Cloud SDK.

Creating an instance and setting the password

To create an instance and set the password for the root user:

  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. For example, in the command line:

    gcloud sql instances set-root-password [YOUR_INSTANCE_NAME] --password [YOUR_INSTANCE_ROOT_PASSWORD]
    

Granting access to App Engine

If your App Engine application and Cloud SQL instance are in different Cloud Platform Console 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 Cloud Platform Console project.

  1. If your App Engine application is in the same Google Cloud Platform project as your Cloud SQL instance, proceed 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 Cloud Platform Console 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 your local environment.
  7. Otherwise, add the service account by clicking Add member.
  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.

Creating a user and managing user access

Use the MySQL Client command line tools or the MySQL Client built into Cloud Shell to create a user. For more information, see Connecting MySQL Client Using IP Addresses.

  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]'@'%';
    

Setting connection strings and adding a library

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

  1. To allow your app to connect to your Cloud SQL instance for local testing, set the related connection string environment variable.

    For example, for the provided code sample:

    export SQL_URL="jdbc:mysql://google/[YOUR_DB_NAME]?cloudSqlInstance=[YOUR_INSTANCE_NAME]&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=[YOUR_USERNAME]&password=[YOUR_PASSWORD]"
    

  2. To allow your app to connect to your Cloud SQL instance when it is deployed, add the user, password, project id, region, and instance name variables from Cloud SQL to the related environment variables in the app.yaml file:

    # YOUR-INSTANCE-NAME is ProjectID:Region:DbInstance
    # YOUR-DB-NAME is your DatabaseName
    env_variables:
      SQL_URL: jdbc:mysql://google/YOUR-DB-NAME?cloudSqlInstance=YOUR-INSTANCE-NAME&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=USERNAME&password=PASSWORD
  3. Add a JDBC libary 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>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>mysql-socket-factory</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 {

  @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");
    // Detect if running remotely or locally and select correct connection url
    String url = System.getenv("SQL_URL");

    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);
    }
  }
}

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