Database migration tool usage

Basic Concepts of Database Migration Tools

Database migration tools are used to manage database schema changes, allowing developers to define database modifications in code and synchronize these changes across different environments. Common migration tools in Koa2 applications include Knex.js, Sequelize, and TypeORM. These tools typically provide CLI commands to create, run, and rollback migrations.

Migration files usually consist of two main methods: up for applying changes and down for reverting changes. This design enables the database version to move forward or backward, facilitating team collaboration and continuous integration.

// Example: Creating a migration file with Knex
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id');
    table.string('username').notNullable();
    table.string('email').unique();
    table.timestamps(true, true);
  });
};

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

Integrating Knex.js with Koa2 for Migrations

Knex.js is a popular SQL query builder that also offers robust migration capabilities. Integrating Knex into a Koa2 project requires installing the necessary dependencies:

npm install knex pg --save
npx knex init

Initialization generates a knexfile.js configuration file, which needs to be modified according to the project environment:

// Example knexfile.js
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: '127.0.0.1',
      user: 'your_db_user',
      password: 'your_db_password',
      database: 'myapp_dev'
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './migrations'
    }
  }
};

Command to create a new migration file:

npx knex migrate:make create_users_table

Writing Complex Migration Logic

Real-world projects often require handling more complex migration scenarios, such as adding foreign key constraints, modifying column types, or performing data transformations. Knex provides a variety of methods to support these operations:

