Data Migration Scripts

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Create robust, safe, and reversible data migration scripts for database schema changes and data transformations with minimal downtime.

When to Use

• Database schema changes
• Adding/removing/modifying columns
• Migrating between database systems
• Data transformations and cleanup
• Splitting or merging tables
• Changing data types
• Adding indexes and constraints
• Backfilling data
• Multi-tenant data migrations

Quick Start

Minimal working example:

import { Knex } from "knex";

// migrations/20240101000000_add_user_preferences.ts
export async function up(knex: Knex): Promise<void> {
  // Create new table
  await knex.schema.createTable("user_preferences", (table) => {
    table.uuid("id").primary().defaultTo(knex.raw("gen_random_uuid()"));
    table
      .uuid("user_id")
      .notNullable()
      .references("id")
      .inTable("users")
      .onDelete("CASCADE");
    table.jsonb("preferences").defaultTo("{}");
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());

    table.index("user_id");
  });

  // Migrate existing data
  await knex.raw(`
    INSERT INTO user_preferences (user_id, preferences)
    SELECT id, jsonb_build_object(
      'theme', COALESCE(theme, 'light'),
// ... (see reference guides for full implementation)

Reference Guides

Detailed implementations in the references/ directory:

| Guide | Contents | |---|---| | Knex.js Migrations (Node.js) | Knex.js Migrations (Node.js) | | Alembic Migrations (Python/SQLAlchemy) | Alembic Migrations (Python/SQLAlchemy) | | Large Data Migration with Batching | Large Data Migration with Batching | | Zero-Downtime Migration Pattern | Zero-Downtime Migration Pattern | | Migration Validation | Migration Validation | | Cross-Database Migration | Cross-Database Migration |

Best Practices

✅ DO

• Always write both up and down migrations
• Test migrations on production-like data
• Use transactions for atomic operations
• Process large datasets in batches
• Add indexes after data insertion
• Validate data after migration
• Log progress and errors
• Use feature flags for application code changes
• Back up database before running migrations
• Test rollback procedures
• Document migration side effects
• Version control all migrations
• Use idempotent operations

❌ DON'T

• Run untested migrations on production
• Make breaking changes without backwards compatibility
• Process millions of rows in single transaction
• Skip rollback implementation
• Ignore migration failures
• Modify old migrations
• Delete data without backups
• Run migrations manually in production