knex-ptosc-plugin

AI Disclosure

An LLM (GPT-5) was used heavily in the creation of this plugin.

WARNING

This code is a very early swing at extending Knex to use pt-online-schema-change for DB migrations. Use in production at your own risk.

Introduction

A Knex helper for running ALTER TABLE operations online using Percona Toolkit's pt-online-schema-change (pt-osc).

This plugin offers an alternative to the normal schema builder, routing the .alterTable() builder through pt-online-schema-change so changes can be executed with minimal locking and downtime. It also exposes alterTableWithPtoscRaw for executing raw ALTER TABLE statements. For operations that can be run INSTANT in the engine, pt-osc will not be invoked; instead knex will handle it.

Github Repo: https://github.com/gwinans/knex-ptosc-plugin

Test App: https://github.com/gwinans/kpp-test-app

Please, come contribute! Star the project!

Features

• Safe execution: No shell concatenation, no command injection — arguments are passed directly to the pt-osc binary.
• Password safety: The database password is passed via MYSQL_PWD environment variable (never on the command line or in logs).
• Atomic migration lock: Uses Knex’s migrations lock row (knex_migrations_lock by default) to prevent concurrent schema changes. Table names can be customized with migrationsTable and migrationsLockTable. Both tables must exist before running migrations; otherwise an error is thrown.
• Dry-run first: Always runs a pt-osc --dry-run before executing.
• Full ALTER support: Works with Knex’s .alterTable() builder or raw ALTER TABLE strings via alterTableWithPtoscRaw.
• Respects Knex bindings: Correctly interpolates values from .toSQL() output.
• Instant alters when possible: Attempts native ALTER TABLE ... ALGORITHM=INSTANT and falls back to pt-osc when unsupported or when MySQL returns error 4092 ("Maximum row versions").
• Native index operations: ADD INDEX and DROP INDEX statements run directly via Knex without pt-osc.

Servers running MySQL 5.6 or 5.7 skip the instant-alter attempt and always use pt-online-schema-change. This will not be changed - MySQL 5.6 and 5.7 are long-past End-of-Life.

Requirements

• Node.js 16+
• Knex configured for MySQL or MariaDB
• Percona Toolkit installed and pt-online-schema-change available in $PATH
• MySQL user with appropriate privileges, as required by Knex.

Installation

npm install knex-ptosc-plugin

Options

| Option | Type | Default | Description | | --- | --- | --- | --- | | password | string | from Knex connection | Override DB password; passed via MYSQL_PWD env | | maxLoad | number | undefined | Passed to --max-load (e.g. Threads_connected=150) | | maxLoadMetric | string | 'Threads_running' | Metric name used in --max-load (e.g. Threads_connected) | | criticalLoad | number | undefined | Passed to --critical-load (e.g. Threads_running=50) | | criticalLoadMetric | string | 'Threads_running' | Metric name used in --critical-load (e.g. Threads_running) | | alterForeignKeysMethod | 'auto' \| 'rebuild_constraints' \| 'drop_swap' \| 'none' | 'auto' | Passed to --alter-foreign-keys-method | | ptoscPath | string | 'pt-online-schema-change' | Path to pt-osc binary | | forcePtosc | boolean | false | Skip the instant-alter attempt and always run pt-osc | | ptoscMinRows | number | 0 | Minimum row count required to use pt-osc; below this, ALTER runs natively | | analyzeBeforeSwap | boolean | true | --analyze-before-swap or --noanalyze-before-swap | | checkAlter | boolean | true | --check-alter or --nocheck-alter | | checkForeignKeys | boolean | true | --check-foreign-keys or --nocheck-foreign-keys | | checkInterval | number | undefined | Passed to --check-interval | | checkPlan | boolean | true | --check-plan or --nocheck-plan | | checkReplicationFilters | boolean | true | --check-replication-filters or --nocheck-replication-filters | | checkReplicaLag | boolean | false | Adds --check-replica-lag | | chunkIndex | string | undefined | Passed to --chunk-index | | chunkIndexColumns | number | undefined | Passed to --chunk-index-columns | | chunkSize | number | 1000 | Passed to --chunk-size | | chunkSizeLimit | number | 4.0 | Passed to --chunk-size-limit | | chunkTime | number | 0.5 | Passed to --chunk-time | | dropNewTable | boolean | true | --drop-new-table or --nodrop-new-table | | dropOldTable | boolean | true | --drop-old-table or --nodrop-old-table | | dropTriggers | boolean | true | --drop-triggers or --nodrop-triggers | | checkUniqueKeyChange | boolean | true | --check-unique-key-change or --nocheck-unique-key-change | | maxLag | number | 25 | Passed to --max-lag | | maxBuffer | number | 10485760 | child_process.execFile maxBuffer in bytes | | logger | { log: Function, error: Function } | console | Override default logging methods | | onProgress | (pct: number, eta?: string) => void | undefined | Callback for progress percentage and optional ETA parsed from output; logs include pt-osc ETA when available | | statistics | boolean | false | Adds --statistics; log and collect internal pt-osc counters | | onStatistics | (stats: Record<string, number>) => void | undefined | Invoked with parsed statistics object when statistics is true | | migrationsTable | string | 'knex_migrations' | Overrides migrations table name used for lock checks | | migrationsLockTable | string | 'knex_migrations_lock' | Overrides migrations lock table name used when acquiring lock | | timeoutMs | number | 30000 | Timeout in ms when acquiring migration lock | | intervalMs | number | 500 | Delay between lock retries in ms |

