Externalized Postgres in Spring Boot

So you’re building a Spring Boot application and you want to use Postgres. There are lots of guides that have various solutions of varying degrees of success. Some use older tools. Here’s my modern (2020) setup that I’m using in production for my project https://plenti.dev.

This project uses Gradle, I don’t know how to do it using other build tools.

Also, I work on a mac- so my software recommendations are biased in that direction. If you know of tools that work the same in other environments I’d love to know about them. Leave a comment!

Before you begin

Make sure your local machine Postgres installation is working correctly . I like to verify my Postgres installation is working by using a GUI client to browse it. Postico is my favorite of these tools.

If you want a super simple way to install and get Posgres up and running, I recommend Postgres.app.

You’re also going to need a user/password combination that works for command line access to Postgres. Run this command in Posgres to get there:

CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD 'password';

build.gradle

Modify your build.gradle to install the following dependencies

application.properties

Now it’s time to set up the application.properties file. Create it if it doesn’t exist. The normal path is src/main/resources/application.properties Edit it to look something like this.

Your application should now start up. If you have already described some entities, then you are likely getting some errors for their database tables not existing. Note that we set things up with spring.jpa.hibernate.ddl-auto=none which means that there is no smart table generation. The best way to keep your database in the correct state is the use migrations. If you’ve followed along, then you’ve already installed the Flyway java database migration tool.

Database Migrations

A Shortcut — Generating your first migration

a lot of time, the embedded databases that you have in the spring boot starter projects get created using spring.jpa.hibernate.ddl-auto=create-drop which generates a schema based on the entity objects you’ve created in your data model. We can use this fact to generate the first migration for our database without having to do a lot of extra work.

1. in your application.properties change line 4 to be spring.jpa.hibernate.ddl-auto=create-drop
2. Start up your application
3. If everything went well, check out your database. It should have a nice collection of tables.
4. You can now use a tool like pg_dump or Postico to get the SQL that generates the database.
5. To use Postico, you’ll have to go one-by-one through the tables and select the DDL button on each one which should give you some SQL output.

Once your SQL is prepared

You should now have the SQL schema code to generate the tables that your database needs.

1. Delete and recreate the database. We’re not going to use the hibernate generated DDL anymore.
2. change line 4 of your application.properties back to spring.jpa.hibernate.ddl-auto=none
3. Create file src/main/resources/db/migration/V1__initial_setup.sql
4. Paste your prepared SQL code into this file
5. Run ./gradlew flywayMigrate -i

Boot your app and verify that everything works.

Congratulations! You now have an external database for your Spring Boot application.