Spring Boot with PostgreSQL: A Step-by-Step Guide

Here, we will explore how to build a database-driven backend application using PostgreSQL and Spring Boot. The first step is creating a Spring Boot sample app, followed by creating and exposing APIs, using an MVC architecture, then connecting it to PostgreSQL using Flyway, a database migration tool, and testing it. Additionally, I will provide a link to the code’s GitHub repository.

We are going to build an employee directory application that can create, update, fetch, and delete employee records. To develop our application layer, we will use IntelliJ IDEA Community Edition 2023.1. To test it, we will use Postman, an API platform.

Part 1 : Create a Spring Boot project using Spring Initializr

Step 1: Go to: https://start.spring.io/

1. Project — Gradle-Groovy
2. Language — Java
3. Spring Boot — 3.2.1
4. Group — com.example(Depending on your use case, you can modify it)
5. Artifact — springboot (Depending on your use case, you can modify it)
6. Name — springboot (Depending on your use case, you can modify it)
7. Package name — com.example.springboot
8. Packaging — Jar
9. Java — 17

Step 2: The next step is to add dependencies, which are external libraries that our project needs to run.

1. Spring Boot Actuator: Equips your application with production-ready features for monitoring and managing health, status, metrics, and more.
2. Lombok: An annotation processor operating at compile time, Lombok reduces a lot of boilerplate code such as getters, setters, and toString().
3. Spring Data JPA: Simplifies the development of Spring-based applications requiring data source access. Built on the Java Persistence API (JPA), it offers a convenient way to handle data in relational databases.
4. Spring Web: Facilitates the construction of RESTful applications using the Spring MVC framework.
5. Flyway Migration: A database migration tool that simplifies versioning and management of changes to your database schema.
6. PostgreSQL Driver: Empowers Java applications to establish connections and interact seamlessly with PostgreSQL databases.

After configuring, your build.gradle file should look something like this:

plugins {
 id 'java'
 id 'org.springframework.boot' version '3.2.1'
 id 'io.spring.dependency-management' version '1.1.4'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
 sourceCompatibility = '17'
}

configurations {
 compileOnly {
  extendsFrom annotationProcessor
 }
}

repositories {
 mavenCentral()
}

