Connect To Postgres

Watch Code

When we tried to start our Spring Boot application earlier, we found that it failed to start. The reason for this being that we added PostgreSQL dependencies to our project, and the Spring Boot autoconfiguration has seen them and went about creating the necessary configuration to use a PostgreSQL data source.

That said, our app currently has no way of knowing about things like the password we’ve set in our docker-compose.yml file, so let’s fix this.

In the src/main/resources directory, we have our application.properties file – this is where we’ll set this information.

We’ll add the following to our properties file:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=changemeinprod!

These properties tell Spring that we’ll connect to a PostgreSQL database – it’s worked this much out for itself, but we’ll be explicit here, along with the rest of the configuration.

We tell our app where to find the database, namely on localhost running on port 5432. We also specify to connect to the postgres database.

Then we specify the username of postgres and the same password that we specified in our docker-compose.yaml file.

This configuration is enough to connect to the database, but there is a missing piece of the puzzle – how are we to set the database schema up once we’re connected? Do we create all the necessary tables, indexes and sequences manually? That could get tedious, so let’s add one more line to our properties file:

spring.jpa.hibernate.ddl-auto=update

This line will cause our app to automatically set up, and even update the database schema to match the JPA entities we’ll create for our project – but more on those later.

Whilst we’re here, I mentioned that we’ll not be using PostgreSQL for our tests, of which we only have a single one – we’ll not be writing tests for this application as we’re focused on building, however we still want that test we have to pass.

So let’s correctly configure our test to use the H2 in-memory database. Let’s create a resources directory in src/test and in there an application.properties with the following content:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

This configuration will be used by tests to connect to the H2 in-memory database, and not the Postgres one.

Summary

Spring Boot requires database configuration properties to connect to PostgreSQL
Configuration is added in src/main/resources/application.properties
Test configuration in src/test/resources/application.properties
Spring Boot can now connect and manage the PostgreSQL database
Run tests using H2 instead of PostgreSQL