knex-automigrate

Table schema based database migration tool, built on top of knex.js.

Define your table schema once and let knex-automigrate handle CREATE, ALTER, and DROP operations automatically — no numbered migration files needed.

• Written in TypeScript with full type declarations included
• Migration schema file name must start with table_ or view_
• Currently supported dialects for index migration: mysql, mysql2

Installation

$ npm install knex-automigrate

For CLI usage (global install):

$ npm install knex-automigrate -g

Usage

Before (traditional database migration with knex.js)

$ knex migrate:make create_users_table

// 201701010000_create_users_table.js
exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTableIfNotExists('users', function(table) {
      table.increments('user_id').unsigned().comment('PK');
      table.string('email', 128).notNullable().comment('E-Mail');
      table.string('nickname', 128).notNullable().comment('Name');
    })
  ]);
};

$ knex migrate:latest

$ knex migrate:make alter_users_table

// 201701010000_alter_users_table.js
exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.alterTable('users', function(table) {
      table.dropColumn('nickname');
      table.string('email', 64).notNullable().comment('E-Mail').alter();
      table.string('name', 64).notNullable().comment('Name');
    })
  ]);
};

$ knex migrate:latest

Migration files are,

App
　├─ migrations
　│　　├─ 201701010000_create_users_table.js
　│　　└─ 201701010000_alter_users_table.js
　└─ knexfile.js

After (database migration with knex-automigrate)

Migration files are named with table_ or view_ prefix. The prefix determines whether the file defines table schemas or view schemas.

// table_users.js
exports.auto = function(migrator, knex) {
  return [
    migrator('users', function(table) {
      table.increments('user_id').unsigned().comment('PK');
      table.string('email', 64).notNullable().comment('E-Mail');
      table.string('name', 64).notNullable().comment('Name');
    }),
  ];
};

// view_users.js
exports.auto = function(migrator, knex) {
  return [
    migrator('user_information', (view) => {
      // If view.columns() is missing,
      // the columns will default to those defined in the 'select()' statement.
      view.as(knex('users').select('user_id', 'email', 'name'));
    }),
  ];
};

$ knex-automigrate migrate:auto

Migration files are,

App
　├─ migrations
　│　　├─ table_users.js
　│　　└─ view_users.js
　└─ knexfile.js

Simply edit the schema file and run migrate:auto again — columns are added, altered, or dropped automatically to match the definition.

CLI

Usage: knex-automigrate [options] [command]

Commands:
  migrate:auto           Run all migration table schemas.

Options:
  -V, --version      output the version number
  --debug            Run with debugging.
  --safe             Run as safe mode, which does not delete existing columns.
  --knexfile [path]  Specify the knexfile path.
  --cwd [path]       Specify the working directory.
  --env [name]       environment, default: process.env.NODE_ENV || development
  -h, --help         output usage information

Programmatic Usage

const Automigrate = require('knex-automigrate');

await Automigrate({
  config: {
    client: 'mysql2',
    connection: {
      host: '127.0.0.1',
      port: 3306,
      database: 'my_database',
      user: 'root',
      password: null,
    },
    safe: false, // set true to prevent dropping existing columns
  },
  cwd: __dirname,           // directory to scan for table_*.js / view_*.js files
  verbose: true,            // set false to suppress console output
  tables: (migrator, knex) => [
    migrator('users', (table) => {
      table.increments('user_id').unsigned().comment('PK');
      table.string('email', 128).notNullable().comment('E-Mail');
      table.string('name', 64).notNullable().comment('Name');
      table.datetime('created_at').notNullable().defaultTo(knex.fn.now()).comment('Created at');

      table.primary(['user_id']);
      table.unique(['email'], 'uk_users_email');
      table.index(['created_at'], 'idx_users_created_at');
    }),
  ],
  views: (migrator, knex) => [
    migrator('user_summary', (view) => {
      view.as(knex('users').select('user_id', 'email', 'name'));
    }),
  ],
});

TypeScript

import Automigrate from 'knex-automigrate';

await Automigrate({
  config: {
    client: 'mysql2',
    connection: { host: '127.0.0.1', database: 'my_database', user: 'root', password: null },
  },
  tables: (migrator, knex) => [
    migrator('users', (table) => {
      table.increments('user_id').unsigned().comment('PK');
      table.string('email', 128).notNullable().comment('E-Mail');
    }),
  ],
});

Options

| Option | Type | Description | |--------|------|-------------| | config | Knex.Config & { safe?: boolean } | Knex configuration. Set safe: true to prevent dropping columns. | | cwd | string | Directory to scan for table_*.js / view_*.js migration files. | | verbose | boolean | Enable/disable console output. Default: true. | | tables | (migrator, knex) => MigrationEntry[] | Inline table definitions (in addition to file-based). | | views | (migrator, knex) => MigrationEntry[] | Inline view definitions (in addition to file-based). |

Supported index types

// Primary key
table.primary(['id']);

// Unique key
table.unique(['email'], 'uk_users_email');

// Regular index
table.index(['status'], 'idx_users_status');

// Fulltext index
table.index(['title'], 'ft_articles_title', { indexType: 'FULLTEXT' });

// Fulltext index with ngram parser
table.index(['body'], 'ft_articles_body', { indexType: 'FULLTEXT', parser: 'ngram' });

How it works

1. Reads the current table schema from the database (SHOW CREATE TABLE)
2. Compares it with the defined schema
3. Automatically generates and runs the appropriate DDL:
   • New table → CREATE TABLE
   • New column → ALTER TABLE ADD COLUMN
   • Changed column → ALTER TABLE MODIFY COLUMN
   • Removed column → ALTER TABLE DROP COLUMN (unless safe: true)
   • New index → CREATE INDEX / ALTER TABLE ADD INDEX
   • View → CREATE OR REPLACE VIEW

Dependencies

• knex.js (peer dependency, ^3.1.0)

License

MIT License