Database Migrations Concepts

Understanding Database Migrations

Database migrations are a structured way to evolve your database schema over time. They allow you to track, version, apply, and revert changes to your database structure in a controlled and systematic manner.

Think of migrations as a version control system specifically for your database schema. Just as Git tracks changes to your code, migrations track changes to your database structure and data.

Why Database Migrations Are Essential

Key Benefits of Database Migrations

• Version Control: Track changes to your database schema over time
• Consistent Environments: Ensure development, staging, and production databases have identical structures
• Team Collaboration: Allow multiple developers to make and share database changes
• Rollback Capability: Safely revert database changes if problems occur
• Automated Deployment: Include database changes in your CI/CD pipeline
• Documentation: Self-document database evolution with migration files
• Data Integrity: Safely transform data during schema changes

Challenges Without Migrations

Without using database migrations, teams often face several challenges:

• Manual Schema Updates: Developers manually running SQL scripts or commands
• Environment Drift: Different database structures across environments
• Lost Changes: Database changes not tracked in version control
• Deployment Errors: Failed deployments due to database inconsistencies
• Undocumented Changes: No clear history of how the database evolved

Key Concepts in Database Migrations

Migration Files

Migration files are scripts that define a specific change to your database. Each migration typically includes:

• Up Migration: Code to apply the change
• Down Migration: Code to revert the change
• Version/Timestamp: A unique identifier, often a timestamp
• Description: A brief explanation of the change

Migration State Table

Migration frameworks typically maintain a special table in your database that tracks which migrations have been applied. This table might contain:

• Migration identifier/version
• Timestamp of when it was applied
• Status (applied, reverted, failed)
• Checksum to detect tampered migration files

Migration Lifecycle

Types of Database Changes

Migrations can handle various types of changes:

• Structural Changes: Adding, modifying, or removing tables and columns
• Data Migrations: Transforming or moving data
• Index Changes: Adding or removing indexes for performance
• Constraint Changes: Adding, modifying, or removing constraints
• Stored Procedure/Function Updates: Adding or updating database code

Migration Strategies

Migration Naming Conventions

Well-named migration files help understand the database evolution at a glance:

• Timestamp-based: 20230315143227_add_user_table.js
• Sequential: 001_initial_schema.js
• Feature-based: 202303_user_authentication.js

Zero-Downtime Migrations

For production applications, zero-downtime migrations are crucial. This involves multiple smaller migrations instead of a single large change:

Safe vs. Unsafe Migrations

Understanding which database changes are safe to perform in production is crucial:

Migration Tools for Different Databases

SQL Database Migration Tools

NoSQL Database Migration Tools

NoSQL databases also have migration solutions, though they're often handled differently:

• MongoDB: mongodb-migrations, migrate-mongo, mongoose-migrations
• Cassandra: Cassandra Migration Tool
• DynamoDB: dynamodb-migrations
• Elasticsearch: elasticsearch-migration

Working with Knex.js Migrations

Let's explore how to create and manage migrations using Knex.js, a popular SQL query builder for Node.js that includes a robust migrations system.

Setting Up Knex Migrations

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

// Initialize Knex configuration
npx knex init

// Create a migration file
npx knex migrate:make create_users_table
            

Writing a Migration

// migrations/20230315143227_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id').primary();
    table.string('username').notNullable().unique();
    table.string('email').notNullable().unique();
    table.string('password_hash').notNullable();
    table.boolean('is_active').defaultTo(true);
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
};

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

Running Migrations

// Run all pending migrations
npx knex migrate:latest

// Rollback the last batch of migrations
npx knex migrate:rollback

// Rollback all migrations
npx knex migrate:rollback --all

// Check migration status
npx knex migrate:status
            

Common Knex Migration Operations

// Adding a column
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.string('first_name');
    table.string('last_name');
  });
};

exports.down = function(knex) {
  return knex.schema.table('users', function(table) {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

// Creating an index
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.index(['last_name', 'first_name'], 'idx_users_name');
  });
};

exports.down = function(knex) {
  return knex.schema.table('users', function(table) {
    table.dropIndex(['last_name', 'first_name'], 'idx_users_name');
  });
};

// Adding a foreign key
exports.up = function(knex) {
  return knex.schema.table('posts', function(table) {
    table.integer('user_id').unsigned();
    table.foreign('user_id').references('users.id');
  });
};

exports.down = function(knex) {
  return knex.schema.table('posts', function(table) {
    table.dropForeign('user_id');
    table.dropColumn('user_id');
  });
};
            

Data Migrations

Schema migrations modify the structure of your database, but data migrations transform the data itself. These are essential when changing how data is represented.

Common Data Migration Scenarios

• Splitting a field into multiple fields
• Combining multiple fields into one
• Converting data formats (e.g., JSON to structured fields)
• Populating new fields based on existing data
• Moving data between tables

Example: Splitting Name into First and Last Name

