Redgate Flyway

Migrations-based approach - generating migration scripts

This approach is for teams looking to improve developer productivity by generating versioned migration scripts.  This is supported for SQL Server, Oracle, PostgreSQL, and MySQL databases. 

1) Generate a versioned migrations script based on changes made to your development database.

2) Capture the development database changes to objects and reference data into a schema model on disk.  Committing this to version control will give you a history of how each object changes over time. 

3) Deploying your database changes to downstream environments. 

Onboarding tips

The following tips focus on the first step above to generate migration scripts from your development database.


Flyway project setup

Ensure that a Flyway migrations project has been created with the development database, shadow database, and baseline configured.

Generating migration scripts can be configured for development teams as follows:

  1. Migration scripts are generated in Flyway Desktop 
  2. Migration scripts are auto-generated as part of a pull request
  3. Migration scripts are auto-generated as part of an automated pipeline

For options 2 and 3, developers need not install or interact directly with the Flyway tooling, which means this model is well suited to teams that have an existing development process.

1. Migration scripts are generated in Flyway Desktop

This is suited for developers who want full control over the script generation process.

Using Flyway Desktop

    1. Save changes in the Schema Model
    2. Visit the Generate migrations page to review changes in your Schema Model that aren't reflected in your migration scripts
    3. Select the specific changes to be added to a new migration script
    4. Select Generate scripts to generate the new versioned migration script as well as optionally generating the corresponding undo script.

2. Migration scripts are generated as part of a pull request

It is possible to configure script generation when using an ORM and SSDT or any database development process without Flyway Desktop.

Using Flyway CLI

    1. Create a GitHub Action "on: pull_request" that:
      1. Use flyway to save to the schema model
      2. Run flyway clean migrate on the shadow database
      3. Generate a migration script from the changes between the schema model and the shadow
      4. Optionally generate the undo script
    2. The migration script and undo migration script can be reviewed before the pull request is merged

3. Migration scripts are generated as part of an automated pipeline

This approach is required when migration script generation can't easily be run as part of a pull request, or this isn't the desired approach. This is particularly suited to teams looking to benefit from auto-generation of migration scripts without interrupting their existing development practices.

To implement, use the same approach as described in #2 above, but implement as a pipeline in your CI/CD tool instead of on Pull Request. This can be manually triggered or run on a schedule.




Didn't find what you were looking for?