Migration scripts capture incremental changes to your development database.  These are SQL scripts that can capture schema and data changes (eg., CREATE, ALTER, INSERT, UPDATE, etc.).  They should be tracked in version control.  These scripts are used to deploy to your other environments in a consistent way.  The same scripts will be run in the same order on each environment, which gives you a repeatable deployment process.

Flyway Desktop allows you to manage and create database migration scripts, which you can then view on the Migrations page.  We support the following kinds of migration scripts:

Versioned migration scripts 

Versioned migrations have a version number, a description, and a checksum.  They start with a V followed by a version number, two underscores, and a description (eg., V001.002__NewTwitterColumn.sql).  The version number must be unique. The description is purely informative for you to be able to remember what each migration script does. The checksum is there to detect if any changes have been made to the script since it was applied to the database.

Versioned migrations are applied to a target database in order exactly once.  The redgate_schema_history table tracks which versioned migrations have already been applied and stores the checksum to make sure they haven't changed.  It is best practice not to make changes to existing versioned migrations once they have been applied to a permanent downstream environment.  It's best to create a new versioned migration script and roll forward any necessary changes. 

Repeatable migration scripts

Repeatable migrations have a description and a checksum, but no version number. Instead of being run just once, they are (re-)applied every time their checksum changes.   They start with an R followed by two underscores and a description (eg., R__SetDefaults.sql).  The filename and checksum are also stored in the redgate_schema_history table so we know when the repeatable migrations need to be reapplied.

Post-deployment script

When a new project is created for Oracle databases, Flyway Desktop adds an `afterMigrate__fix_invalid_objects.sql` script, which contains the SQL code required to recompile invalid database objects after each successful migration. For existing projects, this `afterMigrate__fix_invalid_objects.sql` script is added to the project if it does not exist. However, if a script of this name already exists, then it will not be replaced.

Flyway Desktop is built on top of Flyway, if you want to add more scripts that hook into the deployment lifecycle like a Pre-deployment Script (beforeMigrate) or a script to run if something fails (afterMigrateError), then read more about Callbacks.  Since Flyway Desktop is built on top of Flyway, it is aware of these scripts and will use them correctly, but it does not currently manage them in the Flyway Desktop GUI so they must be managed externally if required for your purposes.  If you'd like to use any of these additional features, please contact our team so we can consider adding support within Flyway Desktop.  

Undo Migration Scripts

We have built-in support for undo migration scripts.  Undo migration scripts allows you to write a down script for each versioned migration script to help with rolling back changes.  Our built-in support automatically generates the undo migration script when you're generating the versioned migration scripts.

You can view Undo migration scripts within the Migrations page by clicking on an undoable migration and then toggling the content view.  You can learn more about Flyway Undo Migrations on the  Undo migration scripts start with a U, followed by the same number as the versioned migration script that it corresponds to, two underscores, and then a description (eg., U001.002__UndoNewTwitterColumn.sql).

Order of execution for Migration Scripts

Within a single migration run, all pending versioned migrations are applied in order first followed by any repeatable migrations that have changed.  Repeatable migrations are applied in the order of their description.  To keep track of which migrations (versioned and repeatable) have already been applied, when and by whom, a redgate_schema_history table is added to the first schema specified in your project. At the end of a successful migration run, the post-deployment script described above is run.

In Oracle, if a migration script fails, then the redgate_schema_history table will indicate which script has failed and will stop executing any remaining scripts.  Once any unwanted database changes have been removed, flyway repair must be run before new migrations can be applied.

Migration script states

Because Flyway Desktop is built on top of Flyway, you may come across a number of different migration script states when working in Flyway Desktop. The most common ones include pending (i.e. the script has not yet been applied), success (i.e. the script was applied successfully) and failed (i.e. the script failed during migration). You may also occasionally see some less common states such as future (i.e. a migration that has been applied but cannot be found on disk and is newer than all existing migrations) and missing (i.e. a migration that has been applied but cannot be found on disk and is not newer than all existing migrations). More details on these states can be found here: Migrations > Migration States.