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

The migrations-first workflow

Making database changes in development

Flyway relies on maintaining a valid Schema History Table 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 valid.

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 changes 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 additional micro migration scripts which transition 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 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.

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 dev DB 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.

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, the means we are choosing to share changes is 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 that don't 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 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 a state-based approach for collaborating on changes in development. The state-based approach doesn't care what the state of the database is, as database updates are calculated dynamically based on the target state.  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:

This model is less suited for team-based development. Although this model works well for individual developers, or in teams where only one developer makes database changes, it works less well in teams in which changes are made regularly. This is because sharing changes in a team context using migrations relies on databases being in a valid state for the migration scripts to successfully apply. As development databases are under constant development and therefore not necessarily in the right state to receive new migration scripts. This can be mitigated if there is a process in place to reset a development database to a known good state before updating it with new migration scripts.

For larger teams, or teams that are made changes regularly, adopting a state-based development model is a better fit.

Discipline is required to keep the Flyway Schema History Table accurate. The Schema History Table is managed by Flyway to maintain a record of which migrations have been run against the database. This works well so long as all changes applied to the database are made via running flyway migrate. However, the reality is that development is more fluid, and developers will often change their dev DB directly and iterate on changes until they've "got it right" at which point a migration script is authored. If a migration script is created after the changes already exist in the database, there is no entry in the Schema History Table, which means that any subsequent attempt to run flyway migrate to apply new migration scripts will fail, as this will also attempt to apply the script we created earlier whose changes have been applied outside of the flyway migrate process. The options are as follows:

Rebuild the Schema History Table

1. When applying a new migration scripts run flyway clean migrate. This means that flyway recreates the database and the Schema History Table ensuring a consistent state. This draw back of this solution is that it removes any schema changes or test data that a developer might prefer to keep hold of. It also only works for projects that have a complete set of migration scripts to rebuild the database.
2. Same as the above but restore dev from a backup. This has the same drawbacks except it doesn't rely on a complete set of migration scripts to exist. An additional drawback is that often restoring a full production copy can be a very slow process, not only in terms of the time to perform the restore, but also any administrative hoops to jump through to get access to a production backup. 

Keep the Schema History Table accurate if a migration script has been created after the changes have been applied

1. After the migration script has been created, manually undo the schema changes from the dev database, and run the migration script.
2. Flyway Teams and Flyway Enterprise have a feature to "mark the migration script as applied". This ensures that the migration script corresponding to the previously applied schema changes will be considered applied. See skipExecutingMigrations. 
3. When creating the migration script to cover changes that have already been made, first create a blank migration script and then run flyway migrate to record it as run. This migration script can how be edited to include the actual changes. This will result flyway validate failing as the checksum of the "applied" migration will have changed. However, this can be rectified by running flyway repair. 

Apply to Database in the Schema Model screen should be avoided. If a migrations-first model is being used by those who own Flyway Teams or Flyway Enterprise licenses, care must be taken to avoid using the Apply to Database feature in the Schema Model pane, as the state-based database update will not update the Schema History Table leaving it in an inconsistent state.

Less flexibility where deployment ordering is important. In this model the migration scripts are the deployment artifact. Flyway's ordering is determined by the migration's version number ordering. Therefore the default deployment order is determined by when developers happen to create the migration scripts, which may not coincide with the ordering a business has chosen to deploy the value to customers. The Cherry Pick feature (available in Flyway Teams Edition) can override the default deployment ordering, but this requires users to be aware of the "mis-ordering" and to make a conscious decision to override Flyway's behavior.

If a more control of deployment ordering is desired, the state-base dev, migrations deployment model (described below) decouples the development process with the deployment process, allowing the creation of migration scripts to be deferred until the correct deployment ordering is known.