CI/CD Databases with Flyway

A solution for continuous database integration

6 min readAug 4, 2022

When working for large organizations, we developers do not usually deploy our own applications. Therefore, we may need to leverage a migration tool to continuously integrate and deploy database schemas along with the rest of the new code.

In this article, we will explore how migration tools like Flyway can help us promote software releases in different environments easily and reliably.

Migration tools vs Hibernate ddl-auto

If you have some professional experience, the difference between a migration tool and Hibernate might seem rather obvious. But many students keep asking me “why not use ddl-auto instead?”

The spring.jpa.hibernate.ddl-auto property can be used to perform DDL operations based on the current object model mapping (typically JPA Entities) when the application starts.

Hibernate provides four property values to this end: none, validate, create-drop and update. Spring internally chooses a default value for this property: create-drop if we are using an embedded database, or none otherwise.

We are not digging deeper into these four properties today. What we are interested in knowing is that we can use the property value update to compare the current object model with the existing schema, and Hibernate will update the schema accordingly.

Although this may sound good enough, there are certain drawbacks:

Hibernate does not store the change log, making it difficult to know what was changed between deployments or to perform rollbacks.
In Hibernate, it becomes challenging to use vendor-specific database features without sacrificing application portability.
Hibernate does not delete existing tables or columns, even if they are no longer needed by the application.

How does Flyway work?

Migration Metadata

Flyway creates a special table called the “Flyway Schema History” to store metadata, such as script name, migration version, execution status, execution date-time, etc.

Then, to keep track of the schema evolution, Flayway logs the metadata into the flyway_schema_history table, leaving this table as the only source of truth, and allowing it to check if a new migration script has been added.

In addition, it validates the integrity of older scripts using a checksum stored in the table. So, if any of the scripts in the table has been modified, the application will fail to start.

Script Naming Convention

In order to continuously add new migration scripts, Flyway enforces a naming convention:

<Prefix><Version>__<Name>.sql

Where:

<Prefix> stands for the migration prefix. By default, it is V.
<Version> stands for the migration version number. It can be divided into major and minor versions based on the Semantic Versioning recommendation: MAJOR.MINOR.
<Name> stands for the basic description of the migration. Note that a double underscore separates the name from the version number.

Example:

V1.0__add_tables.sql
V1.1__add_data.sql

Flyway on Spring

Flyway Core Dependency

To enable Flyway on Spring, all we need to do is add the flyway-core dependency. If you are working on a Maven project, you will need the following dependency in your pom.xml file.

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

With this dependency, Flyway automatically scans the classpath looking for the migration scripts, and executes them against the database during application startup. By default, it will scan for files located in the path classpath:db/migration, but you can modify that location by setting the path in the spring.flyway.locations property.

Spring Configuration

First, add the connection to the database in yourapplication.yml file. In this example, we are using a H2 database, but Flyway is compatible with all popular databases: MySQL, Oracle, Postgres and so forth.

spring:  
  datasource:
    driver-class-name: org.h2.Driver
    username: sa
    password:
    url: "jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE"
  h2:
    console:
      enabled: true
      path: /h2-console
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none

Additionally, we are setting the property spring.jpa.hibernate.ddl-auto as none.

Database Migration

Let’s add the first migration script V1.0__add_tables.sql into classpath:db/migration.

drop table if exists trainee;
drop table if exists skill;
drop table if exists trainee_skill;

create table trainee
(
    trainee_id bigint       not null auto_increment primary key,
    start_date datetime(6)  not null,
    end_date   datetime(6)  null,
    name       varchar(36) not null
);

create table skill
(
    skill_id    bigint       not null primary key,
    description varchar(255) null,
    name        varchar(36) null
);

create table trainee_skill
(
    trainee_id bigint not null,
    skill_id   bigint not null,
    primary key (trainee_id, skill_id),
    constraint trainee_skill_trainee_id_fk
        foreign key (trainee_id) references trainee (trainee_id),
    constraint trainee_skill_skill_id_fk
        foreign key (skill_id) references skill (skill_id)
);

Here, we define the DDL operations and create three tables: trainee, skill and trainee_skill.

Now that we have the DDL for the tables, we define the object model mapping.

In the code below, you’ll see two JPA Entities: Trainee and Skill.

import java.time.LocalDateTime
import javax.persistence.*

@Entity
@Table(name = "trainee")
open class Trainee {

    @get:Id
    @get:GeneratedValue
    @get:Column(name = "trainee_id")
    open var id: Long? = null

    @get:Column(nullable = false)
    open var name: String? = null

    @get:Column(nullable = false)
    open var startDate: LocalDateTime? = null

    open var endDate: LocalDateTime? = null

    @get:ManyToMany
    @get:JoinTable(
        name = "trainee_skill",
        joinColumns = [JoinColumn(name = "trainee_id")],
        inverseJoinColumns = [JoinColumn(name = "skill_id")]
    )
    open var skills: Set<Skill> = HashSet()
}

@Entity
@Table(name = "skill")
open class Skill {

    @get:Id
    @get:Column(name = "skill_id")
    open var id: Long? = null

    @get:Column(nullable = false)
    open var name: String? = null

    open var description: String? = null
}

Once the application has raised, we can access to the H2 Console (http://localhost:<port>/h2-console) with the credentials bellow:

JDBC URL: jdbc:h2:mem:db
User Name: sa
Password: <empty>

By querying the flyway_schema_history_table, we can see that the script was indeed executed, and the schema was created.

Database Seeding

When deploying a new application, it is common to load seed data, such as user accounts, into the initial setup of the application.

Let’s add the seed script V1.1__add_data.sql into classpath:db/migration.

insert into skill(skill_id, description, name)
values (1, 'Domain-specific language for managing data in relational database management systems.', 'SQL');
insert into skill(skill_id, description, name)
values (2, 'Java based programing language for developing web applications.', 'Kotlin');
insert into skill(skill_id, description, name)
values (3, 'Spring framework for building web applications on top of the Java EE platform.', 'Spring');
insert into skill(skill_id, description, name)
values (4, 'ORM tool for mapping an object-oriented domain model to a relational database.', 'Hibernate');

insert into trainee(trainee_id, start_date, name)
values (1, current_timestamp(), 'Joe Friend');
insert into trainee(trainee_id, start_date, name)
values (2, current_timestamp(), 'Juan Amigo');
insert into trainee(trainee_id, start_date, name)
values (3, current_timestamp(), 'Jane Fresh');
insert into trainee(trainee_id, start_date, name)
values (4, current_timestamp(), 'Joana Fresco');

insert into trainee_skill values (1, 1);
insert into trainee_skill values (1, 2);
insert into trainee_skill values (2, 2);
insert into trainee_skill values (2, 3);
insert into trainee_skill values (3, 3);
insert into trainee_skill values (3, 4);
insert into trainee_skill values (4, 1);
insert into trainee_skill values (4, 2);
insert into trainee_skill values (4, 3);
insert into trainee_skill values (4, 4);