Using Google Cloud SQL

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.

For information on pricing and restrictions imposed by both Cloud SQL and App Engine, see Pricing and Access Limits.

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:
    Go to App Engine

    The Dashboard opens if an App Engine application already exists in your project. Otherwise, you are prompted to choose the region where you want your App Engine application located.

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

Configuring your local environment

You can either use a local MySQL server to test your application or you can connect to Cloud SQL.

If you want to test your application with a local MySQL server, install it now:

Setting up the Cloud SQL instance

  1. Create a Second Generation Cloud SQL instance and configure the root user.
  2. If you don't want to use the root user to connect, create a user.
  3. Using the Cloud SDK, get the Cloud SQL instance connection name to use as a connection string in your application code:
    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
  4. Create a database on your Cloud SQL instance named sqldemo.

    For more information, see Creating a database.

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

  1. Add the Cloud SQL instance connection name, database, user, and password to pom.xml.

    Java 8

      <properties>
    <!-- INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details > Properties
         or gcloud sql instances describe <instance>
         project:region:instance for Cloud SQL 2nd Generation or
         project:instance        for Cloud SQL 1st Generation
    -->
        <INSTANCE_CONNECTION_NAME></INSTANCE_CONNECTION_NAME>
        <user>root</user>
        <password>myPassword</password>
        <database>sqldemo</database>
    
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
      </properties>

    Java 7

      <properties>
    <!-- INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details > Properties
         or gcloud sql instances describe <instance>
         project:region:instance for Cloud SQL 2nd Generation or
         project:instance        for Cloud SQL 1st Generation
    -->
        <INSTANCE_CONNECTION_NAME></INSTANCE_CONNECTION_NAME>
        <user>root</user>
        <password>myPassword</password>
        <database>sqldemo</database>
    
    <!-- ...
      </properties>

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

    Java 8

    <!-- Driver injected at runtime by the use of <use-google-connector-j> in appengine-web.xml -->
    <dependency> <!-- Only used locally -->
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.42</version>  <!-- v5.x.x is for production, v6.x.x EAP X DevAPI -->
    </dependency>
    <dependency>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>mysql-socket-factory</artifactId> <!-- mysql-socket-factory-connector-j-6 if using 6.x.x -->
      <version>1.0.4</version>
    </dependency>

    Java 7

    <dependency> <!-- ONLY USED LOCALY -->
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.40</version>  <!-- v5.x.x is for production, v6.x.x EAP X DevAPI -->
    </dependency>
    <dependency>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>mysql-socket-factory</artifactId>
      <version>1.0.4</version>
    </dependency>

Code sample overview

The following code sample creates a visitor log in a Cloud SQL instance. It writes visit information to Cloud SQL and then reads and returns the last ten visits:

Java 8

@SuppressWarnings("serial")
// With @WebServlet annotation the webapp/WEB-INF/web.xml is no longer required.
@WebServlet(name = "CloudSQL", description = "CloudSQL: Write low order IP address to Cloud SQL",
    urlPatterns = "/cloudsql")
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 {
      ApiProxy.Environment env = ApiProxy.getCurrentEnvironment();
      Map<String,Object> attr = env.getAttributes();
      String hostname = (String) attr.get("com.google.appengine.runtime.default_version_hostname");

      String url = hostname.contains("localhost:")
          ? System.getProperty("cloudsql-local") : System.getProperty("cloudsql");
      log("connecting to: " + url);
      try {
        conn = DriverManager.getConnection(url);
      } catch (SQLException e) {
        throw new ServletException("Unable to connect to Cloud SQL", e);
      }

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

Java 7

@SuppressWarnings("serial")
public class CloudSqlServlet extends HttpServlet {

  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {
    String path = req.getRequestURI();
    if (path.startsWith("/favicon.ico")) {
      return; // ignore the request for favicon.ico
    }
    // 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");
    String url;
    if (System
        .getProperty("com.google.appengine.runtime.version").startsWith("Google App Engine/")) {
      // Check the System properties to determine if we are running on appengine or not
      // Google App Engine sets a few system properties that will reliably be present on a remote
      // instance.
      url = System.getProperty("ae-cloudsql.cloudsql-database-url");
      try {
        // Load the class that provides the new "jdbc:google:mysql://" prefix.
        Class.forName("com.mysql.jdbc.GoogleDriver");
      } catch (ClassNotFoundException e) {
        throw new ServletException("Error loading Google JDBC Driver", e);
      }
    } else {
      // Set the url with the local MySQL database connection url when running locally
      url = System.getProperty("ae-cloudsql.local-database-url");
    }
    log("connecting to: " + 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);
    }
  }
}

The properties set in the maven pom are used to create a connection URL below:

Java 8

<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
  <threadsafe>true</threadsafe>
  <runtime>java8</runtime>
  <use-google-connector-j>true</use-google-connector-j>

  <service>cloudsql</service>

  <system-properties>
    <property name="cloudsql" value="jdbc:google:mysql://${INSTANCE_CONNECTION_NAME}/${database}?user=${user}&amp;password=${password}" />
    <property name="cloudsql-local" value="jdbc:mysql://google/${database}?useSSL=false&amp;cloudSqlInstance=${INSTANCE_CONNECTION_NAME}&amp;socketFactory=com.google.cloud.sql.mysql.SocketFactory&amp;user=${user}&amp;password=${password}" />
  </system-properties>
</appengine-web-app>

Java 7

<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
  <threadsafe>true</threadsafe>
  <use-google-connector-j>true</use-google-connector-j>
  <system-properties>
    <property name="ae-cloudsql.cloudsql-database-url" value="jdbc:google:mysql://${INSTANCE_CONNECTION_NAME}/${database}?user=${user}&amp;password=${password}" />
    <property name="ae-cloudsql.local-database-url" value="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" />
  </system-properties>
</appengine-web-app>

Testing in your development environment

To test your app with the local development server:

  1. If you are using a local MySQL server, start the MySQL server in your development environment.
  2. Start the development server. For example, if you use Maven:

    Java 8

    mvn appengine:run

    Java 7

    mvn appengine:devserver

  3. The web server is now running and listening for requests on port 8080. To view, visit the following URL:

    http://localhost:8080/

Something go wrong? See Using the Local Development Server for more information.

Deploying your app

To upload your app to App Engine:

Run the following Maven command:

Java 8

mvn appengine:deploy

Java 7

mvn clean appengine:update

That's it! Open your browser and view the app at http://[YOUR_PROJECT_ID].appspot.com.

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

App Engine standard environment for Java