Flyway

Introduction about flyway, how to use in projects

Flyway is used to do migrations in database, It reads the SQL files from spring boot app and runs the queries in the database.

Steps -

1. For migration, store SQL files in db.migration folder.

2. By default, flyway creates a table in the databse to track the files that have been executed.

3. We can change the database to connect for flyway, by default, it creates in main database.

4. Every SQL file is transactional in flyway, if problems occur in any place in SQL file, it will roll back the previous content of the same file.

5. Naming of SQL file for flyway = V<squence_number>__.sql

6. We can configure these naming rules in the properties file.

7. In flyway, it is possible to do migration from Java code as well, for that, we need to extend the class with BaseJavaMigration class and write our queries in the migrate method.

8. Use repeatable migrations (R__ prefix) for tasks that may need to be re-applied, such as refreshing views or procedures.

9. Add jar file -

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

Package name for Log level -

org.flywaydb.core: debug

Properties file

spring:
  flyway:
    # these properties are not required and picked by config by default , the db connection will be same as main connection and if want to do in other db then can config
    enabled: true
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/flyway-test
    user: postgres
    password: root

    locations: "classpath:db/migration"  # if want to place sql files in another location , default is db/migration
    table: "flyway_schema_history" # default table name for flyway table , can change to other tables
    sqlMigrationPrefix: "V"  # prefix for sql files
    sqlMigrationSeparator: "__"  # separator for prefix and name
    sqlMigrationSuffixes: ".sql" # suffix to check for files in migration folder
    #initSqls:  #initial sqls to run at start time before migration
    #group: true # to group the files that are being run first time by flyway

    out-of-order: true # allows flyway to migrate the sqls which were not in sequence and are being executed later

1. What is the use of out-of-order true?

When we are working with multiple people, then there is an issue how we can maintain order or versions of the files. In this case, we can use out-of-order true as it allows executing SQL scripts in any order. By default, a flyway executes SQL scripts in the number of in the sequence of version numbering. By making out of order true, we can execute the SQL files, which are not in sequence.

2. Any suggestions how we should do versioning ?

For every major SQL changes we should create a new version number, and we should create a version number in the form of decimal, like 1.1 or 1.2.

By default, we should start a version number with 1.1. And if the changes are in a similar way, we can increase the decimal count, like 1.2 or 1.3.

Using this way, we can make sure like the changes are grouped and all the major SQL changes have a different number.

3. Can we change the content of the script once it's executed?

No, we cannot change the content of a script once it is executed in the database, as every SQL script is counted as a transaction. So if you want to do the modification in the script or add or remove any changes, we should create a script with the sub number.

If you still would like to modify the existing script, then there is a hack that could only work on your system. First, we need to open the database, find the SQL migration table. Then search for the last entry of the script that you need to modify. Delete the entry and run the application.

Note When you will run the application the script will execute again. But before running it make sure you have rolled back the changes.

or use repeatable migrations.

4. What are Repeatable Migrations?

• Use repeatable migrations (R__ prefix) for tasks that may have to be re-applied, such as refreshing views or procedures:

  R__refresh_views.sql

• These migrations are executed every time their checksum changes.

5. How to use Flyway with Multiple Profiles?

• Use Spring profiles to apply Flyway configurations conditionally:

  # application-dev.properties
  spring.flyway.locations=classpath:db/migration/dev
  
  # application-prod.properties
  spring.flyway.locations=classpath:db/migration/prod

6. How to do validation and downgrades?

• Validate the schema against migration files to detect discrepancies:

  spring.flyway.validate-on-migrate=true

• Rollbacks are not supported directly, but you can write a downgrade migration script to revert changes.

7. How to integrate flyway in existing databases?

• Use the baseline feature to initialize Flyway in an already existing database:

  spring.flyway.baseline-on-migrate=true
  spring.flyway.baseline-version=1

• This creates a baseline version to prevent Flyway from trying to apply old migrations to an existing schema.

8. How to manage multiple databases?

• If your application interacts with multiple databases, configure Flyway for each:

  @Bean(name = "flywayDb1")
  public Flyway flywayDb1() {
      return Flyway.configure()
                   .dataSource(db1DataSource())
                   .locations("classpath:db/migration/db1")
                   .load();
  }
  
  @Bean(name = "flywayDb2")
  public Flyway flywayDb2() {
      return Flyway.configure()
                   .dataSource(db2DataSource())
                   .locations("classpath:db/migration/db2")
                   .load();
  }

9. Example of run fly scripts by using Java configuration

@Configuration
public class UsingConfig {

    @Autowired
    private DataSource dataSource;

    // using java code , useful when we have to migration from two locations or in two diff data sources, so one can migrate from properties file config and another from here
//    @PostConstruct
    public void migrateQueries(){
        Flyway.configure().dataSource(dataSource).locations("classpath:db/migration").load().migrate();
    }
}