Can't connect to my database from IntelliJ

Answered
Avatar
Ariel
Created

I have a reactive postgres database defined as follows in my application.properties file:

spring.datasource.url=r2dbc:postgresql://psql-db:5432/db
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=*****
spring.datasource.password=*****

And here is my docker-compose.yml file:

version: "3.8"

services:
  psql-db:
    image: postgres
    container_name: psql-db
    restart: always
    environment:
      - POSTGRES_USER=*****
      - POSTGRES_PASSWORD=*****
      - POSTGRES_DB=db
    ports:
      - '5432:5432'

  spring-app:
    container_name: spring-app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - '8080:8080'
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://psql-db:5432/db
      - SPRING_DATASOURCE_USERNAME=*****
      - SPRING_DATASOURCE_PASSWORD=*****
      - SPRING_JPA_HIBERNATE_DDL_AUTO=update
    depends_on:
      - psql-db

I'm trying to access it via the following properties:

But when I'm testing the connection, I get:
DBMS: PostgreSQL (no ver.)
Case sensitivity: plain=mixed, delimited=exact
The connection attempt failed.

Votes

0

Share

33 comments
Avatar
Jacky Liu
Created

I checked your project again. If you are talking about the database in https://intellij-support.jetbrains.com/hc/en-us/community/posts/19638866362514/comments/19658551063570, it's the database on your local machine but not the docker machine's database, localhost and 0.0.0.0 will point to different dabase server.

Your project is Spring JDBC-based, so no table will be auto-created. You have to create a table before running it in the docker. Only JPA has such a feature for auto table creation, take a good example here: https://www.baeldung.com/spring-data-jpa-generate-db-schema 

If you want to start the app in docker, please follow the guide here:

https://www.jetbrains.com/help/idea/docker-compose.html

A simple way is just run it from the gutter run icon in file docker-compose.yml.

 

0
Avatar
Ariel
Created

Why do you think no table will be auto-created?

I'm running this project in docker via:
mvn clean package
docker compose up

Then, I'm calling the following endpoints in Postman:
http://localhost:8080/song/add
http://localhost:8080/song/list

They do work as intended, proving that a table has been created properly.

0
Avatar
Jacky Liu
Created

Hello,

Your project has some problems, and it is not IDEA-related.
Your data is stored and loaded from the embedded H2 database but not from the postgress db. Here is the simple way to prove it:

1. create file src/main/resources/application.properties

spring.profiles.active=dev

2. comment out application-dev.properties 's debug log

logging.level.root=DEBUG

3. open http://localhost:8080/song/list

Now, please check the output in the Java application(you can even run this application just from IDEA but not in docker):

2024-07-27T01:09:18.605+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT SONG.* FROM SONG]
2024-07-27T01:09:18.606+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] io.r2dbc.h2.client.SessionClient         : Request:  SELECT SONG.* FROM SONG
2024-07-27T01:09:18.607+08:00 DEBUG 47079 --- [java] [ctor-http-nio-2] io.r2dbc.h2.client.SessionClient         : Response: org.h2.result.LocalResult@206fb2da columns: 3 rows: 0 pos: -1

io.r2dbc.h2 is the embed database used by Spring JDBC, it doesn't use the postgresql db. Also as I said, the table is not auto created in postgresql, it's created in the embed H2 db.

I'm not quite sure what's the reason but since it's not related with IDEA so maybe check some other examples. Also the spring doc has sections about this: https://docs.spring.io/spring-framework/reference/data-access/jdbc/initializing-datasource.html But all the configuration is YAML (application.yml) based.

 

0

Please sign in to leave a comment.