Context & Why: Moving Away from Midnight SQL Hotfixes
Early last year, our release process had a serious failure point: manual schema updates. Developers pasted migration snippets into Slack threads, and on-call engineers ran them by hand on production nodes during maintenance windows. At roughly 35 microservices and 120 database tables, human error caught up with us. A developer would skip an index addition or execute a foreign key patch out of sequence. That triggered three major rollback incidents in a single quarter.
We needed our database state to live alongside our code in Git. More importantly, we needed every environment—from local Docker setups to our multi-node staging and production clusters—to update deterministically on merge. That requirement brought us to Flyway.
Flyway tracks applied changes using plain SQL scripts with structured filenames. When it runs against a database, it checks a metadata table named flyway_schema_history. This table stores the script version, execution status, execution time in milliseconds, and a CRC32 checksum. If someone edits an already-applied migration script in Git, Flyway detects the checksum mismatch and fails the build immediately before applying new changes.
Installation: Local Development and Container Runtimes
You can run Flyway through standalone binaries, build tool plugins (Maven, Gradle), or official Docker images. For local development, package managers get you running in seconds. In automated CI/CD runners, container images provide consistency without local driver dependencies.
Method 1: Direct CLI Installation
For macOS and Linux developer machines, install the standalone binary directly:
# macOS using Homebrew
brew install flyway
# Linux x64 CLI manual download
wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/10.10.0/flyway-commandline-10.10.0-linux-x64.tar.gz | tar xvz
sudo ln -s $(pwd)/flyway-10.10.0/flyway /usr/local/bin/flyway
# Verify installation
flyway -vMethod 2: Running via Docker
The official Docker image bundles pre-configured JDBC drivers for PostgreSQL, MySQL, MariaDB, and Oracle. Mount your migration directory and configuration folder directly:
docker run --rm \
-v $(pwd)/sql/migrations:/flyway/sql \
-v $(pwd)/conf:/flyway/conf \
flyway/flyway:10.10.0 infoConfiguration: Script Naming, Multi-Engine Setups, and Baselines
Flyway relies on strict naming conventions to determine execution order and script behavior.
Migration File Naming Conventions
Flyway scans migration directories and categorizes files based on their prefixes:
- Versioned Migrations (
V<Version>__<Description>.sql): Applied once in strict ascending order (e.g.,V1.0__create_users_table.sql,V1.1__add_user_profile.sql). - Repeatable Migrations (
R__<Description>.sql): Applied after all versioned migrations finish. They re-run automatically whenever their file contents and checksum change (ideal for views, stored procedures, and triggers). - Undo Migrations (
U<Version>__<Description>.sql): Reverses specific versioned changes. Note that undo functionality requires a Flyway Teams or Enterprise license.
Configuring Database Connections
Store connection parameters in a flyway.conf file or supply them via environment variables (FLYWAY_URL, FLYWAY_USER, FLYWAY_PASSWORD). Here is a standard configuration file:
# PostgreSQL Configuration
flyway.url=jdbc:postgresql://localhost:5432/app_production
flyway.user=postgres_user
flyway.password=secure_password
flyway.schemas=public
flyway.locations=filesystem:./sql/migrations
# Baseline settings for existing databases
flyway.baselineOnMigrate=true
flyway.baselineVersion=0.0
flyway.baselineDescription=Initial baseline
# MySQL Configuration Example
# flyway.url=jdbc:mysql://localhost:3306/app_production?useSSL=false&allowPublicKeyRetrieval=true
# flyway.user=mysql_user
# flyway.password=secure_password
# flyway.schemas=app_productionWriting Migration Scripts
Keep your DDL changes atomic and dialect-specific. Below is an initial PostgreSQL schema in sql/migrations/V1.0__init_schema.sql:
-- PostgreSQL initial user table
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users(email);Follow up with an incremental addition in sql/migrations/V1.1__add_user_profile.sql:
ALTER TABLE users ADD COLUMN full_name VARCHAR(120);
ALTER TABLE users ADD COLUMN metadata JSONB;During initial migrations and seed script preparation, I often run into cases where mock user data comes in bulk formats. When I need to quickly convert CSV to JSON for data imports, I use toolcraft.app/en/tools/data/csv-to-json — runs in the browser so no data leaves your machine. It makes preparing JSON payloads for migration test seeds painless.
Automating Migrations in GitHub Actions
Never run schema migrations from application startup hooks in multi-replica environments. When three Kubernetes pods boot at the same time and attempt concurrent migrations, race conditions can occur. Instead, run migrations as a dedicated pipeline step before deploying application code:
name: Deploy Database Migrations
on:
push:
branches:
- main
paths:
- 'sql/migrations/**'
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- name: Checkout Code
uses: actions/checkout@v4
- name: Run Flyway Migration
uses: docker://flyway/flyway:10.10.0
with:
args: >-
-url="${{ secrets.DB_JDBC_URL }}"
-user="${{ secrets.DB_USER }}"
-password="${{ secrets.DB_PASSWORD }}"
-locations="filesystem:./sql/migrations"
migrateVerification, Troubleshooting, and Recovery
Database migrations require proactive validation in CI pull requests and clear recovery steps when syntax errors slip through.
1. Inspecting Migration State
Check which migrations have been applied and which are pending execution:
flyway infoThe CLI outputs a tabular log:
+-----------+---------+--------------------+------+---------------------+---------+
| Category | Version | Description | Type | Installed On | State |
+-----------+---------+--------------------+------+---------------------+---------+
| Versioned | 1.0 | init schema | SQL | 2026-03-15 10:20:00 | Success |
| Versioned | 1.1 | add user profile | SQL | 2026-04-02 08:14:22 | Success |
| Versioned | 1.2 | add audit triggers | SQL | | Pending |
+-----------+---------+--------------------+------+---------------------+---------+2. Catching Accidental File Edits with flyway validate
Add flyway validate to every pull request check. If a developer edits an older migration file instead of creating a new version, Flyway rejects the build before it touches any staging or production database:
flyway validateWhen validation catches a checksum divergence, you will see output like this:
ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1.1
- Applied to database : -1829384912
- Resolved locally : 19283746153. Recovering from Failed Migrations
How you recover from a failed migration depends on your database engine. PostgreSQL wraps DDL statements inside transactions, rolling back partial changes automatically if an error occurs. MySQL, however, does not support transactional DDL. If a script fails midway on MySQL, earlier statements in that file remain committed while Flyway records the migration as Failed.
To recover from a failed state:
- Check the database manually and roll back any uncommitted or partially applied DDL statements.
- Fix the root cause in the migration script.
- Run
flyway repairto remove the failed entry fromflyway_schema_historyand recalculate checksums:
flyway repairRe-run flyway migrate once repaired. With automated validation in CI and explicit pipeline triggers, schema synchronization takes under 5 seconds per deployment, keeping database state consistent across your entire infrastructure.
