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.
- in your
application.properties
change line 4 to bespring.jpa.hibernate.ddl-auto=create-drop
- Start up your application
- If everything went well, check out your database. It should have a nice collection of tables.
- You can now use a tool like
pg_dump
or Postico to get the SQL that generates the database. - 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.
- Delete and recreate the database. We’re not going to use the
hibernate
generated DDL anymore. - change line 4 of your
application.properties
back tospring.jpa.hibernate.ddl-auto=none
- Create file
src/main/resources/db/migration/V1__initial_setup.sql
- Paste your prepared SQL code into this file
- Run
./gradlew flywayMigrate -i
Boot your app and verify that everything works.
Congratulations! You now have an external database for your Spring Boot application.