Database as a Service

Now that we’ve got our service pretty might tightened up lets get started moving towards deploying it. The first step is using a real database instead of the in memory one.

Cloud Based Database Setup

We’re going to use postgreSQL as our database. The most straightforward option is to run postgreSQL locally. However, this is not going to reflect how we would run in the cloud. Running a DB locally also uses up resources that we would rather have for our application. Although this doesn’t really matter much when we’re running locally, it’s going to have a large impact when we deploy into a production environment where every instance and CPU cycle costs us money.

So lets get started with a DB SaaS solution right off the bat. A very good, easy to setup and inexpensive solution is heroku. Get started by creating an account. Then we’re going to follow the instructions here to setup our database instance and get our datasource configuration. Keep track of these items:

• datasource connection url
• login
• password

Setup our Application Configuration

Open up our application.yaml file and add this configuration, making sure to put your datasource url into the correct location. The format is jdbc:postgresql://${host}/${database}

---

spring:
  profiles: herokudb
  datasource:
    hikari:
      connectionTimeout: 20000
      maximumPoolSize: 2
    url: jdbc:postgresql://ec2***.amazonaws.com:5432/df***e

We’re defining a spring profile here because we don’t want to use this datasource by default. If we put it into the default profile, it will become visible to our tests and they will prefer postgreSQL to H2, which is not preferred. By putting the postgreSQL configuration into a separate profile, we can control when we connect to it.

We could put our username and password here, but that would be extremely unsafe and also problematic if we have several environments (dev, test, prod, etc…). So for the meantime we’re going to put that into our runtime configuration.

Go into the Edit Configurations… menu item on the run dropdown in intellij. Go into the Configuration tab of the MediumCustomerApplication application. Expand the VM options section and add this configuration (replacing with your username and password)

-Dspring.profiles.active=herokudb
-Dspring.datasource.username=amk***d
-Dspring.datasource.password=2a***97c

I also had to update my pom.xml in order to get it working:

<dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
</dependency>
<dependency>
   <groupId>org.liquibase</groupId>
   <artifactId>liquibase-core</artifactId>
</dependency>

Click OK and then startup our application. We should see that it connected to our postgres database.

2020-03-30 13:54:01.157  INFO [customer,,,] 7732 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:postgresql://ec2****.amazonaws.com:5432/df***e'

Lets confirm with the DB Browser. Click on the DB Browser tab under project on intellij

Create a new connection to postgreSQL. Add your host information, username and password. Change the Database to the one supplied on the heroku datasource configuration page.

When we test connection we should see a success. Now lets put some data in and make a request to confirm we can see the state of the database.

Go ahead and use the postman POST statement we used to create a customer. Open up a DB browser window, change the database to public and execute this query:

select * from customer;

we should see this data returned:

One thing to make note of here is that if we send the POST request in multiple times, we get different behavior. It appears that new records are allowed to be created in postgres. We will address this as we add validation to this service.

Build and Commit

git checkout -b postgres
mvn clean install
git add .
git commit -m "Added postgreSQL configuration"
git push --set-upstream origin postgres
git checkout master
git merge postgres
git push