Use Spring Data R2DBC with Azure SQL Database

Article
05/26/2023

This article demonstrates creating a sample application that uses Spring Data R2DBC to store and retrieve information in Azure SQL Database by using the R2DBC implementation for Microsoft SQL Server from the r2dbc-mssql GitHub repository.

R2DBC brings reactive APIs to traditional relational databases. You can use it with Spring WebFlux to create fully reactive Spring Boot applications that use non-blocking APIs. It provides better scalability than the classic "one thread per connection" approach.

Prerequisites

An Azure subscription - create one for free.
Java Development Kit (JDK), version 8 or higher.
Apache Maven.
Azure CLI.

sqlcmd Utility.
cURL or a similar HTTP utility to test functionality.

See the sample application

In this article, you'll code a sample application. If you want to go faster, this application is already coded and available at https://github.com/Azure-Samples/quickstart-spring-data-r2dbc-sql-server.

Prepare the working environment

First, set up some environment variables by using the following commands:

export AZ_RESOURCE_GROUP=database-workshop
export AZ_DATABASE_NAME=<YOUR_DATABASE_NAME>
export AZ_LOCATION=<YOUR_AZURE_REGION>
export AZ_SQL_SERVER_ADMIN_USERNAME=spring
export AZ_SQL_SERVER_ADMIN_PASSWORD=<YOUR_AZURE_SQL_ADMIN_PASSWORD>
export AZ_SQL_SERVER_NON_ADMIN_USERNAME=nonspring
export AZ_SQL_SERVER_NON_ADMIN_PASSWORD=<YOUR_AZURE_SQL_NON_ADMIN_PASSWORD>
export AZ_LOCAL_IP_ADDRESS=<YOUR_LOCAL_IP_ADDRESS>

Replace the placeholders with the following values, which are used throughout this article:

<YOUR_DATABASE_NAME>: The name of your Azure SQL Database server, which should be unique across Azure.
<YOUR_AZURE_REGION>: The Azure region you'll use. You can use eastus by default, but we recommend that you configure a region closer to where you live. You can see the full list of available regions by using az account list-locations.
<AZ_SQL_SERVER_ADMIN_PASSWORD> and <AZ_SQL_SERVER_NON_ADMIN_PASSWORD>: The password of your Azure SQL Database server, which should have a minimum of eight characters. The characters should be from three of the following categories: English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on).
<YOUR_LOCAL_IP_ADDRESS>: The IP address of your local computer, from which you'll run your Spring Boot application. One convenient way to find it is to open whatismyip.akamai.com.

Next, create a resource group by using the following command:

az group create \
    --name $AZ_RESOURCE_GROUP \
    --location $AZ_LOCATION \
    --output tsv

Create an Azure SQL Database instance

Next, create a managed Azure SQL Database server instance by running the following command.

Note

The MS SQL password has to meet specific criteria, and setup will fail with a non-compliant password. For more information, see Password Policy.

az sql server create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME \
    --location $AZ_LOCATION \
    --admin-user $AZ_SQL_SERVER_ADMIN_USERNAME \
    --admin-password $AZ_SQL_SERVER_ADMIN_PASSWORD \
    --output tsv

Configure a firewall rule for your Azure SQL Database server

Azure SQL Database instances are secured by default. They have a firewall that doesn't allow any incoming connection. To be able to use your database, you need to add a firewall rule that will allow the local IP address to access the database server.

Because you configured your local IP address at the beginning of this article, you can open the server's firewall by running the following command:

az sql server firewall-rule create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME-database-allow-local-ip \
    --server $AZ_DATABASE_NAME \
    --start-ip-address $AZ_LOCAL_IP_ADDRESS \
    --end-ip-address $AZ_LOCAL_IP_ADDRESS \
    --output tsv

If you're connecting to your Azure SQL Database server from Windows Subsystem for Linux (WSL) on a Windows computer, you need to add the WSL host ID to your firewall.

Obtain the IP address of your host machine by running the following command in WSL:

cat /etc/resolv.conf

Copy the IP address following the term nameserver, then use the following command to set an environment variable for the WSL IP Address:

export AZ_WSL_IP_ADDRESS=<the-copied-IP-address>

Then, use the following command to open the server's firewall to your WSL-based app:


az sql server firewall-rule create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME-database-allow-local-ip-wsl \
    --server $AZ_DATABASE_NAME \
    --start-ip-address $AZ_WSL_IP_ADDRESS \
    --end-ip-address $AZ_WSL_IP_ADDRESS \
    --output tsv

Configure an Azure SQL database

The Azure SQL Database server that you created earlier is empty. It doesn't have any database that you can use with the Spring Boot application. Create a new database called demo by running the following command:

az sql db create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name demo \
    --server $AZ_DATABASE_NAME \
    --output tsv

Create an SQL database non-admin user and grant permission

This step will create a non-admin user and grant all permissions on the demo database to it.

Create a SQL script called create_user.sql for creating a non-admin user. Add the following contents and save it locally:

cat << EOF > create_user.sql
USE demo;
GO
CREATE USER $AZ_SQL_SERVER_NON_ADMIN_USERNAME WITH PASSWORD='$AZ_SQL_SERVER_NON_ADMIN_PASSWORD'
GO
GRANT CONTROL ON DATABASE::demo TO $AZ_SQL_SERVER_NON_ADMIN_USERNAME;
GO
EOF

Then, use the following command to run the SQL script to create the non-admin user:

sqlcmd -S $AZ_DATABASE_NAME.database.windows.net,1433  -d demo -U $AZ_SQL_SERVER_ADMIN_USERNAME -P $AZ_SQL_SERVER_ADMIN_PASSWORD  -i create_user.sql

Note

For more information about creating SQL database users, see CREATE USER (Transact-SQL).

Create a reactive Spring Boot application

To create a reactive Spring Boot application, we'll use Spring Initializr. The application that we'll create uses:

Spring Boot 2.7.11.
The following dependencies: Spring Reactive Web (also known as Spring WebFlux) and Spring Data R2DBC.

Generate the application by using Spring Initializr

Generate the application on the command line by running the following command:

curl https://start.spring.io/starter.tgz -d dependencies=webflux,data-r2dbc -d baseDir=azure-database-workshop -d bootVersion=2.7.11 -d javaVersion=17 | tar -xzvf -

Add the reactive Azure SQL Database driver implementation

Open the generated project's pom.xml file to add the reactive Azure SQL Database driver from the r2dbc-mssql GitHub repository.

After the spring-boot-starter-webflux dependency, add the following text:

<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-mssql</artifactId>
    <scope>runtime</scope>
</dependency>

Configure Spring Boot to use Azure SQL Database

Open the src/main/resources/application.properties file, and add the following text:

logging.level.org.springframework.data.r2dbc=DEBUG

spring.r2dbc.url=r2dbc:pool:mssql://$AZ_DATABASE_NAME.database.windows.net:1433/demo
spring.r2dbc.username=nonspring@$AZ_DATABASE_NAME
spring.r2dbc.password=$AZ_SQL_SERVER_NON_ADMIN_PASSWORD

Replace the two $AZ_DATABASE_NAME variables and the $AZ_SQL_SERVER_NON_ADMIN_PASSWORD variable with the values that you configured at the beginning of this article.

Note

For better performance, the spring.r2dbc.url property is configured to use a connection pool using r2dbc-pool.

You should now be able to start your application by using the provided Maven wrapper as follows:

./mvnw spring-boot:run

Here's a screenshot of the application running for the first time:

Create the database schema

Inside the main DemoApplication class, configure a new Spring bean that will create a database schema, using the following code:

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.ClassPathResource;
import org.springframework.data.r2dbc.connectionfactory.init.ConnectionFactoryInitializer;
import org.springframework.data.r2dbc.connectionfactory.init.ResourceDatabasePopulator;

import io.r2dbc.spi.ConnectionFactory;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Bean
    public ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {
        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator(new ClassPathResource("schema.sql"));
        initializer.setDatabasePopulator(populator);
        return initializer;
    }
}

This Spring bean uses a file called schema.sql, so create that file in the src/main/resources folder, and add the following text:

DROP TABLE IF EXISTS todo;
CREATE TABLE todo (id INT IDENTITY PRIMARY KEY, description VARCHAR(255), details VARCHAR(4096), done BIT);

Stop the running application, and start it again using the following command. The application will now use the demo database that you created earlier, and create a todo table inside it.

./mvnw spring-boot:run

Here's a screenshot of the database table as it's being created:

Code the application

Next, add the Java code that will use R2DBC to store and retrieve data from your Azure SQL Database server.

Create a new Todo Java class, next to the DemoApplication class, using the following code:

package com.example.demo;

import org.springframework.data.annotation.Id;

public class Todo {

    public Todo() {
    }

    public Todo(String description, String details, boolean done) {
        this.description = description;
        this.details = details;
        this.done = done;
    }

    @Id
    private Long id;

    private String description;

    private String details;

    private boolean done;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getDetails() {
        return details;
    }

    public void setDetails(String details) {
        this.details = details;
    }

    public boolean isDone() {
        return done;
    }

    public void setDone(boolean done) {
        this.done = done;
    }
}

This class is a domain model mapped on the todo table that you created before.

To manage that class, you need a repository. Define a new TodoRepository interface in the same package, using the following code:

package com.example.demo;

import org.springframework.data.repository.reactive.ReactiveCrudRepository;

public interface TodoRepository extends ReactiveCrudRepository<Todo, Long> {
}

This repository is a reactive repository that Spring Data R2DBC manages.

Finish the application by creating a controller that can store and retrieve data. Implement a TodoController class in the same package, and add the following code:

package com.example.demo;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@RestController
@RequestMapping("/")
public class TodoController {

    private final TodoRepository todoRepository;

    public TodoController(TodoRepository todoRepository) {
        this.todoRepository = todoRepository;
    }

    @PostMapping("/")
    @ResponseStatus(HttpStatus.CREATED)
    public Mono<Todo> createTodo(@RequestBody Todo todo) {
        return todoRepository.save(todo);
    }

    @GetMapping("/")
    public Flux<Todo> getTodos() {
        return todoRepository.findAll();
    }
}

Finally, halt the application and start it again using the following command:

./mvnw spring-boot:run

Test the application

To test the application, you can use cURL.

First, create a new "todo" item in the database using the following command:

curl --header "Content-Type: application/json" \
    --request POST \
    --data '{"description":"configuration","details":"congratulations, you have set up R2DBC correctly!","done": "true"}' \
    http://127.0.0.1:8080

This command should return the created item, as shown here:

{"id":1,"description":"configuration","details":"congratulations, you have set up R2DBC correctly!","done":true}

Next, retrieve the data by using a new cURL request with the following command:

curl http://127.0.0.1:8080

This command will return the list of "todo" items, including the item you've created, as shown here:

[{"id":1,"description":"configuration","details":"congratulations, you have set up R2DBC correctly!","done":true}]

Here's a screenshot of these cURL requests:

Congratulations! You've created a fully reactive Spring Boot application that uses R2DBC to store and retrieve data from Azure SQL Database.

Clean up resources

To clean up all resources used during this quickstart, delete the resource group by using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

Next steps

To learn more about deploying a Spring Data application to Azure Spring Apps and using managed identity, see Tutorial: Deploy a Spring application to Azure Spring Apps with a passwordless connection to an Azure database.

To learn more about Spring and Azure, continue to the Spring on Azure documentation center.

Spring on Azure

Use Spring Data R2DBC with Azure SQL Database

Prerequisites

See the sample application

Prepare the working environment

Create an Azure SQL Database instance

Configure a firewall rule for your Azure SQL Database server

Configure an Azure SQL database

Create an SQL database non-admin user and grant permission

Create a reactive Spring Boot application

Generate the application by using Spring Initializr

Add the reactive Azure SQL Database driver implementation

Configure Spring Boot to use Azure SQL Database

Create the database schema

Code the application

Test the application

Clean up resources

Next steps

See also

Feedback

Feedback

Additional resources