Azure Spring Cloud - PostGres Database

Photo by Glenn Carstens-Peters on Unsplash

Azure Spring Cloud - PostGres Database

Kumar Pallav's photo
Kumar Pallav
Jan 23, 2023·

5 min read

Table of contents

PostgreSQL is a powerful, open-source object-relational database system that is widely used for storing and managing data. It is known for its reliability, flexibility, and performance, making it a popular choice for a variety of applications.

Spring Azure Apps is a framework for building cloud-native Java applications that are designed to run on Microsoft Azure. It provides a range of tools and features for developing, deploying, and managing applications on Azure, including support for various database technologies.

Agenda

In this article, we will explore how to use PostgreSQL with Spring Azure Apps to build and deploy cloud-native applications. We will cover the following topics:

  • Setting up a PostgreSQL database on Azure

  • Connecting to the PostgreSQL database from a Spring Azure App

  • Using PostgreSQL with the Spring Data JPA module

  • Deploying a Spring Azure App with PostgreSQL to Azure

Setting up a PostgreSQL database on Azure

To use PostgreSQL with a Spring Azure App, you will first need to set up a PostgreSQL database on Azure. There are a few different options for hosting a PostgreSQL database on Azure, including:

  • Azure Database for PostgreSQL: This is a fully-managed PostgreSQL database service that is built on Azure infrastructure. It is easy to set up and manage and includes features like high availability, automatic backups, and security.

  • Azure Virtual Machines: You can also set up a PostgreSQL database on an Azure Virtual Machine (VM). This allows you to have more control over the configuration and management of the database, but requires more work to set up and maintain.

In this article, we will focus on using Azure Database for PostgreSQL, as it is the easiest option for getting started.

Using Azure Portal

To create an Azure Database for PostgreSQL instance, follow these steps:

  1. Go to the Azure portal and sign in with your Azure account.

  2. Click the "Create a resource" button in the top left corner of the portal.

  3. In the search box, type "Azure Database for PostgreSQL" and press enter.

  4. Click on the "Azure Database for PostgreSQL" option that appears in the search results.

  5. Click the "Create" button to start the creation process.

  6. Fill out the form with the necessary information for your database, including the server name, resource group, and location. Make sure to choose "Flexible Server" as the deployment option.

  7. Click the "Create" button to create the database.

    Once done click on create the below picture will appear

It may take a few minutes for the database to be created. Once it is ready, you will see it listed in the Azure portal under the "All resources" section.

Using Azure CLI

export AZ_RESOURCE_GROUP=codingsaint
export AZ_DATABASE_SERVER_NAME=codingsaintdb
export AZ_DATABASE_NAME=superhero
export AZ_LOCATION=eastus
export AZ_POSTGRESQL_AD_NON_ADMIN_USERNAME=codingsaint
export AZ_LOCAL_IP_ADDRESS=<YOUR_IP_ADDRESS>
export CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName -o tsv)

Now, Let's add create the DB server

az postgres flexible-server create --resource-group $AZ_RESOURCE_GROUP --name $AZ_DATABASE_SERVER_NAME --location $AZ_LOCATION --yes --output tsv

This will give the output as below , it contains userid and password for database, do note it.

Checking the existence of the resource group 'codingsaint'...
Resource group 'codingsaint' exists ? : True 
Detected current client IP : 101.0.63.192
Creating PostgreSQL Server 'codingsaintdb' in group 'codingsaint'...
Your server 'codingsaintdb' is using sku 'Standard_D2s_v3' (Paid Tier). Please refer to https://aka.ms/postgres-pricing for pricing details
Configuring server firewall rule to accept connections from '101.0.63.192'...
Creating PostgreSQL database 'flexibleserverdb'...
Make a note of your password. If you forget, you would have to reset your password with "az postgres flexible-server update -n codingsaintdb -g codingsaint -p <new-password>".
Try using 'az postgres flexible-server connect' command to test out connection.
postgresql://puzzledpie9:Yv9GFa2gVPXXHTvQ1nyJfA@codingsaintdb.postgres.database.azure.com/postgres?sslmode=require    flexibleserverdb    FirewallIPAddress_2023-1-23_7-6-2    codingsaintdb.postgres.database.azure.com    /subscriptions/29f6fde1-6736-4a36-b820-c879ba4868d1/resourceGroups/codingsaint/providers/Microsoft.DBforPostgreSQL/flexibleServers/codingsaintdb    East US    Yv9GFa2gVPXXHTvQ1nyJfA    codingsaint    Standard_D2s_v3        puzzledpie9    13