// migrations/20230316123045_split_user_name.js
exports.up = function(knex) {
  return knex.schema
    // First add the new columns
    .table('users', function(table) {
      table.string('first_name');
      table.string('last_name');
    })
    // Then migrate the data
    .then(() => {
      return knex('users').select('id', 'name');
    })
    .then((users) => {
      // Process each user
      const updates = users.map(user => {
        const nameParts = user.name.split(' ');
        const firstName = nameParts[0] || '';
        // Join all remaining parts as last name
        const lastName = nameParts.slice(1).join(' ') || '';
        
        return knex('users')
          .where('id', user.id)
          .update({
            first_name: firstName,
            last_name: lastName
          });
      });
      
      return Promise.all(updates);
    });
};

exports.down = function(knex) {
  return knex.schema
    // First combine the data back
    .then(() => {
      return knex('users').select('id', 'first_name', 'last_name');
    })
    .then((users) => {
      const updates = users.map(user => {
        const fullName = `${user.first_name || ''} ${user.last_name || ''}`.trim();
        
        return knex('users')
          .where('id', user.id)
          .update({ name: fullName });
      });
      
      return Promise.all(updates);
    })
    // Then remove the columns
    .then(() => {
      return knex.schema.table('users', function(table) {
        table.dropColumn('first_name');
        table.dropColumn('last_name');
      });
    });
};
            

Migration Best Practices

General Best Practices

• One Change Per Migration: Keep migrations focused on a single logical change
• Always Include Down Migrations: Make sure every change is reversible
• Never Edit Existing Migrations: Once a migration is in version control, treat it as immutable
• Test Migrations: Always test migrations on a copy of production data
• Version Control: Keep migrations in your code repository
• Meaningful Names: Use descriptive names for migration files
• Backup Before Migrating: Always back up production data before running migrations

Production Migration Strategies

• Run During Low-Traffic Periods: Schedule migrations when usage is minimal
• Break Large Migrations into Smaller Steps: Multiple small migrations are safer than one big one
• Isolation: Run migrations in a transaction when possible
• Monitor Performance: Watch for locks and slow queries during migration
• Have a Rollback Plan: Know exactly how to revert if something goes wrong
• Apply CI/CD Practices: Automate migration testing and deployment

Handling Migrations in Different Environments

Development Environment

In development, migrations should be easy to apply and revert for rapid iteration:

• Run migrations automatically during setup
• Make it easy to reset the database when needed
• Provide seed data for testing

// Example development workflow with Knex
// Reset and set up the database
npx knex migrate:rollback --all
npx knex migrate:latest
npx knex seed:run
            

Staging/Test Environment

Staging environments should mimic production as closely as possible:

• Run migrations as part of CI/CD pipeline
• Test with production-like data volumes
• Verify migration performance

// Example CI/CD workflow
// In your CI/CD pipeline
echo "Running migrations on staging..."
npx knex migrate:latest --env staging

echo "Running tests..."
npm test

// If tests pass, continue to production deployment
            

Production Environment

Production migrations require careful planning and execution:

• Always backup before migrating
• Consider running migrations during maintenance windows
• Monitor closely during and after migration
• Have a rollback strategy ready

// Example production migration script
#!/bin/bash
# Backup the database
echo "Creating database backup..."
pg_dump -U $DB_USER -h $DB_HOST $DB_NAME > backup_$(date +%Y%m%d_%H%M%S).sql

# Run the migration
echo "Running migrations..."
npx knex migrate:latest --env production

# Verify the migration
echo "Verifying database state..."
# Run verification queries or checks

# If verification fails
if [ $? -ne 0 ]; then
  echo "Migration verification failed! Rolling back..."
  npx knex migrate:rollback --env production
fi
            

MongoDB Migration Example with Mongoose

Since MongoDB is schema-less, migrations work differently. However, with Mongoose, we can still benefit from a structured migration approach.

Setting Up mongoose-migrate

// Install mongoose-migrate
npm install mongoose-migrate

// Create a migration
npx mongoose-migrate create add-email-to-users
            

Writing a MongoDB Migration

// migrations/20230317145623-add-email-to-users.js
const mongoose = require('mongoose');
const User = mongoose.model('User');

module.exports = {
  async up() {
    // Get all users without an email field
    const users = await User.find({ email: { $exists: false } });
    
    // Update each user
    const updates = users.map(user => {
      // Generate a placeholder email based on username
      const email = `${user.username.toLowerCase()}@example.com`;
      
      return User.updateOne(
        { _id: user._id },
        { $set: { email: email } }
      );
    });
    
    // Execute all updates
    await Promise.all(updates);
    
    // Update the schema to validate email in future documents
    // (This assumes you're updating your Mongoose schema separately)
    console.log(`Updated ${updates.length} users with placeholder emails.`);
  },
  
  async down() {
    // Remove email field from all users
    await User.updateMany(
      {},
      { $unset: { email: "" } }
    );
    
    console.log('Removed email field from all users.');
  }
};
            

