Redgate Flyway

State-Based Approach – Flyway CLI

Page last updated 24 December 2025

👉 Want to skip ahead and use ready-made scripts? Clone the full repo here: Flyway Helper Files GitHub Repo.

Why choose State-Based?

State-based deployments let you define your database schema as a model and automatically generate deployment scripts to bring any target database in line with that model. This approach is:

  • Recommended for new users: Quickest time-to-value (Especially when paired with our Chinook sample schema to get up and running)
  • Ideal for automation: Perfect for CI/CD workflows where you want predictable, repeatable deployments.

Unlike migrations-based workflows, you don’t need to handcraft every script. Instead, Flyway compares your model to the target database and generates the necessary changes.

State-based workflows typically involve two stages:

1. Developer Workflow Simulation

Commands like diff and model simulate what developers do in Flyway Desktop:

  • diff: Compares two sources (e.g., Development DB vs model) and shows the differences.
  • model: Analyse the above diff output and save changes to your Flyway Schema Model.

2. DevOps Workflow Simulation

Commands like prepare, snapshot, check, and deploy are used in CI/CD pipelines:

  • prepare: Generates a deployment script based on differences between model and target.
  • snapshot: Captures the state of a database for drift detection and auditability of database state.
  • check: Validates that the deployment script is safe and any known risk for the upcoming deployment
  • deploy: Applies the generated script to the target database.

Tip - Not every customer needs both stages—if your goal is simply to deploy changes, you can start at prepare → deploy.

💡Using the Chinook Sample Database

For the fastest start, use the Chinook schema for SQL Server, PostgreSQL, Oracle, or MySQL. It’s included in our Flyway Helper Files repo.
Run the setup script for your database engine, then proceed with the steps below.
(Prefer your own schema? That works too—just adjust the connection details accordingly)

Next Steps

Below, you’ll find expandable sections for each command in the workflow.

The full repository can be found here: 👉 Flyway Helper Files GitHub Repo

Each section includes:

  • Purpose – Why the command matters.
  • When to use – Where it fits in the workflow.
  • Helper script links – Quick access to ready-made scripts.
  • How to configure – Variables explained for easy setup.

Diff Command

Purpose

Create an artifact that details the differences between two sources—these can be databases, a schema model, or a snapshot. The artifact can then be used by subsequent actions (e.g., prepare) or reviewed manually. This is essential for understanding what changes are required before deployment.

💡 When to use diff

  • To compare environments (e.g., development vs schema model) and capture differences.
  • To generate a differences artifact for review or automation.
  • To feed into Prepare for script generation in CI/CD workflows.
  • You want a human‑readable differences report to review in PRs or as part of code review (This requires the use of diffText after first running diff)

If you’re only automating deployments (CI/CD), you can skip diff and start with prepare to generate a deployment script directly from model → target.

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
ARTIFACT_FILENAMEPath and filename for the differences artifact (ZIP containing results).
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).
SOURCE_ENVIRONMENTName of the source environment (e.g., development).
SOURCE_ENVIRONMENT_USERNAMEUsername for the source environment (leave empty for Windows Authentication).
SOURCE_ENVIRONMENT_PASSWORDPassword for the source environment (leave empty for Windows Authentication).
TARGET_ENVIRONMENTName of the target environment (e.g., schemaModel for state-based).

Important:

  • For real deployments, do not store sensitive values in plaintext. Use environment variables or secure credential stores.
  • If using Windows Authentication, USERNAME and PASSWORD can be left empty.

For additional parameters and options (like comparing snapshots or customizing output), see the Flyway Diff command reference.


Model Command

Purpose

Capture changes from a differences artifact (created by diff) and apply them to your schema model. This updates the model so it reflects the latest state of your source environment. It’s a key step in maintaining an accurate schema model for state-based deployments.

💡 When to use model

  • After running diff to identify changes between environments.
  • When you want to update your schema model based on those differences.
  • Typically part of the developer workflow (simulating Flyway Desktop behavior).

If you’re only wanting to deploy changes and not automating the maintenance of the schema model, you can skip model and go straight to prepare.

📚 Helper Script Links

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
ARTIFACT_FILENAMEPath to the differences artifact created by diff (ZIP file).
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).

For additional parameters and options (like customizing schema model location), see the Flyway Model command reference.

Prepare Command

Purpose

Generate a deployment script (and optionally an undo script) to bring the target environment in line with the source environment. By default, this means applying changes from the schema model to the target database. This is the first step in the CI/CD workflow for state-based deployments.

💡 When to use prepare

  • When you need a deployment script for review or automation.
  • After maintaining your schema model (via diff and model) or when ready to deploy changes.
  • Typically part of DevOps workflows—used in pipelines before check and deploy.

📚 Helper Script Links

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
SCRIPT_FILENAMEName of the generated deployment script (e.g., Flyway_Deployment_Script.sql).
UNDO_FILENAMEName of the generated undo script (e.g., Flyway_Undo_Script.sql).
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).
SOURCE_ENVIRONMENTName of the source environment (usually schemaModel for desired state).
TARGET_ENVIRONMENTName of the target environment (e.g., Test).
TARGET_ENVIRONMENT_USERNAMEUsername for the target environment (leave empty for Windows Authentication).
TARGET_ENVIRONMENT_PASSWORDPassword for the target environment (leave empty for Windows Authentication).

Important:

  • For real deployments, do not store sensitive values in plaintext. Use environment variables or secure credential stores.
  • If using Windows Authentication, USERNAME and PASSWORD can be left empty.

