First Steps With GCP SQL

In this post, we will take a look at how we can use Google Cloud Platform (GCP) SQL as a database for our Spring Boot application. We will investigate how we can use the Cloud database from our development machine and how we can use it from GCP itself.

1. Create a GCP Account

First, you will need to create a GCP account. Check out the first paragraph of a previous post on how to do so.

2. Create the Example Application

We will create a Spring Boot MVC web application, which uses a PostgreSQL database in the Cloud. Go to start.spring.io and create a Java 11 Web MVC project. The application will simply return a list of Shows when accessing a shows URL. The sources are available at GitHub.

The only domain object is Show, which has an id and a title. The getters and setters are left out for brevity.

public class Show {
    private Long id;
    private String title;
    ...
}

For accessing the database, we will use JdbcTemplate. We only define a findAll method in the ShowDao, which retrieves all Shows from the database. We use a ShowRowMapper in order to map the table columns to the Showdomain object.

@Repository
public class ShowDaoImpl implements ShowDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public List findAll() {
        return jdbcTemplate.query("select * from show", new ShowRowMapper());
    }

}

class ShowRowMapper implements RowMapper {
    @Override
    public Show mapRow(ResultSet rs, int rowNum) throws SQLException {
        Show show = new Show();

        show.setId(rs.getLong("ID"));
        show.setTitle(rs.getString("TITLE"));

        return show;
    }
}

The ShowService will retrieve the data from the database via the ShowDaoobject.

@Component
public class ShowServiceImpl implements ShowService {

    @Resource
    ShowDao showDao;

    @Override
    public List findAll() {
        return showDao.findAll();
    }

}

And finally, we define a shows URL that routes to the shows method from the ShowController and that will return the values from the database as JSON.

@RestController
public class ShowController {

    @Resource
    ShowService showService;

    @RequestMapping("/shows")
    public List shows() {
        return showService.findAll();
    }
}

In file src/main/resources/schema.sql, we define the Show table:

CREATE TABLE show (
    id SERIAL NOT NULL,
    title VARCHAR(200) NOT NULL,
    PRIMARY KEY (id)
);

In file src/main/resources/data.sql, we define some contents for the Showtable:

INSERT INTO show VALUES (1, 'My Developer Planet Show 1');
INSERT INTO show VALUES (2, 'My Developer Planet Show 2');
INSERT INTO show VALUES (3, 'My Developer Planet Show 3');
INSERT INTO show VALUES (4, 'My Developer Planet Show 4');
INSERT INTO show VALUES (5, 'My Developer Planet Show 5');

In the pom.xml file, we need a dependency for Spring Web MVC and a dependency for Spring Cloud GCP PostgreSQL.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
<dependency>
  <groupId>org.springframework.cloud</groupId>
  <artifactId>spring-cloud-gcp-starter-sql-postgresql</artifactId>
  <version>1.1.1.RELEASE</version>
</dependency>

3. Use GCP SQL From Your Local Development Machine

Now that we have created our application, it is time to set up the database and the necessary configuration in our application in order to run it from our local development machine.

3.1 Create Instance

In the GCP console, go to SQL and create a SQL instance.

We have the choice between MySQL and PostgreSQL. We choose PostgreSQL.

Fill in the necessary configuration options. We set the Instance ID to myspringcloud, set up a password for the postgres user, choose a region, and click the Create button.

After a minute or so, the SQL instance is created.

3.2 Create Database

Click the Instance ID myspringcloud and select the Databases tab.

Click the Create database button and create a myspringclouddb database.

3.3 Allow Connection From Local Machine

By default, it is not allowed to connect to the database from outside your GCP account. We, therefore, need to allow the IP address from our local development machine. Go to the Instance Details and the Connections tab. Ensure that Public IP is enabled.

Click the Add network button, fill in a name for your connection, e.g. mydevelopermachine, and fill in your public IP address which can be retrieved via whatismyip.com. At last, click the Done button and the Save button.

3.4 Create a Service Account

The last thing to do is to create a service account for our local development machine. Go to IAM & admin - Service accounts and click the Create Service Account button.

