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 to install the following dependencies
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.
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.propertieschange line 4 to be
- 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_dumpor 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
hibernategenerated DDL anymore.
- change line 4 of your
- Create file
- Paste your prepared SQL code into this file
./gradlew flywayMigrate -i
Boot your app and verify that everything works.
Congratulations! You now have an external database for your Spring Boot application.