Migration Tools Usage

Introduction to Migration Tools

Database migration tools are specialized software that help developers track, version, and apply changes to database schemas. These tools automate the process of evolving your database as your application grows and changes.

In the previous lecture, we discussed the concepts and importance of database migrations. Today, we'll focus on practical usage of popular migration tools, examining their specific features, command-line interfaces, and integration with JavaScript ecosystems.

Knex.js Migrations in Depth

Knex.js is a popular SQL query builder for Node.js that includes a robust migrations system. It supports PostgreSQL, MySQL, SQLite, and MSSQL databases.

Setting Up Knex

// Install Knex and your database driver
npm install knex pg
            

The knexfile.js Configuration

The knexfile.js is the heart of Knex migrations configuration, defining connection details for different environments.

// knexfile.js
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: 'localhost',
      database: 'my_app_dev',
      user: 'postgres',
      password: 'password'
    },
    migrations: {
      directory: './db/migrations',
      tableName: 'knex_migrations'
    },
    seeds: {
      directory: './db/seeds'
    }
  },
  
  staging: {
    client: 'postgresql',
    connection: {
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    },
    migrations: {
      directory: './db/migrations',
      tableName: 'knex_migrations'
    }
  },
  
  production: {
    client: 'postgresql',
    connection: {
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      ssl: { rejectUnauthorized: false }
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      directory: './db/migrations',
      tableName: 'knex_migrations'
    }
  }
};
            

Knex Migration Commands

Specifying Environment

// Run migrations in a specific environment
npx knex migrate:latest --env production
            

Complete Knex Migration Example

Let's create a complete example of a blog system with users, posts, and comments:

// Create migration files
npx knex migrate:make create_users_table
npx knex migrate:make create_posts_table
npx knex migrate:make create_comments_table
npx knex migrate:make add_categories_to_posts
            

Now let's implement each migration file:

// migrations/YYYYMMDDHHMMSS_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id').primary();
    table.string('username', 50).notNullable().unique();
    table.string('email', 100).notNullable().unique();
    table.string('password_hash', 255).notNullable();
    table.string('first_name', 50);
    table.string('last_name', 50);
    table.text('bio');
    table.boolean('is_admin').defaultTo(false);
    table.timestamps(true, true); // Creates created_at and updated_at columns
  });
};

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

// migrations/YYYYMMDDHHMMSS_create_posts_table.js
exports.up = function(knex) {
  return knex.schema.createTable('posts', function(table) {
    table.increments('id').primary();
    table.string('title', 255).notNullable();
    table.text('content').notNullable();
    table.text('excerpt');
    table.string('slug', 255).notNullable().unique();
    table.integer('author_id').unsigned().notNullable();
    table.foreign('author_id').references('users.id').onDelete('CASCADE');
    table.string('status', 20).defaultTo('draft'); // draft, published, archived
    table.timestamp('published_at');
    table.timestamps(true, true);
  });
};

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

// migrations/YYYYMMDDHHMMSS_create_comments_table.js
exports.up = function(knex) {
  return knex.schema.createTable('comments', function(table) {
    table.increments('id').primary();
    table.text('content').notNullable();
    table.integer('user_id').unsigned().notNullable();
    table.foreign('user_id').references('users.id').onDelete('CASCADE');
    table.integer('post_id').unsigned().notNullable();
    table.foreign('post_id').references('posts.id').onDelete('CASCADE');
    table.boolean('is_approved').defaultTo(true);
    table.timestamps(true, true);
  });
};

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

// migrations/YYYYMMDDHHMMSS_add_categories_to_posts.js
exports.up = function(knex) {
  return knex.schema
    // Create categories table
    .createTable('categories', function(table) {
      table.increments('id').primary();
      table.string('name', 50).notNullable().unique();
      table.string('slug', 50).notNullable().unique();
      table.text('description');
      table.timestamps(true, true);
    })
    // Create join table for many-to-many relationship
    .createTable('post_categories', function(table) {
      table.increments('id').primary();
      table.integer('post_id').unsigned().notNullable();
      table.foreign('post_id').references('posts.id').onDelete('CASCADE');
      table.integer('category_id').unsigned().notNullable();
      table.foreign('category_id').references('categories.id').onDelete('CASCADE');
      // Unique constraint to prevent duplicate post-category pairs
      table.unique(['post_id', 'category_id']);
    });
};

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

Running the Migrations

// Run all migrations
npx knex migrate:latest

// Check migration status
npx knex migrate:status
            

Sequelize Migrations

Sequelize is a popular ORM (Object-Relational Mapper) for Node.js that supports various SQL databases. Its migration system is integrated with its model definitions.

Setting Up Sequelize CLI

// Install Sequelize, CLI, and your database driver
npm install sequelize sequelize-cli pg pg-hstore
            

