{"id":16408,"library":"knex-migrator","title":"Knex Migrator","description":"knex-migrator is a robust database migration tool built on top of knex.js, specifically designed to handle complex schema changes and initializations. Currently at version 5.3.2, it is known for its stability and is actively used in production by platforms like Ghost CMS. Key features include distinct differentiation between database initialization and subsequent migrations, support for a structured database schema, comprehensive rollback capabilities with auto-rollback on error, transactional migrations, and a migration lock mechanism to prevent concurrent execution. It supports MySQL and SQLite3. The tool emphasizes a strict separation of DDL (Data Definition Language) and DML (Data Manipulation Language) statements within migration scripts to ensure atomic operations and prevent implicit commits, which is a critical differentiator for maintaining data integrity during schema evolution.","status":"active","version":"5.3.2","language":"javascript","source_language":"en","source_url":"git@github.com:TryGhost/knex-migrator","tags":["javascript","ghost","migration","knex","knex-migrations","knex migration","knex migrations","bookshelf migration","bookshelf"],"install":[{"cmd":"npm install knex-migrator","lang":"bash","label":"npm"},{"cmd":"yarn add knex-migrator","lang":"bash","label":"yarn"},{"cmd":"pnpm add knex-migrator","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Core database query builder and migration engine. Required for database interaction and schema manipulation. Specific client drivers (e.g., `mysql2`, `sqlite3`) are also needed depending on the database choice.","package":"knex","optional":false},{"reason":"Required if using MySQL as the database client for Knex.","package":"mysql2","optional":true},{"reason":"Required if using SQLite3 as the database client for Knex.","package":"sqlite3","optional":true}],"imports":[{"note":"The primary programmatic interface is typically the default export (a class) to instantiate the migrator. Named imports for the main class are incorrect.","wrong":"import { KnexMigrator } from 'knex-migrator';","symbol":"KnexMigrator","correct":"import KnexMigrator from 'knex-migrator';\n// Or for CommonJS:\nconst KnexMigrator = require('knex-migrator');"},{"note":"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`.","symbol":"CLI Usage","correct":"npx knex-migrator init\nnpx knex-migrator migrate\nnpx knex-migrator rollback"},{"note":"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.","wrong":"export default { /* ... config object ... */ };","symbol":"MigratorConfig.js","correct":"module.exports = { /* ... config object ... */ };"}],"quickstart":{"code":"/* MigratorConfig.js */\nmodule.exports = {\n  database: {\n    client: process.env.DB_CLIENT || 'sqlite3',\n    connection: {\n      filename: process.env.DB_FILENAME || './dev.sqlite3'\n    }\n  },\n  migrationPath: __dirname + '/migrations',\n  currentVersion: '1.0'\n};\n\n/* migrations/init/1-create-users-table.js */\nexports.up = function (knex) {\n  return knex.schema.createTable('users', function (table) {\n    table.increments('id').primary();\n    table.string('name').notNullable();\n    table.string('email').unique().notNullable();\n    table.timestamps(true, true);\n  });\n};\n\nexports.down = function (knex) {\n  return knex.schema.dropTable('users');\n};\n\n// To run:\n// 1. Install `knex` and `knex-migrator`, e.g., `npm install knex sqlite3 knex-migrator`\n// 2. Create `MigratorConfig.js` and `migrations/init/1-create-users-table.js` as above.\n// 3. Run initial migration:\n//    npx knex-migrator init\n// 4. Then run any subsequent migrations (e.g., after adding new migration files to `migrations/versions/1.0/`):\n//    npx knex-migrator migrate","lang":"javascript","description":"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."},"warnings":[{"fix":"Implement replica-aware logic at the application level or use a separate database provisioning tool.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Ensure migrations are run in a single-process, controlled environment, especially when using SQLite.","message":"SQLite databases do not support read locks by default, which can lead to concurrency issues if multiple processes try to run migrations simultaneously.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Always provide a `down` function that logically reverses the changes made by the `up` function in your migration files.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"A previous migration process died or was interrupted, leaving a lock entry in the database.","error":"Error: Migration lock already exists. Please run 'knex-migrator rollback' or delete the lock table manually."},{"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`.","cause":"The database server is not running, is inaccessible, or the connection details in `MigratorConfig.js` are incorrect.","error":"Error: connect ECONNREFUSED 127.0.0.1:<PORT>"},{"fix":"Install the necessary database client driver using `npm install <client_name>` or `yarn add <client_name>`.","cause":"The specified `client` in `MigratorConfig.js` (e.g., 'mysql2', 'sqlite3') does not have its corresponding npm package installed.","error":"Error: Knex client not installed: <client_name>. Install by: npm install <client_name>"},{"fix":"Ensure `MigratorConfig.js` is present in the project root or specify its path using the `--mgpath` CLI option.","cause":"The `MigratorConfig.js` file is missing, misspelled, or located in a directory not discoverable by `knex-migrator`.","error":"Error: No MigratorConfig.js found in the current directory or specified path."}],"ecosystem":"npm"}