Use Spring Data JPA with Azure Database for PostgreSQL

This tutorial demonstrates how to store data in Azure Database for PostgreSQL using Spring Data JPA.

The Java Persistence API (JPA) is the standard Java API for object-relational mapping.

In this tutorial, we include two authentication methods: Microsoft Entra authentication and PostgreSQL authentication. The Passwordless tab shows the Microsoft Entra authentication and the Password tab shows the PostgreSQL authentication.

Microsoft Entra authentication is a mechanism for connecting to Azure Database for PostgreSQL using identities defined in Microsoft Entra ID. With Microsoft Entra authentication, you can manage database user identities and other Microsoft services in a central location, which simplifies permission management.

PostgreSQL authentication uses accounts stored in PostgreSQL. If you choose to use passwords as credentials for the accounts, these credentials will be stored in the user table. Because these passwords are stored in PostgreSQL, you need to manage the rotation of the passwords by yourself.

Prerequisites

  • PostgreSQL command line client.

  • If you don't have a Spring Boot application, create a Maven project with the Spring Initializr. Be sure to select Maven Project and, under Dependencies, add the Spring Web, Spring Data JDBC, and PostgreSQL Driver dependencies, and then select Java version 8 or higher.

Important

To use passwordless connections, configure the Microsoft Entra admin user for your Azure Database for PostgreSQL Flexible Server instance. For more information, see Manage Microsoft Entra roles in Azure Database for PostgreSQL - Flexible Server.

See the sample application

In this tutorial, 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-jpa-postgresql.

Configure a firewall rule for your PostgreSQL server

Azure Database for PostgreSQL instances are secured by default. They have a firewall that doesn't allow any incoming connection.

To be able to use your database, open the server's firewall to allow the local IP address to access the database server. For more information, see Firewall rules in Azure Database for PostgreSQL - Flexible Server.

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

Create a PostgreSQL non-admin user and grant permission

Next, create a non-admin user and grant all permissions to the database.

You can use the following method to create a non-admin user that uses a passwordless connection.

  1. Use the following command to install the Service Connector passwordless extension for the Azure CLI:

     az extension add --name serviceconnector-passwordless --upgrade
    
  2. Use the following command to create the Microsoft Entra non-admin user:

      az connection create postgres-flexible \
           --resource-group <your_resource_group_name> \
           --connection postgres_conn \
           --target-resource-group <your_resource_group_name> \
           --server postgresqlflexibletest \
           --database demo \
           --user-account \
           --query authInfo.userName \
           --output tsv
    

    When the command completes, take note of the username in the console output.

Store data from Azure Database for PostgreSQL

Now that you have an Azure Database for PostgreSQL Flexible Server instance, you can store data by using Spring Cloud Azure.

To install the Spring Cloud Azure Starter JDBC PostgreSQL module, add the following dependencies to your pom.xml file:

  • The Spring Cloud Azure Bill of Materials (BOM):

    <dependencyManagement>
      <dependencies>
        <dependency>
          <groupId>com.azure.spring</groupId>
          <artifactId>spring-cloud-azure-dependencies</artifactId>
          <version>5.9.1</version>
          <type>pom</type>
          <scope>import</scope>
        </dependency>
      </dependencies>
    </dependencyManagement>
    

    Note

    If you're using Spring Boot 2.x, be sure to set the spring-cloud-azure-dependencies version to 4.15.0. This Bill of Material (BOM) should be configured in the <dependencyManagement> section of your pom.xml file. This ensures that all Spring Cloud Azure dependencies are using the same version. For more information about the version used for this BOM, see Which Version of Spring Cloud Azure Should I Use.

  • The Spring Cloud Azure Starter JDBC PostgreSQL artifact:

    <dependency>
      <groupId>com.azure.spring</groupId>
      <artifactId>spring-cloud-azure-starter-jdbc-postgresql</artifactId>
    </dependency>
    

Note

Passwordless connections have been supported since version 4.5.0.

Configure Spring Boot to use Azure Database for PostgreSQL

To store data from Azure Database for PostgreSQL using Spring Data JPA, follow these steps to configure the application:

  1. Configure Azure Database for PostgreSQL credentials by adding the following properties to your application.properties configuration file.

    logging.level.org.hibernate.SQL=DEBUG
    
    spring.datasource.url=jdbc:postgresql://postgresqlflexibletest.postgres.database.azure.com:5432/demo?sslmode=require
    spring.datasource.username=<your_postgresql_ad_non_admin_username>
    spring.datasource.azure.passwordless-enabled=true
    
    spring.jpa.hibernate.ddl-auto=create-drop
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    

Important

To use passwordless connections, configure the Microsoft Entra admin user for your Azure Database for PostgreSQL Single Server instance. For more information, see Use Microsoft Entra ID for authentication with PostgreSQL.

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-jpa-postgresql.

Configure a firewall rule for your PostgreSQL server

Azure Database for PostgreSQL instances are secured by default. They have a firewall that doesn't allow any incoming connection.

To be able to use your database, open the server's firewall to allow the local IP address to access the database server. For more information, see Create and manage firewall rules for Azure Database for PostgreSQL - Single Server using the Azure portal.

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

Create a PostgreSQL non-admin user and grant permission

Next, create a non-admin user and grant all permissions to the database.

