Sqitch vs Flyway: Dependency-Based vs Version-Ordered Migrations

Two feature branches each added a migration numbered V58. One merged first; the other now fails Flyway validation with a version conflict, and renumbering it to V59 means its checksum changes on every database where someone already ran it locally. A colleague points out that Sqitch would not have this problem, because it orders changes by declared dependencies rather than by version numbers, and every change ships with its own revert and verify script. That is true, and it is also a different operational model with its own costs. This guide compares the two approaches on the questions that matter for zero-downtime delivery — ordering under parallel development, rollback, verification and tooling fit — so you can choose deliberately. It extends Migration Tool Comparison.

Symptom / Error Signatures

Teams start comparing the two when they hit problems like these:

• Flyway: Validate failed: Detected resolved migration not applied to database: 58 or Found more than one migration with version 58 after a merge. The outOfOrder setting helps but makes environments apply changes in different orders.
• Flyway Community lacks undo migrations (U files require a paid edition), so rollback relies on hand-written forward fixes.
• No built-in way to verify that a migration’s effect is present, beyond the history table saying it ran.
• Sqitch, for teams already using it: plan file conflict in git on every merge, because every branch appends to the same sqitch.plan lines, and changes that must be reworked with sqitch rework confuse newcomers.

Root Cause Analysis

The tools answer “in what order do changes apply?” differently. Flyway sorts migrations by version number; a database is described by the highest version applied, and every environment applies the same total order. That makes Flyway easy to reason about and easy to integrate — a directory of SQL files, a JVM or native CLI, plugins for Maven, Gradle and Spring Boot. The cost is that parallel branches compete for the next number.

Sqitch orders changes by a plan file, sqitch.plan, in which each change is named and may declare requires and conflicts on other changes. Sqitch refuses to deploy a change whose requirements are not deployed. Each change is three scripts: deploy/ makes the change, revert/ undoes it, and verify/ proves it is present, typically by selecting from the new object in a way that fails if it is missing. Sqitch records deployments in a registry schema and uses the plan’s hashes to detect tampering. Parallel branches do not collide on numbers, but they do both append to the plan file, which produces a textual merge conflict that is usually trivial to resolve.

Immediate Mitigation

If you are stuck on a Flyway version collision today:

1. Renumber the unmerged migration before it reaches any shared environment. A migration that has only run on developer laptops can be renumbered; developers reset their local databases. Use timestamps for new versions (for example V20260918113000__add_region.sql) so collisions stop happening.

2. If both versions already ran somewhere shared, do not renumber. Enable outOfOrder=true for that deploy only, apply the missing migration, and record the decision. Out-of-order application is safe only when the two migrations are independent — they touch different objects — which the review must confirm.

# Shell · Flyway CLI · one-off deploy to apply an older-numbered migration that was skipped
# WARNING: only safe when the skipped migration is independent of migrations already applied.
flyway -url="$JDBC_URL" -user=migrator -outOfOrder=true migrate
flyway -url="$JDBC_URL" -user=migrator info   # confirm both versions show Success

If you are evaluating Sqitch, try it on one service first:

3. Scaffold a change with dependencies and all three scripts.

# Shell · repository root · Sqitch CLI installed
# WARNING: sqitch init writes sqitch.conf and sqitch.plan; commit both.
sqitch init app --engine pg
sqitch add orders_table -n "Create orders table"
sqitch add add_region --requires orders_table -n "Add region to orders"

4. Write deploy, revert and verify for the change. The verify script should fail when the change is absent.

-- PostgreSQL · deploy/add_region.sql · Sqitch runs each script as written; this one manages its own transaction
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS region text;
COMMIT;
-- revert/add_region.sql:  BEGIN; ALTER TABLE orders DROP COLUMN IF EXISTS region; COMMIT;
-- verify/add_region.sql:  SELECT region FROM orders WHERE false;   -- errors if the column is missing

Permanent Fix / Long-Term Pattern

Whichever tool you choose, the zero-downtime rules are identical: every change is additive-first, locks are bounded with lock_timeout, non-transactional statements live alone, and destructive steps wait for the contract phase of Expand and Contract Methodology. Neither tool enforces these; your review and linting do.

With Flyway, eliminate collisions structurally by using timestamp versions, and replace the missing undo feature with the discipline in writing safe down migrations for automated rollback — or accept forward-only fixes and design for them. With Sqitch, treat revert scripts as production code: test that deploy → verify → revert → deploy round-trips in CI, and remember that a revert which drops a column destroys data written since deploy, so production rollbacks should still prefer forward fixes once real data exists. Sqitch’s verify scripts are worth copying even into Flyway projects as post-deploy checks. If you need to switch tools, the approach in migrating from Flyway to Liquibase without downtime — baseline the new tool at the old tool’s end state — applies equally to Sqitch.

Verification Checklist

• New Flyway migrations use timestamp versions, and CI rejects duplicate versions before merge.
• Any use of outOfOrder is limited to a single, documented deploy with independent migrations.
• Sqitch changes declare requires for every object they depend on, and sqitch verify passes after each deploy.
• CI runs a deploy, revert, re-deploy round trip for every new Sqitch change.
• Zero-downtime rules (lock timeouts, additive-first, separated non-transactional steps) are linted regardless of the tool.

Frequently Asked Questions

Is Sqitch better for zero-downtime migrations than Flyway? Neither tool makes DDL safer by itself. Both execute the SQL you write; zero-downtime behaviour comes from the statements and their sequencing. Sqitch’s advantages are dependency-based ordering and built-in revert and verify scripts; Flyway’s are simplicity and ecosystem integration.

Can Flyway express dependencies between migrations? Not directly. Order is the version number. Teams approximate dependencies by keeping related changes in one migration or by reviewing order carefully, and use timestamp versions to reduce collisions between branches.

Does Sqitch wrap scripts in transactions automatically? No. Each deploy, revert and verify script is run as written, so you include BEGIN and COMMIT where you want atomicity and omit them for statements such as CREATE INDEX CONCURRENTLY that must run outside a transaction.

Should production rollbacks use Sqitch revert scripts? Only when the revert is non-destructive or no meaningful data has been written since the deploy. Reverting an added column drops whatever was written to it, so after real traffic, a forward fix is usually safer than a revert.