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

Transactional Migrations vs. Disabling Transactions

The goal is to deploy schema changes in a small application without downtime. Knex runs migrations inside a transaction by default, ensuring atomicity, but MySQL 5.7+ rejects certain ALTER TABLE statements that cannot be executed transactionally. Setting migrations: { transaction: false } allows those ALTERs to run, but the migration loses atomic rollback, risking a partially applied schema if the script fails mid‑run.

Constraints:

• Zero‑downtime deployment.
• Target database: MySQL 5.7+ (non‑transactional DDL).
• Portability to PostgreSQL (transactional DDL).

Unresolved decision: How to balance atomicity with compatibility, and what safeguards are necessary when disabling transactions?

Questions:

1. What failure modes arise if a migration with transaction: false aborts after some ALTER statements?
2. Can Knex automatically detect and retry failed DDL within a transaction on MySQL?
3. Is there a recommended pattern to split large ALTERs into safe steps while still achieving zero downtime?