Enter a name for your service account, in our case mydevelopermachine, and click the Create button.

Grant the service account the Owner role and click the Continue button.

In the last step, we click the Create key button.

Choose JSON as Key type and click the Create button.

A JSON file is downloaded; keep it safe somewhere, we will need it later on.

3.5 Spring Database Configuration

Now that we have set up the database, it is time to return to the Spring application. We will need to configure some properties in the application.properties file.

spring.datasource.username=postgres
spring.datasource.password=[password]
spring.cloud.gcp.sql.database-name=myspringclouddb
spring.cloud.gcp.sql.instance-connection-name=mysqlcloud-236114:europe-west1:myspringcloud
spring.datasource.continue-on-error=true
spring.datasource.initialization-mode=always
spring.datasource.schema=file:src/main/resources/schema.sql
spring.datasource.data=file:src/main/resources/data.sql
spring.cloud.gcp.project-id=mysqlcloud-236114
spring.cloud.gcp.credentials.location=file:src/main/resources/service-account.json

Let’s explain the different properties:

spring.datasource.username: this is the database user we will use. It is also possible of course to create a user specifically for your application, but for simplicity, we use the postgres user for it.

spring.datasource.password: the password of the postgres user, replace [password] with your own password.

spring.cloud.gcp.sql.database-name: the GCP Cloud database name which we defined previously.

spring.cloud.gcp.sql.instance-connection-name: the connection name of the GCP SQL instance. This must be of the form [gcp-project-id]:[region]:[instance-name]

spring.datasource.continue-on-error: we will create the tables on startup, this property will prevent failure when the table already exists.

spring.datasource.initialization-mode: this property will create the table and fill the table with data during startup.

spring.datasource.schema: we indicate where the schema for our database can be found.

spring.datasource.data: we indicate where the data for our database can be found.

spring.cloud.gcp.project-id: this property is necessary for accessing the Cloud database from our local development machine and must contain the GCP project ID.

spring.cloud.gcp.credentials.location: this property is necessary for accessing the Cloud database from our local development machine and must refer to the JSON file we downloaded for our service account.

3.6 Run the Application

Run the Spring Boot application from our local development machine.

$ mvn spring-boot:run

Go to the URL http://localhost:8080/shows when the application has started successfully. The following response is returned.

[
  {"id":1,"title":"My Developer Planet Show 1"},
  {"id":2,"title":"My Developer Planet Show 2"},
  {"id":3,"title":"My Developer Planet Show 3"},
  {"id":4,"title":"My Developer Planet Show 4"},
  {"id":5,"title":"My Developer Planet Show 5"}
]

4. Use GCP SQL From the GCP Console

In this paragraph, we will adapt our previously created application and configuration in order to run it from the GCP console. The sources can be found in branch feature/gcpconsole available at GitHub.

We need to adapt the Java version in our pom.xml file (Java 1.8 is the default on GCP console):

<properties>
  <java.version>1.8</java.version>
</properties>

In the application.properties file, we remove the entries which were necessary for access from our local development machine: spring.cloud.gcp.project-id and spring.cloud.gcp.credentials.location.

We also don’t need the service account mydevelopermachine and it is not necessary to enable the Public IP connection.

Go to the GCP console and clone the git repository:

$ git clone --branch feature/gcpconsole https://github.com/mydeveloperplanet/myspringcloudgcpplanet.git

Enter the myspringcloudgcpplanet directory and run the application:

$ mvn spring-boot:run

Start the Web Preview which will open a browser tab to the following URL:

https://8080-dot-6340638-dot-devshell.appspot.com/?authuser=0

This will return a 404 error. Adapt the URL in order to retrieve the shows:

https://8080-dot-6340638-dot-devshell.appspot.com/shows/?authuser=0

The response is identical to the one issued from our local machine.

Conclusion

In this post, we created a Spring Boot MVC web application with a Cloud PostgreSQL database. We ran the application from our local development machine and from within the GCP console. It was fairly easy to use the GCP SQL database. In only a few minutes, you have an up-and-running database in the Cloud that can be accessed from a local machine or from within your GCP account. Don’t forget to remove the database instances after experimenting!