Flyway - SQL migration-scripts versions on multiple git branches

Question

In the project we use Flyway migration tool to develop db schema along with git history of those scripts.

The idea is that each script number is prefixed with number increasing by the value of 10 and after that the name, so for example:

• 0000_name_one.sql
• 0010_name_two.sql
• 0020_name_three.sql

etc.

However since we had to start working on multiple branches (two main branches are for example: master + master_ext) plus we have some dev-branches for time-consuming features - there is a problem with choosing right script's numbers.

When having long-term dev-branch introducing few changescripts, when merging it to either master or master_ext, where someone had also introduced same numbers of different scripts we would have conflicts. (merge will probably succeed due to differences in names but flyway migration will fail)

Is there any pattern or good practice how to maintain SQL scripts versions when working on multiple branches?

Thanks in advance

Answer 1

You should not that the version of a migration is the part up to th "__" (double underscore) where the description part is starting.

The naming scheme for migrations (according to documentation on migrations) is:

<flag char><version><separator><description><suffix>

(actually much of this is configurable, but just assume it has not been changed)

• flag char is one of V (for versioned migrations), R (for repeatable migrations, U (for undo migrations)
• version is anything not using the separator string. Usually some numeric scheme is applied.
• separator_ is "__" (double underscore)
• suffix is any defined suffix for your migrations (e.g. .sql)

As version may be anything you could easily introduce a component that is specific to a developer or branch and by such means will help ensuring unique names.

(BTW: according to the documentation, the names you showed in the question are not proper flyway migration names!)

HOWEVER:

You are still left with the problem of ensuring a valid ordering according to logical dependencies.

Luckily, in most cases ordering constraints only occur within a unit of development, usually within a single branch. So, ensuring that all related migrations are executed in order will give a proper result. To get a useful ordering across different units of development, you might just revert to time scale.

E.g. using something along: <yyymmdd>.<unitseq>.<subseq> where

• yyyymmdd is the date mark of a unit of development (e.g. start of development or planned date of deployment to production)
• unitseq is a sequence number identifying the unit of development. This needs to ensure proper ordering! So, using a fixed set of characters would help.
• subseq is the ordering sequence number within a unit of development.

For more complex cases that require proper ordering of development strains you might still need to watch out for proper ordering across units of development.

Back to your example from the question:

As you do not have a separator with your names, the following variants still would be assumed to be different by flyway:

• 0000_name_one.sql
• 0000_name_one_projectA.sql
• 0000_name_one_project_B.sql

Following my suggestion you might turn to using:

• V20180211.p000.0000__name_one.sql
• V20180302.p001.0000__name_one_projectA.sql
• V20180301.p002.0000__name_one_projectB.sql

Be aware, that the execution order in both versions is different!

Answer 2

Flyway does not provide any in build features to perform the same.

However I have done this for my project and i think so far this is the best we can do.

1. Make Flyway versions as actual timestamp so it does not matter which git branch you create it will be always unique and in order.
2. Write a program to generate this version and ask all your developers to use this file in order to get there next version which need to be used.

Below is a sample I use currently

package com.demo;


import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class GenerateFileVersion {

    public static void main (String... args){
        GenerateFileVersion f=new GenerateFileVersion();
        f.fileVersion();
    }

    private String trimOrPad(String str, int length, char padChar) {
        String result;
        if (str == null) {
            result = "";
        } else {
            result = str;
        }

        if (result.length() > length) {
            return result.substring(0, length);
        } else {
            while (result.length() < length) {
                result = padChar+result;
            }

            return result;
        }
    }

    private String fileVersion(){
        Date date = new Date();
        Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("CST6CDT"));
        calendar.setTime(date);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss");

        StringBuffer sb=new StringBuffer();
        sb.append(calendar.get(Calendar.YEAR)%100);
        sb.append(".");
        sb.append(this.trimOrPad(String.valueOf(calendar.get(Calendar.MONTH)+1),2,'0'));
        sb.append(".");
        sb.append(this.trimOrPad(String.valueOf(calendar.get(Calendar.DATE)),2,'0'));
        sb.append(".");
        sb.append(this.trimOrPad(String.valueOf(calendar.get(Calendar.HOUR_OF_DAY)),2,'0'));
        sb.append(this.trimOrPad(String.valueOf(calendar.get(Calendar.MINUTE)),2,'0'));
        sb.append(this.trimOrPad(String.valueOf(calendar.get(Calendar.SECOND)),2,'0'));

        System.out.println("Choose Your Next File Name From below list...");

        int i=0;

        for(ENVIRONMENT env: ENVIRONMENT.values()){
            System.out.println("Next File Name for Making DDL Change : "+"V"+sb.toString()+this.trimOrPad(String.valueOf(i++),2,'0')+"__"+env.toString()+"_DDL.sql");
            System.out.println("Next File Name for Making DML Change : "+"V"+sb.toString()+this.trimOrPad(String.valueOf(i++),2,'0')+"__"+env.toString()+"_DML.sql");
        }

        return sb.toString();
    }

    private enum ENVIRONMENT{COMMON(1),LOCAL(9),STAGE(4),MTF(5),PERF(7),PROD(2);
        private int value;
        private ENVIRONMENT(int value) { this.value = value; }
    }


}

