Configuring Spring Boot for PostgreSQL

Configuring Spring Boot for PostgreSQL

8 Comments

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 emulates other RDBMS with the help of JPA and Hibernate. Obviously, they don’t provide persistent storage; but they a fast way to test persistent functions of your Spring Boot application without going through the hassles of installing 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

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 the 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.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.

Spring Framework 5
Click Here to Become a Spring Framework Guru!

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.

About jt

    You May Also Like

    8 comments on “Configuring Spring Boot for PostgreSQL

    1. June 22, 2016 at 11:52 am

      Can you also post it for gradle?

      Reply
    2. July 21, 2016 at 5:13 am

      Hey. Nice blog. Congrats.

      I only have 1 quick question. Where are you defining psql schema that contains the tables?? Is it defined in the class that maps a table or in the application.properties as well?? Because ive tried after some research to define it there with the property “spring.datasource.schema=…” and its not working.
      My project its failing and i think thats the main reason.
      I know you post it long time ago, but if you could help me.

      Thanks in advance.

      Reply
      • July 21, 2016 at 9:16 am

        Check you datasource url. The schema is at the end.

        Reply
    3. September 3, 2016 at 1:15 pm

      I got rid of that line spring.jpa.hibernate.ddl-auto=create-drop and it stopped giving me errors

      Reply
    4. October 13, 2016 at 10:46 pm

      How can I store encrypted password in properties instead of plain text from security perspective and while connecting to database, can spring boot decrypt automatically and connect to db successfully?

      Reply
    5. January 11, 2017 at 4:09 pm

      You will also need this line…

      “spring.datasource.driver-class-name=org.postgresql.Driver”

      Reply
    6. March 10, 2017 at 8:04 pm

      Hi ,could you help me with an error that comes out at the time of running the program:

      org.postgresql.util.PSQLException: FATAL: no PostgreSQL user name specified in startup packet

      Thanks and I hope you can help me

      Reply
    7. June 9, 2017 at 7:22 am

      What would the spring.datasource.url look like for PostgreSQL?

      Reply

    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.