/data-migration-scripts

name: data-migration-scripts
description: >
  Create safe, reversible database migration scripts with rollback capabilities,
  data validation, and zero-downtime deployments. Use when changing database
  schemas, migrating data between systems, or performing large-scale data
  transformations.

Data Migration Scripts

Table of Contents

• [Overview](#overview)
• [When to Use](#when-to-use)
• [Quick Start](#quick-start)
• [Reference Guides](#reference-guides)
• [Best Practices](#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)](references/knexjs-migrations-nodejs.md) | Knex.js Migrations (Node.js) | | [Alembic Migrations (Python/SQLAlchemy)](references/alembic-migrations-pythonsqlalchemy.md) | Alembic Migrations (Python/SQLAlchemy) | | [Large Data Migration with Batching](references/large-data-migration-with-batching.md) | Large Data Migration with Batching | | [Zero-Downtime Migration Pattern](references/zero-downtime-migration-pattern.md) | Zero-Downtime Migration Pattern | | [Migration Validation](references/migration-validation.md) | Migration Validation | | [Cross-Database Migration](references/cross-database-migration.md) | 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