Initialize Sequelize Project

// Initialize Sequelize
npx sequelize-cli init
            

This command creates the following folders:

• config: Contains the database configuration file
• models: Contains all models for your project
• migrations: Contains all migration files
• seeders: Contains all seed files

Configuring Sequelize

// config/config.json
{
  "development": {
    "username": "postgres",
    "password": "password",
    "database": "my_app_dev",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "postgres",
    "password": "password",
    "database": "my_app_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false
      }
    }
  }
}
            

Sequelize Migration Commands

Creating Models and Migrations

With Sequelize, you can generate both a model and its migration in one command:

// Generate User model and migration
npx sequelize-cli model:generate --name User --attributes username:string,email:string,password:string,isAdmin:boolean

// Generate Post model and migration
npx sequelize-cli model:generate --name Post --attributes title:string,content:text,userId:integer
            

Example Migration Files

// migrations/YYYYMMDDHHMMSS-create-user.js
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      isAdmin: {
        type: Sequelize.BOOLEAN,
        defaultValue: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

// migrations/YYYYMMDDHHMMSS-create-post.js
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Posts', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false
      },
      content: {
        type: Sequelize.TEXT,
        allowNull: false
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'Users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Posts');
  }
};
            

Data Migrations with Sequelize

// migrations/YYYYMMDDHHMMSS-add-default-admin.js
'use strict';
const bcrypt = require('bcrypt');

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Create an admin user
    await queryInterface.bulkInsert('Users', [{
      username: 'admin',
      email: 'admin@example.com',
      password: await bcrypt.hash('securepassword', 10),
      isAdmin: true,
      createdAt: new Date(),
      updatedAt: new Date()
    }]);
  },

  down: async (queryInterface, Sequelize) => {
    // Remove the admin user
    await queryInterface.bulkDelete('Users', { 
      username: 'admin' 
    });
  }
};
            

Prisma Migrate

Prisma is a modern database toolkit that includes Prisma Migrate for database migrations. It takes a different approach than most other tools, using a declarative schema file.

Setting Up Prisma

// Install Prisma CLI
npm install prisma --save-dev

// Initialize Prisma
npx prisma init
            

Defining a Prisma Schema

With Prisma, you define your data model in the schema.prisma file:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  isAdmin   Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]
  comments  Comment[]
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  content   String
  published Boolean   @default(false)
  author    User      @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  comments  Comment[]
  categories Category[] @relation("PostToCategory")
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Category {
  id        Int      @id @default(autoincrement())
  name      String   @unique
  posts     Post[]   @relation("PostToCategory")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
            

Prisma Migration Commands

Creating and Applying Migrations

// Create an initial migration
npx prisma migrate dev --name init

// Make changes to schema.prisma, then create a new migration
// For example, add a new field to the User model:
// bio String?

// Create migration for the changes
npx prisma migrate dev --name add_user_bio
            

Viewing Generated Migrations

Prisma generates SQL migration files based on your schema changes:

// prisma/migrations/20230518123456_init/migration.sql
-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "password" TEXT NOT NULL,
    "isAdmin" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- CreateTable
-- ... rest of the migration ...

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- ... rest of the foreign keys ...
            

MongoDB Migration Tools

Although MongoDB is schemaless, applications often need to evolve their data structure. Let's explore tools for MongoDB migrations.

Migrate-Mongo

Migrate-mongo is a database migration tool for MongoDB, inspired by tools like Flyway and Liquibase, but designed specifically for MongoDB.

Setting Up migrate-mongo

// Install migrate-mongo
npm install -g migrate-mongo

// Initialize a migrate-mongo project
migrate-mongo init
            

Configuring migrate-mongo

// migrate-mongo-config.js
const config = {
  mongodb: {
    url: process.env.MONGO_URL || "mongodb://localhost:27017",
    databaseName: process.env.MONGO_DB || "myapp",
    options: {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    }
  },
  migrationsDir: "migrations",
  changelogCollectionName: "changelog",
  migrationFileExtension: ".js",
  useFileHash: false
};

module.exports = config;
            

Creating Migrations

// Create a new migration
migrate-mongo create add-user-roles
            

Writing MongoDB Migrations

// migrations/20230519123456-add-user-roles.js
module.exports = {
  async up(db, client) {
    // Update all users to have a roles array with a default role
    await db.collection('users').updateMany(
      { roles: { $exists: false } },
      { $set: { roles: ['user'] } }
    );
    
    // Create an admin user
    await db.collection('users').updateOne(
      { email: 'admin@example.com' },
      { $set: { roles: ['user', 'admin'] } },
      { upsert: false }
    );
  },

  async down(db, client) {
    // Remove the roles field from all users
    await db.collection('users').updateMany(
      {},
      { $unset: { roles: "" } }
    );
  }
};
            

migrate-mongo Commands

MongoDB Schema Validation

While MongoDB is schema-less, you can enforce schemas using JSON Schema validation. Migrations can add or modify these validations.

// Migration to add schema validation
module.exports = {
  async up(db, client) {
    await db.command({
      collMod: 'users',
      validator: {
        $jsonSchema: {
          bsonType: 'object',
          required: ['email', 'username', 'password'],
          properties: {
            email: {
              bsonType: 'string',
              pattern: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'
            },
            username: {
              bsonType: 'string',
              minLength: 3,
              maxLength: 20
            },
            password: {
              bsonType: 'string',
              minLength: 8
            },
            roles: {
              bsonType: 'array',
              items: {
                bsonType: 'string',
                enum: ['user', 'admin', 'editor']
              }
            }
          }
        }
      },
      validationLevel: 'moderate', // 'strict' or 'moderate'
      validationAction: 'error' // 'error' or 'warn'
    });
  },

  async down(db, client) {
    // Remove the validation
    await db.command({
      collMod: 'users',
      validator: {},
      validationLevel: 'off',
      validationAction: 'warn'
    });
  }
};
            

Complex Migration Scenarios

Let's explore some complex real-world migration scenarios and how to handle them with the tools we've discussed.

Scenario 1: Safe Schema Changes in Production

Adding a NOT NULL column to a large table in production can be risky. Here's how to do it safely with Knex:

// Step 1: Add the column as nullable
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.string('phone_number');
  });
};

