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.
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
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <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
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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;@SpringBootApplicationpublic 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
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 | . ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: 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: default2019-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 hierarchy2019-09-03 19:37:57.414 INFO 52212 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startupReading 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 hierarchy2019-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
1 2 3 4 | <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
01 02 03 04 05 06 07 08 09 10 11 12 13 14 | 2019-09-03 21:48:48.996 INFO 39044 --- [ main] o.f.core.internal.util.VersionPrinter : Flyway 3.2.1 by Boxfuse2019-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: 12019-09-03 21:48:49.765 INFO 39044 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema "public": 12019-09-03 21:48:49.766 INFO 39044 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema "public" to version 2 - add registration number column2019-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 startupReading 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.
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.
You can download the source code of this example here: Spring Boot with Flyway and PostgreSQL
