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

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

application.properties

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

  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

  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.

Full sack engineer with an ethic of “do more with less”. Avid gardener and creator of Plenti (plenti.dev)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store