CLI Tools

# Initialize a new knex project
knex init [options]

# Options:
#   --knexfile <path>        Specify knexfile path (default: knexfile.js)
#   --cwd <path>             Specify working directory
#   --client <name>          Database client (pg, mysql, sqlite3, etc.)
#   --connection <string>    Connection string
#   --esm                    Generate ES module knexfile

# Create a new migration file
knex migrate:make <name> [options]

# Run all pending migrations to latest version
knex migrate:latest [options]

# Run the next migration up
knex migrate:up [name] [options]

# Run the specified migration down
knex migrate:down [name] [options]

# Rollback the last batch of migrations
knex migrate:rollback [options]

# Show current migration version
knex migrate:currentVersion [options]

# List migration status (completed and pending)
knex migrate:list [options]
knex migrate:status [options]

# Force unlock migration lock (use with caution)
knex migrate:unlock [options]

# Migration options:
#   --knexfile <path>           Knexfile path
#   --cwd <path>                Working directory
#   --env <name>                Environment name
#   --esm                       Use ES modules
#   --specific <version>        Run specific migration version
#   --all                       Rollback all migrations (for rollback command)
#   --migrations-directory <path>  Migrations directory
#   --migrations-table-name <name>  Migration table name
#   --timestamp-filename-prefix     Use timestamp prefix for new migrations

# Create a new seed file
knex seed:make <name> [options]

# Run all seed files
knex seed:run [options]

# Seed options:
#   --knexfile <path>        Knexfile path
#   --cwd <path>             Working directory  
#   --env <name>             Environment name
#   --esm                    Use ES modules
#   --specific <file>        Run specific seed file
#   --timestamp-filename-prefix  Use timestamp prefix for new seeds

# Global options (available for all commands):
#   --help                   Show help information
#   --version                Show version number
#   --debug                  Enable debug output
#   --verbose                Enable verbose output
#   --knexfile <path>        Path to knexfile (default: knexfile.js)
#   --knexpath <path>        Path to knex module
#   --cwd <path>             Current working directory
#   --client <client>        Database client override
#   --connection <string>    Connection string override
#   --env <name>             Environment name (default: development)
#   --esm                    Enable ES module support

/**
 * Knexfile configuration object
 */
interface KnexConfig {
  // Database connection settings
  client: string;
  connection: string | ConnectionConfig | (() => ConnectionConfig);
  
  // Connection pool settings
  pool?: {
    min?: number;
    max?: number;
    createTimeoutMillis?: number;
    acquireTimeoutMillis?: number;
    idleTimeoutMillis?: number;
    reapIntervalMillis?: number;
    createRetryIntervalMillis?: number;
    propagateCreateError?: boolean;
  };

  // Migration settings
  migrations?: {
    database?: string;
    directory?: string | string[];
    extension?: string;
    tableName?: string;
    schemaName?: string;
    disableTransactions?: boolean;
    sortDirsSeparately?: boolean;
    loadExtensions?: string[];
    stub?: string;
  };

  // Seed settings
  seeds?: {
    database?: string;
    directory?: string | string[];
    loadExtensions?: string[];
    timestampFilenamePrefix?: boolean;
    stub?: string;
  };

  // Additional options
  debug?: boolean;
  useNullAsDefault?: boolean;
  acquireConnectionTimeout?: number;
  asyncStackTraces?: boolean;
}

/**
 * Multi-environment knexfile
 */
interface KnexFileConfig {
  development: KnexConfig;
  staging?: KnexConfig;
  production: KnexConfig;
  test?: KnexConfig;
  [environment: string]: KnexConfig;
}

# Environment variables:
# NODE_ENV              - Sets the default environment (development, production, etc.)
# KNEX_PATH             - Path to knex module
# DEBUG                 - Enable debug output (set to 'knex:*')
# DATABASE_URL          - Database connection URL

# Exit codes:
# 0  - Success
# 1  - General error  
# 2  - Migration up to date (migrate:latest when no migrations to run)
# 3  - Migration warning/non-critical error

/**
 * Default knexfile.js template generated by 'knex init'
 */
module.exports = {
  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    },
    useNullAsDefault: true
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user: 'username', 
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user: 'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

/**
 * Default migration template generated by 'knex migrate:make'
 */
exports.up = function(knex) {
  // Add schema changes here
};

exports.down = function(knex) {
  // Add rollback logic here
};

/**
 * Migration with configuration
 */
exports.config = {
  transaction: false  // Disable transaction for this migration
};

