postgresql spring-boot jpa docker-compose spring-data-jpa

Spring JPA with docker compose: Unable to determine Dialect without JDBC metadata (please set 'jakarta.persistence.jdbc.url' or 'hibernate.dialect'

I am unable to connect my spring boot application to a PostgreSQL database using Spring JPA.

Below is my application.yaml file.

spring:
  application:
    name: jaah
  datasource:
    url: jdbc:postgresql://db:5432/taah
    username: waah
    password: blah
    driverClassName: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        ddl-auto: none
        boot:
          '[allow_jdbc_metadata_access]': false
  sql:
    init:
      mode: never
  main:
    allow-bean-definition-overriding: true
logging:  
    level:    
      org:
        hibernate: DEBUG
server:
  port: 8080
  address: 0.0.0.0

and below is the docker compose.yaml file. The database is hosted on post 5432 INSIDE the container and 5434 on the outside.

db:
    image: postgres
    container_name: db
    ports:
      - 5434:5432
    restart: always
    env_file:
      - .env
    volumes:
      - postgresdata:/var/lib/postgresql/data
 
    environment:
       - POSTGRES_DB=taah
       - POSTGRES_USER=waah
       - PGUSER=waah
       - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}                  #from env
    healthcheck:
      test: ["CMD-SHELL","psql -h localhost -U $${POSTGRES_USER} -c select 1 -d $${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - mynetwork

spring_boot_server:
    image: backend
    build: .
    depends_on: 
      db:
        condition: service_healthy
    ports:
      - "8080:8080"  
    networks:
      - mynetwork
    environment:
      - SERVER_PORT=8080  
networks:
  mynetwork:
    driver: bridge

My DockerFile below

FROM openjdk:21
ARG JAR_FILE=target/*.jar
COPY ${JAR_FILE} app.jar
EXPOSE 8080
ENTRYPOINT ["java","-jar","/app.jar"]

The application worked when I ran the docker compose and the server separately i.e when I ran the postgres docker container and THEN the spring application connected to the database at port 5434.

This was the last message before the server started db | 2024-09-15 11:14:15.980 UTC [1] LOG: database system is ready to accept connections I bash'd into the postgres container sudo docker exec -it db /bin/sh

psql -h db -p 5432 -U waah -d taah Password for user waah: waah=# \dt Did not find any relations.

This was AFTER I restarted the container using

sudo docker-compose down --volumes
sudo docker-compose build
sudo docker-compose up

I also changed the database url to jdbc:postgresql://localhost:5432/taah but with no avail

Solution

My Environment:

Java: 17.0.11
Maven : Apache Maven 3.9.5
Docker version 27.2.1
docker-compose version 1.25.0

docker-compose.yaml

version: '3.3' # Add version

services:      # Add services 
  db:
    image: postgres
    container_name: db
    ports:
      - 5432:5432
    restart: always
    env_file:
      - .env
    volumes:
      - postgresdata:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=taah
      - POSTGRES_USER=waah
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}                  #from env  
    #- PGUSER=waah   # Remove PGUSER
    healthcheck:
      test: ["CMD-SHELL","psql -h localhost -U $${POSTGRES_USER} -c select 1 -d $${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - mynetwork

  spring_boot_server:
    image: backend
    build: .
    depends_on:
      - db
      # Remove condition: service_healthy
    ports:
      - "8080:8080"  
    networks:
      - mynetwork
    environment:
      - SERVER_PORT=8080
      
networks:
  mynetwork:
    driver: bridge
    
volumes:
  postgresdata:

Add version: version: '3.3'
Add services: services:
Remove PGUSER #- PGUSER=waah
Change port: port 5432:5432
Remove condition: service_healthy
change:

    depends_on:
      - db

Project Tree

demo-jpa-st
├── docker-compose.yaml
├── Dockerfile
├── .dockerignore
├── .env
├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── example
        │           ├── controller
        │           │   └── PersonController.java
        │           ├── DemoJpaApplication.java
        │           ├── HelloController.java
        │           ├── model
        │           │   └── Person.java
        │           ├── repository
        │           │   └── PersonRepository.java
        │           ├── service
        │           │   └── PersonService.java
        │           └── WebConfig.java
        └── resources
            └── application.yaml

Dockerfile

FROM openjdk:17-jdk-alpine

ARG JAR_FILE=target/*.jar

WORKDIR /app

COPY ${JAR_FILE} /app/app.jar

EXPOSE 8080
ENTRYPOINT ["java","-jar","/app/app.jar"]

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo-jpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo-jpa</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Swagger -->
        <dependency>
            <groupId>org.springdoc</groupId>
            <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
            <version>2.2.0</version>
        </dependency>

    </dependencies>

    <build>
        <finalName>app</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

.dockerignore

.*
/src
/.mvn
pom.xml
.dockerignore
.env
docker-compose.yaml

.env

POSTGRES_PASSWORD=blah

application.yaml

spring:
  application:
    name: jaah
  datasource:
    url: jdbc:postgresql://db:5432/taah
    username: waah
    password: blah
    driverClassName: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        ddl-auto: none
        boot:
          '[allow_jdbc_metadata_access]': false
  sql:
    init:
      mode: never
  main:
    allow-bean-definition-overriding: true
logging:
  level:
    org:
      hibernate: DEBUG
server:
  port: 8080
  address: 0.0.0.0

DemoJpaApplication.java

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoJpaApplication {

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

}

HelloController.java

package com.example;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class HelloController {

    @GetMapping("/hello/{name}")
    public String sayHello(
            @PathVariable String name,
            @RequestParam(value = "greeting", defaultValue = "Hello") String greeting) {
        return String.format("%s, %s!", greeting, name);
    }
}

WebConfig.java

package com.example;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.web.config.EnableSpringDataWebSupport;

@Configuration
@EnableSpringDataWebSupport(pageSerializationMode = EnableSpringDataWebSupport.PageSerializationMode.VIA_DTO)
public class WebConfig {
}

model/Person.java

package com.example.model;

import io.swagger.v3.oas.annotations.media.Schema;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Schema(hidden = true)
    private Long id;
    private String name;
    private String mail;

    // Constructors, getters, and setters
    public Person() {
    }

    public Person(String name, String mail) {
        this.name = name;
        this.mail = mail;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMail() {
        return mail;
    }

    public void setMail(String mail) {
        this.mail = mail;
    }
}

repository/PersonRepository.java

package com.example.repository;

import com.example.model.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.PagingAndSortingRepository;

public interface PersonRepository extends JpaRepository<Person, Long>, PagingAndSortingRepository<Person, Long> {
}

service/PersonService.java

package com.example.service;

import com.example.model.Person;
import com.example.repository.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import java.util.Optional;

@Service
public class PersonService {
    @Autowired
    private PersonRepository personRepository;

    public Page<Person> getAllPersons(int page, int size) {
        return personRepository.findAll(PageRequest.of(page, size));
    }

    public Optional<Person> getPersonById(Long id) {
        return personRepository.findById(id);
    }

    public Person savePerson(Person person) {
        return personRepository.save(person);
    }

    public void deletePerson(Long id) {
        personRepository.deleteById(id);
    }
}

controller/PersonController.java

package com.example.controller;

import com.example.model.Person;
import com.example.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.*;

import java.util.Optional;

@RestController
@RequestMapping("/persons")
public class PersonController {
    @Autowired
    private PersonService personService;

    @GetMapping
    public Page<Person> getAllPersons(@RequestParam(defaultValue = "0") int page, @RequestParam(defaultValue = "10") int size) {
        return personService.getAllPersons(page, size);
    }

    @GetMapping("/{id}")
    public Optional<Person> getPersonById(@PathVariable Long id) {
        Optional<Person> person = personService.getPersonById(id);
        return personService.getPersonById(id);
    }

    @PostMapping
    public Person createPerson(@RequestBody Person person) {
        return personService.savePerson(person);
    }

    @PutMapping("/{id}")
    public Optional<Person> updatePerson(@PathVariable Long id, @RequestBody Person updatedPerson) {
        Optional<Person> optionalPerson = personService.getPersonById(id);
        if (optionalPerson.isPresent()) {
            Person person = optionalPerson.get();
            person.setName(updatedPerson.getName());
            person.setMail(updatedPerson.getMail());
            return Optional.ofNullable(personService.savePerson(person));
        }
            return optionalPerson ;
    }

    @DeleteMapping("/{id}")
    public void deletePerson(@PathVariable Long id) {
        personService.deletePerson(id);
    }
}

Spring Boot Application Build

mvn clean package

Docker-compose Run

Delete Old Docker Image

find backend's old image id

$ docker image ls
REPOSITORY                                       TAG                           IMAGE ID       CREATED          SIZE
backend                                          latest                        3b9a589b85e5   37 minutes ago   378MB

delete backend old image

$ docker image rm -f 3b9a589b85e5

Run

$ docker-compose up

Check container status

run command:

docker ps

return:

$ docker ps
CONTAINER ID   IMAGE      COMMAND                   CREATED          STATUS                    PORTS                                       NAMES
27acf24ea6ae   backend    "java -jar /app/app.…"   17 minutes ago   Up 17 minutes             0.0.0.0:8080->8080/tcp, :::8080->8080/tcp   demo-jpa-st_spring_boot_server_1
059726d230f2   postgres   "docker-entrypoint.s…"   17 minutes ago   Up 17 minutes (healthy)   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   db

Test Backend

Test 1. Create a Person (POST)

curl -X POST http://localhost:8080/persons \
  -H "Content-Type: application/json" \
  -d '{"name": "John Doe", "mail": "johndoe@example.com"}'

curl -X POST http://localhost:8080/persons \
  -H "Content-Type: application/json" \
  -d '{"name": "Jane Doe", "mail": "janedoe@example.com"}'

curl -X POST http://localhost:8080/persons \
  -H "Content-Type: application/json" \
  -d '{"name": "Alice", "mail": "alice@example.com"}'

get return

{"id":1,"name":"John Doe","mail":"johndoe@example.com"}

{"id":2,"name":"Jane Doe","mail":"janedoe@example.com"}

{"id":3,"name":"Alice","mail":"alice@example.com"}

Test 2. Read All Persons with Pagination (GET)

curl -X GET "http://localhost:8080/persons?page=0&size=5"

get return

{"content":[{"id":1,"name":"John Doe","mail":"johndoe@example.com"},{"id":2,"name":"Jane Doe","mail":"janedoe@example.com"},{"id":3,"name":"Alice","mail":"alice@example.com"}],"page":{"size":5,"number":0,"totalElements":3,"totalPages":1}}

swagger ui test

http://localhost:8080/swagger-ui/index.html