Google Cloud SQL — 4 ways (Spring)

Yesterday, I wrote of my investigations using Google Cloud SQL from Golang. Today, I’ve a summary using Spring. I’m going to optimistically state that there are 4 ways to do this. Truthfully, as I start writing, I have only tried 2 of these! ;-)

Please refer to yesterday’s post for the steps to:

  • Create GCP Project
  • Create Databases (MySQL; PostgreSQL)
  • Install the Cloud SQL Proxy
  • Configure Application Default Credentials

Spring

Spring provides a straightforward “Accessing Relational Data using JDCB with Spring” tutorial. We’ll use this as the basis for the 4 ways to connect to Cloud SQl databases with one very minor (!) code change. The tutorial uses H2 and it’s syntax differs from the syntax used by MySQL and by PostgreSQL MySQL and PostgreSQL both require Application.java line #32 to be:

jdbcTemplate.execute("DROP TABLE IF EXISTS customers");

I recommend you clone the Spring sample and run it per the instructions. I’m going to use Maven in this post:

mvn spring-boot:run

The console output should include:

.   ____          _            __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.5.6.RELEASE)
[main] hello.Application: Starting Application
...
[main] hello.Application: Creating tables
[main] hello.Application: Inserting customer record for John Woo
[main] hello.Application: Inserting customer record for Jeff Dean
[main] hello.Application: Inserting customer record for Josh Bloch
[main] hello.Application: Inserting customer record for Josh Long
[main] hello.Application: Querying for customer records where first_name = 'Josh':
[main] hello.Application: Customer[id=3, firstName='Josh', lastName='Bloch']
[main] hello.Application: Customer[id=4, firstName='Josh', lastName='Long']
[main] hello.Application: Started Application in 4.978 seconds (JVM running for 8.497)
[INFO] -------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] -------------------------------------------------------------
[INFO] Total time: 7.752 s
[INFO] Finished at: 2017-08-20T18:19:08Z
[INFO] Final Memory: 34M/559M
[INFO] -------------------------------------------------------------

Google Cloud SQL Spring Boot Starter

Google provides plugins to facilitate using Google Cloud Platform services from within Spring. Google Cloud SQL is now supported. The artifacts are not yet available in the central Maven repository. So, the first step is to clone Google’s repo, build and install the artifacts locally:

git clone https://github.com/spring-cloud/spring-cloud-gcp && \
cd spring-cloud-gcp && \
./mvnw install

All being well, this should conclude:

[INFO] ------------------------------------------------------------
[INFO] Reactor Summary:
[INFO]
[INFO] Spring Cloud GCP Dependencies ...................... SUCCESS
[INFO] Spring Cloud GCP ................................... SUCCESS
[INFO] Spring Cloud GCP Core Module ....................... SUCCESS
[INFO] Spring Cloud GCP Pub/Sub Module .................... SUCCESS
[INFO] Spring Cloud GCP Storage Module .................... SUCCESS
[INFO] Spring Cloud GCP Starters .......................... SUCCESS
[INFO] Spring Cloud GCP Core Starter ...................... SUCCESS
[INFO] Spring Cloud GCP Pub/Sub Starter ................... SUCCESS
[INFO] Spring Cloud GCP Storage Starter ................... SUCCESS
[INFO] Spring Cloud GCP Cloud SQL Starter ................. SUCCESS
[INFO] Spring Integration GCP ............................. SUCCESS
[INFO] Spring Cloud GCP Documentation ..................... SUCCESS
[INFO] ------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------

We will reference the “Spring Cloud GCP Cloud SQL Starter” artifact below.

MySQL

I recommend you comment out the database dependencies that aren’t being used. This minimizes confusion when swapping between databases. Comment out the H2 dependency in pom.xml:

<!--
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
-->

MySQL uses Prepared Statements with “?” denoting parameters. Please ensure you use this statement for Application.java line #45:

jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);

1. Google Cloud SQL Spring Boot Starter

You may reference the “Spring Cloud GCP Cloud SQL Starter” from your project pom.xml:

<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-gcp-starter-sql</artifactId>
<version>1.0.0.BUILD-SNAPSHOT</version>
</dependency>

Create a file called “application.properties” in src/main/resources”. The “resources” directory must be at the same level as the “java” directory. The content of the file should be as follows. NB in this case, you refer to the Cloud SQL database by the Instance ID (${ROOT}) not the connection name (${INSTANCE}):

spring.cloud.gcp.projectId=${PROJECT}
spring.cloud.gcp.sql.instanceName={$ROOT}-m
spring.cloud.gcp.sql.databaseName=${DBNAME}
spring.cloud.gcp.sql.userName=${DBUSER}
spring.cloud.gcp.sql.password=${DBPASS}

Run your code!

mvn spring-boot:run

2. Cloud SQL Proxy

When using the Cloud SQL Proxy as a “companion process”, your code uses a regular MySQL driver and treats the Cloud SQL MySQL instance as if it were running on localhost. To use the MySQL JDBC driver, add the following dependency to the pom.xml:

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

