Spring Boot with Flyway and PostgreSQL Example

Joel Patrick LlosaSeptember 19th, 2019Last Updated: September 18th, 2019

0 432 6 minutes read

Welcome Java Code Geeks to the Spring Boot with Flyway and PostgreSQL example. In this example we will imagine ourselves as making an application for a car dealership. Well, just a very tiny fraction of it. As we all know, the software we create changes as the business need changes.

PostgreSQL is an object-relational database management system. It’s one of the most popular databases used in the world. Flyway is an open-source database-migration tool, similar to Liquibase.

1. Assumptions

This article is not for the absolute beginner. It is assumed that you know your way around Eclipse. You are familiar with Maven and Spring. This project has been created using Eclipse Oxygen so all instructions are based on this IDE. Basically, this article assumes that you have done some coding. Last but not the least, you must have read and done Java JDBC PostgreSQL Connection Example.

2. Tools

3. Project Setup

After going through Java JDBC PostGreSQL Connection Example, you should have your PostgreSQL server set up and you should be able to see the records using pgAdmin 4.

Spring Boot with Flyway - View All Rows — View All Rows

Now that our database is set up, let’s connect to it and read the records. Create a Maven project in Eclipse. Our pom.xml should look like the one below:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.javacodegeeks.example</groupId>
  <artifactId>spring-boot-flyway-postgresql</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.9.RELEASE</version>
  </parent>
  
  <properties>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      </dependency>
    </dependencies>
    <build>
      <plugins>
        <plugin>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
      </plugins>
    </build>
</project>

As shown above, our project has 2 dependencies as of the moment. We are using spring-boot-starter-jdbc, which means that we can use all the Spring modules included in it. For example, the Spring Core and Spring JDBC modules will be available for us to use plus many more. The next dependency is PostgreSQL. The Spring Boot Maven plugin enables us to package the project as an executable jar. We will add Flayway later.

4. Initial Java Code

Our code below reads from the database and prints the records. Did you get the class name? Instead of SpringBootPostgreSQL, it’s SummerShoeLandMailSql. Get it? Anyway, the class name is just for fun. In all seriousness, it should be descriptive and meaningful (DAMP) so that it is easy to maintain.

SummerShoeLandMailSql.java

package com.javacodegeeks.example;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
@SpringBootApplication
public class SummerShoeLandMailSql implements CommandLineRunner {
    
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    public static void main(String[] args) {
        SpringApplication.run(SummerShoeLandMailSql.class, args);
    }
    @Override
    public void run(String... args) throws Exception {      
        readRecords();
    }
    
    private void readRecords() {
        System.out.println("Reading cars records...");
        System.out.printf("%-30.30s  %-30.30s%n", "Model", "Price");
        jdbcTemplate.query("SELECT * FROM cars", new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                System.out.printf("%-30.30s  %-30.30s%n", rs.getString("model"), rs.getString("price"));
            }
            
        });
    }
}

Instead of annotating our class with @Configuration, @EnableAutoConfiguration, and @ComponentScan, we use the @SpringBootApplication annotation as a convenient alternative. This annotation tells Spring Boot to scan for other components, add beans based on the classpath, and tags the class as a source of bean definitions.

We implemented the CommandLineRunner because we want to execute the run method after the application context is loaded.

Spring Boot automatically creates JdbcTemplate because we are using the Spring JDBC module (remember the spring-boot-starter-jdbc dependency?). @Autowired automatically loads JdbcTemplate.

The main method uses SpringApplication.run() to run the application.

We then used the readRecords method to execute a SELECT statement and our RowCallbackHandler handles the printing of the movie records. Lastly, the records are printed in a nice column.

5. Initial Code Output

Initial Console Output

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.9.RELEASE)
2019-09-03 19:37:56.309  INFO 52212 --- [           main] c.j.example.SummerShoeLandMailSql        : Starting SummerShoeLandMailSql on inspiron-15-7k with PID 52212 (D:\confluence-service\workspace\spring-boot-flyway-postgresql\target\classes started by jpllosa in D:\confluence-service\workspace\spring-boot-flyway-postgresql)
2019-09-03 19:37:56.312  INFO 52212 --- [           main] c.j.example.SummerShoeLandMailSql        : No active profile set, falling back to default profiles: default
2019-09-03 19:37:56.356  INFO 52212 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@587c290d: startup date [Tue Sep 03 19:37:56 BST 2019]; root of context hierarchy
2019-09-03 19:37:57.414  INFO 52212 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
Reading cars records...
Model                           Price                         
mondeo                          £20,000.00                    
mustang                         £40,000.00                    
2019-09-03 19:37:58.667  INFO 52212 --- [           main] c.j.example.SummerShoeLandMailSql        : Started SummerShoeLandMailSql in 2.614 seconds (JVM running for 2.995)
2019-09-03 19:37:58.668  INFO 52212 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@587c290d: startup date [Tue Sep 03 19:37:56 BST 2019]; root of context hierarchy
2019-09-03 19:37:58.668  INFO 52212 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

