Knex Migrator

Common errors

• Error: Migration lock already exists. Please run 'knex-migrator rollback' or delete the lock table manually.

  cause A previous migration process died or was interrupted, leaving a lock entry in the database.
  fix Run `npx knex-migrator rollback --force` to try and clear the lock and potentially revert partially applied migrations, or manually inspect and clear the `migrations_lock` table.

• Error: connect ECONNREFUSED 127.0.0.1:<PORT>

  cause The database server is not running, is inaccessible, or the connection details in `MigratorConfig.js` are incorrect.
  fix Verify that the database server is running and reachable from the application's host, and double-check all connection parameters (host, port, user, password, database name) in `MigratorConfig.js`.

• Error: Knex client not installed: <client_name>. Install by: npm install <client_name>

  cause The specified `client` in `MigratorConfig.js` (e.g., 'mysql2', 'sqlite3') does not have its corresponding npm package installed.
  fix Install the necessary database client driver using `npm install <client_name>` or `yarn add <client_name>`.

• Error: No MigratorConfig.js found in the current directory or specified path.

  cause The `MigratorConfig.js` file is missing, misspelled, or located in a directory not discoverable by `knex-migrator`.
  fix Ensure `MigratorConfig.js` is present in the project root or specify its path using the `--mgpath` CLI option.

Warnings

• gotcha knex-migrator does not support database replicas directly due to limitations in knex.js. Configure your application to handle replicas outside of the migration process.
  Fix: Implement replica-aware logic at the application level or use a separate database provisioning tool.
• gotcha SQLite databases do not support read locks by default, which can lead to concurrency issues if multiple processes try to run migrations simultaneously.
  Fix: Ensure migrations are run in a single-process, controlled environment, especially when using SQLite.
• breaking Mixing DDL (Data Definition Language) and DML (Data Manipulation Language) statements within a single migration script is highly discouraged, especially in MySQL, where DDL statements implicitly commit transactions. This can lead to partial changes and data inconsistency if an error occurs.
  Fix: Separate DDL and DML operations into distinct migration scripts or use transactional blocks carefully for DML within DDL if absolutely necessary and database client supports it without implicit commits.
• gotcha If the migration process terminates unexpectedly (e.g., process crash), the migration lock in the database might not be released. This prevents further migrations until manually cleared.
  Fix: Manually release the lock by running `knex-migrator rollback --force` (if you are sure about the state) or by directly inspecting and clearing the `migrations_lock` table in the database. Always check the database state first.
• gotcha It is highly recommended to implement both `up` (for applying) and `down` (for reverting) functions in every migration script. This ensures a reliable and complete rollback capability.
  Fix: Always provide a `down` function that logically reverses the changes made by the `up` function in your migration files.

Install

• npm install knex-migrator npm
• yarn add knex-migrator yarn
• pnpm add knex-migrator pnpm

Imports

• KnexMigrator
  wrong:

  import { KnexMigrator } from 'knex-migrator';

  correct:

  import KnexMigrator from 'knex-migrator';
  // Or for CommonJS:
  const KnexMigrator = require('knex-migrator');

  The primary programmatic interface is typically the default export (a class) to instantiate the migrator. Named imports for the main class are incorrect.
• CLI Usage

  npx knex-migrator init
  npx knex-migrator migrate
  npx knex-migrator rollback

  Most common usage is via the globally installed `knex-migrator` CLI or `npx` for project-local execution. This doesn't involve a direct JS `import`.
• MigratorConfig.js
  wrong:

  export default { /* ... config object ... */ };

  correct:

  module.exports = { /* ... config object ... */ };

  The configuration file `MigratorConfig.js` is typically a CommonJS module export, though ESM could be supported depending on Node.js environment and explicit configuration. Adhere to CommonJS for compatibility.

Quickstart

This quickstart demonstrates how to set up `knex-migrator` with an SQLite3 database, configure `MigratorConfig.js`, create a basic `init` migration file, and execute the initial migration using the CLI.

/* MigratorConfig.js */
module.exports = {
  database: {
    client: process.env.DB_CLIENT || 'sqlite3',
    connection: {
      filename: process.env.DB_FILENAME || './dev.sqlite3'
    }
  },
  migrationPath: __dirname + '/migrations',
  currentVersion: '1.0'
};

/* migrations/init/1-create-users-table.js */
exports.up = function (knex) {
  return knex.schema.createTable('users', function (table) {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.string('email').unique().notNullable();
    table.timestamps(true, true);
  });
};

exports.down = function (knex) {
  return knex.schema.dropTable('users');
};

// To run:
// 1. Install `knex` and `knex-migrator`, e.g., `npm install knex sqlite3 knex-migrator`
// 2. Create `MigratorConfig.js` and `migrations/init/1-create-users-table.js` as above.
// 3. Run initial migration:
//    npx knex-migrator init
// 4. Then run any subsequent migrations (e.g., after adding new migration files to `migrations/versions/1.0/`):
//    npx knex-migrator migrate