Nithin Bharadwaj

Posted on Apr 20

How to Build a Bulletproof Database Migration System in JavaScript

#programming #devto #javascript #softwareengineering

As a best-selling author, I invite you to explore my books on Amazon. Don't forget to follow me on Medium and show your support. Thank you! Your support means the world!

Let me tell you about something that used to keep me awake at night. Picture this: you've built an application that people actually use. Real users, real data. Then you need to change how you store that data. Maybe you need to add a new field, split a table, or change how relationships work. The thought of running SQL commands directly on your production database should make your palms sweat. I've been there. That's where database migrations come in.

Think of migrations as version control for your database structure. Just like Git tracks changes to your code, migrations track changes to your database. You write small, incremental scripts that move your database from one state to another. You can also go backward if something goes wrong. This approach has saved me from disaster more times than I can count.

Let me show you how I handle migrations in my JavaScript projects. I'll start with the foundation - a migration management system. This class keeps track of what's been applied and what needs to run next.

class MigrationManager {
  constructor(database, options = {}) {
    this.database = database;
    this.options = {
      migrationsTable: options.migrationsTable || 'schema_migrations',
      migrationDirectory: options.migrationDirectory || './migrations',
      validateChecksums: options.validateChecksums !== false,
      ...options
    };

    this.migrations = new Map();
    this.appliedMigrations = new Set();
    this.setupMigrationSystem();
  }

  async setupMigrationSystem() {
    // Create the table that tracks migrations if it doesn't exist
    await this.createMigrationsTable();

    // Load which migrations have already been applied
    await this.loadAppliedMigrations();

    // Find all migration files in the directory
    await this.discoverMigrations();
  }
}

This manager class is the brains of the operation. It knows about your database connection, where to find migration files, and which migrations have already been applied. The setupMigrationSystem method gets everything ready to work. First, it makes sure we have a table to track migrations. Then it loads what's already been done. Finally, it discovers new migration files.

Versioning is crucial. Each migration needs a unique identifier that shows its order. I use timestamps because they're naturally ordered and avoid conflicts between team members. Every migration file starts with a timestamp, like 1698765432100_create_users_table.js. This ensures they run in the right order.

async function createMigration(name) {
  const timestamp = Date.now();
  const version = `${timestamp}_${Math.random().toString(36).substr(2, 6)}`;

  const template = `
module.exports = {
  version: '${version}',
  name: '${name}',

  async migrate(db) {
    // Your migration code here
  },

  async rollback(db) {
    // Your rollback code here
  }
};`;

  const filename = `${version}_${name}.js`;
  await writeFile(`./migrations/${filename}`, template);

  return { version, filename };
}

Creating a migration should be easy. This function generates a new migration file with a unique version and basic structure. The version combines a timestamp with a random string. This approach means two developers can create migrations at the same time without conflicts.

Now let's look at what goes inside a migration. A migration has two main parts: the migrate function that applies changes, and the rollback function that reverses them. Always write both. You might think you'll never need to roll back, but trust me, you will.

