The challenge of building a truly reactive microservice with a relational database

In 2011, when reactive applications started to become more common in Java, I often joked about them as accessing SQL databases with JDBC was a fully blocking mechanism. Of course, at that time it was thought that SQL databases were old stuff, and that schema-less NoSQL solutions would be the norm in the next 5 years. Spring was dead! SQL databases would die next!

In a weird twist of history, years later we still have Spring and SQL databases ruling the world... But now they both work beautifully in a reactive, cloud-native and serverless architecture!

In this blog post we're going to see how to build such an application, with the following technologies:

• Spring Webflux, so we have reactive REST endpoints
• R2DBC, so we use new, reactive SQL drivers that allow our application to be fully reactive from the database to the REST endpoints
• The serverless version of SQL Server, so we scale our database depending on our needs

WARNING many technologies that we use here, including R2DBC, are very new and are not production-ready yet. We're building a working application, and we hope that this post will help spread and stabilize those technologies, but use them at your own risk!

The sample application

We are developing here a simple Spring Webflux microservice, that talks to an SQL database. The final code is available on GitHub at https://github.com/jdubois/spring-reactive-sql-server.

Serverless SQL Server

Not all databases have reactive drivers with R2DBC, and that's why we selected SQL Server here. The other reason is that is has a "serverless" option, which costs very little money. In fact, scalability options of SQL Server are impressive, as that's the only cloud database with the three following options:

• Very high performance, availability and storage (up to 100 To!), with the hyperscale option
• Optimized price/performance and scalability using elastic pools of databases
• Very low cost, and on-demand pricing, using (my favorite!) serverless database instances

We're going to use here the "serverless" option, so we can build everything for an extremely low price: go to the Azure portal and select "SQL Database":

We selected the cheapest option: our database will cost us 0.15 Euros per month, plus 0.000067 Euros per second used (so for one hour, it will be about 0,24 Euros). That's really good for a dev or test environment, as it's only billed when it's used.

To create the database schema, we have a simple script available here:

CREATE TABLE person (
    id BIGINT NOT NULL IDENTITY PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    company VARCHAR(100)
)

There are many options to run this script on the database, but the easiest one is to use the online editor that is available through the Azure portal:

R2DBC usage and configuration

R2DBC is a specification for reactive drivers for Java. More specifically, we will use RD2BC SQL Server, which is its implementation for Microsoft SQL Server.

We had quite a number of issues using it, for example this annoying SSL bug, and that's why we are using snapshot releases here:

<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-mssql</artifactId>
    <version>0.8.0.BUILD-20190819.142517-35</version>
</dependency>
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-r2dbc</artifactId>
    <version>1.0.0.gh-151-SNAPSHOT</version>
</dependency>

Those issues are currently being addressed by the Spring team, and we will update the sample project as soon as stable releases are available.

Spring configuration for R2DBC

There's a Spring Boot starter for automatically configuring R2DBC with Spring Boot, or you can of course configure R2DBC manually, as we did here:

    @Bean
    public MssqlConnectionFactory connectionFactory() {
        log.info("Connecting to database '{}'...", host);
        return new MssqlConnectionFactory(MssqlConnectionConfiguration.builder()
                .host(host)
                .port(1433)
                .database(database)
                .username(username)
                .password(password)
                .build());
    }

Data access code with Spring Data and R2DBC

The good news is that Spring Data works partially with R2DBC: you won't have all the features like on-the-fly SQL queries generation, but most of the framework works, which means we can have very simple repositories, as shown here:

@Repository
public interface PersonRepository extends ReactiveCrudRepository<Person, Long> {
}

So this repository can then be used in our REST endpoints, as a usual Spring JDBC or Spring JPA repository:

@RestController
public class PersonController {

    private final PersonRepository personRepository;

    public PersonController(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }

    @GetMapping("/persons")
    public Flux<Person> list() {
        return personRepository.findAll();
    }
}

Conclusion and final thoughts

The sample project that we developed here is available on https://github.com/jdubois/spring-reactive-sql-server. In the future, that project should use stable versions of R2DBC, as well as its starter and connection pool projects, but it's already fully usable.

The good news is that, despite using some very cutting-edge features, our code still looks like a usual Spring Boot application: most of the configuration and API used should be familiar to Spring users, and this runs like any usual Spring Boot application.

The killer feature of this setup, is that thanks to the "serverless" version of SQL Server, and thanks to R2DBC and Spring Webflux, we are able to have a very scalable application, which cost will vary depending on usage. On the application side, using Spring Webflux means that our application starts very quickly (a bit more than 1 second on a MacBook Pro), and that memory usage is limited. Our end result is thus highly scalable and efficient!