Using Postgres Effectively in Spring Boot Applications

If you are creating a new Spring Boot project, one question you will inevitably need to answer is: What database should I use? Luckily, Spring Boot supports many databases such as H2, MySQL, Postgres, MongoDB, and more.

As of May 2023, Postgres is ranked fourth in the DB-Engines rankings for relational databases, making it a popular choice for Spring Boot projects.

To use Postgres, you need to:

1. Add dependencies to your build tool.
2. Create a new Postgres database with psql.
3. Connect Spring Boot to your Postgres database.
4. Create a Java entity with Hibernate.
5. Create a repository with Spring Data JPA.
6. Create a controller that uses your repository.

In the following sections, we will explore each step in detail. Note that this post assumes you already have a Spring Boot project set up with the Spring Web starter (a collection of dependencies commonly used together) and Postgres downloaded on your local machine.

If you need help bootstrapping your Spring Boot project, consider reading my previous post on Spring Initializr. If you have not downloaded Postgres before, go to the official Postgres download page.

Add Dependencies To Your Build Tool

You will need to add two dependencies to your Spring Boot project:

1. Spring Boot Starter Data JPA – Spring Boot Starter Data JPA is a starter for using Spring Data JPA with Hibernate as the JPA provider.
2. PostgreSQL JDBC Driver – The Postgres JDBC driver is a software component that enables Java applications to connect to and interact with Postgres databases.

Since I am using Gradle, I would add these two lines to the dependencies block in the build.gradle file:

dependencies {
  // ...
  
  implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
  implementation 'org.postgresql:postgresql'
}

If you generated your project with Spring Initializr, Spring Boot’s Gradle dependency management plugin automatically manages the versions of your dependencies using the versions listed in this table, so you do not need to include the versions.

If you are using Maven, you should add the following lines to the <dependencies> element in your pom.xml file:

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

Once again, the code snippet above assumes you are using Spring Boot’s Maven dependency management plugin, so no version numbers are needed.

To actually download these new dependencies, you will need to rebuild in your IDE (IntelliJ IDEA usually has a pop-up when you add a new dependency) or in a terminal (gradle build for Gradle and mvn install for Maven).

Create A New Postgres Database With psql

One way to create a new Postgres database is with psql, a command-line interface for managing Postgres databases. Start by opening a terminal and connecting as the postgres superuser:

$ psql -U postgres

Next, I recommend creating a new user to own the new database to promote better security and access privilege management instead of making the postgres superuser the database owner. To create a new user, run the following SQL statement:

postgres=# CREATE USER <YOUR_USERNAME> WITH PASSWORD '<YOUR_PASSWORD>';

Next, create a new database and specify the owner:

postgres=# CREATE DATABASE <YOUR_DATABASE_NAME> OWNER <YOUR_USERNAME>;

Connect Spring Boot To Your Postgres Database

Now that you have a Postgres database, it is time to configure the Spring Boot project to connect to this database. In the src/main/resources folder, there should be an application.properties or application.yml file. The purpose of this file is to configure various properties, such as server port, database connection details, logging configuration, and more.

If you have an application.properties file, add the following content to your file:

spring.datasource.url=jdbc:postgresql://localhost:5432/<YOUR_DATABASE_NAME>
spring.datasource.username=<YOUR_USERNAME>
spring.datasource.password=<YOUR_PASSWORD>
spring.jpa.hibernate.ddl-auto=<create | create-drop | update | validate | none>
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

If you have an application.yml file, add the following instead:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/<YOUR_DATABASE_NAME>
    username: <YOUR_USERNAME>
    password: <YOUR_PASSWORD>
  jpa:
    hibernate:
      ddl-auto: <create | create-drop | update | validate | none>
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

The Postgres JDBC driver uses the URL, username, and password for connecting to your new Postgres database. The dialect is PostgreSQLDialect so Hibernate knows which SQL dialect to use when generating and executing SQL queries. The ddl-auto property sets the behavior of Hibernate’s schema generation tool and has five possible values:

1. create – On application start-up, drop all tables managed by Hibernate, then create them from scratch.
2. create-drop – On application start-up, create all tables managed by Hibernate. On shutdown, drop all of them.
3. update – On application start-up, update the existing tables to match the schema Hibernate expects if necessary.
4. validate – On application start-up, check that the existing tables match the schema Hibernate expects, and throw an exception if they do not match.
5. none – Do not perform any automatic schema management.

The correct value for spring.jpa.hibernate.ddl-auto depends on your specific use case. For this demo, you can use create-drop, which cleans up after itself when the Spring Boot application shuts down.

Now that you have the necessary configuration, when you start your Spring Boot application, you should see the following logs indicating that Spring Boot can successfully connect to your Postgres database:

HHH000204: Processing PersistenceUnitInfo [name: default]
HHH000412: Hibernate ORM core version 6.1.7.Final
HikariPool-1 - Starting...
HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@6d294ddc
HikariPool-1 - Start completed.
HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect

Create A Java Entity With Hibernate