Managing MongoDB Schema Changes

With MongoDB, we need to handle both the data migration and schema changes:

// Update your Mongoose schema
const userSchema = new mongoose.Schema({
  username: { type: String, required: true },
  // Add the new email field
  email: { 
    type: String, 
    required: true, // Will only affect new documents
    validate: {
      validator: function(v) {
        return /^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$/.test(v);
      },
      message: props => `${props.value} is not a valid email address!`
    }
  },
  createdAt: { type: Date, default: Date.now }
});

// Update the model
const User = mongoose.model('User', userSchema);
            

Real-World Migration Scenarios

Scenario 1: Adding a Required Field

You need to add a new required field to a table with existing data. This requires multiple steps:

1. Add the column as nullable
2. Deploy code that populates the field for new records
3. Run a data migration to populate the field for existing records
4. Add a NOT NULL constraint after all data is populated

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

// Step 2: Populate existing data
exports.up = function(knex) {
  return knex('users')
    .select('id', 'contact_info')
    .then(users => {
      const updates = users.map(user => {
        // Extract phone from contact_info JSON or use placeholder
        let phoneNumber = null;
        try {
          const contactInfo = JSON.parse(user.contact_info);
          phoneNumber = contactInfo.phone || '000-000-0000';
        } catch (e) {
          phoneNumber = '000-000-0000';
        }
        
        return knex('users')
          .where('id', user.id)
          .update({ phone_number: phoneNumber });
      });
      
      return Promise.all(updates);
    });
};

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

Scenario 2: Table Splitting

You need to split a large table into two separate tables:

// Step 1: Create new addresses table
exports.up = function(knex) {
  return knex.schema.createTable('addresses', function(table) {
    table.increments('id').primary();
    table.string('address').notNullable();
    table.string('city').notNullable();
    table.string('state').notNullable();
    table.string('zip').notNullable();
    table.string('country').notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
};

// Step 2: Migrate address data
exports.up = function(knex) {
  return knex('users')
    .select('id', 'address', 'city', 'state', 'zip', 'country')
    .then(users => {
      // Insert addresses and track user_id -> address_id mapping
      const addressInserts = [];
      const userUpdates = [];
      
      users.forEach(user => {
        // Skip users with no address
        if (!user.address) return;
        
        // Insert address and then update user
        addressInserts.push(
          knex('addresses')
            .insert({
              address: user.address,
              city: user.city || '',
              state: user.state || '',
              zip: user.zip || '',
              country: user.country || 'Unknown'
            })
            .returning('id')
            .then(([addressId]) => {
              userUpdates.push(
                knex('users')
                  .where('id', user.id)
                  .update({ address_id: addressId })
              );
            })
        );
      });
      
      // Wait for all inserts to complete
      return Promise.all(addressInserts)
        .then(() => Promise.all(userUpdates));
    });
};

// Step 3: Add foreign key constraint
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.foreign('address_id').references('addresses.id');
  });
};

// Step 4: Remove old columns
exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.dropColumn('address');
    table.dropColumn('city');
    table.dropColumn('state');
    table.dropColumn('zip');
    table.dropColumn('country');
  });
};
            

Practice Activities

Activity 1: Create a Migration Plan

Design a migration plan for adding a new feature to an e-commerce application:

1. Add a customer loyalty program
2. Each customer will have a points balance
3. Each order will earn points based on total amount
4. Points can be redeemed for discounts

Your plan should include:

• What tables need to be created or modified
• The order of migrations
• How to handle existing customers
• Any data transformations needed

Activity 2: Implement Knex Migrations

Create a series of Knex migration files for a blog platform with the following features:

1. Users table (id, username, email, password_hash)
2. Posts table (id, title, content, user_id, created_at, updated_at)
3. Comments table (id, content, user_id, post_id, created_at)
4. Add a status field to posts (draft, published, archived)
5. Add tags to posts (many-to-many relationship)

Activity 3: Data Migration Challenge

Create a migration to transform data in the following scenario:

1. You have a products table with a JSON 'metadata' column
2. The metadata contains information about product dimensions
3. You need to extract this data into dedicated columns
4. After extraction, update the application to use the new columns

Original structure:

// Products table
{
  id: 1,
  name: 'Desk Lamp',
  price: 29.99,
  metadata: '{"dimensions": {"height": 45, "width": 15, "depth": 15}, "unit": "cm"}'
}
            

New structure should have:

// Products table
{
  id: 1,
  name: 'Desk Lamp',
  price: 29.99,
  height: 45,
  width: 15,
  depth: 15,
  dimension_unit: 'cm',
  metadata: '{}' // Other metadata preserved
}
            

Further Reading

• Knex.js Migrations Documentation
• MongoDB Schema Versioning Pattern
• Flyway Documentation
• GitHub's Online Schema Change Tool (gh-ost)
• PostgreSQL at Scale: Database Schema Changes Without Downtime