Connect App Engine to Cloud SQL

blog-post-img
By Simon Martinelli
11.07.2022

Building modern applications requires a reliable and scalable database solution. For Java developers on Google Cloud, Cloud SQL offers a powerful option, supporting popular choices like PostgreSQL, MySQL, and SQL Server. This guide will walk you through securely connecting your Java App Engine application to a Cloud SQL database.

Choosing and Setting Up Your Cloud SQL 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.

Securely Connecting Your App Engine App

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=dev

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.

Need help configuring Cloud SQL for your Java application? Contact me for help.

Related Posts

Simon Martinelli
Follow Me
Latest posts by Simon Martinelli (see all)