Connect to Cloud SQL from App Engine
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
Then you’ll need to set username and password using the standard Spring Data properties:
But wait! I don’t want to provide the password here! Is there no other way?
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:
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:
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.