Problem connecting Postgres to my Spring Boot app

Questions / Help
1

if you worked with java spring boot application, you would know that connecting database in you application is very simeple.

you need these dependency

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
  </dependency>

Then, in your configuration file (application.properties), you add:

spring.datasource.url=jdbc:postgresql://localhost:5432/<YOUR_DATABASE_NAME>
spring.datasource.username=<YOUR_USERNAME>
spring.datasource.password=<YOUR_PASSWORD>
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

This connects your Spring Boot app to a PostgreSQL database.

The issue I’m facing now is when migrating to Fly.io.

I can create the PostgreSQL database on Fly without problems.
But I can’t connect my Spring Boot app to it.

here is what i see when i create database in terminal

Postgres cluster developemntdatabase created
  Username:    postgres
  Password:    3GqY0VzYr6U6Bc3
  Hostname:    developemntdatabase.internal
  Flycast:     fdaa:xxxxxxxxxx
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:3GqY0VzYr6U6Bc3@developemntdatabase.flycast:5432

Save your credentials in a secure place -- you won't be able to see them again!

Connect to postgres
Any app within the xxxx organization can connect to this Postgres using the above connection string

Now that you've set up Postgres, here's what you need to understand: https://fly.io/docs/postgres/getting-started/what-you-should-know/

the problem in here is there is one thing missing which is the database name.

spring boot expects

spring.datasource.url=jdbc:postgresql://localhost:5432/<YOUR_DATABASE_NAME>
spring.datasource.username=<YOUR_USERNAME>
spring.datasource.password=<YOUR_PASSWORD>

which can also be via ENV

fly gave me only username and pasword. and after port 5432 there must be a /databasename.
and if there is no name after port, spring wont connect it and it will return error

i need help with this.

here is also my dockerfile

FROM maven:3-amazoncorretto-17 AS build
WORKDIR /app/myapp
COPY pom.xml .
COPY src ./src

RUN mvn package

# Stage 2: Package the application in a runtime image
FROM amazoncorretto:17-alpine
WORKDIR /app/myapp
COPY --from=build /app/myapp/target/ROOT.jar app.jar

ENTRYPOINT ["java", "-jar", "app.jar"]

and no worries the credential above is internal network and i destroyed the app before posting this

2

My first observation is that you have a typo in your hostname:

@developemntdatabase.flycast:5432

I guess that should be:

@developmentdatabase.flycast:5432

(Of course if you used this typo consistently across your config, it should work. But it would be remiss of readers not to point it out).

3

I would also guess, based on your console output, that you’ve create a database cluster, not a database. So one solution to this is to shell into your machine, get the psql environment running like so:

psql -h developemntdatabase.internal -U 'postgres'

(You may need to install this temporarily using your Linux distro’s package system. On Alpine I use apk add postgresql15-client.)

And then from there you can create the actual database:

CREATE DATABASE mydb;

The other remark I’d make is that Spring should allow you to connect without a database name. Connecting to a database server is perfectly allowable without a database name to default to, and Spring should be no different. That said, if you do this manual fix, you can just use mydb as the name.

1 Like
4

It might also be worth seeing if the cluster creation process created a default database, which you can do with psql. This resource makes it seem like it might be called public:

5

Thanks friend, I fixed it with your help, it works now.
you ware right i just launched the cluster and i didn’t create the actual database, i thought everything was going to be created by fly.
thanks again

1 Like
6

i used fly proxy command to open a secure tunnel from my local machine to remote Postgres app.

fly proxy 5432 -a my-postgres-db

This binds my local port 5432 to Fly’s internal Postgres port 5432.

After starting the proxy, I could connect it as if the DB were local

psql -h localhost -p 5432 -U postgres -d postgres

then finaly create

CREATE DATABASE myappdb;
1 Like
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.