// A simple migration example
module.exports = {
  version: '1698765432100_abc123',
  name: 'create_users_table',

  async migrate(db) {
    await db.query(`
      CREATE TABLE users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email VARCHAR(255) UNIQUE NOT NULL,
        username VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
  },

  async rollback(db) {
    await db.query('DROP TABLE IF EXISTS users');
  }
};

This migration creates a users table. The migrate function runs the SQL to create the table. The rollback function drops the table if we need to undo the change. Notice how simple and focused it is. Each migration should do one logical change.

Running migrations happens in order. The manager looks at all migration files, sorts them by version, and applies any that haven't been run yet. It keeps track in the database which ones have been applied successfully.

async function runMigrations() {
  const manager = new MigrationManager(database);
  const pending = manager.getPendingMigrations();

  for (const migration of pending) {
    console.log(`Applying ${migration.name}...`);
    await migration.migrate(database);

    // Record that this migration was applied
    await recordMigration(migration);
  }

  console.log(`Applied ${pending.length} migrations`);
}

The key here is that migrations run in a specific order and each is recorded after successful completion. If a migration fails partway through, we don't mark it as completed. This means we can fix the issue and try again.

Rolling back is just as important as moving forward. Sometimes a migration has unexpected consequences. Maybe it causes performance issues, or there's a bug in the logic. Being able to revert safely is non-negotiable.

async function rollbackMigrations(targetVersion) {
  const manager = new MigrationManager(database);
  const applied = manager.getAppliedMigrations();

  // Find which migrations to roll back
  let toRollback = applied;
  if (targetVersion) {
    const index = applied.findIndex(m => m.version === targetVersion);
    toRollback = applied.slice(0, index + 1);
  }

  // Roll back in reverse order
  toRollback.reverse();

  for (const migration of toRollback) {
    console.log(`Rolling back ${migration.name}...`);
    await migration.rollback(database);

    // Remove the migration record
    await removeMigrationRecord(migration);
  }
}

Rollback happens in reverse order. If we applied migrations A, B, then C, we roll back C, then B, then A. This ensures we return to a consistent state. The targetVersion parameter lets us roll back to a specific point in history.

Not all migrations are simple table creations. Real-world scenarios often involve data transformation. Let me show you a more complex example where we change how user roles are stored.

module.exports = {
  version: '1698765432200_migrate_roles',
  name: 'convert_roles_to_enum',

  async migrate(db) {
    // Create a new enum type for roles
    await db.query(`
      CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator')
    `);

    // Add the new column
    await db.query(`
      ALTER TABLE users 
      ADD COLUMN role user_role DEFAULT 'user'
    `);

    // Convert existing data
    const users = await db.query('SELECT id, old_role FROM users');

    for (const user of users) {
      let newRole = 'user';
      if (user.old_role === 'administrator') newRole = 'admin';
      if (user.old_role === 'mod') newRole = 'moderator';

      await db.query(
        'UPDATE users SET role = $1 WHERE id = $2',
        [newRole, user.id]
      );
    }

    // Remove the old column
    await db.query('ALTER TABLE users DROP COLUMN old_role');
  },

  async rollback(db) {
    // Add back the old column
    await db.query(`
      ALTER TABLE users 
      ADD COLUMN old_role VARCHAR(50)
    `);

    // Convert data back
    const users = await db.query('SELECT id, role FROM users');

    for (const user of users) {
      let oldRole = 'user';
      if (user.role === 'admin') oldRole = 'administrator';
      if (user.role === 'moderator') oldRole = 'mod';

      await db.query(
        'UPDATE users SET old_role = $1 WHERE id = $2',
        [oldRole, user.id]
      );
    }

    // Remove the new column
    await db.query('ALTER TABLE users DROP COLUMN role');

    // Remove the enum type
    await db.query('DROP TYPE IF EXISTS user_role');
  }
};

This migration converts string-based roles to an enum type. Notice how the rollback carefully reverses every step. We add back the old column, convert the data back, then remove the new column and enum type. Data migrations like this require extra care because you're changing existing information.

Sometimes you need to handle large datasets. Running updates on millions of rows at once can lock tables and cause downtime. Batch processing solves this by working in small chunks.

async function migrateLargeDataset(db) {
  const batchSize = 1000;
  let offset = 0;
  let processed = 0;

  do {
    // Get a batch of records
    const users = await db.query(
      `SELECT id, data FROM users 
       WHERE processed = false 
       ORDER BY id 
       LIMIT $1 OFFSET $2`,
      [batchSize, offset]
    );

    if (users.length === 0) break;

    // Process this batch
    for (const user of users) {
      // Transform data
      const newData = transformData(user.data);

      await db.query(
        'UPDATE users SET data = $1, processed = true WHERE id = $2',
        [newData, user.id]
      );
    }

    processed += users.length;
    offset += batchSize;

    console.log(`Processed ${processed} records`);

  } while (true);

  console.log(`Total processed: ${processed}`);
}

Batch processing keeps each transaction small and manageable. We process records in groups of 1000 (or whatever size makes sense for your database). This approach prevents timeouts and reduces locking. The processed flag ensures we don't reprocess the same records if the migration needs to restart.

Not all changes are additions. Sometimes you need to remove things. But deleting data or columns can be risky. A safer approach is to deprecate first, then remove later.

async function safeColumnRemoval(db) {
  // First migration: mark column as deprecated
  await db.query(`
    COMMENT ON COLUMN users.old_column 
    IS 'DEPRECATED: Will be removed after 2024-01-01'
  `);

  // Update application code to stop using this column

  // Second migration (weeks or months later): actually remove
  await db.query('ALTER TABLE users DROP COLUMN old_column');
}

This two-step process gives you time to update your application code. First, you mark the column as deprecated. Your application should stop using it. After sufficient time has passed (and you're sure nothing depends on it), you can safely remove it.

Testing migrations is critical. I never run a migration in production without testing it first. Here's how I structure my migration tests.

describe('User table migration', () => {
  let testDb;

  beforeEach(async () => {
    // Set up a test database
    testDb = await createTestDatabase();
  });

  afterEach(async () => {
    // Clean up test database
    await testDb.close();
  });

  test('migration applies correctly', async () => {
    // Run the migration
    const migration = require('./migrations/1698765432100_create_users_table');
    await migration.migrate(testDb);

    // Verify the table was created
    const tables = await testDb.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_name = 'users'
    `);

    expect(tables).toHaveLength(1);
  });

  test('rollback works correctly', async () => {
    // Apply then roll back
    const migration = require('./migrations/1698765432100_create_users_table');
    await migration.migrate(testDb);
    await migration.rollback(testDb);

    // Verify table was removed
    const tables = await testDb.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_name = 'users'
    `);

    expect(tables).toHaveLength(0);
  });
});

Each migration gets its own test file. We test both the migrate and rollback functions. The test database is isolated from development and production databases. This setup catches issues before they reach real data.

Sometimes you need to run custom validation during a migration. Maybe you need to check data quality or enforce business rules.

async function validateDataDuringMigration(db) {
  // Check for invalid data before making changes
  const invalidUsers = await db.query(`
    SELECT id, email 
    FROM users 
    WHERE email NOT LIKE '%@%'
  `);

  if (invalidUsers.length > 0) {
    throw new Error(
      `Found ${invalidUsers.length} users with invalid emails. ` +
      'Fix data before running migration.'
    );
  }

  // If validation passes, proceed with migration
  await db.query(`
    ALTER TABLE users 
    ADD CONSTRAINT valid_email 
    CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,}$')
  `);
}

Validation migrations check that data meets certain criteria before applying changes. If the data isn't ready, the migration fails with a clear error message. This prevents applying changes to invalid data.

Database constraints are another important aspect. Adding constraints helps maintain data integrity, but they need to be added carefully.

async function addConstraintsSafely(db) {
  // First, check if any existing data violates the constraint
  const violatingRows = await db.query(`
    SELECT id 
    FROM users 
    WHERE username IS NULL 
    OR LENGTH(username) < 3
  `);

  if (violatingRows.length > 0) {
    // Fix the data first
    await db.query(`
      UPDATE users 
      SET username = 'user_' || id 
      WHERE username IS NULL OR LENGTH(username) < 3
    `);
  }

  // Now it's safe to add the constraint
  await db.query(`
    ALTER TABLE users 
    ADD CONSTRAINT username_not_null 
    CHECK (username IS NOT NULL AND LENGTH(username) >= 3)
  `);
}

This migration first identifies rows that would violate the new constraint. It fixes those rows, then adds the constraint. This approach prevents the constraint from failing due to existing bad data.

Index management is another common migration task. Adding indexes can improve performance, but they come with costs during creation and maintenance.

async function manageIndexes(db) {
  // Add index for common query patterns
  await db.query(`
    CREATE INDEX idx_users_email_status 
    ON users(email, status) 
    WHERE status = 'active'
  `);

  // Remove unused indexes
  const unusedIndexes = await identifyUnusedIndexes(db);

  for (const index of unusedIndexes) {
    await db.query(`DROP INDEX IF EXISTS ${index.name}`);
  }
}

This migration adds a partial index for a common query pattern while removing indexes that aren't being used. Partial indexes (with WHERE clauses) are smaller and faster than full table indexes.

Sometimes you need to coordinate migrations across multiple services. This requires careful planning and communication.

async function coordinatedMigration(db, serviceClient) {
  // Step 1: Prepare database changes
  await db.query('ALTER TABLE orders ADD COLUMN new_field VARCHAR(255)');

  // Step 2: Notify other services
  await serviceClient.notify({
    event: 'schema_change',
    change: 'orders.new_field_added',
    timestamp: Date.now()
  });

  // Step 3: Wait for acknowledgment
  await waitForAcknowledgements(['inventory-service', 'billing-service']);

  // Step 4: Backfill data
  await backfillNewField(db);
}

When multiple services use the same database, changes need coordination. This pattern adds a field, notifies dependent services, waits for them to acknowledge, then proceeds with data population.

Finally, let me share my personal checklist for every migration. This has prevented many late-night debugging sessions.

const migrationChecklist = {
  before: [
    'Test migration on development database',
    'Verify rollback works correctly',
    'Check for data inconsistencies',
    'Estimate execution time on production data size',
    'Schedule during low-traffic period',
    'Create database backup',
    'Notify team members',
  ],

  during: [
    'Monitor performance metrics',
    'Watch for errors or timeouts',
    'Log progress at regular intervals',
    'Have rollback script ready',
  ],

  after: [
    'Verify application works correctly',
    'Check database constraints',
    'Monitor for performance regressions',
    'Update documentation',
    'Remove deprecated code that used old schema',
  ]
};

This checklist covers the entire migration process. The "before" items are about preparation and safety. The "during" items focus on monitoring and having an escape plan. The "after" items ensure everything works correctly and stays maintainable.

Database migrations don't have to be scary. With the right approach, they become a routine part of application development. Start simple, test thoroughly, and always have a way back. The techniques I've shown here have evolved from years of trial and error. They've handled everything from small startups to large-scale applications with millions of users.

Remember that migrations are about more than just changing database structure. They're about managing risk, preserving data, and enabling your application to evolve. Each migration is a small, safe step forward. Taken together, they allow your application to grow and adapt without breaking what already works.

The key insight I've gained is this: treat your database schema like you treat your code. Version it, test it, review changes carefully, and deploy incrementally. This mindset shift transforms database changes from a source of anxiety into just another part of your development workflow.

Start with simple table creations and work up to complex data transformations. Build your migration system incrementally too. Add features as you need them. The most important thing is to begin tracking your schema changes systematically. Once you have that foundation, everything else becomes much easier.

I still get nervous before big migrations, but now I have confidence in the process. The system handles the details, and I can focus on the actual changes needed. That peace of mind is worth every line of migration code I've written.

📘 Checkout my latest ebook for free on my channel!

Be sure to like, share, comment, and subscribe to the channel!

101 Books

101 Books is an AI-driven publishing company co-founded by author Aarav Joshi. By leveraging advanced AI technology, we keep our publishing costs incredibly low—some books are priced as low as $4—making quality knowledge accessible to everyone.

Check out our book Golang Clean Code available on Amazon.

Stay tuned for updates and exciting news. When shopping for books, search for Aarav Joshi to find more of our titles. Use the provided link to enjoy special discounts!