Flyway

Deployment approaches with Flyway

There are two deployment approaches (or models) that Flyway supports: State and Migrations-based deployments.

You can configure the approach for your project when creating a new project or in the project settings dialog.

Schema model is the state-based approach.  

Migrations is the migrations-based approach.

In a State-based approach, the CREATE DDL for each object is stored in version control.  The state of any static data can also be stored in version control.  At deployment time, this DDL state is compared to a target database and the target is updated to reflect the state of the objects/static data.  

In a Migrations-based approach, the SQL scripts to CREATE/ALTER/DROP and/or INSERT/UPDATE/DELETE are stored in version control.  At deployment time, Flyway checks to see which scripts have been applied to the target database and will execute any pending scripts. 

In Flyway, these approaches can be combined.  You can use state for your development database and migrations for deploying to your downstream environments where more control is needed.  This article introduces these approaches and discusses the main pros and cons of each:

State-based deployments (using the Schema model)

FLYWAY TEAMS FLYWAY ENTERPRISE

The source of truth in a state-based approach is the schema model, which is a set of files that representing the latest state of each object.  Flyway Enterprise can also create files for the state of the static data in your database.  In this approach, no migration scripts are needed.

In this model, developers use Flyway Desktop to save database changes to their local schema model. The files in the schema model can be committed and pushed to a version control system.  If each developer is on their own dedicated development database, they can pull their colleagues changes from version control and use the Schema model's Apply to Database to update their dedicated development database with the latest changes. Flyway will compare the state of the schema model on disk with the state of the database and apply any differences to the development database..

A similar approach is taken at deployment time. Flyway will compare the schema model on disk with the downstream target databases and will generate a deployment script based on the differences between the schema model and the target database objects/static data.

Pros:

  1. It's simple to get started

Cons:

  1. You don't have complete control over the deployment script.  You may need to update the script for more complicated database changes (new NOT NULL column that doesn't have a default, splitting tables or columns, one-off scripts to update transactional data, etc.).  Here's a list of changes that a state-based approach may have problems with.  You will need additional processes to handle these situations if you are deploying your database changes through an automated deployment pipeline.

Migrations-based deployments 

In this model, the sole source of truth for both development collaboration and deployment is the set of migration scripts. These are the ALTER scripts that capture exactly how to modify a target database.  

In this model, developers create migration scripts and push them to their version repository when they have database changes that are ready to share or deploy. When developers take on database changes created by others, they will pull new migration scripts to their local working folder and run flyway migrate to apply the changes.

For deployment, these very same migration scripts are applied using flyway migrate against downstream test environments all the way to production.

Pros:

  1. You have full control over the scripts that run at deployment time.  This is extremely important for automated database deployments.

Cons:

  1. It is harder to understand how each object changes over time. 
  2. In some cases, deployments will go through intermediary steps that aren't necessary.  For example, if a stored procedure changed 3 times, there will be 3 versioned migration scripts that alters that same procedure instead of just running the last.  This is usually quick for certain object types.  If you're changes touch a table, you'll want to be more careful about this and you may want to remove unapplied migration scripts in that case.      
  3. Optional: There's some additional setup required to generate migration scripts including setting a baseline and having a shadow database/schema. (Flyway Enterprise)
  4. Potentially: The number of migration scripts can get quite large.  You may want to re-baseline.  Some teams do this on a yearly basis.  Some teams never do this.  It will depend. 

The combined State and Migrations-based approach

FLYWAY TEAMS FLYWAY ENTERPRISE

This approach takes the best of both state and migrations and applies them to the development process and the deployment process accordingly. The state model becomes the source of truth for development collaboration, and the set of migrations is the source of truth for deployment. This decoupling means that developers who want to share changes with other developers aren't forced at this early state to create a migration script that will impact how and in which order the changes are eventually deployed to the customer.

As in the state-based approach developers will save and collaborate on changes by saving them to and applying them from the schema model using Flyway. 

When ready to deploy, developers or DBAs can create migration scripts based on the changes that exist in the schema model. As with the migrations-based approach, deployments rely on running migration scripts against the downstream environments.

Pros:

  1. You have the history of how each object changes over time.
  2. You have full control over the scripts that run at deployment time.  This is extremely important for automated database deployments.
  3. Optional: You can generate a versioned migration script that covers all your changes for deployment instead of having each developer generate migrations.  This could reduce the overall number of migrations.    

Cons: 

  1. In some cases, deployments will go through intermediary steps that aren't necessary.  For example, if a stored procedure changed 3 times, there will be 3 versioned migration scripts that alter that same procedure instead of just running the last.  This is usually quick for certain object types.  If you're changes touch a table, you'll want to be more careful about this and you may want to remove unapplied migration scripts in that case.      
  2. Optional: There's some additional setup required to generate migration scripts including setting a baseline and having a shadow database/schema. (Flyway Enterprise)
  3. Potentially: The number of migration scripts can get quite large.  You may want to re-baseline.  Some teams do this on a yearly basis.  Some teams never do this.  It will depend. 

Didn't find what you were looking for?