Can't connect to my database from IntelliJ
Answered
I have a reactive postgres database defined as follows in my application.properties file:spring.datasource.url=r2dbc:postgresql://psql-db:5432/dbspring.datasource.driver-class-name=org.postgresql.Driverspring.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=exactThe connection attempt failed.
Please sign in to leave a comment.
Hello,
Does it work if you use “localhost” instead of “psql-db”?
Yes, “Test connection” will work, but I can't make valid queries to the db. It's like there is no schema defined. When I manage, somehow, to define a schema then the queries return empty values.
Could you please attach screenshots with queries? Is it possible to open DB structure in “Databases” window? Is it possible to open table content by double click?
I'm switching to DataGrip, where the problem is the same when I use “localhost”. For the query not to produce an error, I had to link the “schema.sql” file that is in my IntelliJ project. In the db that is in my Docker container, I have for sure a song named ‘FOO’.
Is needed table shown in “Database Explorer” window?
The answer is yes, but it is only because I imported “schema.sql” via “SQL Scripts / Run SQL Script”. I don't think I would have to do that with the “real” DB, the one that is inside Docker.
Is it possible to connect to DB using other client (
pgadminordbeaver)? Will they show other results?It looks like you have container with DB, but DB itself doesn't contain any data.
I have a.container with a DB and I surely have data in it. I added the data via a REST interface and checked that it's there.
Is it shown properly if you use other DB clients?
With pgAdmin, I can't connect at all to the DB. It should not be so difficult to connect to a DB which is inside a Docker container.
Please try to run “Select * from song”. What results will be shown? What is shown if you try to get table content via REST?
“SELECT * FROM song” returns empty results. Important: When I configure the DB with “localhost” in DataGrip, I have to load the “schema.sql” file, otherwise the “song” table is not recognized. But regarding the “real” DB table that is in Docker: it already have a schema defined when the Spring app starts in the container.
Could you please try to connect to DB from the container using command line: https://medium.com/@bennokohrs/connect-to-postgresql-database-inside-docker-container-7dab32435b49
Will it work and show table content?
I have followed the instructions and “SELECT * FROM song” returns nothing on the docker postgress image.
Here is the log related to the db when “docker compose up” is starting:
psql-db | The files belonging to this database system will be owned by user "postgres".psql-db | This user must also own the server process.psql-db |psql-db | The database cluster will be initialized with locale "en_US.utf8".psql-db | The default database encoding has accordingly been set to "UTF8".psql-db | The default text search configuration will be set to "english".psql-db |psql-db | Data page checksums are disabled.psql-db |psql-db | fixing permissions on existing directory /var/lib/postgresql/data ... okpsql-db | creating subdirectories ... okpsql-db | selecting dynamic shared memory implementation ... posixpsql-db | selecting default max_connections ... 100psql-db | selecting default shared_buffers ... 128MBpsql-db | selecting default time zone ... Etc/UTCpsql-db | creating configuration files ... okpsql-db | running bootstrap script ... okpsql-db | performing post-bootstrap initialization ... okpsql-db | initdb: warning: enabling "trust" authentication for local connectionspsql-db | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.psql-db | syncing data to disk ... okpsql-db |psql-db |psql-db | Success. You can now start the database server using:psql-db |psql-db | pg_ctl -D /var/lib/postgresql/data -l logfile startpsql-db |psql-db | waiting for server to start....2024-06-23 16:53:52.716 UTC [48] LOG: starting PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bitpsql-db | 2024-06-23 16:53:52.719 UTC [48] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"psql-db | 2024-06-23 16:53:52.731 UTC [51] LOG: database system was shut down at 2024-06-23 16:53:52 UTCpsql-db | 2024-06-23 16:53:52.737 UTC [48] LOG: database system is ready to accept connectionspsql-db | donepsql-db | server startedpsql-db | CREATE DATABASEpsql-db |psql-db |psql-db | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*psql-db |psql-db | 2024-06-23 16:53:52.952 UTC [48] LOG: received fast shutdown requestpsql-db | waiting for server to shut down....2024-06-23 16:53:52.955 UTC [48] LOG: aborting any active transactionspsql-db | 2024-06-23 16:53:52.958 UTC [48] LOG: background worker "logical replication launcher" (PID 54) exited with exit code 1psql-db | 2024-06-23 16:53:52.958 UTC [49] LOG: shutting downpsql-db | 2024-06-23 16:53:52.961 UTC [49] LOG: checkpoint starting: shutdown immediatepsql-db | 2024-06-23 16:53:53.054 UTC [49] LOG: checkpoint complete: wrote 922 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.026 s, sync=0.058 s, total=0.096 s; sync files=301, longest=0.010 s, average=0.001 s; distance=4255 kB, estimate=4255 kB; lsn=0/1912038, redo lsn=0/1912038psql-db | 2024-06-23 16:53:53.060 UTC [48] LOG: database system is shut downpsql-db | donepsql-db | server stoppedpsql-db |psql-db | PostgreSQL init process complete; ready for start up.psql-db |psql-db | 2024-06-23 16:53:53.215 UTC [1] LOG: starting PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bitpsql-db | 2024-06-23 16:53:53.217 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432psql-db | 2024-06-23 16:53:53.218 UTC [1] LOG: listening on IPv6 address "::", port 5432psql-db | 2024-06-23 16:53:53.222 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"psql-db | 2024-06-23 16:53:53.233 UTC [64] LOG: database system was shut down at 2024-06-23 16:53:53 UTCpsql-db | 2024-06-23 16:53:53.242 UTC [1] LOG: database system is ready to accept connections…
psql-db | 2024-06-23 16:58:53.276 UTC [62] LOG: checkpoint starting: timepsql-db | 2024-06-23 16:58:57.538 UTC [62] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.246 s, sync=0.008 s, total=4.263 s; sync files=12, longest=0.004 s, average=0.001 s; distance=260 kB, estimate=260 kB; lsn=0/1953458, redo lsn=0/1953420psql-db | 2024-06-23 17:03:52.271 UTC [103] FATAL: role "postgres" does not existCan you spot something abnormal?
One thing is sure: my REST interface works and can write into “some” database.
Could you please check list of available tables using DB client from the container?
According to provided data, DB is created, but no tables or data is added. Does your Spring app shows table content just after starting container? Isn't there any data generated in application initialisation code?
In the container, “SHOW TABLES” returns nothing.
- I'm not showing any table just after starting the container|.
- No data is generating during application initialization.
It looks strange that there is no data shown in DB client, but Application could get it.
I posted something but i somehow disappeared. Trying again…
In another Spring project, everything is working as intended with my DB and Datagrip. The repository looks like this:
public interface SongRepository extends JpaRepository<Song, Long>{Optional<Song> findByName(String name);Optional<Song> findByComposer(String composer);}In my other project which is not working, the repository looks like this:
public interface SongRepository extends ReactiveCrudRepository<Song, Long> {@Query("SELECT * FROM song WHERE name = :name")Flux<Song> findByName(String name);@Query("SELECT * FROM song WHERE composer = :composer")Flux<Song> findByComposer(String composer);}It looks like IntelliJ and Datagrip have a problem handling reactive databases?
Do you have the same DB settings in application which is shown fine in DataGrip?
The docker-compose.xml is exactly the same (see in on of my earlier posts), but the application properties files are different:
spring.datasource.url=jdbc:postgresql://psql-db:5432/dbspring.datasource.driver-class-name=org.postgresql.Driverspring.datasource.username=*****spring.datasource.password=*****spring.datasource.url=r2dbc:postgresql://psql-db:5432/dbspring.datasource.driver-class-name=org.postgresql.Driverspring.datasource.username=*****spring.datasource.password=*****Even though I'm not sure these files are taken in count at all.
Hi Ariel. Does it make a difference if you use
jdbcinstead ofr2dbcin thespring.datasource.urlproperty for the reactive project?Hi Arina, it doesn't make any difference.
I had doubts before, but now I'm sure: the application property file is not taken in count, at least the “spring.datasource” values.
In that case, could you please upload application logs to https://uploads.jetbrains.com and provide the id of the upload here? I'll check the exceptions and concurrent events.
Help | Show Log in Finder -> idea.log file
Not sure I understand what you're asking. The idea.log file is huge and contains non-relevant information in my opinion.
So I have uploaded the log produced by “docker compose up” where you can see what's going on with the “psql_db” and “spring_app” images.
Upload id: 2024_07_09_5FLU1jUfpcYS5B1Rpv7phK (file: log.txt)
I uploaded a minimal project: Upload id: 2024_07_18_k1VSAeUCx3ChZ96dBrHJqB (file: JAVA.zip)
Hi, please try setting the host value to 0.0.0.0 in the IDEA Database toolwindow. Then, the database should be able to connect to it.
The reason is that if you check the log in the docker db image, the listen address is 0.0.0.0:
I followed your instructions and the “Test Connection” buttons shows “Succeeded”. But when I try to access the content of the DB, I get an error, as if the schema is not known:
The table must be created before it can be selected. Please execute SQL in the file src/main/resources/schema.sql first:
You can open the file and execute it directly in IDEA.
I don't understand why I need to create a table in Datagrip. The table is already created by my Spring application, and I'm able to add a new song to the DB and check the content of the table via Postman.