Does Knex.js provide a mechanism to handle partial DDL failures in non-transactional dialects?

Question

Migration State and DDL Rollbacks
Knex.js manages migration state via a migrations table and a lock mechanism to prevent concurrent execution. For dialects supporting transactional DDL, such as PostgreSQL, Knex wraps migrations in transactions to ensure atomicity. However, MySQL and MariaDB do not support rolling back DDL statements.

Consistency Constraints
When a migration fails on a non-transactional dialect, the migration is not recorded as applied in the database. Despite this, any DDL statements executed prior to the failure remain committed to the schema. This creates a discrepancy between the actual database state and the migration history recorded by Knex.

Because knex migrate:rollback relies on the down method of the last successful batch, it may not target the partial changes left by a failed migration attempt.

Which strategy is recommended to detect and resolve partial schema changes before re-running a failed migration?
  Does Knex provide a built-in way to force a transactional wrap on dialects that do not natively support it, or must this be handled via manual idempotency in the migration scripts?

Tasadduq Burney · Answer

Answer
No, Knex.js does not provide a built‑in mechanism to detect or repair partially applied DDL when a migration fails on a dialect that does not support transactional DDL (e.g., MySQL, MariaDB).
Confirmed facts

Knex records each successfully applied migration in the knex_migrations table and uses a lock table to prevent concurrent runs.
  By default Knex wraps a migration in a transaction only when the dialect supports transactional DDL; there is no option to force a transaction on MySQL/MariaDB because their storage engines implicitly commit each DDL statement.
  The migrate:rollback command only reverses the last recorded batch; it never targets objects that were created before a failed migration attempt.

Likely explanation of the discrepancy
On MySQL/MariaDB each DDL statement (CREATE, ALTER, DROP) is auto‑committed immediately. If a migration throws an error after some statements have run, those statements remain in the schema while the migration is never marked as applied in knex_migrations. A subsequent migrate:latest therefore runs the same up against an already‑mutated schema, which can cause duplicate‑object errors or leave the schema in a half‑applied state.
Steps to detect and resolve partial schema changes

Verify that the failed migration is not recorded: npx knex migrate:list or query SELECT * FROM knex_migrations;.
  Inspect the live schema for objects that the migration intended to create (e.g., SHOW TABLES;, SHOW COLUMNS FROM your_table; or query information_schema).
  Based on the migration’s up method, drop or tolerate any partially created objects, or adjust the migration to be idempotent before re‑running.
  Make the migration idempotent: guard table creation with knex.schema.hasTable, column addition with hasColumn, and use raw SQL like CREATE TABLE IF NOT EXISTS where supported.
  After repairing the schema, re‑run npx knex migrate:latest and confirm the migration appears as a new batch in knex_migrations.

Missing diagnostic detail that changes the recommendation
To know exactly which object to drop or keep, you need to identify the specific DDL statement that failed. If the error log shows the failing statement, you can target only that object; otherwise you must audit every object the migration attempts to create before re‑running.

Does Knex.js provide a mechanism to handle partial DDL failures in non-transactional dialects?

Migration State and DDL Rollbacks

Consistency Constraints

1 answer

Answer

Confirmed facts

Likely explanation of the discrepancy

Steps to detect and resolve partial schema changes

Missing diagnostic detail that changes the recommendation

Your perspective belongs here.

0 question comments