In the migrations-based approach, the source of truth for development and deployment is the collection of migration scripts.

Making database changes in development

Flyway relies on maintaining a valid Schema History Table in the target database to ensure that migration scripts can be successfully applied. If a migration script is created independently of how the changes are made, there will be no corresponding entry in the Schema History Table, and therefore subsequent attempts to run flyway migrate will fail as Flyway assumes that the migration script is "pending". Therefore, it is important to ensure that development patterns are used to ensure that the Schema History Table is kept in synch.

Let us consider a simple scenario. Assume that the development database is built from three migrations, V1__change1.sql, V2__change2.sql and V3__change3.sql and a new change, adding Table Foo is required. 

We will consider two scenarios - where we have created the migration script first (A), and where we have changed the database first (B).

Scenario A: Creating the migration script first

1. The developer creates a new file, V4__change4.sql with the CREATE TABLE FOO (...) entry

2. The developer runs flyway migrate to add the new table, which updates the Schema History Table as desired

This is the happy path and all is well. But what if the SQL doesn't run correctly? Or what if further changes are required (maybe a column data type needs changing?)

If a change made by a migration script is incorrect or incomplete, the following remedial patterns exist:

Add a brand new migration script 

• Create V5__change5.sql which contains the necessary ALTER TABLE command to change the data type, resulting in the desired end point, albeit via two migration scripts.

This method is the right one to employ if the V4 migration script has been shared with others or has been deployed in downstream environments, in particular production. If, however, the migration script only exists in the scope of the developer's own environment, creating a brand new migration script could be considered overkill, as it adds an additional migration script which transitions the database through unnecessary states that have no reason to exist. In this case, the desired outcome is to modify the "incorrect" migration script as follows:

Modifying an already run migration script

1. The developer modifies V4__change4.sql to correct the previous code.

At this point flyway migrate (or indeed flyway validate) will fail because Flyway is confused that a migration script that has been applied has since changed, and is flagging the potential (true in this example) for the database to differ from the migration scripts. There are two ways to remedy this:

Option 1 - Manually modify the database state to be consistent with the newly edited migration script

1. The developer alters the table to correspond to the change made in the modified migration script
2. flyway repair is run to let Flyway know that the migration script and the database are now consistent again

or

Option 2 - Revert the database back to the pre-migration state and rerun the migration 

This approach reverts the database back to its state prior to the migration script having been run.

1. To revert to the previous state the developer can -
   1. Delete the new table from the database and delete the corresponding entry in the Schema History Table, or
   2. Restore the development database using a backup, or
   3. Run flyway clean migrate -target=3 to rebuild the database to version 3
2. Flyway is now in the state prior to the script having been run and therefore the developer can run flyway migrate to add the corrected table, which in turn logs the V4 migration in the Schema History Table.

Scenario B: changing the database first

It is commonplace for developers to make changes and iterate on changes to their development database before a migration script has been created.

1. The developer creates table FOO in the development database and alters the table until completely happy with the change

2. Now the migration script can be created either by - 
   1. creating a new file, V4__change4.sql with the CREATE TABLE FOO (...) entry, or
   2. using Flyway Desktop to save the schema model changes to the project, and using the Generate Scripts feature to automatically generate the SQL FLYWAY ENTERPRISE

Because the changes were made without using flyway migrate, the Schema History Table will not have registered that the changes represented by the migration script have been applied. This means that a future flyway migrate will attempt to run the migration script, which will fail as the table already exists in the database. To ensure that the Schema History Table is made consistent, we have the following options:

Option 1 - Use skipExecutingMigrations FLYWAY TEAMS

1. Use flyway migrate -skipExecutingMigrations to instruct Flyway that the changes contained in a migration script have already been applied. See skipExecutingMigrations. 

Option 2 - Revert the database back to the pre-migration state and rerun the migration 

This approach reverts the database back to its state prior to the migration script having been run.

1. To revert to the previous state the developer can:
   1. Delete the new table from the database and delete the corresponding entry in the Schema History Table, or
   2. Restore the dev DB using a backup, or
   3. Run flyway clean migrate -target=3 to rebuild the database to version 3
2. Flyway is now in the state prior to the script having been run and therefore the developer can run flyway migrate to add the corrected table, which in turn logs the migration in the Schema History Table.

Sharing database changes with other developers

In a team context, it is necessary for developers who use their own dedicated database sandboxes to have a mechanism to share their changes with other developers.

Since we are discussing a migration-first model, this means we are choosing to share changes using the migration scripts themselves. The pattern is as follows:

1. Developer A authors a migration script saving it as a Flyway migration script (eg V4__change4.sql that creates a new table FOO)
2. After the necessary local testing, Developer A commits/pushes the migration script to a central VCS location
3. Developer B pulls the migration script to their local working folder
4. Developer B runs flyway migrate to apply the change

This basic flow should work well in most cases, but has two main failure modes:

1. Flyway version number clash (eg Developer B has created their own V4__differentchange4.sql that hasn't yet been pushed to VCS)
2. Migration script fails to run. Developer B's database may have unexpected changes that prevent the migration script from running.

We will consider how best to approach these issues:

How to avoid clashing on the Flyway version number  

Flyway does not allow duplicate version numbers and will fail when it detects them. There are some patterns that can help mitigate or avoid these issues.

1. First one to push wins. Developer A has pushed first, which means that developer B has to rename their own migration script with a new version number. Developer B is now left with an invalid Schema History Table, which needs to be fixed (eg via flyway clean migrate or similar).
2. Consult your co-workers before creating a migration script. Developers can pre-empt the issue by informing their team of the version number they are 'taking'. This only works well with small co-located teams or teams with a really good process around this and they may not make frequent database changes.
3. Use timestamps in the migration versions. Consider having a policy to use timestamps appended to version numbers, eg V4_20220308163340__mychange4.sql. Unless the migration scripts were created at the exact same instant, the version numbers will never clash.

Migration scripts fail to run

In the migrations-first approach, each migration script expects to be run against a database state that has been created by running all preceding migration scripts in order. Any deviation from this may result in a migration script not running. Because a development database is often under development and therefore in an unknown state, there is no guarantee that applying a new migration script will run. It could be that an object that a migration script relies on is being changed, causing it to fail. There are various patterns that can be employed to avoid these issues.

1. Ensure that developers are working on entirely different parts of the system, minimizing the chances that a migration script might be impacted by other changes in the database.
2. Instruct developers to reset/rebuild their development databases before applying migration scripts. This ensures that the database is in exactly the expected state in which to receive the new migration script.
3. Use the Combined state and migrations approach.  This uses the state-based approach for collaborating on changes in development and then migrations for deploying to downstream environments. FLYWAY TEAMS FLYWAY ENTERPRISE

Conclusion

The migrations-based model is conceptually simple and is particularly effective when used by an individual.

Discipline is needed to avoid some of the main pitfalls when working in a team:

• Ensure migration scripts don't have the same version number
• Keep the Flyway Schema History table in synch if you are changing the development database first 
• Development databases may need to be repaired if a migration script touches a part of the development database that is being actively developed for something else.

You may want to consider using the Combined state and migrations approach to simplify collaborating on changes in development, capture the object level history, and still use migrations for deploying to downstream environments. FLYWAY TEAMS FLYWAY ENTERPRISE