Migrating a Data Model

Usually, an application model evolves with new versions. Therefore, the database schema has to follow the model definition. This implies data structures and data need to be migrated. For this use case, we rely on the standard tool Flyway.

Flyway keeps trace of the current version of your schema. This allows Flyway to incrementally execute all the migration scripts from your current database schema version to the target schema version.

In this example, your database is already upgraded with a schema in version 1, if you run Flyway, it will migrate your database schema to version 2.

To achieve migrations, Flyway uses so-called migration scripts. Migration scripts are "sequentially ordered SQL scripts" that incrementally migrate your schema.

In order to recognize migration scripts, Flyway relies on the following naming convention:

An example of migration script folder could be:

gene-model/gene-model-jpa ~> tree -L 1 flyway/db/migration
flyway/db/migration
- v1__Add_item_table.sql # First migration script that adds an 'Activity' table in the SQL schema.
- v2__Add_item_category_table.sql # Migration script that creates an activity table.

By this naming convention Flyway is able to determine that it has to run v2__Add_item_category_table.sql after v1__Add_item_table.sql and only if it has not already been run.

Let's take an example,

Imagine you already have a model with an Item table, and you want to introduce a Category table like in the following diagram.

See below the migration script that will allow us to upgrade V1 schema and data to V2.

It adds a category table and updates the item table accordingly. It also creates a default category and updates the existing Items with it.

-- v2__Add_category_table.sql
-- This migration script introduces item categories.
--

-- #################
-- # Category      #
-- #################

-- Introduce category table
create table category
(
    category_id text not null
        constraint category_pkey
            primary key,
    name text not null
);

alter table category owner to data_server;

create unique index uk_p8a7ixxjnxsm18wq9f1eobrmn
    on category (category_id);

create unique index idx_tdsi66b2e619y4cuympjbrfw7
    on category (name);

-- Create a default category
insert into category (category_id, name) values ('default_category', 'Default');

-- #################
-- # Item          #
-- #################

-- Update item table
alter table item add column category_id text
    constraint fk_1tgju5f47333ash49oqmv8vm2
        references category deferrable;

-- Very important  we migrate the data to (not only the structure). -
-- ------------------------------------------------------------------
-- Update the items accordingly
update item set category_id = 'default_category' where 1 = 1;