Add a PostgreSQL database to your Spring Boot project

An in-memory database may be sufficient during the early phase of project development. However, sooner or later you will need to persist some data. Read this post to learn how to configure a Spring Boot app to use a PostgreSQL database.

What we are going to build

For this article I’m going to use a Spring Boot project with one example endpoint. I will configure it to connect to a PostgreSQL database run from a docker container.

You can find the example project used in this post in the spring-boot-postgres-flyway repository.

Architecture overview

Our example REST API offers one endpoint that enables persisting data about bakery goods. The application relays on a PostgreSQL database that can be instantiated using the docker container that is included in the project. We use profile specific properties to separate configuration applied on the development environment. To examine the SQL commands run automatically against the database I generated the schema.sql file with the DDL script.

You can see the project directory tree on the image below:

Requirements

• We are working on a Spring Boot 2.2.5 project with the Web and JPA dependencies. You  can read about setting up a similar project with Spring Initializr in How to create a new Spring Boot Project post.
• OpenJDK 11

$ java --version
openjdk 11.0.6 2020-01-14
OpenJDK Runtime Environment (build 11.0.6+10-post-Ubuntu-1ubuntu118.04.1)
OpenJDK 64-Bit Server VM (build 11.0.6+10-post-Ubuntu-1ubuntu118.04.1, mixed mode, sharing)

• A PostgreSQL database. You can use the container that is included in the repository and presented in the Setup a PostgreSQL database with Docker article. You will need Docker Engine – Community with Docker CLI for that.

Configure your application to connect to the database

We are going to instruct our app to work with a running PostgreSQL database. If you are using the docker-compose.yml file included in the project, don’t forget to start the container with the database instance.

Add the PostgreSQL dependency

Add the dependency for the driver for PostgreSQL database to your pom.xml file:

<!--pom.xml-->
…
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>
…

How to set up a database on the development environment

Connecting your application to a database requires providing credentials and a database url.

Default properties

The values from application.properties will be used unless they are overridden. In the default properties I specified the database dialect (optional):

# src/main/resources/application.properties
…
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

If you place here values used for connecting to the local database, the app will work on your machine. In case you forget to supersede them on the production, the app won’t be able to connect to the “real” database.

Profile-specific properties

In my app I’m setting the profile-specific properties to make sure that none of the development properties will be applied to the production. I separated the attributes by creating two files: application.properties and application-dev.properties.

The properties for the PostgreSQL database run on my development environment looks like on the snippet below:

# src/main/resources/application-dev.properties
spring.jpa.properties.javax.persistence.schema-generation.database.action=create
…
spring.datasource.url=jdbc:postgresql://localhost:5432/springbootpostgresflyway
spring.datasource.username=db
spring.datasource.password=admin

Set development as the active profile

Now I have to set the active profile. I can do this in two ways. One relays on defining it in the default properties file:

# src/main/resources/application.properties
spring.profiles.active=dev

If several profiles are specified, a last-wins strategy applies. For example, profiles specified by the spring.profiles.active property are added after those configured through the SpringApplication API and therefore take precedence.

https://docs.spring.io/spring-boot/docs/current/reference/html/spring-boot-features.html#boot-features-external-config-profile-specific-properties

I have to remember to override the active profile when running the app on production.

Instead of defining the active profile in the application.properties file, you can edit the application configuration using your IDE. In IntelliJ, select Run → Edit Configuration from the menu and set the profile like on the image below:

Thanks to this, the development properties will be applied only on your machine.

Properties explained

Below you will find description of the options used in my example and also some alternatives that you can apply in your projects.

spring.jpa.database-platform

The target database is auto-detected by default. You can specify the proper value on your own, as we can read in the docs:

The dialect to use is detected by the JPA provider. If you prefer to set the dialect yourself, set the spring.jpa.database-platform property.

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-configure-jpa-properties

spring.datasource.url, spring.datasource.username and spring.datasource.password

url is the JDBC URL of the database. Remember to keep the port, database name and credentials consistent with the database configuration. Make sure that the credentials from the dev environment are not accidentally used for the production.

javax.persistence.schema-generation.database.action

We use this property to define which action should be performed automatically on application startup. The default value is none, available options are listed in the documentation.

spring.jpa.hibernate.ddl-auto – the alternative property for creating a schema

You should follow a single schema generation approach. You can learn more in the Schema generation properties not well documented issue.

Instead of the javax.persistence.schema-generation.database.action property you could use the spring.jpa.hibernate.ddl-auto property to apply the JPA features for DDL generation on the application startup. The standard values are none, validate, update, create, and create-drop.

Be aware of differences in the default value for this property. As we can read in the Spring Boot documentation:

The spring.jpa.hibernate.ddl-auto is a special case, because, depending on runtime conditions, it has different defaults. If an embedded database is used and no schema manager (such as Liquibase or Flyway) is handling the DataSource, it defaults to create-drop. In all other cases, it defaults to none.

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-configure-jpa-properties

Run the application

You can now start the application and verify that it is connected to the database. In my example, Hibernate automatically creates tables based on my entities.

Verify the SQL commands

I store the DDL script in a file so I can easily examine the commands that were run against the database on application startup. Check out the Save Hibernate DDL schema to a file post to learn how you can do the same. The schema.sql file generated in my project looks like this:

-- schema.sql
create table cookie (
    id  bigserial not null,
    flavour varchar(255),
    primary key (id)
)

The file was not added to my repository. It will be generated in the project directory when you start your application.

Verify the database schema

You can see the database schema on the screenshot taken from my IntelliJ on the image below:

The work done in this post in contained in the commit 84a88ff03f8b547d0a0fc608fa99586cc3f62cdc.

Disable automatic schema creation once the tables are initialized

You can keep recreating container and volume with the database content until you are satisfied with the outcome and all entities are polished. Once the schema is ready you can remove the property responsible for creating the tables – the javax.persistence.schema-generation.database.action property.

However, the schema will evolve during the project lifetime, so it is a good idea to implement a better solution for maintaining it. You can learn how to add migrations to this project in the Add the Flyway migrations to a Spring Boot project post.

Troubleshooting

Adjust the application model to the database requirements

If you are transferring the app from the in-memory database (e.g. h2) remember to adjust all entities to the target database requirements – they will be more strict. For example, you can encounter problems with reserved keywords used in the entities. Be sure to check out the database documentation to verify that no conflicts will arise during the database transition.

Make sure that you use the valid names for properties

Spring Boot allows us to set some vendor-independent configuration options provided by Spring Data JPA as external configuration properties for Hibernate. You can find the JPA properties in the documentation. Furthermore, you can check out this appendix with a list of common Spring Boot properties. When a property name is long it is easy to misspell it.

Use the right image version if you run the database with Docker

Make sure that the docker image version provides you with the database version that you actually want for the application.

Photo by  Lucas Allmann on StockSnap