Configuring Spring Boot for PostgreSQL

Spring Boot makes it extremely convenient for programmers to quickly develop Spring applications using an in-memory database, such as H2, HSQLDB, and Derby. These databases are lightweight, easy to use, and emulate other RDBMSs with the help of JPA and Hibernate. Obviously, they don’t provide persistent storage, but they offer a fast way to test persistent functions of your Spring Boot application without going through the hassle of having to install a database server. They are great to use during development when you need to populate your database once your application starts, test your persistent entity mappings, and remove any data when your application ends. To use the embedded databases, you don’t need any special configuration, not even any connection URL. If you are using Maven, you only specify the dependency of the database to use in the POM file. Spring Boot automatically sets up the in-memory database for your use when it finds the database on your classpath.

In-memory databases are useful in the early development stages in local environments, but they have lots of restrictions. As the development progresses, you would most probably require an RDBMS to develop and test your application before deploying it to use a production database server, such as Oracle, MySQL, or PostgreSQL.

Previously, I wrote about creating a web application using Spring Boot and also wrote about configuring Spring Boot to use MySQL for storing the application data. In this post, we will learn how to change Spring Boot from the default in-memory H2 to PostgreSQL, which is one of the most advanced open source database that you will frequently see in production use.

PostgreSQL Configuration

For this post, I’m using PostgreSQL running locally on my laptop. Let’s first start by creating a database for use with Spring Boot. You can do this by using the createdb command line tool. You can locate this tool in the bin folder of your PostgreSQL installation. To create a database named springbootdb, open a command prompt/terminal window and run the following command.

createdb -h localhost -p 5432 -U postgres springbootdb password *********

PostgreSQL Dependency

To use PostgreSQL, you will need the proper database drivers. The required JARs are included in Central Repository of Maven. To include them in your project, you need to add the following dependency (for PostgreSQL 9.2 and later) to your Maven POM file with your specific version.

POM.xml

... 
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.4-1206-jdbc42</version>
</dependency> 
...

For PostgreSQL up to 9.1, you need to add the following dependency with your specific version.

POM.xml

...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901.jdbc4</version>
</dependency>
...

Spring Boot Properties

Our example application is using the H2 database for development. Since H2 is on the classpath, Spring Boot will automatically provide us common sense defaults for the H2 datasource. But this is only if you do not specify another datasource. Thus, by simply providing properties for the PostgreSQL datasource we can override the H2 datasource.

application.properties

spring.datasource.url= jdbc:postgresql://localhost:5432/springbootdb 
spring.datasource.username=postgres spring.datasource.password=postgres@123   
spring.jpa.hibernate.ddl-auto=create-drop

Since, the example web application is using JPA, we configured Hibernate for PostgreSQL in Line 5 to use the create-drop option. This tells Hibernate to recreate the database on startup. However, in testing or production databases, you will want to use the validate option.

Running Spring Boot With PostgreSQL

Once you are done with the preceding configuration, Spring Boot becomes ready for use with PostgreSQL. When you start the project now, the Spring Boot application will use PostgreSQL for the database.