Migrate from Flyway

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 cannot, because Ptah’s format has no reapply semantics to convert it into, and this page is mostly about what that means for you.

What you need

• A ptah binary on your PATH. Install Ptah if necessary.
• A terminal and about eight minutes.

No database server, Docker, or Go toolchain is required.

Convert the directory

Build the Flyway directory

One versioned migration, one with a dotted version and an undo script, and one repeatable.

Bash

mkdir -p ptah-from-flyway/legacy
cd ptah-from-flyway
cat > legacy/V1__create_users.sql <<'SQL'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
SQL
cat > legacy/V1.1__add_email_index.sql <<'SQL'
CREATE UNIQUE INDEX users_email_idx ON users (email);
SQL
cat > legacy/U1.1__add_email_index.sql <<'SQL'
DROP INDEX users_email_idx;
SQL
cat > legacy/R__user_count_view.sql <<'SQL'
CREATE VIEW user_count AS SELECT COUNT(*) AS total FROM users;
SQL

PowerShell

New-Item -ItemType Directory ptah-from-flyway/legacy | Out-Null
Set-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.sql

Run the import

ptah migrations import --source-dir ./legacy --migrations-dir ./migrations

Expected output on standard output:

Wrote 6 migration file(s) to ./migrations
Wrote ./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.sql

Four files became six, and every name changed. Read them before going on.

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 conversion that changes meaning, and the rest of this page is about it.

Apply the result

Validate the sealed directory

ptah migrations validate --dir ./migrations

Expected output on standard output:

OK: migrations directory matches ptah.sum

Apply the converted directory

ptah migrations up --db-url sqlite://app.db --migrations-dir ./migrations

Expected output includes, on standard output:

Current version: 0
Total migrations: 3
Pending migrations: 3

Progress records on standard error carry timestamps and correlation IDs, so this page does not copy those volatile fields.

Verify the recorded state

ptah migrations status --db-url sqlite://app.db --migrations-dir ./migrations

Expected output includes, on standard output:

Current Version: 3
Total Migrations: 3
Applied Migrations: 3
Pending Migrations: 0

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

Change the view the way you would have in Flyway:

Bash

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

PowerShell

@'
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.sql

ptah migrations validate --dir ./migrations

Expected output on standard error:

migration directory does not match ptah.sum:
  changed: 0000000003_repeatable_user_count_view.up.sql

Ptah checksums every migration, and an applied one is not meant to change.

Re-hashing seals the directory and changes nothing else

ptah migrations hash --dir ./migrations

Expected output on standard output:

Wrote ./migrations/ptah.sum
6 migration file(s) hashed

Sealing 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 ./migrations

Expected output includes, on standard output:

Status: ❌ Modified migration detected

So the apply refuses rather than doing nothing:

ptah migrations up --db-url sqlite://app.db --migrations-dir ./migrations

The 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, and it is how every other object in a versioned directory is already handled. If the object is one you would rather declare than migrate, a view is also something direct schema changes converge for you.

Clean up

Bash

cd ..
rm -rf ptah-from-flyway

PowerShell

Set-Location ..
Remove-Item -Recurse -Force ptah-from-flyway

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.

Import an existing migration directory covers the other source tools and the format table.