Statistics example

When statistics: true, pt-online-schema-change prints internal counters at the end of the run. These are parsed into an object, logged via the provided logger, and returned (or sent to onStatistics). Example output:

# Event          Count
# =====          =====
# chunk-size     1000
# copy_rows      12345

Debugging

The full pt-online-schema-change command is logged before execution regardless of DEBUG. Set DEBUG=knex-ptosc-plugin to enable verbose, line-by-line output.

DEBUG=knex-ptosc-plugin knex migrate:latest

Without DEBUG, only high-level progress percentages are logged. If pt-online-schema-change exits with an error, its full stdout and stderr are logged regardless of DEBUG, including any trailing lines that lack a newline.

Testing

Run npm test to execute linting and unit tests. Run npm run test:coverage to include coverage reporting. Continuous integration also clones the kpp-test-app and runs its migrations against a MySQL instance (root/test, database ptosc) to verify end-to-end behavior.

Safety & Security

• No shell: Commands are executed via child_process.spawn with an args array, so backticks, semicolons, or other shell metacharacters in table names or SQL will not be interpreted by a shell.
• Password is hidden: Never appears in process list, logs, or command history.
• Atomic locks: Lock acquisition uses UPDATE ... WHERE is_locked=0 to avoid stealing locks from another process.
• Dry-run first: Always verifies the migration before execution.

Example Migration

The alterTableWithPtosc helper accepts a table name and a schema builder callback, ensuring all changes use Knex's builder API.

CommonJS

const { alterTableWithPtosc } = require('knex-ptosc-plugin');

exports.up = function (knex) {
  return alterTableWithPtosc(
    knex,
    'widgets',
    (table) => {
      table.bigInteger('qty').alter();
    },
    { chunkSize: 500 }
  );
};

exports.down = function (knex) {
  return alterTableWithPtosc(
    knex,
    'widgets',
    (table) => {
      table.integer('qty').alter();
    },
    { chunkSize: 500 }
  );
};

ESM

import { alterTableWithPtosc } from 'knex-ptosc-plugin';

export function up(knex) {
  return alterTableWithPtosc(
    knex,
    'widgets',
    (table) => {
      table.bigInteger('qty').alter();
    },
    { chunkSize: 500 }
  );
}

export function down(knex) {
  return alterTableWithPtosc(
    knex,
    'widgets',
    (table) => {
      table.integer('qty').alter();
    },
    { chunkSize: 500 }
  );
}

Raw SQL

import { alterTableWithPtoscRaw } from 'knex-ptosc-plugin';

export function up(knex) {
  return alterTableWithPtoscRaw(
    knex,
    'ALTER TABLE widgets ALTER COLUMN qty TYPE BIGINT',
    { statistics: true }
  );
}

Advanced Usage

Handling Foreign Keys

pt-online-schema-change may refuse to run when tables have foreign key constraints. Use alterForeignKeysMethod: 'drop_swap' or 'rebuild_constraints' to control how pt-osc manages them:

return alterTableWithPtosc(
  knex,
  'widgets',
  (table) => {
    table.dropColumn('category_id');
  },
  { alterForeignKeysMethod: 'drop_swap' } // or 'rebuild_constraints'
);

Replica Lag Safeguard

Enable checkReplicaLag: true to have pt-osc abort if replicas fall behind, useful in production environments with read replicas:

return alterTableWithPtosc(
  knex,
  'widgets',
  (table) => {
    table.string('status').defaultTo('pending');
  },
  { checkReplicaLag: true }
);

Capture Statistics

Collect pt-osc statistics by setting statistics: true and handling them in the onStatistics callback:

return alterTableWithPtosc(
  knex,
  'widgets',
  (table) => {
    table.bigInteger('qty').alter();
  },
  {
    statistics: true,
    onStatistics: (stats) => {
      console.log('pt-osc stats', stats);
    },
  }
);

Troubleshooting

• pt-online-schema-change: command not found
  The plugin runs which pt-online-schema-change during initialization and will throw if the binary cannot be found. Make sure Percona Toolkit is installed and in your PATH, or pass ptoscPath in options.

• Permission errors
  Verify you followed the installation instructions for Knex.

• Foreign key issues
  Use alterForeignKeysMethod: 'rebuild_constraints' or 'drop_swap' if pt-osc refuses to run due to FK constraints. Be careful with self-referencing FKs. Even ptosc can't handle them correctly.

License

This project is licensed under the MIT License.

© 2025 Geoff Winans