Connecting to Heroku Postgres from Spring Boot

In this article, I will show you how to connect and use your Heroku Postgres within Spring Boot.
If you haven’t done yet, first go to your Heroku dashboard, navigate to Resources tab and add Heroku Postgres add-on to your project. And then you can click into the database add-on to find your credentials in Settings tab. Heroku already has a nice document to learn how to connect this database locally. There are also lot more information on that page. I recommend you to read it.
Once you have access to your DB, you can move to the next part, which is connecting it via Spring Boot.

Spring Boot Configuration 

So, how to connect to Heroku Postgresql with Spring Boot?
First you should add required dependencies to your project. All you need is spring-boot-starter-data-jpa and postgresql dependencies. This is my current dependencies, versions may be different by the time you are reading this tutorial:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
</dependency>

Spring Boot uses application.properties to configure the database. Easiest way to connect to database is providing database information inside the application.properties.
Example application.property should look like this:

spring.datasource.url=${SPRING_DATASOURCE_URL}
spring.datasource.username=${SPRING_DATASOURCE_USERNAME}
spring.datasource.password=${SPRING_DATASOURCE_PASSWORD}
spring.jpa.database-platform=${SPRING_JPA_DATABASE-PLATFORM}

 

Heroku Configuration

As you can see I used environment variables instead of directly writing the clear values. Because you might want to share this application on anywhere and you don’t want to give your database credentials as plain text. Setting environment variables on Heroku is very easy. Just navigate to Settings tab of your dashboard and click on Reveal Config Vars. You will see DATABASE_URL is already set there. It is automatically created by Heroku. We need to add our values one by one. After everything is done, it should look like this:
heroku-config

One very very important note is that, do NOT directly copy the value from URI(Postgres Credentials page) page or DATABASE_URL(Heroku Config Vars page) into the SPRING_DATASOURCE_URL. It wouldn’t work.
URI/DATABASE_URL consist of: postgres://username:password@host:port/database
But SPRING_DATASOURCE_URL should be: jdbc:postgresql://host:port/database
We are not adding username and password to the url because we are providing them as separate values. Also we are adding jdbc at the beginning and adding ql at the end of postgres.
You might notice I also have a variable called SPRING_JPA_HIBERNATE_DDL-AUTO. This is not mandatory but very convenient if you don’t want to create your tables with sql commands. Hibernate will do it automatically for you. You can add it to your application.properties like:

spring.jpa.hibernate.ddl-auto=${SPRING_JPA_HIBERNATE_DDL-AUTO}


Of course you don’t need to set this as environment variable, you can directly set it to update. Possible values for this configuration:

  • validate: validate the schema, makes no changes to the database.
  • update: update the schema.
  • create: creates the schema, destroying previous data.
  • create-drop: drop the schema at the end of the session

So, update makes the most sense for me. Because create would destroy all the previous data when application restarts.
We are more or less ready to go but if you run your application like this, you might see an error in the logs like:

Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException


and

Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.

Now this is not because you done something wrong. And this error will not prevent your application from booting up. But still it is annoying and we can prevent it by adding one more parameter to our application.properties:

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation = true


After adding this parameter error should not appear. You can find more information regarding that error on this discussion in Github.

One more note, Spring Boot will use Hikari pool if you don’t specify a datasource type. I had no problem with default settings but if you are having a problem with Hikari, you can try using Tomcat by giving the following parameter in application.properties:

spring.datasource.type = org.apache.tomcat.jdbc.pool.DataSource

Also you have to provide related Tomcat dependency:

<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
    <version>9.0.21</version>
</dependency>
P.S:
My hobby Spring Boot application OfficeEyes is already deployed on Heroku and using Heroku Postgres. You can check how I am using repository and model objects by checking that project.
You can also check answers to this StackOverFlow question which helped me when I was trying to connect to Heroku Postgres at the first time.
I hope this article gave you overall idea on connecting to Heroku Postgres from Spring Boot. Thanks for reading!
If you liked the content please share it!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.