Skip to content
EDB
Webinar Series: EDB Postgres Distributed • Oct 12 & 26 • Register Now

Blog

Building Reactive PostgreSQL Repositories for Spring Boot Applications – Part 1

EDB Team8/21/2020
Thought leadership

Developing a frontend for a database can be a challenging task, especially when there are multiple runtimes / frameworks available for the language of your choice. We have been dealing with Java and the Spring Boot framework and have showcased how to develop RESTful APIs, how to manage migrations etc. We dealt with the “go-to” Java development practices, with a thread-per-connection model of web applications. Spring Boot Reactive is a new model of development your database-frontends that supports non-blocking operations. 

When you are developing web apps that rely heavily on databases (such as PostgreSQL) or APIs written on top of databases like PostgreSQL or caching systems, you should minimize the latency as much as possible. Thread-per-connection models do not scale as much, since each thread waits until the underlying PostgreSQL engine or caching or API endpoint has responded. During this time, thread is held back from handling other requests in the web server pool. This is a blocking approach, as your threads are “blocked” from handling other requests. The non-blocking approach, or the reactive approach, on the other hand handles events. A single thread starts to handle the events, the events can be:

  1. Incoming request
  2. Incoming data (from PostgreSQL)
  3. Incoming response (from cache / API endpoint)
  4. Outgoing response
  5. Successful file read/write operations.

There are various other operations that each “trigger an event” that is used by non-blocking, reactive code to process requests. The primary benefit of this model is that you end up using a single thread to handle all the requests on your server.

Note: This will be a multiblog series, and we will discuss Reactive repositories for PostgreSQL database and their support for Java Spring Boot applications. In this blog, you will learn how to set up the repository and the Spring Boot application. Going forward, next blogs will discuss other aspects of the R2DBC repositories, such as how to improve performance, or using the fluent APIs and method naming conventions to support the underlying PostgreSQL database queries. 

Spring Boot Reactive for PostgreSQL

Spring Boot Reactive provides built-in support for PostgreSQL databases, and you can bootstrap your existing or fresh PostgreSQL databases with Spring Boot apps written with Reactive practices. If you are new to Project Reactor, you can use only the database-layer as the reactive approach and PostgreSQL databases can be connected / queried in your existing Spring Boot apps. 

Spring Boot Reactive provides support for R2DBC (Reactive Relational Database Connectivity) compared to RDBC (non-reactive one). PostgreSQL queries are mapped either via a @Query annotation, or they are “automagically” generated by R2DBC engine for us. In this article, we will neither create new @Query methods nor try the method naming conventions for SQL query generation, rather stick with the built-in CRUD methods.

R2DBC vs JDBC

Before we showcase code, it is important to understand how R2DBC and JDBC differ, in terms of API, performance and support.

Note that JPA is based on top of JDBC, so comparison is included. One more thing to understand is that you cannot mix JPA and R2DBC, I will show one of such examples moving forward.

Primary differences are:

  1. You utilize non-blocking methods.
  2. You create reactive CRUD repositories (see the next section).
  3. You utilize Flux or Mono types to work with the data; direct records are not provided, which leads to blocking code.
  4. You will need to create the database schema / tables manually. R2DBC does not provide built-in support for table creation like Hibernate does.

R2DBC is rather a new framework, so the support and features are less as compared to Hibernate (which is over a decade old). You can review how R2DBC methods are translated to PostgreSQL queries here.

Building Reactive CRUD Repository

Let’s start developing a CRUD repository for our PostgreSQL database. You will require to use the Reactive plugins in your Spring Boot:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Pull the dependencies using Maven, and then start the development. First, create the Model class that we will use in this sample. Create a new file called Book.java, and add the following code to it:

package com.demos.reactivepostgresql.models;

import org.springframework.data.annotation.Id;

public class Book {

    @Id
    private Long id;

    private String title;

    private String author;

    public Book() {}

    public Book(String title, String author) {

        this.title = title;
        this.author = author;
    }

    public Book(Long id, String title, String author) {
        
        this.id = id;
        this.title = title;
        this.author = author;
    }

    public Long getId() {

        return this.id;
    }

    public void setId(Long id) {

        this.id = id;
    }

    public String getTitle() {

        return this.title;
    }

    public String getAuthor() {

        return this.author;
    }
}

This model contains a basic Book sample, that we will use to perform reactive-CRUD on our PostgreSQL database. 

Column Information and Primary Keys

One of the ways in which R2DBC differs from JDBC-based JPA is that it does not provide support for schema-management, which is a huge difference, especially if you come from an ORM-based background. 