To create the tables that will populate your Postgres database, you need to create entities, which are POJOs (plain old Java objects) that represent a table. An instance of an entity corresponds to a row in the underlying table.

For example, if you were building an employee management application, you would need to create an employees table. To do that, define a new Employee class annotated with @Entity. Optionally, if you want your tables to have plural names, you can use the @Table annotation to override the default table name. You will also need to designate at least one field as the primary key with the @Id annotation:

@Entity
@Table(name = "employees")
public class Employee {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer id;
  
  private String firstName;
  
  private String lastName;
  
  private LocalDate dateOfBirth;
  
  // Hibernate expects entities to have a no-arg constructor,
  // though it does not necessarily have to be public.
  private Employee() {}
  
  public Employee(String firstName, String lastName, LocalDate dateOfBirth) {
    this.firstName = firstName;
    this.lastName = lastName;
    this.dateOfBirth = dateOfBirth;
  }
  
  public Integer getId() {
    return this.id;
  }
  
  public String getFirstName() {
    return this.firstName;
  }

  public String getLastName() {
    return this.lastName;
  }

  public LocalDate getDateOfBirth() {
    return this.dateOfBirth;
  }
}

Note that by annotating the id field with @GeneratedValue, Postgres is responsible for creating an id for new Employee objects, so an id parameter in the constructor is unnecessary.

Create A Repository With Spring Data JPA

Now that you have an employees table in your Postgres database, you need a way to interact with it in your Spring Boot project. Spring Data JPA introduces a useful abstraction called a repository that fulfills this purpose. A repository is an interface that extends one of the repository interfaces provided by Spring Data JPA. For example, you can extend the CrudRepository interface, which provides generic CRUD operations and has the following definition:

@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {

  <S extends T> S save(S entity);
  
  <S extends T> Iterable<S> saveAll(Iterable<S> entities);
  
  Optional<T> findById(ID id);
  
  boolean existsById(ID id);
  
  Iterable<T> findAll();
  
  Iterable<T> findAllById(Iterable<ID> ids);
  
  long count();
  
  void deleteById(ID id);
  
  void delete(T entity);
  
  void deleteAllById(Iterable<? extends ID> ids);
  
  void deleteAll(Iterable<? extends T> entities);
  
  void deleteAll();
}

Miraculously, all you need to do to define a repository for the Employee entity is to extend the CrudRepository interface, indicating the type of the entity and id:

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {}

What happens internally is Spring Data JPA uses reflection and code generation to create a concrete implementation of these interfaces at runtime so that you do not have to write this boilerplate code yourself.

Create A Controller That Uses Your Repository

To use your employee repository, create a controller for your employee resources. In Spring Boot, a controller is a class annotated with @RestController and contains handler methods for REST endpoints. By creating a constructor that has an employeeRepository parameter, Spring Boot knows to inject an instance of EmployeeRepository when your controller is created:

@RestController
public class EmployeeController {

  private final EmployeeRepository employeeRepository;

  public EmployeeController(EmployeeRepository employeeRepository) {
    this.employeeRepository = employeeRepository;
  }
}

The only thing left to do is define simple handlers for GET and POST requests to /employees:

@RestController
public class EmployeeController {
  // ...
  
  @GetMapping("/employees")
  public Iterable<Employee> findAllEmployees() {
    return this.employeeRepository.findAll();
  }

  @PostMapping("/employees")
  public Employee addOneEmployee(@RequestBody Employee employee) {
    return this.employeeRepository.save(employee);
  }
}

To test these REST endpoints, start your Spring Boot application. Then, you can send a GET request to /employees using cURL:

$ curl -s localhost:8080/employees
[]

As expected, there are no employees, so the response body is simply an empty array. Next, send a POST request to /employees to add one employee:

$ curl -s -X POST localhost:8080/employees \
  -H "Content-Type: application/json" \
  -d '{"firstName": "foo", "lastName": "bar", "dateOfBirth": "2023-05-04"}'
{"id":1,"firstName":"foo","lastName":"bar","dateOfBirth":"2023-05-04"}

To check that the new employee is in the database, send another GET request to /employees:

$ curl -s localhost:8080/employees
[{"id":1,"firstName":"foo","lastName":"bar","dateOfBirth":"2023-05-04"}]

As you can see, the response body now has an employee. You can also check that the new employee is in your Postgres database by querying the employees table with psql. To do that, first connect to your Postgres database. Assuming your username is testuser, and your database is testdb, the psql command would look like:

$ psql -U testuser -d testdb

Next, run the following SQL query on the employees table:

testdb=> SELECT * FROM employees;
 id | date_of_birth | first_name | last_name
----+---------------+------------+-----------
  1 | 2023-05-04    | foo        | bar
(1 row)

As you can see, the new employee is indeed in our Postgres database.

Conclusion

Congratulations! You now know how to use Postgres in Spring Boot projects. Along the way, you also learned how to create entities with Hibernate and how to create and use repositories with Spring Data JPA.

Even if you decide to use other relational databases in your future Spring Boot projects, you will likely need to go through similar steps.