How to add Flyway to an existing application

After my talk about combining Flyway, Hibernate, and jOOQ at the JavaLand conference, one of the participants asked me how to add Flyway to an existing application that’s already deployed in production. This is a common question because database migrations get often ignored for new projects. And that’s understandable. You don’t need it for the first installation, and there are many more urgent tasks you need to work on.

But ignoring your database migration in the beginning creates new problems later on. For your first update, you not only need to add Flyway to your project, but you also need to define an update process that works with your existing database and can set up a new one from scratch. Luckily, Flyway provides a simple solution for this:

  1. You start by generating a DDL script of your existing database. This will be the first migration script for all new installations.
  2. After that, you need to baseline your existing database so that Flyway knows that it doesn’t have to execute the 1st migration script.

After that, there is no difference between your project and one that used Flyway from the beginning. You can define the next migration steps as SQL scripts or Java classes, and Flyway will execute them automatically to migrate an existing database or create a new one.

Generate a DDL script of your database

The goal of this script is to recreate the entire structure of your current database before Flyway executes all other migration scripts. This includes all database schemas, tables, sequences, constraints, functions, stored procedures, etc. If your application requires a predefined set of reference data, you should include them in this script as well.

Here you can see a simple example of the script that creates the database used in this month’s coding challenge in the Persistence Hub.

CREATE TABLE public.chess_game (
    id bigint NOT NULL,
    date date,
    round integer NOT NULL,
    version integer NOT NULL,
    chess_tournament_id bigint,
    player_black_id bigint,
    player_white_id bigint
);
ALTER TABLE public.chess_game OWNER TO postgres;

CREATE TABLE public.chess_player (
    id bigint NOT NULL,
    birth_date date,
    first_name character varying(255),
    last_name character varying(255),
    version integer NOT NULL
);
ALTER TABLE public.chess_player OWNER TO postgres;

CREATE TABLE public.chess_tournament (
    id bigint NOT NULL,
    end_date date,
    name character varying(255),
    start_date date,
    version integer NOT NULL
);
ALTER TABLE public.chess_tournament OWNER TO postgres;

CREATE TABLE public.chess_tournament_games (
    chess_tournament_id bigint NOT NULL,
    games_id bigint NOT NULL
);
ALTER TABLE public.chess_tournament_games OWNER TO postgres;

CREATE TABLE public.chess_tournament_players (
    tournaments_id bigint NOT NULL,
    players_id bigint NOT NULL
);
ALTER TABLE public.chess_tournament_players OWNER TO postgres;

CREATE SEQUENCE public.hibernate_sequence
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.hibernate_sequence OWNER TO postgres;

CREATE SEQUENCE public.player_sequence
    START WITH 100
    INCREMENT BY 50
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.player_sequence OWNER TO postgres;

CREATE SEQUENCE public.tournament_sequence
    START WITH 100
    INCREMENT BY 50
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.tournament_sequence OWNER TO postgres;

ALTER TABLE ONLY public.chess_game
    ADD CONSTRAINT chess_game_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.chess_player
    ADD CONSTRAINT chess_player_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.chess_tournament_games
    ADD CONSTRAINT chess_tournament_games_pkey PRIMARY KEY (chess_tournament_id, games_id);

ALTER TABLE ONLY public.chess_tournament
    ADD CONSTRAINT chess_tournament_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.chess_tournament_players
    ADD CONSTRAINT chess_tournament_players_pkey PRIMARY KEY (tournaments_id, players_id);

ALTER TABLE ONLY public.chess_tournament_games
    ADD CONSTRAINT uk_aydrmqjva2yao3biowshrrcx8 UNIQUE (games_id);

ALTER TABLE ONLY public.chess_game
    ADD CONSTRAINT fk1kenmun90s7uhly5kkk9o6rsf FOREIGN KEY (player_black_id) REFERENCES public.chess_player(id);

ALTER TABLE ONLY public.chess_tournament_players
    ADD CONSTRAINT fk5ykqwib1neqhak6wwuhsusf5w FOREIGN KEY (tournaments_id) REFERENCES public.chess_tournament(id);

ALTER TABLE ONLY public.chess_tournament_players
    ADD CONSTRAINT fkfmhm06fi40ak53r6gofvoyr44 FOREIGN KEY (players_id) REFERENCES public.chess_player(id);

ALTER TABLE ONLY public.chess_tournament_games
    ADD CONSTRAINT fkhoasvgr0mq1tkj5308chmd97v FOREIGN KEY (games_id) REFERENCES public.chess_game(id);

ALTER TABLE ONLY public.chess_game
    ADD CONSTRAINT fkikaihvc8m29y7fqtk5brfwk48 FOREIGN KEY (player_white_id) REFERENCES public.chess_player(id);

ALTER TABLE ONLY public.chess_game
    ADD CONSTRAINT fkquj6n755j3k650vwhoabw44yu FOREIGN KEY (chess_tournament_id) REFERENCES public.chess_tournament(id);

ALTER TABLE ONLY public.chess_tournament_games
    ADD CONSTRAINT fkuqqdoorh4jhfx6mqe3wsy5ni FOREIGN KEY (chess_tournament_id) REFERENCES public.chess_tournament(id);

The best and easiest way to create this script is to use the backup tools provided by your database. It usually only takes a few clicks or a short command to export the current table structure. Or, if you’re using Hibernate or any other JPA implementation, you can use its schema export feature.

In the next step, you need to rename the script to follow Flyway’s naming convention V<VERSION>__<DESCRIPTION>.sql, e.g., V1__initial_version.sql, and copy it to Flyway’s migration folder. The command-line client uses the folder ./sql by default. And Spring Boot’s Flyway integration expects these files in your project’s main/resources/db/migration folder. You can override the default by setting the flyway.locations property in your configuration.

Baseline your database

As I explained in my introduction to Flyway, Flyway writes a record to the flyway_schema_history table for each performed migration step. Flyway uses this information to determine the current database version and compares it with the available migration scripts to find the ones it needs to execute.

This table doesn’t exist when you add Flyway to an existing application and database. Without that table, Flyway expects that the database is empty and executes all available migration scripts. This will obviously fail because you already created your database without using Flyway.

You, therefore, need to tell Flyway that the database is already in version 1. You can do that by executing the baseline command in the command-line client. Flyway then creates the flyway_schema_history table and adds the first record.

The baseline command requires the connection information to your database and the version and description of the current database schema.

flyway -url=jdbc:postgresql://localhost:5432/codingChallenge-220404 
       -user=postgres 
       -password=postgres 
       -baselineVersion=1 
       -baselineDescription=initial_version 
       baseline

Please make sure that the provided baselineVersion and baselineDescription match the file name of your 1st migration script. In my example, the name of that file is V1__initial_version.sql. I, therefore, set baselineVersion to 1 and baselineDescription to initial_version.

When you execute the baseline command, Flyway creates its flyway_schema_version table and documents the execution of the baseline command.

The flyway_schema_version table with 1 record documenting the execution of the baseline command.

Based on this record, Flyway knows that the database is in version 1 and will not execute the migration script V1__initial_version.sql.

Summary

As you saw in this article, you can introduce Flyway to existing projects in 2 steps:

  1. You need to create an SQL script that recreates your current database. The easiest way to do that is to create a backup using the tools provided by your database.
  2. After that, you need to baseline your existing databases so that Flyway skips the execution of the first migration script.

After performing these 2 steps, you’ve successfully added the Flyway database migration to your existing application. Flyway will treat the existing database that contains the baseline version in the same ways as a new database created using the 1st migration script.