Once you are ready to deploy the changes you have made to your development database, and have optionally saved to your schema model, you can generate migrations.

In order to generate migrations, Flyway needs to run a comparison to find the differences between your schema model or development database and your migrations. This captures all development changes into a migration script.

Once saved in your project, migration scripts can be deployed to other environments, or committed to your version control system and used in your deployment pipeline.

Comparison source

Flyway can either generate migrations based upon the contents of your schema model, or, directly from migrations.

See the tutorials listed below for more information

Comparison target

You will need to specify the target database you are ultimately looking to migrate. This could be a production database or a carbon copy of your production database or a means of provisioning a copy of it.

Flyway can generate a migration directly against against this database. This has the benefit that little setup is required. but it effectively only allows a single migration to be generated between each deployment. If you run this process and you have already got migrations which have not been deployed against the target database, the migration contents are liable to be duplicated and your migrations will not deploy. See this tutorial, for how to implement this workflow, though note that this is a preview feature in Flyway Desktop.

In order to get a representation of the result of running all of your migrations which is guaranteed to be accurate, it is recommended to use a shadow or build environment. A baseline of your target database is necessary, or a means of provisioning your target database in the baseline state, so that running all the migrations against your baseline will capture the full schema of your database. Capturing the baseline state can be slightly fiddly and take a few attempts with mature databases, but it is a one-time set-up pain and Flyway Desktop can help guide you through some of the common pitfalls such as invalid objects. See the tutorials below for more guidance:

• Generate migrations using a shadow database
• Generate migrations without a shadow database

Configuring a shadow environment

For guidance on where to locate a shadow database, see Shadow and build environments.

Follow this tutorial to configure the connection, or this tutorial if using . For guidance on more complex database connections, see Connecting to environments.

Choosing where to save environment configuration

If you have a dedicated shadow database, you will likely want to store that database connection information in your user settings. This allows you to configure your database without sharing that connection information through Version Control.

If everyone on your team is using the same shadow database, then it is preferable to save the database information to the project settings so that the connection information does not need to be reentered by everyone on the team. Note that sharing a shadow database is not recommended, as you will get unpredictable behaviour if multiple people connect to it simultaneously.

It is also possible to spread the configuration between the two files. It might be that the URL is the same but your credentials differ.

Baselining

When working with an existing database, we need a baseline that reflects the state of your objects and reference data that have already been deployed to production so that you can generate new migrations based on changes in your development environment from this point forward. For more information, see Baselines.

For generating a baseline script, see this tutorial. For provisioning a baseline state, see these tutorials:

• Clones as baselines
• Backups as baselines
• Use Oracle's Flashback Technology for Baselining

Invalid objects

Some databases are online, but contain objects that are invalid.  This could be caused by references to objects that no longer exist or objects/columns that have been renamed.  This could also be caused if external dependencies aren't available in the environment you're re-creating the database on.  We see this a lot with large systems have been around a long time.  This means if you try to re-create the database from scratch using a script, it will error. 

To fix this, we recommend you find any invalid objects and fix them in the database before creating the baseline script. See this tutorial for more guidance. Note that you do not need to address this issue if provisioning a baseline state.

SQL Server - Redgate SQL Prompt

If you have Redgate SQL Prompt installed, open SSMS and run SQL Prompt > Find Invalid Objects.  This will also identify invalid objects that needs to be reviewed.

Generating undo migrations 

You can also generate undo migrations automatically alongside your versioned migrations. The undo script compares the shadow Database, which is the result of the migration scripts in the projects, to the Schema model folder on disk.  This will capture the schema differences between these two states and give you a starting point to undo the changes in the corresponding Versioned migration.  You may need to be manually edit the Undo migration script to ensure data integrity, capture data changes, or other optimizations. See this tutorial for how to enable this.

Generated migration naming

When you generate migration scripts, the version number will automatically be incremented.  By default, a timestamp is included to minimize the chance of merge conflicts.  If you do not want the timestamp in your versioning, then you can remove it from the generated script.  Future version numbers will increment the smallest number by default and will not include the timestamp if it's removed.

Generated scripts that may need extra review

There are some change types which may be liable to be scripted out in a way which might need an additional review. See here for a comprehensive list.

Relevant tutorials

• Tutorial - Configure the shadow database
• Tutorial - Configure shadow schema for Oracle
• Tutorial - Generate migrations from the schema model using a shadow database
• Tutorial - Generate undo scripts
• Tutorial - Generate a baseline script
• Tutorial - Find invalid objects before baselining
• Tutorial - Clones as baselines
• Tutorial - Use Oracle's Flashback Technology for Baselining
• Tutorial - Creating multiple shadow databases at once
• Tutorial - Tweaking version numbering
• Tutorial - Generating migrations with SQL Server Memory Optimized Tables