Spring boot with Flyway and PostgreSQL

Postgre is one of the most popular database tools used by Spring Boot developers. In this tutorial, we are going to learn more about Postgres and how to integrate it with a Spring Boot application by Flyway.

Let’s dig in!

Overview

• PostgreSQL is one of the most famous RDBMS around and can runs on all major operating systems, so that it has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.
• Flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration. It is based around just 7 basic commands: Migrate, Clean, Info, Validate, Undo, Baseline and Repair.
  It has a Command-line client, so that if you are on the JVM, we recommend using the Java API for migrating the database on application startup.

Get Started!!!

Install PostgreSQL:

To install and test PostgreSQL, I recommend using Docker:
Try:

docker pull postgres:11
docker run --name dev-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:11

Or you can run the configuration available in the file postgresql.yml:

---Run cmd: docker-compose -f postgressql.yml up -d ---

---- postgresql.yml file ----
version: '1'
services:
  postgresql:
    image: postgres:11.5
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
    ports:
      - 5432:5432

Create a Spring Boot App With PostgreSQL:

First, you need to create a new project with the required dependencies.
You can use Spring Initialzr for this.
In this article, I will use gradle to build a demo project.

After initializing a spring project, you can see the configuration in the gradle.build file:

dependencies {
    ...
	compile("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
	compile("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	compile("org.springframework.boot:spring-boot-starter-security:${springBootVersion}")
	compile("org.flywaydb:flyway-core:5.2.0")
	runtime("org.postgresql:postgresql:42.2.2")
    ...
}

Add Database Configuration for PostgreSQL

First, change your main class and add the @EnableTransactionManagement annotation (probably com.okta.developer.postgresql.PostgresqlApplication) to connect system with database dependencies and don't forget to create a bean to configure JpaTransactionManager, like this:

--- Application.java file ---

[package]

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;

@SpringBootApplication
@EnableTransactionManagement
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
    
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }
}

Then you create a new database:

docker exec dev-postgres psql -U postgres -c"CREATE DATABASE mydb"

And configure it to connect from your application to it in src/main/resources/application.properties file:

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=username
spring.datasource.password=password
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create

Create JPA Entities and JPA repository

• Create Account.java file:

[package]

[import]

@Entity
@Table(name = "accounts") // accounts is table name in DB 
public class Account {
    @Id
    @Column(name = "id")
    @GeneratedValue
    private UUID accountId;
    @Column(name = "account_name", nullable = false)
    private String accountName;
    @Column(name = "created_date", nullable = false)
    private Date createdDate;
    @Column(name = "last_updated", nullable = false)
    private Date lastUpdated;
}

• Create AccountRepository file:

[package]

import [...url to Account file];
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;
import java.util.UUID;

@Repository
public interface AccountRepository extends JpaRepository<Account, UUID> {

    Optional<Account> findByAccountId(UUID accountId);

    Account findByAccountName(String accountName);
}

You can see more JPA config to connect database with spring here

Versioning Schema Changes in PostgreSQL With Flyway

Flyway looks for SQL scripts on your project’s resource path and runs all scripts not previously executed in a defined order. Flyway stores what files were executed into a particular table called SCHEMA_VERSION.

By default, Flyway looks at files in the format V$X__$DESCRIPTION.sql, where $X is the migration version name, in folder src/main/resources/db/migration like this:

--- create new a file: V1__init.sql ---

### DDL
create table accounts (
	id uuid not null default uuid_generate_v4() primary key,
	account_name varchar (255) not null,
	created_date timestamp not null default now(),
    last_updated timestamp not null default now()
);

### Insert data
insert into accounts (account_name, created_date, last_updated) values ('admin', now(), now());
insert into accounts (account_name, created_date, last_updated) values ('user', now(), now());

Or you can use java function for run migrations in foldersrc/main/java/db/migration  like this:

--- migration.java file ---
package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.UUID;

public class V2__migrate_account_name_for_accounts_table extends BaseJavaMigration {
    String queryListAccount = "SELECT * FROM accounts";
    String queryUpdateAccount = "UPDATE accounts SET account_name= '%s'";

    public void migrate(Context context) throws Exception {

        try (Statement selectListAccount = context.getConnection().createStatement()) {
            try (ResultSet accounts = selectListAccount.executeQuery(queryListAccount)) {
                while (accounts.next()) {
                    String defaultName = "anonymous"
                    try (Statement updateAllAccountNames = context.getConnection().createStatement()) {
                        updateAllAccountNames.execute(String.format(defaultName));
                    }
                }
            }
        }
    }
}

Run Project:

You can now start your app:

./gradle

Conclusion

With the above example, we have seen the flexibility in the way the flight path is handled. Hope this blog post helps you get there.
Thanks for watching ^^.