Connect App Engine to Cloud SQL
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
I’m fighting for simplicity in software development.