Flyway

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 practice

Description

X-Database Solution - Flyway
Oracle / SQL Server / PostgreSQL / MySQL

SQL Server Only -
SQL Source Control and
SQL Change Automation

Version control your database

Follow 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 Desktop

SQL 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:

Oracle cmdlines (schema and data)
SQL Server cmdlines (schema and data)

SQL Change Automation Invoke-DatabaseBuild

Static code analysis

Highlights potential issues, provides advice on best practices, and enforces naming conventions.

flyway check -code

This is included with SQL Change Automation.

Database unit testing

Redgate 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 Server - tSQLt
Oracle - utPLSQL
PostgreSQL - pgTAP

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:

Oracle cmdlines (schema and data)
SQL Server cmdlines (schema and data)

SQL Change Automation:

New-DatabaseReleaseArtifact - Source = path to scripts folder
Use-DatabaseReleaseArtifact)

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 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. (Note:	Oracle cmdlines (schema and data) SQL Server cmdlines (schema and data)
Deployment script preview (aka DryRun)	Review and approve the exact deployment script that will be executed on the target.	Oracle cmdlines (schema and data) SQL Server cmdlines (schema and data)
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.	Oracle cmdlines (schema and data) SQL Server cmdlines (schema and data)

These report and checks are included in SQL Change Automation.

Deployment warnings report

Get warnings about data loss or potential problems. See the full list of deployment warnings.

Oracle cmdlines (schema)
SQL Server cmdlines (schema)

SQL Server cmdlines (schema and data)

Snapshots for rollbacks

Use 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.

flyway snapshot

SQL Server cmdlines (schema and data)

Test rollback scripts

Flyway 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

Oracle cmdlines (schema and data)
SQL Server cmdlines (schema and data)

SQL Server cmdlines (schema and data)

For more information and examples about automating database releases, see Deploying database changes via a pipeline.

Flyway

Database DevOps Practices