// Step A: Deploy application code that populates the column for new rows

// Step 2: Populate existing rows
exports.up = function(knex) {
  return knex('users')
    .whereNull('phone_number')
    .update({
      phone_number: 'Unknown' // Default value
    });
};

// Step 3: Make the column NOT NULL
exports.up = function(knex) {
  return knex.schema.alterTable('users', function(table) {
    table.string('phone_number').notNullable().alter();
  });
};
            

Scenario 2: Data Transformation with Progress Tracking

For large tables, transforming data can take time. Here's how to handle it with progress tracking:

// For PostgreSQL with Knex
exports.up = async function(knex) {
  // Create progress tracking table
  await knex.schema.createTable('migration_progress', function(table) {
    table.string('migration_name').primary();
    table.integer('processed_count').defaultTo(0);
    table.integer('total_count');
    table.timestamp('started_at').defaultTo(knex.fn.now());
    table.timestamp('completed_at');
  });
  
  // Get total count
  const { count } = await knex('large_table').count('id as count').first();
  
  // Initialize progress
  await knex('migration_progress').insert({
    migration_name: 'transform_large_table_data',
    total_count: count
  });
  
  // Process in batches
  const batchSize = 1000;
  let processedCount = 0;
  
  while (processedCount < count) {
    // Get a batch of records
    const records = await knex('large_table')
      .select('id', 'data_column')
      .orderBy('id')
      .limit(batchSize)
      .offset(processedCount);
    
    // Process each record
    const updates = records.map(record => {
      const transformedData = someTransformation(record.data_column);
      
      return knex('large_table')
        .where('id', record.id)
        .update({ transformed_column: transformedData });
    });
    
    // Execute updates
    await Promise.all(updates);
    
    // Update progress
    processedCount += records.length;
    await knex('migration_progress')
      .where('migration_name', 'transform_large_table_data')
      .update({ processed_count: processedCount });
    
    console.log(`Processed ${processedCount} of ${count} records`);
  }
  
  // Mark as completed
  await knex('migration_progress')
    .where('migration_name', 'transform_large_table_data')
    .update({ completed_at: knex.fn.now() });
};
            

Scenario 3: Renaming a Column Without Downtime

Renaming a column typically requires a table lock. Here's a safer approach:

// Step 1: Add the new column
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.string('family_name');
  });
};

// Step 2: Deploy code that writes to both columns

// Step 3: Copy data from old to new column
exports.up = function(knex) {
  return knex('users')
    .whereNotNull('last_name')
    .whereNull('family_name')
    .update({
      family_name: knex.ref('last_name')
    });
};

// Step 4: Deploy code that only reads from the new column

// Step 5: Remove the old column
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.dropColumn('last_name');
  });
};
            

Integration with Node.js Applications

Let's explore how to integrate migration tools with your Node.js application:

Programmatic Migration Execution

// src/db/migrations.js
const path = require('path');
const knex = require('knex');
const config = require('../../knexfile');

// Get environment from NODE_ENV or default to development
const environment = process.env.NODE_ENV || 'development';
const knexInstance = knex(config[environment]);

async function migrateDatabase() {
  try {
    console.log(`Running migrations for ${environment} environment...`);
    
    // Run all pending migrations
    const [batchNo, log] = await knexInstance.migrate.latest();
    
    if (log.length === 0) {
      console.log('Already up to date');
    } else {
      console.log(`Batch ${batchNo} run: ${log.length} migrations`);
      console.log(`Migrations that were run: ${log.join(', ')}`);
    }
    
    return { success: true };
  } catch (error) {
    console.error('Migration failed:', error);
    return { success: false, error };
  }
}

async function rollbackMigration() {
  try {
    console.log(`Rolling back last batch for ${environment} environment...`);
    
    // Rollback the last batch of migrations
    const [batchNo, log] = await knexInstance.migrate.rollback();
    
    if (log.length === 0) {
      console.log('No migrations to rollback');
    } else {
      console.log(`Batch ${batchNo} rolled back: ${log.length} migrations`);
      console.log(`Rolled back: ${log.join(', ')}`);
    }
    
    return { success: true };
  } catch (error) {
    console.error('Rollback failed:', error);
    return { success: false, error };
  }
}

module.exports = {
  migrateDatabase,
  rollbackMigration
};
            

Integration with Application Startup

// src/server.js
const express = require('express');
const { migrateDatabase } = require('./db/migrations');

const app = express();
const PORT = process.env.PORT || 3000;

async function startServer() {
  try {
    // Run migrations before starting the server
    const migrationResult = await migrateDatabase();
    
    if (!migrationResult.success) {
      console.error('Failed to run migrations, server will not start');
      process.exit(1);
    }
    
    // Set up routes, middleware, etc.
    app.use(express.json());
    
    // Start the server
    app.listen(PORT, () => {
      console.log(`Server running on port ${PORT}`);
    });
  } catch (error) {
    console.error('Failed to start server:', error);
    process.exit(1);
  }
}

startServer();
            

Creating Migration Scripts

// package.json
{
  "scripts": {
    "start": "node src/server.js",
    "dev": "nodemon src/server.js",
    
    "migrate": "knex migrate:latest",
    "migrate:dev": "knex migrate:latest --env development",
    "migrate:prod": "knex migrate:latest --env production",
    
    "rollback": "knex migrate:rollback",
    "reset-db": "knex migrate:rollback --all && knex migrate:latest",
    
    "create-migration": "knex migrate:make",
    "seed": "knex seed:run"
  }
}
            

Best Practices and Common Pitfalls

Migration Best Practices

• Always Backup Before Production Migrations: Create a database backup before running migrations
• Test Migrations on Copies of Production Data: Find issues before they reach production
• Use Transactions When Possible: Ensure migrations are atomic
• Lock Down Migration Files: Once committed, never modify migrations in version control
• Use Forward-Only Migrations in Production: Avoid rollbacks in production when possible
• Include Database Migrations in Code Reviews: Get extra eyes on database changes
• Understand Database Locks: Know which operations lock tables and for how long
• Monitor Performance During Migrations: Watch for database performance issues

Common Pitfalls

• Forgetting to Run Migrations: Deploy code that depends on database changes without applying the migrations
• Long-Running Migrations: Migrations that take too long and cause application downtime
• Missing Down Migrations: Inability to revert changes if needed
• Migration Failures in Production: Not having a plan for when migrations fail
• Data Loss During Migrations: Dropping columns or tables without backing up data
• Environment Differences: Migrations that work in development but fail in production
• Concurrent Migrations: Multiple developers or deployment pipelines running migrations at the same time

Practice Activities

Activity 1: Knex.js Migration Exercise

Create a complete migration system for a social media application with the following features:

1. Set up a Knex.js project with a knexfile.js for development and production
2. Create migrations for users, posts, comments, and likes tables
3. Add a followers relationship between users (many-to-many)
4. Create a migration to add post visibility (public, friends, private)
5. Write a data migration that populates default values for post visibility

Activity 2: Schema Evolution Challenge

You have an existing users table with a name field. Create a series of migrations to:

1. Add first_name and last_name fields (nullable initially)
2. Create a data migration that splits the name field into first_name and last_name
3. Make first_name not nullable after data is migrated
4. Add an index on the last_name field
5. Remove the original name field

Activity 3: MongoDB Migration with migrate-mongo

Set up a migrate-mongo project and create migrations for:

1. Adding a JSON Schema validator to the users collection
2. Converting user.address from a string to a structured object with street, city, state, zip
3. Adding a roles array to users and populating default values
4. Adding a createdAt timestamp to all existing documents

Further Reading

• Knex.js Migration Documentation
• Sequelize Migrations
• Prisma Migrate Documentation
• migrate-mongo GitHub Repository
• MongoDB Schema Validation
• Flyway Documentation (Java-based migration tool)
• Evolutionary Database Design by Martin Fowler