MySQL and MSSQL migration safety when Knex cannot wrap DDL in a transaction

Answer to the Core Question

1. Mark migrations with transaction: false on MySQL and, if you want explicit visibility, on MSSQL. This makes the non‑atomic nature obvious to anyone reading the migration file and prevents accidental reliance on a transaction that never exists.

2. Rely on a pre‑migration backup plus a hand‑crafted down script for reliable recovery. Knex will not record a migration until the up promise resolves, so a partial failure leaves the schema ahead of the knex_migrations table. The only guaranteed way to roll back is either a point‑in‑time restore or a manual compensating change.

3. Write migrations to be idempotent. If a migration can be safely retried after a partial failure, you can re‑run knex migrate:latest without invoking down. Use IF NOT EXISTS checks, ALTER TABLE IF EXISTS, or query the information schema before applying changes.

Why transaction: false Is Useful

• MySQL’s DDL statements automatically commit; wrapping them in a BEGIN … COMMIT block does nothing.
• MSSQL supports transactional DDL, but Knex’s default behavior depends on the driver (e.g., tedious may wrap each migration in a transaction). Explicitly disabling the wrapper removes the illusion of atomicity.
• Team members see the flag and understand that a failure will leave the database state inconsistent with the migration log.

Safe Recovery Path

1. Take a full backup. Prefer a point‑in‑time snapshot or logical dump before running any migration script.
2. Run the migration. If it fails mid‑file, the first part may already have been applied.
3. Determine the partial state. Inspect information_schema or sys.tables to see which objects exist.
4. Apply a hand‑crafted down script. This script should reverse only the changes that succeeded. It can be as simple as dropping a newly created table or removing a column that was added.
5. Mark the migration as completed. If the down script was applied, you can manually insert a record into knex_migrations or delete the failed entry to keep the log in sync.

Idempotent Migration Example

exports.up = async function(knex) {
  // Create table only if it does not exist
  await knex.schema.raw(
    `CREATE TABLE IF NOT EXISTS users (
      id INT PRIMARY KEY,
      name VARCHAR(255)
    );`
  );

  // Add column only if missing
  const hasColumn = await knex.schema.hasColumn('users', 'email');
  if (!hasColumn) {
    await knex.schema.alterTable('users', table => {
      table.string('email').unique();
    });
  }
};

exports.down = async function(knex) {
  // Drop column only if it exists
  const hasColumn = await knex.schema.hasColumn('users', 'email');
  if (hasColumn) {
    await knex.schema.alterTable('users', table => {
      table.dropColumn('email');
    });
  }
  // Drop table only if it exists
  const hasTable = await knex.schema.hasTable('users');
  if (hasTable) {
    await knex.schema.dropTable('users');
  }
};

This pattern guarantees that running the migration twice produces no errors and that a failed run can be retried safely.

When to Use transaction: false on MSSQL

If your team prefers a strict rule that all migrations must be atomic, you can keep the default transaction wrapper on MSSQL. However, if you want developers to explicitly acknowledge that a migration may leave the database in an intermediate state, add the flag.

Missing Diagnostic Detail

To give a more tailored recommendation, could you confirm which Knex driver and version you are using for MySQL and MSSQL? Different drivers handle transactions slightly differently.