You can also add more features to your project to make sure all developers do not do any mistake you can add below program as junit test case so that build fails if there are any files does not follow flyway versioning standard.

Sample is below

package com.demo;

import junit.framework.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.reflections.Reflections;
import org.reflections.scanners.ResourcesScanner;
import org.reflections.util.ClasspathHelper;
import org.reflections.util.ConfigurationBuilder;
import org.reflections.util.FilterBuilder;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootRestApplicationTests {

    @Test
    public void checkDuplicateVersion() {

        System.out.println("Starting checkDuplicateVersion");

        List<ClassLoader> classLoadersList = new LinkedList<>();
        classLoadersList.add(ClasspathHelper.contextClassLoader());
        classLoadersList.add(ClasspathHelper.staticClassLoader());

        Reflections reflections = new Reflections(new ConfigurationBuilder()
                .setScanners(new ResourcesScanner())
                .setUrls(ClasspathHelper.forManifest(ClasspathHelper.forClassLoader(classLoadersList.toArray(new ClassLoader[0]))))
                .filterInputsBy(new FilterBuilder().include(FilterBuilder.prefix("local"))));

        Reflections reflections1 = new Reflections(new ConfigurationBuilder()
                .setScanners(new ResourcesScanner())
                .setUrls(ClasspathHelper.forManifest(ClasspathHelper.forClassLoader(classLoadersList.toArray(new ClassLoader[0]))))
                .filterInputsBy(new FilterBuilder().include(FilterBuilder.prefix("common"))));

        Stream<String> stream1=  reflections.getStore().get("ResourcesScanner").keys().stream();
        Stream<String> stream2=  reflections1.getStore().get("ResourcesScanner").keys().stream();

        Stream<String> resultingStream=Stream.of(stream1,stream2).flatMap(i -> i);

        //resultingStream.forEach(System.out::println);

        List<String> existingFileVersions=
                resultingStream
                        .filter(f -> f.startsWith("V"))
                        .filter(f -> f.endsWith(".sql"))
                        //.forEach(System.out::println);
                        .map(n -> n.split("__")[0].substring(1))
                        //.forEach(System.out::println);
                        .collect(Collectors.toList());

        Set<String> duplicateVersion=existingFileVersions.stream().filter(i -> Collections.frequency(existingFileVersions, i) >1)
                .collect(Collectors.toSet());

        duplicateVersion.forEach( i -> System.out.println("Duplicate Version found "+i));

        Assert.assertEquals(0,duplicateVersion.size());
    }

    @Test
    public void checkFlywayFileNamingStandard(){

        System.out.println("Starting checkFlywayFileNamingStandard");

        List<ClassLoader> classLoadersList = new LinkedList<>();
        classLoadersList.add(ClasspathHelper.contextClassLoader());
        classLoadersList.add(ClasspathHelper.staticClassLoader());

        Reflections reflections = new Reflections(new ConfigurationBuilder()
                .setScanners(new ResourcesScanner())
                .setUrls(ClasspathHelper.forManifest(ClasspathHelper.forClassLoader(classLoadersList.toArray(new ClassLoader[0]))))
                .filterInputsBy(new FilterBuilder().include(FilterBuilder.prefix("local"))));

        Reflections reflections1 = new Reflections(new ConfigurationBuilder()
                .setScanners(new ResourcesScanner())
                .setUrls(ClasspathHelper.forManifest(ClasspathHelper.forClassLoader(classLoadersList.toArray(new ClassLoader[0]))))
                .filterInputsBy(new FilterBuilder().include(FilterBuilder.prefix("common"))));

        Stream<String> stream1=  reflections.getStore().get("ResourcesScanner").keys().stream();
        Stream<String> stream2=  reflections1.getStore().get("ResourcesScanner").keys().stream();

        Stream<String> resultingStream=Stream.of(stream1,stream2).flatMap(i -> i);
        //resultingStream.forEach(System.out::println);

        resultingStream
                .filter(f -> f.endsWith(".sql"))
                .forEach(n -> {

                    if(!n.split("__")[0].toUpperCase().startsWith("V")){
                        System.out.println("File starts with " + n + " Does not start with Letter V or v. Please fix it.");
                        Assert.fail();
                    }

                    for(String s : n.split("__")[0].substring(1).split("\\.")){
                        try {
                            //System.out.println(n);
                            Integer.valueOf(s);
                        }catch(Exception e){
                            //e.printStackTrace();
                            System.out.println("File starting with "+ n + " does not match flyway standard");
                            System.out.println("Flyway standard is V{version}__{description}.sql");
                            Assert.fail();
                        }
                    }
                });
    }

}

For more details you can see my project here