Create a file called “application.properties” in src/main/resources”. The “resources” directory must be at the same level as the “java” directory. The content of the file should be:

spring.jpa.hibernate.ddl-auto=create
spring.datasource.url=jdbc:mysql://localhost:3306/${DBNAME}
spring.datasource.username=${DBNAME}
spring.datasource.password=${DBPASS}

Please replace the ${VARIABLE} values using the constants for your database name, user and password.

Now, run the Cloud SQL Proxy configured for MySQL via TCP sockets:

./cloud_sql_proxy -instances=${INSTANCE}-m=tcp:3306

Run your code!

mvn spring-boot:run

PostgreSQL

PostgreSQL uses Prepared Statements with “$” prefixing each parameter. Please ensure you use this statement for Application.java line #45:

jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES ($1,$2)", splitUpNames);

3. Google Cloud SQL Spring Boot Starter

You may reference the “Spring Cloud GCP Cloud SQL Starter” from your project pom.xml:

<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-gcp-starter-sql</artifactId>
<version>1.0.0.BUILD-SNAPSHOT</version>
</dependency>

Additionally, we must reference the PostgreSQL JDBC Driver and Google Cloud SQL Socket Factory for JDBC Driver (for PostgreSQL):

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.4</version>
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>postgres-socket-factory</artifactId>
<version>1.0.4</version>
</dependency>

Create a file called “application.properties” in src/main/resources”. The “resources” directory must be at the same level as the “java” directory. The content of the file should be as follows:

spring.cloud.gcp.projectId=${PROJECT}
spring.cloud.gcp.sql.instanceName={$ROOT}-p

spring.cloud.gcp.sql.databaseName=${DBNAME}
spring.cloud.gcp.sql.userName=${DBUSER}
spring.cloud.gcp.sql.password=${DBPASS}

spring.cloud.gcp.sql.jdbcDriver=org.postgresql.Driver
spring.cloud.gcp.sql.jdbcUrl=jdbc:postgresql://google/${DBNAME}?socketFactory=com.google.cloud.sql.postgres.SocketFactory&socketFactoryArg=${INSTANCE}-p

Run your code!

mvn spring-boot:run

4. Cloud SQL Proxy

When using the Cloud SQL Proxy as a “companion process”, your code uses a regular PostgreSQL driver and treats the Cloud SQL PostgreSQL instance as if it were running on localhost. To use the PostgreSQL JDBC driver, add the following dependency to the pom.xml:

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.4</version>
</dependency>

Create a file called “application.properties” in src/main/resources”. The “resources” directory must be at the same level as the “java” directory. The content of file should be:

spring.datasource.url=jdbc:postgresql://localhost:5432/${DBNAME}
spring.datasource.username=${DBUSER}
spring.datasource.password=${DBPASS}

You do not need to remove the properties for the other database drivers. These will be ignored when you comment out the driver references.

Now run the Cloud SQL Proxy configured for PostgreSQL via TCP sockets:

./cloud_sql_proxy -instances=${INSTANCE}-p=tcp:5432

Run your code!

mvn spring-boot:run

Conclusion

We demonstrated 4 ways to connect (Java) Spring code to Google Cloud SQL.

If you’d prefer to also run the Java code from within a Docker container, you can use the Maven image (JDK 8). You must use a service account instead of Application Default Credentials and the service account must be granted the role “Cloud SQL > Cloud SQL Client”.

To facilitate using your editor of choice etc., I recommend you create the Spring example on your localhost and map this directory into the container.

docker run \
--interactive \
--tty \
--net=host \
--volume=/path/to/your/client_secrets.json:/client_secrets.json \
--volume=/path/to/your/${PROJECT}:/projects/${PROJECT} \
maven:3-jdk-8 /bin/bash

Then, within the container:

export GOOGLE_APPLICATION_CREDENTIALS=/client_secrets.json
cd /projects && \
git clone https://github.com/spring-cloud/spring-cloud-gcp && \
cd spring-cloud-gcp && \
./mvnw install && \
cd /projects/${PROJECT}

If you would prefer, you may also run the Cloud SQL Proxy as a Docker container. However, you must also use a service account instead of Application Default Credentials and the service account must be granted the role “Cloud SQL > Cloud SQL Client”.

Here’s the format for MySQL:

docker run \
--volume=/path/to/client-secret.json:/client_secrets.json \
--publish=127.0.0.1:3306:3306 \
gcr.io/cloudsql-docker/gce-proxy:1.10 \
/cloud_sql_proxy \
-instances=${INSTANCE}-m=tcp:0.0.0.0:3306 \
-credential_file=/client_secrets.json

Here’s the format for PostgreSQL:

docker run \
--volume=/path/to/you/client-secret.json:/client_secrets.json \
--publish=127.0.0.1:5432:5432 \
gcr.io/cloudsql-docker/gce-proxy:1.10 \
/cloud_sql_proxy \
-instances=${INSTANCE}-p=tcp:0.0.0.0:5432 \
-credential_file=/client_secrets.json