For additional parameters and options (like forcing script generation or customizing output paths), see the Flyway Prepare command reference.


Snapshot Command

Purpose

Create a schema snapshot of the target database and store it for future comparison or drift detection.
Snapshots can now be saved directly into the flyway_snapshot_history table in the target database, which is extremely valuable for CI/CD workflows. This eliminates the need for shared storage between environments and makes it easier for commands like check to retrieve historical states for drift analysis.

💡 When to use snapshot

  • To capture the current state of a database for auditing or drift detection.
  • Before or after deployments to maintain a history of schema states.
  • Especially useful in CI/CD pipelines, where storing snapshots in the database simplifies access for checks like drift detection.

📚 Helper Script Links

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).
TARGET_ENVIRONMENTName of the target environment (e.g., Test).
TARGET_ENVIRONMENT_USERNAMEUsername for the target environment (leave empty for Windows Authentication).
TARGET_ENVIRONMENT_PASSWORDPassword for the target environment (leave empty for Windows Authentication).

Important:

  • For real deployments, do not store sensitive values in plaintext. Use environment variables or secure credential stores.
  • If using Windows Authentication, username and password fields can be left empty.

This script uses -snapshot.filename=snapshotHistory:Snapshot-<date> to store the snapshot in the flyway_snapshot_history table.
The -snapshot.historyLimit=5 parameter ensures only the last 5 snapshots are retained for this environment.

For additional parameters and options (like customizing snapshot retention or exporting snapshots to files), see the Flyway Snapshot command reference.


Check Command

Purpose

Generate a comprehensive report that analyzes:

  • Pending changes between source and target environments.
  • Drift detection by comparing the target database against its last known snapshot.
  • Code analysis for potential issues in the deployment script.

The report helps ensure deployments are safe and predictable, and it’s especially valuable in CI/CD workflows.

💡 When to use check

  • Before deploying changes to validate:
    • What changes will be applied.
    • Whether the target environment has drifted from its expected state.
    • If the deployment script passes code quality checks.
  • Typically part of DevOps workflows, right after prepare and before deploy.

    If this is the first time running check against the target environment, you may see warnings about missing snapshots. Run Snapshot first or use Deploy, which can create snapshots automatically.

📚 Helper Script Links

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
REPORT_FILENAMEName of the generated HTML report (e.g., Flyway-Check-All_Report.html).
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).
SCRIPT_FILENAMEName of the deployment script to validate (generated by prepare).
SOURCE_ENVIRONMENTName of the source environment (usually schemaModel).
TARGET_ENVIRONMENTName of the target environment (e.g., Test).
TARGET_ENVIRONMENT_USERNAMEUsername for the target environment (leave empty for Windows Authentication).
TARGET_ENVIRONMENT_PASSWORDPassword for the target environment (use env vars in production).

Important:

  • For real deployments, do not store sensitive values in plaintext. Use environment variables or secure credential stores.
  • If using Windows Authentication, USERNAME and PASSWORD can be left empty.

For additional parameters and options (including customizing scope and output), see the Flyway Check command reference and its sub-pages.

What the Check report includes

  • Changes analysis: Lists pending changes between source and target.
  • Drift detection: Compares the target database against its last snapshot in flyway_snapshot_history.
  • Code analysis: Reviews the deployment script for potential issues (e.g., destructive operations).

Deploy Command

Purpose

Execute the deployment script (generated by prepare) against the target environment. This applies the changes required to bring the target database in line with the desired state.
Optionally, deploy can save a snapshot of the target database after deployment, which is extremely useful for drift detection and auditing in CI/CD workflows.

💡 When to use deploy

  • After validating the deployment script with check.
  • To apply changes to the target environment in a controlled, automated way.
  • Typically the final step in the state-based CI/CD workflow.

📚 Helper Script Links

🔧 How to Configure

These variables are defined at the top of the helper scripts and are consistent across platforms:

VariablePurpose
SCRIPT_FILENAMEPath to the deployment script to execute (generated by prepare).
WORKING_DIRECTORYRoot folder for your Flyway state-based project (contains schema model).
TARGET_ENVIRONMENTName of the target environment (e.g., Test).
TARGET_ENVIRONMENT_USERNAMEUsername for the target environment (leave empty for Windows Authentication).
TARGET_ENVIRONMENT_PASSWORDPassword for the target environment (leave empty for Windows Authentication).
SAVE_SNAPSHOTBoolean (true/false) to save a snapshot in the target environment after deployment.

Important:

  • For real deployments, do not store sensitive values in plaintext. Use environment variables or secure credential stores.
  • If using Windows Authentication, username and password fields can be left empty.

Setting SAVE_SNAPSHOT=true ensures the target environment’s state is captured in the flyway_snapshot_history table immediately after deployment—ideal for drift detection and compliance.

For additional parameters and options (like customizing snapshot behavior or deployment scope), see the Flyway Deploy command reference.


✔️ What You’ve Achieved 

Congratulations! You’ve now completed the State-Based Flyway CLI workflow end-to-end:

  • Captured differences between environments with Diff.
  • Updated your schema model using Model.
  • Generated deployment and undo scripts with Prepare.
  • Created snapshots for drift detection using Snapshot.
  • Validated changes and code quality with Check.
  • Applied changes to your target environment using Deploy.

By following these steps, you’ve proven the workflow works and mastered the basics of Flyway CLI for state-based deployments. This foundation gives you the flexibility to integrate Flyway into almost any workflow—including CI/CD pipelines—at your own pace.

Next Steps





Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal