Migrations & Seeds

/**
 * Access the migrator instance
 * @returns Migrator for database migration operations
 */
migrate: Knex.Migrator;

interface Migrator {
  /**
   * Create a new migration file
   * @param name - Name of the migration
   * @param config - Optional migration configuration
   * @returns Promise resolving to the migration file path
   */
  make(name: string, config?: MigratorConfig): Promise<string>;

  /**
   * Run all pending migrations to latest
   * @param config - Optional migration configuration
   * @returns Promise with batch number and list of executed migrations
   */
  latest(config?: MigratorConfig): Promise<[number, string[]]>;

  /**
   * Rollback the last batch of migrations
   * @param config - Optional migration configuration
   * @param all - Whether to rollback all migrations
   * @returns Promise with batch number and list of rolled back migrations
   */
  rollback(config?: MigratorConfig, all?: boolean): Promise<[number, string[]]>;

  /**
   * Run the next migration up
   * @param config - Optional migration configuration
   * @returns Promise with batch number and list of executed migrations
   */
  up(config?: MigratorConfig): Promise<[number, string[]]>;

  /**
   * Run the next migration down
   * @param config - Optional migration configuration
   * @returns Promise with batch number and list of rolled back migrations
   */
  down(config?: MigratorConfig): Promise<[number, string[]]>;

  /**
   * Get current migration status
   * @param config - Optional migration configuration
   * @returns Promise with current migration status code
   */
  status(config?: MigratorConfig): Promise<number>;

  /**
   * Get current schema version
   * @param config - Optional migration configuration
   * @returns Promise with current version string
   */
  currentVersion(config?: MigratorConfig): Promise<string>;

  /**
   * List all migrations with their status
   * @param config - Optional migration configuration
   * @returns Promise with arrays of completed and pending migrations
   */
  list(config?: MigratorConfig): Promise<[string[], string[]]>;

  /**
   * Force free migration lock (use with caution)
   * @param config - Optional migration configuration
   * @returns Promise that resolves when lock is freed
   */
  forceFreeMigrationsLock(config?: MigratorConfig): Promise<void>;
}

interface MigratorConfig {
  /**
   * Database name for migrations (overrides connection config)
   */
  database?: string;

  /**
   * Directory containing migration files
   */
  directory?: string | readonly string[];

  /**
   * File extension for migration files (default: 'js')
   */
  extension?: string;

  /**
   * Name of the migrations table (default: 'knex_migrations')
   */
  tableName?: string;

  /**
   * Schema name for the migrations table
   */
  schemaName?: string;

  /**
   * Whether to disable transactions for migrations (default: false)
   */
  disableTransactions?: boolean;

  /**
   * Whether to sort directories separately (default: false)
   */
  sortDirsSeparately?: boolean;

  /**
   * File extensions to load for migration files
   */
  loadExtensions?: readonly string[];

  /**
   * Custom migration source interface
   */
  migrationSource?: MigrationSource<any>;

  /**
   * Whether to include timestamp filename prefix (default: true)
   */
  timestampFilenamePrefix?: boolean;

  /**
   * Stub file to use for new migrations
   */
  stub?: string;
}

/**
 * Access the seeder instance
 * @returns Seeder for database seeding operations
 */
seed: Knex.Seeder;

interface Seeder {
  /**
   * Create a new seed file
   * @param name - Name of the seed
   * @param config - Optional seeder configuration
   * @returns Promise resolving to the seed file path
   */
  make(name: string, config?: SeederConfig): Promise<string>;

  /**
   * Run all seed files
   * @param config - Optional seeder configuration
   * @returns Promise with array of executed seed files
   */
  run(config?: SeederConfig): Promise<[string[]]>;

  /**
   * Set seeder configuration
   * @param config - Seeder configuration
   * @returns Seeder instance
   */
  setConfig(config: SeederConfig): Seeder;
}

interface SeederConfig {
  /**
   * Database name for seeds (overrides connection config)
   */
  database?: string;

  /**
   * Directory containing seed files
   */
  directory?: string | readonly string[];

  /**
   * File extensions to load for seed files
   */
  loadExtensions?: readonly string[];

  /**
   * Whether to include timestamp filename prefix (default: false)
   */
  timestampFilenamePrefix?: boolean;

  /**
   * Whether to sort directories separately (default: false)
   */
  sortDirsSeparately?: boolean;

  /**
   * Stub file to use for new seeds
   */
  stub?: string;

  /**
   * Whether to run seeds recursively in subdirectories
   */
  recursive?: boolean;
}

/**
 * Migration file interface
 */
interface Migration {
  /**
   * Forward migration - apply schema changes
   * @param knex - Knex instance
   * @returns Promise that resolves when migration is complete
   */
  up(knex: Knex): Promise<void>;

  /**
   * Reverse migration - rollback schema changes
   * @param knex - Knex instance
   * @returns Promise that resolves when rollback is complete
   */
  down(knex: Knex): Promise<void>;