Once Server is created let's create a database on the server. As we have added the export commands in the previous step, we will create a database name superhero for our application

az postgres flexible-server db create  --resource-group $AZ_RESOURCE_GROUP --database-name $AZ_DATABASE_NAME --server-name $AZ_DATABASE_SERVER_NAME --output tsv

This will create a database for our application. Now we need to provide network access to the database. The command below will help us to create it.

Using PostgreSQL with the Spring Data JPA module

Connecting to the PostgreSQL database from a Spring Azure App

Now that you have a PostgreSQL database on Azure, you can start building a Spring Azure App that connects to it.

To connect to the PostgreSQL database from a Spring Azure App, you will need to do the following:

  1. Add the PostgreSQL JDBC driver dependency to your project. You can do this by adding the following dependency to your pom.xml file:
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId></dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<dependency>
    <groupId>com.azure.spring</groupId>
    <artifactId>spring-cloud-azure-starter-jdbc-postgresql</artifactId>
<dependency>
    <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
</dependency></dependency>
    <groupId>com.azure.spring</groupId>
    <artifactId>spring-cloud-azure-starter</artifactId>
</dependency>

Update application.properties with parameters recieved while creating DB

logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:postgresql://codingsaintdb.postgres.database.azure.com/postgres?sslmode=require

spring.datasource.username=puzzledpie9
spring.datasource.password=Yv9GFa2gVPXXHTvQ1nyJfA
#spring.datasource.azure.passwordless-enabled=true
spring.jpa.show-sql=true
spring.sql.init.mode=always
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = create-drop

Lets create an entity for superhero

Entity

package com.kp.superhero;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Superhero {

    @Id
    private String id;
    private String name;
    private String power;
}

Repository

package com.kp.superhero;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface SuperheroRepository extends CrudRepository< Superhero,String> {
}

Controller

package com.kp.superhero;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@RestController
public class SuperheroController {
    private final SuperheroRepository superheroRepository;

    public SuperheroController(SuperheroRepository superheroRepository) {
        this.superheroRepository = superheroRepository;
    }

    @PostMapping("/superhero")
    public ResponseEntity<Superhero> add(@RequestBody Superhero superhero){
        var sh= superheroRepository.save(superhero);
        return new ResponseEntity<>(sh, HttpStatus.CREATED);
    }
    @GetMapping("superhero/{id}")
    private Superhero getSuperhero(@PathVariable("id") String id){
        return superheroRepository.findById(id).get();
    }
}

Now we can start the server and check if everything works

Once server is started use postman or curl to validate

curl --location --request POST 'localhost:8080/superhero' \
--header 'Content-Type: application/json' \
--data-raw '{
    "id":"01",
    "name": "codingsaint",
    "power": "Bug Killer"
}'
{"id":"01","name":"codingsaint","power":"Bug Killer"}

This will return the inserted Superhero to Azure DB. We can use the get URL to retrieve it to validate the insert .

curl --location --request GET 'localhost:8080/superhero/01'
{"id":"01","name":"codingsaint","power":"Bug Killer"}

Deploying a Spring Azure App with PostgreSQL to Azure

Create a superhero app exactly as we did in last article using web console and enable

URL by assigning endpoint blog.pallav.dev/first-spring-boot-app-at-az..

To deploy we will use the below command from the target directory where the jar file is located after the maven build.

az spring app deploy -n superhero -g codingsaint -s codingsaintsvc --artifact-path superhero-0.0.1-SNAPSHOT.jar

Once done we can check via POSTMAN

Did you find this article valuable?

Support Kumar Pallav by becoming a sponsor. Any amount is appreciated!