Migrate from Flyway
Convert a Flyway directory, and choose what a repeatable migration becomes, a one-time migration or a Ptah repeatable migration.
Flyway names a file by what it is: V for a versioned migration, U for its
undo script, R for a repeatable that re-runs whenever its body changes. The
first two convert cleanly. The third converts in one of two ways. By default
it becomes a one-time migration, which changes what editing it means. With
--repeatables on-change it becomes a Ptah repeatable migration, which keeps
the Flyway contract. This page runs both.
What you need
Section titled “What you need”- A
ptahbinary on yourPATH. Install Ptah if necessary. - A terminal and about ten minutes.
No database server, Docker, or Go toolchain is required.
Convert the directory
Section titled “Convert the directory”Build the Flyway directory
Section titled “Build the Flyway directory”One versioned migration, one with a dotted version and an undo script, and one repeatable.
mkdir -p ptah-from-flyway/legacycd ptah-from-flywaycat > legacy/V1__create_users.sql <<'SQL'CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL);SQLcat > legacy/V1.1__add_email_index.sql <<'SQL'CREATE UNIQUE INDEX users_email_idx ON users (email);SQLcat > legacy/U1.1__add_email_index.sql <<'SQL'DROP INDEX users_email_idx;SQLcat > legacy/R__user_count_view.sql <<'SQL'CREATE VIEW user_count AS SELECT COUNT(*) AS total FROM users;SQLNew-Item -ItemType Directory ptah-from-flyway/legacy | Out-NullSet-Location ptah-from-flyway@'CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL);'@ | Set-Content legacy/V1__create_users.sql@'CREATE UNIQUE INDEX users_email_idx ON users (email);'@ | Set-Content legacy/V1.1__add_email_index.sql@'DROP INDEX users_email_idx;'@ | Set-Content legacy/U1.1__add_email_index.sql@'CREATE VIEW user_count AS SELECT COUNT(*) AS total FROM users;'@ | Set-Content legacy/R__user_count_view.sqlRun the import
Section titled “Run the import”ptah migrations import --source-dir ./legacy --migrations-dir ./migrationsExpected output on standard output:
Wrote 6 migration file(s) to ./migrationsWrote ./migrations/ptah.sum 0000000001_v1_create_users.up.sql 0000000001_v1_create_users.down.sql 0000000002_v1_1_add_email_index.up.sql 0000000002_v1_1_add_email_index.down.sql 0000000003_repeatable_user_count_view.up.sql 0000000003_repeatable_user_count_view.down.sqlFour files became six, and every name changed. Read them before going on.
What Flyway’s file kinds became
Section titled “What Flyway’s file kinds became”V1.1__ flattened to version 2. Dotted versions have no place in Ptah’s
ten-digit version slots, so the order is kept and the original spelling moves
into the description: v1_1_add_email_index.
U1.1__ became the down file of migration 2. An undo script is a rollback,
which is exactly what Ptah’s down file is, so this one converts without losing
anything.
R__ became version 3, ordered after every versioned migration. That is
the default conversion, and the one that changes meaning. The next sections show
what it costs, and Keep the repeatable runs the other
conversion.
Apply the result
Section titled “Apply the result”Validate the sealed directory
Section titled “Validate the sealed directory”ptah migrations validate --dir ./migrationsExpected output on standard output:
OK: migrations directory matches ptah.sumApply the converted directory
Section titled “Apply the converted directory”ptah migrations up --db-url sqlite://app.db --migrations-dir ./migrationsExpected output includes, on standard output:
Current version: 0Total migrations: 3Pending migrations: 3Progress records on standard error carry timestamps and correlation IDs, so this page does not copy those volatile fields.
Verify the recorded state
Section titled “Verify the recorded state”ptah migrations status --db-url sqlite://app.db --migrations-dir ./migrationsExpected output includes, on standard output:
Current Version: 3Total Migrations: 3Applied Migrations: 3Pending Migrations: 0What the repeatable costs now
Section titled “What the repeatable costs now”In Flyway, editing R__user_count_view.sql and redeploying re-runs it. Try the
same thing here.
The edit is refused
Section titled “The edit is refused”Change the view the way you would have in Flyway:
cat > migrations/0000000003_repeatable_user_count_view.up.sql <<'SQL'CREATE VIEW user_count AS SELECT COUNT(*) AS total, MIN(id) AS first_id FROM users;SQL@'CREATE VIEW user_count AS SELECT COUNT(*) AS total, MIN(id) AS first_id FROM users;'@ | Set-Content migrations/0000000003_repeatable_user_count_view.up.sqlptah migrations validate --dir ./migrationsExpected output on standard error:
migration directory does not match ptah.sum: changed: 0000000003_repeatable_user_count_view.up.sqlPtah checksums every migration, and an applied one is not meant to change.
Re-hashing seals the directory and changes nothing else
Section titled “Re-hashing seals the directory and changes nothing else”ptah migrations hash --dir ./migrationsExpected output on standard output:
Wrote ./migrations/ptah.sum6 migration file(s) hashedSealing describes the files to each other. The database separately recorded the checksum of what version 3 was when it ran, and that record is untouched:
ptah migrations status --db-url sqlite://app.db --migrations-dir ./migrationsExpected output includes, on standard output:
Status: ❌ Modified migration detectedSo the apply refuses rather than doing nothing:
ptah migrations up --db-url sqlite://app.db --migrations-dir ./migrationsThe refusal names version 3 and both checksums: stored is what ran, current
is what the file says now. This is the contract a Flyway repeatable loses.
Editing and redeploying is not how you change that view here. It does not
re-run the view, and it stops the whole directory from applying until the file
carries the bytes the database recorded.
Getting out of this state has an order: put the file back as it was, which clears the refusal, and then add a new migration carrying the change. A new migration on its own cannot run, because the refusal precedes every migration in the directory.
That new migration each time the definition changes is what replaces the repeatable in a directory imported this way. If the object is one you would rather declare than migrate, a view is also something direct schema changes converge for you.
Keep the repeatable
Section titled “Keep the repeatable”Import the same source again with --repeatables on-change, into a directory
of its own:
ptah migrations import --source-dir ./legacy --migrations-dir ./migrations-on-change --repeatables on-changeExpected output on standard output:
Wrote 5 migration file(s) to ./migrations-on-changeWrote ./migrations-on-change/ptah.sum 0000000001_v1_create_users.up.sql 0000000001_v1_create_users.down.sql 0000000002_v1_1_add_email_index.up.sql 0000000002_v1_1_add_email_index.down.sql R__user_count_view.sqlThe repeatable is now R__user_count_view.sql: one file with no version and no
down file. On an engine that does not run repeatable migrations (see
Repeatable migrations), ptah migrations up
refuses the directory, so import with the default there. Apply the directory to a database of its own:
ptah migrations up --db-url sqlite://on-change.db --migrations-dir ./migrations-on-changeExpected output includes, on standard output:
Database is now at version: 2Ran 1 repeatable migrations:Edit it and redeploy
Section titled “Edit it and redeploy”Change the view the way you would have in Flyway. The file now runs again against a database that already holds the view, so it drops the view first:
cat > migrations-on-change/R__user_count_view.sql <<'SQL'DROP VIEW IF EXISTS user_count;CREATE VIEW user_count AS SELECT COUNT(*) AS total, MIN(id) AS first_id FROM users;SQL@'DROP VIEW IF EXISTS user_count;CREATE VIEW user_count AS SELECT COUNT(*) AS total, MIN(id) AS first_id FROM users;'@ | Set-Content migrations-on-change/R__user_count_view.sqlThe file changed, so seal the directory again:
ptah migrations hash --dir ./migrations-on-changeExpected output on standard output:
Wrote ./migrations-on-change/ptah.sum5 migration file(s) hashedRedeploy:
ptah migrations up --db-url sqlite://on-change.db --migrations-dir ./migrations-on-changeExpected output includes, on standard output:
Ran 1 repeatable migrations:The run executed the edited view and recorded the digest of the new content, so the next run has nothing to do:
ptah migrations status --db-url sqlite://on-change.db --migrations-dir ./migrations-on-changeExpected output includes, on standard output:
Repeatable Migrations: 1Status: ✅ Database is up to dateRepeatable migrations covers the order they run in, what happens when one fails, and what they refuse.
Clean up
Section titled “Clean up”cd ..rm -rf ptah-from-flywaySet-Location ..Remove-Item -Recurse -Force ptah-from-flywayWhere this leaves you
Section titled “Where this leaves you”Flyway’s callbacks, placeholders and flyway.conf have no destination in a
migration directory and are not converted. Read them once before deleting the
source directory; a placeholder in particular may be carrying an environment
difference the converted SQL now hard-codes.
If a database already has Flyway’s flyway_schema_history table, do not run
ptah migrations up against it. Record the history as already applied first:
see Adopt an existing database. A read-only
check beside Flyway
leaves that table out.
Import an existing migration directory covers the other source tools and the format table.