The output shows the contents of the database.

6. Flyway

All of a sudden, the business need has change. Since we are practicing Agile wherein we welcome changing requirement, our application must evolve. Our change requirement is to add a registration number to the car. This is where Flyway comes in. First, let’s add the Flyway dependency to the pom.xml like so:

Flyway artifact

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

Create the below SQL in your src/main/resources/db/migration directory.

V2__add_registration_number_column.sql

1	`ALTER` `TABLE` `public.cars` `ADD` `registration_number` `VARCHAR(255);`

The naming convention for the migration script is <prefix><version>__<description>.sql. The default prefix is V but may be configured using the flyway.sqlMigrationPrefix property. version is the version number. It should always start with 1 but in our example it is 2. Why is that?. Major and minor versions may be separated by an underscore. description is separated from the version number with a double underscore. As the word says, it’s a description of the migration. We are not starting with version 1 because our database already exists.

Add the line below to application.properties.

application.properties

1	`flyway.baselineOnMigrate=true`

We have this property because we already have an existing database. This will automatically call baseline when migrate is executed against a non-empty schema with no metadata table. This will initialize the Flyway metadata table.

Let’s run the application again.

Console Output

2019-09-03 21:48:48.996  INFO 39044 --- [           main] o.f.core.internal.util.VersionPrinter    : Flyway 3.2.1 by Boxfuse
2019-09-03 21:48:49.373  INFO 39044 --- [           main] o.f.c.i.dbsupport.DbSupportFactory       : Database: jdbc:postgresql://localhost:5432/example (PostgreSQL 10.4)
2019-09-03 21:48:49.417  INFO 39044 --- [           main] o.f.core.internal.command.DbValidate     : Validated 1 migration (execution time 00:00.014s)
2019-09-03 21:48:49.449  INFO 39044 --- [           main] o.f.c.i.metadatatable.MetaDataTableImpl  : Creating Metadata table: "public"."schema_version"
2019-09-03 21:48:49.737  INFO 39044 --- [           main] o.f.core.internal.command.DbBaseline     : Schema baselined with version: 1
2019-09-03 21:48:49.765  INFO 39044 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": 1
2019-09-03 21:48:49.766  INFO 39044 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 2 - add registration number column
2019-09-03 21:48:49.805  INFO 39044 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "public" (execution time 00:00.063s).
2019-09-03 21:48:49.893  INFO 39044 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
Reading cars records...
Model                           Price                         
mondeo                          £20,000.00                    
mustang                         £40,000.00                    
2019-09-03 21:48:49.918  INFO 39044 --- [           main] c.j.example.SummerShoeLandMailSql        : Started SummerShoeLandMailSql in 2.073 seconds (JVM running for 2.454)

As the console output shows, Flyway did it’s job. A schema_version is added by Flyway as shown below.

Spring Boot with Flyway - Flyway Schema Version — Flyway Schema Version

As the console output shows, the version 2 migration was applied. Our ALTER TABLE also took effect. The registration_column now exists as shown below.

And the software development cycle continues…

7. Spring Boot with Flyway and PostgreSQL Summary

In summary, we created our database first. Next, we included the spring-boot-starter-jdbc dependency to make available all the Spring modules we needed to make JDBC operations. We then added the database dependency, in this case PostgreSQL. After that, we used JdbcTemplate to perform our SQL commands. Then we set the Flyway baseline which created a table with migration metadata. We then created an SQL file which is used by Flyway to modify our database. We can now picture out how our software evolves.

8. Download the Source Code

This is an example about Spring Boot with Flyway and PostgreSQL.

Download
You can download the source code of this example here: Spring Boot with Flyway and PostgreSQL

Spring Boot with Flyway and PostgreSQL Example

1. Assumptions

2. Tools

3. Project Setup

4. Initial Java Code

5. Initial Code Output

Thank you!

6. Flyway

7. Spring Boot with Flyway and PostgreSQL Summary

8. Download the Source Code

Thank you!

Joel Patrick Llosa

Thank you!

1. Assumptions

2. Tools

3. Project Setup

4. Initial Java Code

5. Initial Code Output

Thank you!

6. Flyway

7. Spring Boot with Flyway and PostgreSQL Summary

8. Download the Source Code

Thank you!

Related Articles

Thank you!