Connect to Cloud SQL from App Engine

blog-post-img
By Simon Martinelli
11.07.2022

Most applications need a database to store data and usually, a SQL database is used.
Google Cloud provides Cloud SQL that supports PostgreSQL, MySQL, and SQL Server.

Creating the Database

In my example project I want to use PostgreSQL. For the purpose of an example use JPA with a single Entity and also let JPA create the database table. I wouldn’t recommend that in a real-world project with more than a few entities. There you should better use a database migration tool like Flyway or Liquibase.

To save money I created the smallest possible PostgreSQL instance:

After a while, the database is up and running. I then added a database called playground and also a user with the same name.

Next, I want to connect my Java application to the database.

Connection to the Database

To connect to the database from App Engine you can use the instance name. To make this work you have to add a Google Cloud starter dependency:

<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-sql-postgresql</artifactId>
</dependency>

This started autoconfiguring the connection and you can set the database name and instance name in the properties. The instance name consists of <project-name>:<region>:<database-name>

spring.cloud.gcp.sql.database-name=playground
spring.cloud.gcp.sql.instance-connection-name=organic-gecko-350604:europe-west6:postgresql-playground

Then you’ll need to set username and password using the standard Spring Data properties:

spring.datasource.username=playground
spring.datasource.password=xxx

But wait! I don’t want to provide the password here! Is there no other way?

Secrets Manager

Sure there is! Simply use the Google Cloud Secret Manger. There you can store sensitive information.

But how can I get the password from the secret manager into my application? Simply add another dependency from Google Cloud:

<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-secretmanager</artifactId>
</dependency>

Then you can use a special syntax in your application.properties to get the password:

spring.datasource.password=${sm://postgresql-playground-password}

Local Development

Now when you are developing on your local machine you may want to connect to a local PostgreSQL. I solved this by creating a profile for local development and adding an application-dev.properties file. There you can see the local database connection and also deactivate the Cloud configuration.

spring.cloud.gcp.core.enabled=false
spring.cloud.gcp.sql.enabled=false
spring.cloud.gcp.secretmanager.enabled=false

spring.datasource.url=jdbc:postgresql://localhost:5434/playground
spring.datasource.username=playground
spring.datasource.password=playground

There are various ways to activate this profile. One way is using a system property:

-Dspring.profiles.active=de

Conclusion

Using a SQL database on Google Cloud and connecting a Java application is thanks to the Google Cloud starters straight forwarded. During local development, you don’t need any connections to the cloud if using a local database.