  /**
   * Optional configuration for the migration
   */
  config?: {
    /**
     * Whether to run this migration in a transaction (default: true)
     */
    transaction?: boolean;
  };
}

/**
 * Migration source interface for custom migration loading
 */
interface MigrationSource<TMigrationSpec> {
  /**
   * Get list of available migrations
   * @param loadExtensions - File extensions to load
   * @returns Promise with array of migration identifiers
   */
  getMigrations(loadExtensions: readonly string[]): Promise<string[]>;

  /**
   * Get human-readable name for a migration
   * @param migration - Migration identifier
   * @returns Human-readable migration name
   */
  getMigrationName(migration: string): string;

  /**
   * Load a specific migration
   * @param migration - Migration identifier
   * @returns Promise with migration specification
   */
  getMigration(migration: string): Promise<TMigrationSpec>;
}

/**
 * Seed file interface
 */
interface Seed {
  /**
   * Seed function - insert data into database
   * @param knex - Knex instance
   * @returns Promise that resolves when seeding is complete
   */
  seed(knex: Knex): Promise<void>;
}

/**
 * Batch insert utility for large datasets
 * @param knex - Knex instance
 * @param tableName - Target table name
 * @param data - Array of records to insert
 * @param chunkSize - Number of records per batch (default: 1000)
 * @returns Promise that resolves when all batches are inserted
 */
function batchInsert(knex: Knex, tableName: string, data: readonly any[], chunkSize?: number): Promise<any>;

type MigrationStatus = 0 | 1 | 2 | 3;
// 0 = No migrations have been run
// 1 = Latest migration batch has been run
// 2 = Only older migration batches have been run (ahead of latest batch)  
// 3 = Latest batch includes migrations older than latest migration

interface MigrationLock {
  is_locked: number;
}

interface MigrationBatch {
  id: number;
  name: string;
  batch: number;
  migration_time: Date;
}

const knex = require('knex')({ 
  client: 'postgresql', 
  connection: process.env.DATABASE_URL,
  migrations: {
    directory: './migrations',
    tableName: 'knex_migrations'
  },
  seeds: {
    directory: './seeds'
  }
});

// Create a new migration
await knex.migrate.make('create_users_table');

// Example migration file: 20231201_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id');
    table.string('email').notNullable().unique();
    table.string('password_hash').notNullable();
    table.timestamps(true, true);
  });
};

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

// Run migrations
await knex.migrate.latest(); // Run all pending migrations
await knex.migrate.rollback(); // Rollback last batch
await knex.migrate.rollback(null, true); // Rollback all migrations

// Check migration status
const status = await knex.migrate.status();
const [completed, pending] = await knex.migrate.list();
const currentVersion = await knex.migrate.currentVersion();

// Create a new seed
await knex.seed.make('01_users');

// Example seed file: 01_users.js
exports.seed = async function(knex) {
  // Delete existing entries
  await knex('users').del();
  
  // Insert seed data
  await knex('users').insert([
    {
      email: 'admin@example.com',
      password_hash: '$2b$10$...',
      created_at: new Date(),
      updated_at: new Date()
    },
    {
      email: 'user@example.com', 
      password_hash: '$2b$10$...',
      created_at: new Date(),
      updated_at: new Date()
    }
  ]);
};

// Run seeds
await knex.seed.run();

// Complex migration with transaction control
exports.up = function(knex) {
  return knex.transaction(async trx => {
    // Create new table
    await trx.schema.createTable('user_profiles', table => {
      table.increments('id');
      table.integer('user_id').references('id').inTable('users');
      table.string('first_name');
      table.string('last_name');
    });
    
    // Migrate existing data
    const users = await trx('users').select('*');
    for (const user of users) {
      await trx('user_profiles').insert({
        user_id: user.id,
        first_name: user.first_name,
        last_name: user.last_name
      });
    }
    
    // Drop old columns
    await trx.schema.alterTable('users', table => {
      table.dropColumn('first_name');
      table.dropColumn('last_name');
    });
  });
};

// Batch insert for large seed files
exports.seed = async function(knex) {
  const users = [];
  for (let i = 0; i < 10000; i++) {
    users.push({
      email: `user${i}@example.com`,
      password_hash: '$2b$10$...',
      created_at: new Date(),
      updated_at: new Date()
    });
  }
  
  // Insert in batches of 1000
  await knex.batchInsert('users', users, 1000);
};

// Migration with custom configuration
exports.config = { transaction: false };

exports.up = async function(knex) {
  // Operations that can't run in a transaction
  await knex.raw('CREATE INDEX CONCURRENTLY idx_users_email ON users(email)');
};

// Custom migration source
const customMigrationSource = {
  async getMigrations() {
    return ['001_initial', '002_add_users', '003_add_posts'];
  },
  
  getMigrationName(migration) {
    return migration;
  },
  
  async getMigration(migration) {
    const migrationModule = await import(`./custom-migrations/${migration}`);
    return migrationModule;
  }
};

// Use custom migration source
await knex.migrate.latest({
  migrationSource: customMigrationSource
});