CheatPress

Flyway Database migrations

Version 13.11.1 · source

Flyway, Postcard, light preview Light · Postcard 102 × 152 mm · PDF Flyway, Postcard, dark preview Dark · Postcard 102 × 152 mm · PDF Flyway, Postcard, ink saver preview Ink saver · Postcard 102 × 152 mm · PDF
Flyway, A5, light preview Light · A5 148 × 210 mm · PDF Flyway, A5, dark preview Dark · A5 148 × 210 mm · PDF Flyway, A5, ink saver preview Ink saver · A5 148 × 210 mm · PDF
Flyway, A4, light preview Light · A4 210 × 297 mm · PDF Flyway, A4, dark preview Dark · A4 210 × 297 mm · PDF Flyway, A4, ink saver preview Ink saver · A4 210 × 297 mm · PDF
Flyway, A4 landscape, light preview Light · A4 landscape 297 × 210 mm · PDF Flyway, A4 landscape, dark preview Dark · A4 landscape 297 × 210 mm · PDF Flyway, A4 landscape, ink saver preview Ink saver · A4 landscape 297 × 210 mm · PDF
Flyway, A3, light preview Light · A3 297 × 420 mm · PDF Flyway, A3, dark preview Dark · A3 297 × 420 mm · PDF Flyway, A3, ink saver preview Ink saver · A3 297 × 420 mm · PDF
Flyway, A3 landscape, light preview Light · A3 landscape 420 × 297 mm · PDF Flyway, A3 landscape, dark preview Dark · A3 landscape 420 × 297 mm · PDF Flyway, A3 landscape, ink saver preview Ink saver · A3 landscape 420 × 297 mm · PDF
Flyway, Letter, light preview Light · Letter 8.5 × 11 in · PDF Flyway, Letter, dark preview Dark · Letter 8.5 × 11 in · PDF Flyway, Letter, ink saver preview Ink saver · Letter 8.5 × 11 in · PDF
Flyway, Tabloid, light preview Light · Tabloid 11 × 17 in · PDF Flyway, Tabloid, dark preview Dark · Tabloid 11 × 17 in · PDF Flyway, Tabloid, ink saver preview Ink saver · Tabloid 11 × 17 in · PDF

Start here

flyway init -projectName=<name> -databaseType=<type>
New project
flyway add -description=<text>
New migration file
flyway info
What is applied?
flyway migrate
Apply pending
flyway validate
Detect edited scripts
flyway repair
Fix history table
flyway baseline
Adopt existing DB
flyway clean
Wipe schemas (dev only)

Concepts

  1. V1__init.sql files in locations
  2. Target DB your schema
  3. Schema history flyway_schema_history
  4. validate checksums + order

Migration files

V<n>__<desc>.sql
Versioned migration (runs once)
Two underscores between version and description, e.g. V2__add_users.sql
R__<desc>.sql
Repeatable migration
U<n>__<desc>.sql
Undo migration for a version
Undo needs a paid edition
B<n>__<desc>.sql
Baseline migration
flyway add -description=<text>
Create next versioned script
flyway add -type=undo
Create undo script
flyway add -version=<v> -description=<text>
Create script with explicit version
flyway add -timestamp -description=<text>
Add timestamp to version
flyway add -location=<path>
Choose target directory
flyway add -add.nameOnly=true -q
Print the file name only
${<name>}
Placeholder inside SQL
-locations=filesystem:<dir>
Where to scan for scripts

Project and setup

flyway init -projectName=<name> -databaseType=<type>
Generate project and flyway.toml
flyway init -from=flyway.conf -fromType=Conf
Upgrade an old .conf file
flyway list engines
List supported databases
flyway -v
Print version
flyway -environment=<env>
Pick the target environment
flyway -configFiles=<file>
Use a specific config file
flyway -url="<jdbc-url>" -user=<u> -password=<p> migrate
Connect without a config file
-schemas=<a>,<b>
Schemas Flyway manages
-workingDirectory=<dir>
Run as if in another directory

Migrate and inspect

flyway migrate
Apply all pending migrations
flyway info
Show applied and pending
flyway validate
Compare files with history
flyway migrate -target=<version>
Stop at a version
flyway migrate -outOfOrder=true
Allow lower versions after higher
flyway migrate -baselineOnMigrate=true
Baseline a non-empty DB on first run
flyway migrate -validateOnMigrate=false
Skip validation (avoid)
flyway migrate -mixed=true
Mix transactional and non-transactional
flyway info -infoOfState=pending
Filter by state
flyway info -infoSinceVersion=<v>
From a version onwards
flyway info -infoSinceDate="dd/MM/yyyy HH:mm"
Applied after a date
flyway info -migrationIds=true
Print only the ids
flyway undo
Undo the last versioned migration

Repair and recover

flyway repair
Remove failed rows, realign checksums
Use the same locations as migrate; clean up objects a failed script left behind by hand
flyway baseline
Mark existing DB as migrated
flyway baseline -baselineVersion=<v>
Baseline at a version
flyway clean -cleanDisabled=false
Drop all objects in schemas
Never against production
flyway check -code
Review migrations with code analysis
flyway check -dryrun
Preview a deployment script
flyway check -drift
Detect out-of-band changes
flyway check -changes
Report pending changes

Configuration

flyway.toml
Project config file
[environments.<name>] url, user, password
Define an environment
[flyway] locations = ["filesystem:sql"]
Flyway settings in TOML
FLYWAY_URL / FLYWAY_USER / FLYWAY_PASSWORD
Env var overrides
locations
Folders scanned recursively
table
Name of the history table
defaultSchema
Schema holding the history table
baselineVersion
Version used by baseline
sqlMigrationPrefix / sqlMigrationSeparator
Change naming defaults
connectRetries
Retry connecting to the DB
[flyway.placeholders] name = "value"
Define placeholders
beforeMigrate.sql / afterMigrate.sql
Callback scripts in locations
-X
Debug output
-outputType=json
Machine-readable output
-outputFile=<file>
Also write output to a file
-q
Quiet output

Recipes

First migration
flyway init -projectName=app -databaseType=PostgreSqlflyway add -description=create_users# edit the new file in sql/flyway infoflyway migrate
Adopt an existing database
flyway baseline -baselineVersion=1 -environment=prod# add V2__... and laterflyway migrate -environment=prod
Recover from a failed script
flyway info# fix leftovers by hand, fix the scriptflyway repairflyway migrate
CI deployment
flyway validate -environment=ciflyway migrate -environment=ci -outputType=jsonflyway info -infoOfState=pending