You can use the following method to create a non-admin user that uses a passwordless connection.

  1. Use the following command to install the Service Connector passwordless extension for the Azure CLI:

     az extension add --name serviceconnector-passwordless --upgrade
    
  2. Use the following command to create the Microsoft Entra non-admin user:

      az connection create postgres \
           --resource-group <your_resource_group_name> \
           --connection postgres_conn \
           --target-resource-group <your_resource_group_name> \
           --server postgresqlsingletest \
           --database demo \
           --user-account \
           --query authInfo.userName \
           --output tsv
    

    When the command completes, take note of the username in the console output.

Store data from Azure Database for PostgreSQL

Now that you have an Azure Database for PostgreSQL Single server instance, you can store data by using Spring Cloud Azure.

To install the Spring Cloud Azure Starter JDBC PostgreSQL module, add the following dependencies to your pom.xml file:

  • The Spring Cloud Azure Bill of Materials (BOM):

    <dependencyManagement>
      <dependencies>
        <dependency>
          <groupId>com.azure.spring</groupId>
          <artifactId>spring-cloud-azure-dependencies</artifactId>
          <version>5.9.1</version>
          <type>pom</type>
          <scope>import</scope>
        </dependency>
      </dependencies>
    </dependencyManagement>
    

    Note

    If you're using Spring Boot 2.x, be sure to set the spring-cloud-azure-dependencies version to 4.15.0. This Bill of Material (BOM) should be configured in the <dependencyManagement> section of your pom.xml file. This ensures that all Spring Cloud Azure dependencies are using the same version. For more information about the version used for this BOM, see Which Version of Spring Cloud Azure Should I Use.

  • The Spring Cloud Azure Starter JDBC PostgreSQL artifact:

    <dependency>
      <groupId>com.azure.spring</groupId>
      <artifactId>spring-cloud-azure-starter-jdbc-postgresql</artifactId>
    </dependency>
    

Note

Passwordless connections have been supported since version 4.5.0.

Configure Spring Boot to use Azure Database for PostgreSQL

To store data from Azure Database for PostgreSQL using Spring Data JPA, follow these steps to configure the application:

  1. Configure Azure Database for PostgreSQL credentials by adding the following properties to your application.properties configuration file.

    logging.level.org.hibernate.SQL=DEBUG
    
    spring.datasource.url=jdbc:postgresql://postgresqlsingletest.postgres.database.azure.com:5432/demo?sslmode=require
    spring.datasource.username=<your_postgresql_ad_non_admin_username>@postgresqlsingletest
    spring.datasource.azure.passwordless-enabled=true
    
    spring.jpa.hibernate.ddl-auto=create-drop
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    
  1. Create a new Todo Java class. This class is a domain model mapped onto the todo table that will be created automatically by JPA. The following code ignores the getters and setters methods.

    package com.example.demo;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    
    @Entity
    public class Todo {
    
        public Todo() {
        }
    
        public Todo(String description, String details, boolean done) {
            this.description = description;
            this.details = details;
            this.done = done;
        }
    
        @Id
        @GeneratedValue
        private Long id;
    
        private String description;
    
        private String details;
    
        private boolean done;
    
    }
    
  2. Edit the startup class file to show the following content.

    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.context.event.ApplicationReadyEvent;
    import org.springframework.context.ApplicationListener;
    import org.springframework.context.annotation.Bean;
    import org.springframework.data.jpa.repository.JpaRepository;
    
    import java.util.stream.Collectors;
    import java.util.stream.Stream;
    
    @SpringBootApplication
    public class DemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(DemoApplication.class, args);
        }
    
        @Bean
        ApplicationListener<ApplicationReadyEvent> basicsApplicationListener(TodoRepository repository) {
            return event->repository
                .saveAll(Stream.of("A", "B", "C").map(name->new Todo("configuration", "congratulations, you have set up correctly!", true)).collect(Collectors.toList()))
                .forEach(System.out::println);
        }
    
    }
    
    interface TodoRepository extends JpaRepository<Todo, Long> {
    
    }
    

    Tip

    In this tutorial, there are no authentication operations in the configurations or the code. However, connecting to Azure services requires authentication. To complete the authentication, you need to use Azure Identity. Spring Cloud Azure uses DefaultAzureCredential, which the Azure Identity library provides to help you get credentials without any code changes.

    DefaultAzureCredential supports multiple authentication methods and determines which method to use at runtime. This approach enables your app to use different authentication methods in different environments (such as local and production environments) without implementing environment-specific code. For more information, see DefaultAzureCredential.

    To complete the authentication in local development environments, you can use Azure CLI, Visual Studio Code, PowerShell, or other methods. For more information, see Azure authentication in Java development environments. To complete the authentication in Azure hosting environments, we recommend using user-assigned managed identity. For more information, see What are managed identities for Azure resources?

  3. Start the application. You'll see logs similar to the following example:

    2023-02-01 10:29:19.763 DEBUG 4392 --- [main] org.hibernate.SQL : insert into todo (description, details, done, id) values (?, ?, ?, ?)
    com.example.demo.Todo@1f
    

Deploy to Azure Spring Apps

Now that you have the Spring Boot application running locally, it's time to move it to production. Azure Spring Apps makes it easy to deploy Spring Boot applications to Azure without any code changes. The service manages the infrastructure of Spring applications so developers can focus on their code. Azure Spring Apps provides lifecycle management using comprehensive monitoring and diagnostics, configuration management, service discovery, CI/CD integration, blue-green deployments, and more. To deploy your application to Azure Spring Apps, see Deploy your first application to Azure Spring Apps.

Next steps