Flyway

Database DevOps Practices

A Flyway Enterprise license comes with an entitlement to use automated build and release resources, enabling Database DevOps practices like Continuous Integration and Continuous Delivery.

Here is a list of Database DevOps practices along with the required and recommended resources and technologies for MySQL, Oracle, PostgreSQL, and SQL Server databases.  For all other databases, see the Quickstart Guide for Flyway Desktop.

We're currently working on integrating the state-based approach directly into the flyway cmdline, so it will work for PostgreSQL and MySQL in addition to Oracle and SQL Server.  This new cmdline will replace the individual cmdlines for Oracle and SQL Server, which are documented below when using a state-based approach for deployments.  If you have questions about this or would like to try out early versions of the new cmdline, please reach out to our development team.


Database DevOps practiceDescriptionX-Database Solution - Flyway
Oracle / SQL Server / PostgreSQL / MySQL
SQL Server Only - 
SQL Source Control and
SQL Change Automation
Version control your databaseFollow best practices to understand who changed what, when, and why and work safely and more collaboratively on database changes.  This is also the starting point for for Continuous Integration and Continuous Delivery processes.Flyway DesktopSQL Source Control (state-based)
SQL Change Automation (migrations-based)

Database build

Get fast feedback and make sure there are not any issues with the scripts in the repository (e.g., invalid PL/SQL syntax, dependency issues, etc.).

flyway clean (optional - if you want to build from scratch)

flyway migrate info 

State-based approach:

SQL Change Automation Invoke-DatabaseBuild
Static code analysisHighlights potential issues, provides advice on best practices, and enforces naming conventions.flyway check -codeThis is included with SQL Change Automation.
Database unit testingRedgate sponsors tSQLt and utPLSQL.  These are opensource database unit testing frameworks for SQL Server and Oracle respectively.  Use this to test your packages, procedures, functions, and more to have more confidence in your releases. 

We've heard good things about pgTap for PostgreSQL unit testing.
SQL Change Automation Invoke-DatabaseTests - leverages tSQLt
Automated release with Flyway (migrations-based model)Automatically execute the migration scripts that haven't been run against the target database yet and record their execution in the flyway_schema_history table.  In addition, you'll get:
  • Deployment script preview (dryRun) - Review and approve the exact deployment script that will be executed on the target
  • Change report - See what objects will be changed in this release and exactly how they will be changed.  This is useful for approving a release and provides a history of the changes.
  • Drift detection - Make sure that your Production database hasn't changed since your last deployment or since you generated the deployment script preview to ensure that a dry run on a Staging environment is still valid.

Script Preview - flyway check -dryrun


Change report and drift detection: flyway check

SQL Change Automation:

Automated release with Flyway Desktop (schema-model only), Source Control for Oracle or SQL Source Control (state-based deployment model)

Automatically compare your repository to the target database and apply the schema and static data changes to the target database.

State-based approach:

SQL Change Automation:


SQL Server cmdlines (schema and data)

See a worked example on the Microsoft Dev Blog.

Octopus Deploy Template - Redgate - Create Oracle Release


For the state-based deployment model:

Changes reportSee what objects will be changed in this release and exactly how they will be changed.  This is useful for approving a release and provides a history of the changes.  (Note: 
Deployment script preview (aka DryRun)Review and approve the exact deployment script that will be executed on the target. 
Drift detection - pre-deployment Make sure that your Production database hasn't changed since your last deployment or since you generated the deployment script preview to ensure that a dry run down on a Staging environment is still valid. 
These report and checks are included in SQL Change Automation.
Deployment warnings reportGet warnings about data loss or potential problems.  See the full list of deployment warnings.
Snapshots for rollbacksUse our comparison technology to snapshot your Production database before the deployment.  This snapshot can be used in our compare tools as a source to review changes and generate a rollback script to this state. Read more about rollbacks.
Test rollback scriptsFlyway Enterprise can generate a corresponding Undo Migration Script every time you generate a Versioned Migration Script
If you are using a state-based approach, use our comparison technology to generate a rollback script by specifying Production as your source and the repository as your target.   Read more about rollbacks.

flyway undo

For more information and examples about automating database releases, see Deploying database changes using Flyway.


Didn't find what you were looking for?