exports.up = async function(knex) {
  // Non-transactional operations like CREATE INDEX CONCURRENTLY
};

exports.down = async function(knex) {
  // Corresponding rollback operations
};

/**
 * Default seed template generated by 'knex seed:make'
 */
exports.seed = async function(knex) {
  // Deletes ALL existing entries
  await knex('table_name').del();
  
  // Inserts seed entries
  await knex('table_name').insert([
    {id: 1, colName: 'rowValue1'},
    {id: 2, colName: 'rowValue2'},
    {id: 3, colName: 'rowValue3'}
  ]);
};

/**
 * CLI command result interface
 */
interface CommandResult {
  success: boolean;
  message?: string;
  data?: any;
  error?: Error;
}

/**
 * Migration status result
 */
interface MigrationStatus {
  currentVersion: string;
  completed: string[];
  pending: string[];
  isUpToDate: boolean;
}

/**
 * Migration list result
 */
interface MigrationList {
  completed: Array<{
    name: string;
    batch: number;
    migration_time: Date;
  }>;
  pending: string[];
}

# Initialize a new project
knex init
knex init --client postgresql
knex init --esm  # Generate ES module knexfile

# Create configuration for different databases
knex init --client mysql --connection mysql://user:pass@localhost/dbname
knex init --client sqlite3 --connection ./database.sqlite

# Create migrations
knex migrate:make create_users_table
knex migrate:make add_email_to_users --timestamp-filename-prefix
knex migrate:make create_posts_table --migrations-directory ./database/migrations

# Run migrations
knex migrate:latest                    # Run all pending migrations
knex migrate:latest --env production   # Run in production environment
knex migrate:up                        # Run next migration
knex migrate:up 20231201_create_users  # Run specific migration

# Rollback migrations
knex migrate:rollback                  # Rollback last batch
knex migrate:rollback --all           # Rollback all migrations
knex migrate:down 20231201_create_users # Rollback specific migration

# Check migration status
knex migrate:status                   # Show migration status
knex migrate:currentVersion          # Show current version
knex migrate:list                    # List all migrations

# Create and run seeds
knex seed:make users                 # Create users seed file
knex seed:make initial_data --timestamp-filename-prefix
knex seed:run                        # Run all seeds
knex seed:run --specific users.js    # Run specific seed

# Using different knexfiles and environments
knex migrate:latest --knexfile ./config/database.js
knex migrate:latest --env staging
knex migrate:latest --cwd /path/to/project

# Debug mode
knex migrate:latest --debug
DEBUG=knex:* knex migrate:latest

# ES module support
knex init --esm
knex migrate:make create_users --esm

# Advanced usage with custom directories
knex migrate:make create_users \
  --migrations-directory ./database/migrations \
  --migrations-table-name custom_migrations

# Production deployment example
knex migrate:latest \
  --env production \
  --knexfile ./production.knexfile.js \
  --no-color \
  --verbose

# Seed with specific environment
knex seed:run --env test

# Check if migrations are up to date (useful in CI/CD)
if knex migrate:status --env production | grep -q "No pending migrations"; then
  echo "Migrations are up to date"
else
  echo "Pending migrations found"
  exit 1
fi

# Unlock migrations (emergency use only)
knex migrate:unlock --env production

# Multiple database support
knex migrate:latest --connection postgres://user:pass@localhost/db1
knex migrate:latest --connection mysql://user:pass@localhost/db2

# TypeScript configuration example
knex init --client postgresql --esm
# Creates knexfile.mjs for TypeScript/ES module projects

// knexfile.js - Basic configuration
module.exports = {
  client: 'postgresql',
  connection: process.env.DATABASE_URL,
  migrations: {
    directory: './migrations'
  },
  seeds: {
    directory: './seeds'
  }
};

// knexfile.js - Multi-environment with connection pooling
module.exports = {
  development: {
    client: 'sqlite3',
    connection: { filename: './dev.sqlite3' },
    useNullAsDefault: true,
    migrations: { directory: './migrations' },
    seeds: { directory: './seeds' }
  },

  production: {
    client: 'postgresql',
    connection: {
      connectionString: process.env.DATABASE_URL,
      ssl: { rejectUnauthorized: false }
    },
    pool: { min: 2, max: 20 },
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations'
    }
  }
};

// knexfile.mjs - ES module configuration
export default {
  client: 'postgresql',
  connection: process.env.DATABASE_URL,
  migrations: {
    directory: './migrations',
    extension: 'mjs'
  }
};