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.

You can connect to Cloud SQL First Generation or Second Generation instances.

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

Before you begin

  • Select or create a Cloud Platform Console project.

    Go to the Projects page

  • To test in your development environment, you need to connect to a local MySQL server. Install the MySQL Community Server.

    If you use Linux on a distribution with apt-get, you can run:

    sudo apt-get install mysql-server
    

Setting up

  1. In the same project as your App Engine application, create a Cloud SQL instance and configure the root user.

    You can use a Cloud SQL First Generation or a Second Generation instance. Second Generation instances provide increased performance at a lower cost than First Generation instances. Note that network latency between Second Generation instances and App Engine standard environment is approximately 1 ms longer than for First Generation instances.

    For more information about Cloud SQL Second Generation, see Second Generation Capabilities.

  2. For the Cloud SQL instance, create a database.
  3. The default user for App Engine applications is root@localhost. If you don't want to use the default user, create a user.
  4. If you are using a Cloud SQL First Generation instance, in the Cloud Platform Console, grant your App Engine application access to the Google Cloud SQL instance.

    If you are using a Cloud SQL Second Generation instance and your application is in the same project as the SQL instance, then your application should already have access.

  5. Get the Cloud SQL instance connection name to use as a connection string in your application code. To find the instance connection name, go to the Cloud SQL Instances page in the Cloud Platform Console.
    Go to the Cloud SQL Instances page
    1. Click on the Instance ID for the Cloud SQL instance you are using.
    2. Under Properties, find the Instance connection name.
  6. Add the Cloud SQL instance connection name, database name, user, and password to appengine-web.xml.
    <?xml version="1.0" encoding="utf-8"?>
    <!--
      Copyright 2016 Google Inc. All Rights Reserved.
      Licensed under the Apache License, Version 2.0 (the "License");
      you may not use this file except in compliance with the License.
      You may obtain a copy of the License at
            http://www.apache.org/licenses/LICENSE-2.0
      Unless required by applicable law or agreed to in writing, software
      distributed under the License is distributed on an "AS IS" BASIS,
      WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
      See the License for the specific language governing permissions and
      limitations under the License.
    -->
    <!-- [START config] -->
    <appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
      <application>YOUR-PROJECT-ID</application>
      <version>YOUR-VERSION-ID</version>
      <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-NAME?user=USERNAME&amp;password=PASSWORD" />
        <property name="ae-cloudsql.local-database-url" value="jdbc:mysql://localhost/DATABASE-NAME?user=USERNAME&amp;PASSWORD=password&amp;useSSL=false" />
      </system-properties>
    </appengine-web-app>
    <!-- [END config] -->
    
  7. Add a JDBC library to your application. For example, if you use Maven, you can add the dependency to the project's pom.xml:
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.3</version>
    </dependency>
    <!-- Parent POM defines ${appengine.sdk.version} (updates frequently). -->
    <dependency>
      <groupId>com.google.appengine</groupId>
      <artifactId>appengine-api-1.0-sdk</artifactId>
    </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:

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

The development server in the Google App Engine SDK can use a locally installed MySQL server instance to closely mirror the Google Cloud SQL environment during development.

To test in your development environment:

  1. Update the connection string in your application code to connect to a local instance of MySQL server.
  2. Start the MySQL server in your development environment.
  3. Start the development server. For example, if you use Maven:
    mvn appengine:devserver
  4. The web server is now running and listening for requests on port 8080. To view, visit the following URL:

    http://localhost:8080/

  5. Build and deploy your project to App Engine. For example, if you are using Maven:
    mvn clean package
    mvn appengine:update

Send feedback about...