dependencies {
 implementation 'org.springframework.boot:spring-boot-starter-actuator'
 implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
 implementation 'org.springframework.boot:spring-boot-starter-web'
 implementation 'org.flywaydb:flyway-core'
 compileOnly 'org.projectlombok:lombok'
 runtimeOnly 'org.postgresql:postgresql'
 annotationProcessor 'org.projectlombok:lombok'
 testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

tasks.named('test') {
 useJUnitPlatform()
}

Step 3: You can now download this zip file. Once downloaded and extracted, open this in IntelliJ IDEA.

Step 4: In case the build process does not start automatically, you can Build Project manually. Else, IDE might also ask you the following:

Click on Load.

An alternative approach is:

Step 5: Once the build process is over, go to the main class and run.

You should be getting the following logs:

The log indicates that the application failed to start with the given description. This is attributed to the utilization of the Spring JPA dependency, which anticipates a datasource URL containing database connection information. We’ll address this in the upcoming section.

Congratulations on successfully creating a Spring Boot project!

Part 2 : Creating an application layer using Spring Boot to interact with PostgreSQL

MVC, or Model-View-Controller architecture, is a design pattern that comprises three main layers:

1. Model layer: The Model layer consists of entities or POJOs (Plain Old Java Objects) that handle data. It manages the application’s data logic, responsible for storing and retrieving data from backend data stores.
2. View layer: The View layer represents the application’s presentation, displaying information to the user through the user interface (UI).
3. Controller layer: The Controller layer handles user requests, processes UI requests, invokes necessary operations on the model, and updates the view accordingly.

In our development, we’ll focus on building the model, service, repository, and controller layers. The Service layer serves as an intermediary between the model and controller, containing all the business logic. Let’s walk through the example:

Step 1: In this step, we’ll establish the model layer, which is an Employee class in our case. This class can include attributes such as employee ID, first name, last name, phone number, etc.

package com.example.springboot.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDate;
import java.time.LocalDateTime;

/**
 * An entity class represents a table in a relational database
 */
@NoArgsConstructor
@AllArgsConstructor
@Data
@Entity
@Table(name = "employee")
public class Employee {

    @Id
    private Integer id;
    private String firstName;
    private String lastName;
    private Integer age;
    private String designation;
    private String phoneNumber;
    private LocalDate joinedOn;
    private String address;
    private LocalDate dateOfBirth;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;

}

@NoArgsConstructor, @AllArgsConstructor, and @Dataannotations belong to the Lombok library. The first two, as implied by their names, automatically generate a default constructor and another constructor containing all the attributes, respectively. The @Dataannotation, on the other hand, generates getters, setters, toString(), and other essential methods.

When using @Entity, JPA assumes the existence of a corresponding table in the database to store instances of this entity. The @Tableannotation allows customization by specifying the table name, schema, and additional attributes.

The @Idannotation is employed to declare the primary key of an entity class.

Step 2: Repository layer, implemented as an interface, extends Spring Data’s JPA repository. It incorporates methods for common CRUD operations (Create, Read, Update, Delete) and often supports query methods..

Note: Given the focus on basic CRUD operations, specific methods need not be explicitly mentioned in this interface. Built-in methods such as “findById()”, “findAll()”, “save()”, and “deleteById()” already handle these operations seamlessly.

package com.example.springboot.repository;

import com.example.springboot.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * Repository is an interface that provides access to data in a database
 */
public interface EmployeeRepo extends JpaRepository<Employee, Integer> {
}

Note: In the declaration “JpaRepository<Employee, Integer>” the term “Employee” represents the entity, while “Integer” signifies the data type of the identifier for this entity.

Step 3: The service layer is responsible for managing the business logic. As we are developing a straightforward application with minimal complex logic, the service class will just manage the createdAt and updatedAt values for each entity, along with the required methods for CRUD operation.

package com.example.springboot.service;

import com.example.springboot.entity.Employee;
import com.example.springboot.repository.EmployeeRepo;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * Service layer is where all the business logic lies
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class EmployeeService {

    private final EmployeeRepo employeeRepo;

    public List<Employee> getAllEmployees(){
        return employeeRepo.findAll();
    }

    public Employee getEmployeeById(Integer id){
        Optional<Employee> optionalEmployee = employeeRepo.findById(id);
        if(optionalEmployee.isPresent()){
            return optionalEmployee.get();
        }
        log.info("Employee with id: {} doesn't exist", id);
        return null;
    }

    public Employee saveEmployee (Employee employee){
        employee.setCreatedAt(LocalDateTime.now());
        employee.setUpdatedAt(LocalDateTime.now());
        Employee savedEmployee = employeeRepo.save(employee);

        log.info("Employee with id: {} saved successfully", employee.getId());
        return savedEmployee;
    }

    public Employee updateEmployee (Employee employee) {
        Optional<Employee> existingEmployee = employeeRepo.findById(employee.getId());
        employee.setCreatedAt(existingEmployee.get().getCreatedAt());
        employee.setUpdatedAt(LocalDateTime.now());

        Employee updatedEmployee = employeeRepo.save(employee);

        log.info("Employee with id: {} updated successfully", employee.getId());
        return updatedEmployee;
    }

    public void deleteEmployeeById (Integer id) {
        employeeRepo.deleteById(id);
    }

}

@Slf4j is shorthand for “Simple Logging Facade for Java” — it automates the creation of a logger field named logwithin the class, facilitating the logging of messages.

Step 4: We will now create the controller layer.

package com.example.springboot.controller;

import com.example.springboot.entity.Employee;
import com.example.springboot.service.EmployeeService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * Controller class is where all the user requests are handled and required/appropriate
 * responses are sent
 */
@RestController
@RequestMapping("/employee/v1")
@RequiredArgsConstructor
@Validated
public class EmployeeController {

    private final EmployeeService employeeService;

    /**
     * This method is called when a GET request is made
     * URL: localhost:8080/employee/v1/
     * Purpose: Fetches all the employees in the employee table
     * @return List of Employees
     */
    @GetMapping("/")
    public ResponseEntity<List<Employee>> getAllEmployees(){
        return ResponseEntity.ok().body(employeeService.getAllEmployees());
    }

    /**
     * This method is called when a GET request is made
     * URL: localhost:8080/employee/v1/1 (or any other id)
     * Purpose: Fetches employee with the given id
     * @param id - employee id
     * @return Employee with the given id
     */
    @GetMapping("/{id}")
    public ResponseEntity<Employee> getEmployeeById(@PathVariable Integer id)
    {
        return ResponseEntity.ok().body(employeeService.getEmployeeById(id));
    }

    /**
     * This method is called when a POST request is made
     * URL: localhost:8080/employee/v1/
     * Purpose: Save an Employee entity
     * @param employee - Request body is an Employee entity
     * @return Saved Employee entity
     */
    @PostMapping("/")
    public ResponseEntity<Employee> saveEmployee(@RequestBody Employee employee)
    {
        return ResponseEntity.ok().body(employeeService.saveEmployee(employee));
    }

    /**
     * This method is called when a PUT request is made
     * URL: localhost:8080/employee/v1/
     * Purpose: Update an Employee entity
     * @param employee - Employee entity to be updated
     * @return Updated Employee
     */
    @PutMapping("/")
    public ResponseEntity<Employee> updateEmployee(@RequestBody Employee employee)
    {
        return ResponseEntity.ok().body(employeeService.updateEmployee(employee));
    }

    /**
     * This method is called when a PUT request is made
     * URL: localhost:8080/employee/v1/1 (or any other id)
     * Purpose: Delete an Employee entity
     * @param id - employee's id to be deleted
     * @return a String message indicating employee record has been deleted successfully
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<String> deleteEmployeeById(@PathVariable Integer id)
    {
        employeeService.deleteEmployeeById(id);
        return ResponseEntity.ok().body("Deleted employee successfully");
    }


}

Step 5: While we’ve established the various layers for our application, we haven’t yet specified crucial configurations such as database connection details, flyway configuration, server connection, and more. This is where the application.yml or application.properties file within the resources folder becomes essential.

spring:
  application:
    name: springboot
  jackson:
    property-naming-strategy: SNAKE_CASE
  flyway:
    locations: classpath:/db/migration
    schemas: employees
    baselineOnMigrate: true
    enabled: true
  datasource:
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/employees?currentSchema=employees
    username: postgres
    password: *****

Various property naming strategies, such as snake_case and camelCase, exist. The standard convention is to use camelCase when defining attributes in the entity class and snake_case when storing data in the database.

Within flyway, there is a specified location: db/migration (located under resources). This directory serves as the organizational hub for Flyway to execute SQL-based database migration scripts.

In the datasource configuration, we provide crucial database connection details, including the database name, username, password, and schema.

Step 6: The database migration scripts encompass SQL queries like creating tables or altering tables. These scripts adhere to a naming convention, typically commencing with a version number, an underscore, and a description — e.g., V1__Create_Table.sql or V2__Add_Column.sql.

Flyway, upon detection, automatically executes these migration scripts in ascending order based on their version numbers. It maintains a metadata table (flyway_schema_history) in the database, recording applied scripts.

Note: If you’ve created a table using V1__Create_Table.sql and have run the application, subsequent changes to the table's columns should be addressed in a separate script file, such as V2__Alter_Table.sql. Avoid altering the previous script directly. Flyway ensures that migration scripts are uniquely identified by version numbers, preventing reapplication of previously executed scripts.

CREATE TABLE if not exists employee (
id integer,
first_name varchar(250),
last_name varchar(250),
age integer,
designation varchar(250),
phone_number varchar(250),
joined_on date,
address varchar(250),
date_of_birth date,
created_at timestamp,
updated_at timestamp,
PRIMARY KEY (id)
);

Kudos on successfully developing an application layer with Spring Boot for seamless interaction with PostgreSQL!

Part 3: API testing with Postman

Launch the application and open Postman. The Apache Tomcat server typically operates on port 8080 by default. Execute the specified requests in Postman to obtain the desired outcomes.

Save an employee record:

Request method: POST

URL: http://localhost:8080/employee/v1/

Request Body :

{
    "id" : 1,
    "first_name" : "Pabitra",
    "last_name" : "Jena",
    "age": 25,
    "designation": "Engineer - Target Tech",
    "phone_number": "+91 1234567890",
    "joined_on": "2021-10-06",
    "address": "Bangalore",
    "date_of_birth": "1999-05-26"
}

Log output generated: Employee with id: 1 saved successfully

Similarly, you can save few more records.

Get an employee record by Id:

Request method: GET

URL: http://localhost:8080/employee/v1/1

Response Body :

[
    {
        "id": 1,
        "first_name": "Pabitra",
        "last_name": "Jena",
        "age": 23,
        "designation": "Software Engineer",
        "phone_number": "+91 1234567890",
        "joined_on": "2021-10-06",
        "address": "Bangalore",
        "date_of_birth": "1999-05-26",
        "created_at": "2024-01-06T20:03:51.005465",
        "updated_at": "2024-01-06T20:03:51.005465"
    }
]

Get all employee records:

Request method: GET

URL: http://localhost:8080/employee/v1/

Response Body :

[
    {
        "id": 1,
        "first_name": "Pabitra",
        "last_name": "Jena",
        "age": 23,
        "designation": "Software Engineer",
        "phone_number": "+91 1234567890",
        "joined_on": "2021-10-06",
        "address": "Bangalore",
        "date_of_birth": "1999-05-26",
        "created_at": "2024-01-06T20:03:51.005465",
        "updated_at": "2024-01-06T20:03:51.005465"
    },
    {
        "id": 2,
        "first_name": "Arpita",
        "last_name": "Jena",
        "age": 23,
        "designation": "Data Analyst",
        "phone_number": "+91 1234567891",
        "joined_on": "2020-10-06",
        "address": "Bangalore",
        "date_of_birth": "1999-05-12",
        "created_at": "2024-01-06T20:05:19.697736",
        "updated_at": "2024-01-06T20:05:19.697736"
    }
]

Update an employee record:

Request method: PUT

URL: http://localhost:8080/employee/v1/

Request Body :

{
    "id" : 1,
    "first_name" : "Pabitra",
    "last_name" : "Jena",
    "age": 20,
    "designation": "Engineer",
    "phone_number": "+91 9999999999",
    "joined_on": "2020-10-06",
    "address": "Odisha",
    "date_of_birth": "1999-05-26"
}

Log output generated: Employee with id: 1 updated successfully

Delete an employee record with id:

Request method: DELETE

URL: http://localhost:8080/employee/v1/1

Response: Deleted employee successfully

You may also check the PostgreSQL table using pgAdmin to observe the records following each operation.

And there you have it! We have successfully developed a backend application connected to a PostgreSQL database, capable of executing fundamental CRUD operations.

GitHub Repo Link: Spring Boot with Postgres Example

Please feel free to share your comments, feedback, or queries. I’m eager to read, assist, and connect with you!