Transactional Migrations vs. Disabling Transactions: Which Knex Strategy Avoids ALTER TABLE Errors on MySQL 5.7+?

The premise needs correcting first

MySQL 5.7+ does not reject ALTER TABLE inside a transaction — it executes the statement and issues an implicit commit. MySQL simply has no transactional DDL. So Knex's default per-migration transaction never gave you atomic rollback on MySQL in the first place; it only provides real atomicity on PostgreSQL. The choice isn't "transactions vs. no transactions" on MySQL — both paths leave partial DDL applied on failure. Disabling transactions mainly makes the bookkeeping honest and matches behavior across both databases.

Answers to your three questions

1. Failure modes when a migration aborts mid-run

• Earlier ALTERs are already committed (this happens on MySQL even with transactions enabled).
• The knex_migrations table may not record the batch as complete, so Knex considers the migration pending.
• Re-running fails with duplicate column/table errors because the first half already applied.
• If you mix transactional and non-transactional migrations in one batch, the migration log can end up inconsistent with the actual schema.

2. Can Knex detect and retry failed DDL?

No. Knex has no compensation or automatic retry mechanism for partial DDL. Recovery is manual: either write idempotent migrations using schema.hasTable() / schema.hasColumn() guards, or clean up the half-applied objects before re-running.

3. Safe pattern for large ALTERs with zero downtime

Use expand-and-contract, which sidesteps atomicity entirely by making every step individually safe:

1. Expand: add new nullable columns or tables. Nothing existing breaks.
2. Backfill data in small batches.
3. Deploy code that reads/writes both old and new structures.
4. Contract: drop old columns in a later migration, after the old code is fully gone.

Each migration stays small, so a failure leaves a resume point rather than a broken schema. For big table rewrites, prefer ALGORITHM=INPLACE where your MySQL version supports it, or run pt-online-schema-change / gh-ost outside Knex — Knex doesn't orchestrate online schema changes, and COPY-algorithm ALTERs can hold metadata locks for a long time.

Practical safeguards

• Keep migrations small and idempotent; snapshot the database before applying.
• Run migrations as a separate step before deploying new application code, so a failed migration never coincides with incompatible code.
• Don't trust rollback tests that only ran on PostgreSQL — they prove nothing for MySQL.

Verify on your setup

Knex's config keys (disableTransactions vs. per-migration options) have shifted across versions, so check your installed version's docs. Then confirm the behavior empirically: create a test migration with two ALTERs where the second intentionally fails, run it against MySQL with default settings, and inspect whether the first ALTER persisted and what knex_migrations recorded. Rehearse the expand-and-contract sequence on staging with production-like data volume to measure lock duration per ALTER.

One detail that could change this advice: your actual table sizes. If your largest tables are small (under a few GB), plain ALTERs with expand-and-contract are enough; the external online-schema-change tooling only becomes necessary at scale.