exports.up = async function(knex) {
  await knex.schema.alterTable('posts', (table) => {
    table.integer('author_id').unsigned();
    table.foreign('author_id').references('users.id');
    table.text('content').alter();
  });
  
  // Data migration example
  await knex('posts').update({
    published_at: knex.raw('created_at')
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('posts', (table) => {
    table.dropForeign('author_id');
    table.dropColumn('author_id');
    table.string('content').alter();
  });
};

Transaction Handling in Migrations

To ensure atomicity, migration operations should be executed within transactions. Knex supports automatic transaction management:

exports.up = function(knex) {
  return knex.transaction(trx => {
    return trx.schema.createTable('orders', table => {
      table.increments();
      table.integer('user_id').unsigned();
      table.decimal('total_amount', 10, 2);
      table.enu('status', ['pending', 'paid', 'shipped']);
    })
    .then(() => {
      return trx.schema.createTable('order_items', table => {
        table.increments();
        table.integer('order_id').unsigned();
        table.integer('product_id').unsigned();
        table.integer('quantity');
        table.decimal('price', 10, 2);
      });
    });
  });
};

Handling Migration Conflicts and Team Collaboration

In team development, conflicts may arise when multiple developers create migration files simultaneously. Best practices include:

1. Using timestamps as migration file name prefixes (default in Knex)
2. Committing migration files to version control promptly
3. Pulling the latest code before running migrations
4. Resolving conflicts in chronological order

# Check current migration status
npx knex migrate:status

# Execute pending migrations
npx knex migrate:latest

# Rollback the most recent migration
npx knex migrate:rollback

Production Environment Migration Strategy

Database migrations in production require extra caution:

1. Test all migrations in a staging environment first
2. Backup the database before executing migrations
3. Consider blue-green deployment to minimize downtime
4. Monitor migration execution

// Example production migration script
const knex = require('knex');
const config = require('./knexfile').production;

async function runMigrations() {
  const db = knex(config);
  try {
    await db.migrate.latest();
    console.log('Migration completed successfully');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await db.destroy();
  }
}

runMigrations();

Testing Database Migrations

Writing tests for migration scripts can prevent issues in production:

const test = require('ava');
const knex = require('knex');
const config = require('./knexfile').test;

test.beforeEach(async t => {
  t.context.db = knex(config);
  await t.context.db.migrate.rollback();
  await t.context.db.migrate.latest();
});

test.afterEach.always(async t => {
  await t.context.db.destroy();
});

test('users table should exist after migration', async t => {
  const exists = await t.context.db.schema.hasTable('users');
  t.true(exists);
});

Custom Migration Extensions

For complex projects, extending migration functionality may be necessary. Knex allows creating custom migration sources:

// custom-migration-source.js
const fs = require('fs');
const path = require('path');

class CustomMigrationSource {
  constructor(migrationDirectory) {
    this.migrationDirectory = migrationDirectory;
  }

  getMigrations() {
    return fs.readdirSync(this.migrationDirectory);
  }

  getMigrationName(migration) {
    return migration;
  }

  getMigration(migration) {
    return require(path.join(this.migrationDirectory, migration));
  }
}

// Using a custom migration source
knex.migrate.latest({
  migrationSource: new CustomMigrationSource('./custom_migrations')
});

Multi-Database System Support

When supporting multiple database systems, migration files must account for syntax differences:

exports.up = function(knex) {
  if (knex.client.config.client === 'mysql') {
    return knex.raw(`
      ALTER TABLE users
      MODIFY COLUMN email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    `);
  } else if (knex.client.config.client === 'postgresql') {
    return knex.raw(`
      ALTER TABLE users
      ALTER COLUMN email TYPE VARCHAR(255),
      ALTER COLUMN email SET DATA TYPE VARCHAR(255)
    `);
  } else {
    // SQLite and others don't support direct column type modification
    return knex.schema.renameTable('users', 'old_users')
      .then(() => knex.schema.createTable('users', newTable => {
        // Create new table structure
      }))
      .then(() => knex('old_users').insert().into('users'))
      .then(() => knex.schema.dropTable('old_users'));
  }
};

Performance Optimization Techniques

Large database migrations may require performance optimization:

1. Disable foreign key checks (MySQL)
2. Process data migrations in batches
3. Execute migrations during off-peak hours
4. Consider using temporary tables

exports.up = async function(knex) {
  // MySQL optimization example
  if (knex.client.config.client === 'mysql') {
    await knex.raw('SET FOREIGN_KEY_CHECKS=0');
  }

  // Batch processing example
  const batchSize = 1000;
  let offset = 0;
  let results;
  
  do {
    results = await knex('large_table')
      .select('id', 'old_column')
      .offset(offset)
      .limit(batchSize);
    
    await Promise.all(results.map(row => 
      knex('large_table')
        .where('id', row.id)
        .update('new_column', transformFunction(row.old_column))
    ));
    
    offset += batchSize;
  } while (results.length === batchSize);

  if (knex.client.config.client === 'mysql') {
    await knex.raw('SET FOREIGN_KEY_CHECKS=1');
  }
};

Migrations and Data Seeding

Beyond schema changes, migrations are also useful for initial data seeding. Knex provides seed functionality:

npx knex seed:make initial_data

// seeds/initial_data.js
exports.seed = function(knex) {
  return knex('roles').insert([
    { name: 'admin', permissions: JSON.stringify(['create', 'read', 'update', 'delete']) },
    { name: 'user', permissions: JSON.stringify(['read']) }
  ]);
};

Running seed data:

npx knex seed:run

Migration Version Control

Good version control practices include:

1. Including migration files in version control
2. Ensuring each migration file is independent
3. Avoiding modifications to committed migration files
4. Using migrate:currentVersion to check the current version

npx knex migrate:currentVersion

Error Handling and Recovery

Strategies for handling migration failures:

1. Log detailed error information
2. Provide rollback paths
3. Consider partial migration possibilities
4. Maintain a migration history table

exports.up = async function(knex) {
  try {
    await knex.schema.createTable('new_feature', table => {
      // Table structure definition
    });
    
    await knex('new_feature').insert({
      // Initial data
    });
  } catch (error) {
    console.error('Migration failed:', error);
    // Attempt automatic cleanup
    await knex.schema.dropTableIfExists('new_feature');
    throw error; // Re-throw to mark migration as failed
  }
};

Advanced Features of Migration Tools

Modern migration tools offer advanced features:

1. Conditional migrations
2. Migration dependency management
3. Migration validation
4. Migration hooks

// Conditional migration example
exports.up = function(knex) {
  return knex.schema.hasTable('departments').then(exists => {
    if (!exists) {
      return knex.schema.createTable('departments', table => {
        table.increments('id');
        table.string('name');
      });
    }
  });
};

// Migration hook example
exports.up = function(knex) {
  return knex.schema.createTable('audit_logs', table => {
    // Table structure
  }).then(() => {
    // Post-migration hook
    return knex.raw(`
      CREATE TRIGGER log_user_changes
      AFTER UPDATE ON users
      FOR EACH ROW
      EXECUTE PROCEDURE audit_user_change();
    `);
  });
};