In Hibernate you can specify the @GeneratedValue field, in R2DBC you cannot do that. Instead, you must rely heavily on the database to manage this for you. This becomes a problem when you must create a primary key value for each record. Here is a sample screenshot when you use “integer” in your table and try to add a record:

In PostgreSQL, this is not a problem at all. You can create PostgreSQL tables with a SERIAL (or BIGSERIAL) field and database will automatically provide the values for your records’ primary keys. If you create your tables as:

CREATE TABLE book (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL
)

You are okay! This would result in the following table structure for you:

table example

Regardless of an INSERT or UPDATE query, your Java application will function perfectly, and PostgreSQL will manage the records / primary keys. 

Next step is to create a repository that will be used by rest of our Java application to communicate with the PostgreSQL database. This repository (like our previous ones) is a built-in provided repository that is managed by R2DBC for us.

package com.demos.reactivepostgresql.data;

import com.demos.reactivepostgresql.models.Book;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;

public interface BookRepository extends ReactiveCrudRepository<Book, Long> {
}

The only thing different in this repository and the previously created ones, is the type being used. In future blogs, we will explore how to control the SQL queries that are generated by Reactive repository for PostgreSQL; using PostgreSQL specific SQL queries for CRUDs. 

Performing CRUD Operations

Now, we look at how we can extend the repository to perform the actions on our PostgreSQL database. I will reuse the RESTful components that we wrote in our RESTful sample, but this time we inject our reactive database repository and focus more on PostgreSQL side, rather than RESTful aspects of Java apps. 

package com.demos.reactivepostgresql.controllers;

import com.demos.reactivepostgresql.data.BookRepository;
import com.demos.reactivepostgresql.models.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@RestController
@RequestMapping(value = "/api/books")
public class HomeController {

    @Autowired BookRepository bookRepository;

    @GetMapping("")
    public Flux<Book> getHome() {

        return bookRepository.findAll();

    }

    @PostMapping("")
    public Mono<Book> postBook(@RequestBody Book book) {

        return bookRepository.save(book);
    }

    @PutMapping("")
    public Mono<Book> updateBook(@RequestBody Book book) {

        return bookRepository.save(book);

    }

    @DeleteMapping("")
    public boolean deleteBook(@RequestBody Book book) {

        try {
            bookRepository.deleteById(book.getId()).block(); // Note this!
            return true;

        } catch (Exception e) {

            return false;
        }
    }
}

A few differences:

In JDBC you will return a List<T>, while in R2DBC you return a Flux<T>; they are mapped to a list of records from PostgreSQL. In JDBC you directly return the type of record from database, however in R2DBC you must return a Mono<T> so that your code remains non-blocking; Mono<T> type returns either no record or at max a single record. One example of changing your R2DBC to a blocking-call is shown in the delete route, where we call “block” method to wait until the record is deleted. 

The reason is that delete methods in R2DBC return a Mono<Void>, which if returned from method will not yield any useful information in Postman / interface, thus we block the call, and return a true / false based on situation.

You can get the source code for blocking RESTful controllers from our blog. 

Note: Before running the sample, make sure you have configured PostgreSQL details for R2DBC (and not JDBC). You can use the following sample properties:

## PostgreSQL

spring.r2dbc.url=r2dbc:postgresql://172.17.0.2:5432/postgres?schema=techwriting
spring.r2dbc.username=<username>
spring.r2dbc.password=<password>

Now, just run the application and it should host your API on http://localhost:8080/. If you follow the blog and want to use blocking JDBC-based controllers, you should have your API endpoint hosted as http://localhost:8080/api/books (make sure to change people to books in the endpoint).

Conclusion

In this post, we discussed the “modern” web development stacks like Spring Boot Reactive and integrated it in an existing PostgreSQL database. From performance standpoint, you will see that your PostgreSQL front-facing web apps written in reactive-pattern consume (or require) less compute power and perform like their more-compute-expensive thread-per-connection models. Reactive or non-blocking apps deliver a better performance and support a procedural development as compared to non-blocking development with callbacks.

We also saw how instead of relying on an ORM, you need to define the schema in your database to support your Java web applications. This means that with R2DBC (at least for the moment) you need to know PostgreSQL more than your Java! You can check out our PostgreSQL learning portal to explore hands-on and on-demand learning material as well as join us in our webinars.

You should have the Spring Boot application up and running, and it should listen on the endpoint and provide the empty list of books. Now in the next part, we will perform the CRUD operations on the website. 

If you are interested in exploring the differences in JDBC and R2DBC, please consult these documentations and blogs:

Also visit the official R2DBC documentation to see the project plans.