Datenbankmigrationen richtig gemacht

// migrations/20200501_001_create_orders_table.ts
import { Kysely, sql } from "kysely";
 
export async function up(db: Kysely<any>) {
  await db.schema
    .createTable("orders")
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("customer_id", "uuid", (col) =>
      col.notNull().references("customers.id"),
    )
    .addColumn("status", "varchar(50)", (col) =>
      col.notNull().defaultTo("pending"),
    )
    .addColumn("total", "decimal(10,2)", (col) => col.notNull())
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`),
    )
    .execute();
 
  await db.schema
    .createIndex("idx_orders_customer_id")
    .on("orders")
    .column("customer_id")
    .execute();
}
 
export async function down(db: Kysely<any>) {
  await db.schema.dropTable("orders").execute();
}

-- Phase 1: EXPAND — add the new column, keep the old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
 
-- Phase 2: MIGRATE — backfill data, deploy code that writes to both
UPDATE users SET full_name = first_name || ' ' || last_name
WHERE full_name IS NULL;
 
-- Phase 3: CONTRACT — remove the old columns (after all code uses full_name)
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

// ❌ Single migration — requires downtime, breaks running code
// Deploy migration: rename column from "name" to "full_name"
// Old code writes to "name" → fails
// New code writes to "full_name" → needs migration first
// You MUST take downtime to coordinate
 
// ✅ Expand-contract — zero downtime
// Deploy 1: Add full_name column, code writes to BOTH name and full_name
// Deploy 2: Backfill full_name from name where null
// Deploy 3: Code reads from full_name only
// Deploy 4: Drop name column

-- ❌ DANGEROUS: locks the entire table while rewriting all rows
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ NOT NULL DEFAULT now();
-- On a 10M row table, this takes minutes with an exclusive lock
 
-- ✅ SAFE: add column as nullable (no rewrite), then backfill in batches
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
-- Instant — no table rewrite
 
-- Backfill in batches to avoid long-running transactions
UPDATE orders SET shipped_at = created_at
WHERE id IN (SELECT id FROM orders WHERE shipped_at IS NULL LIMIT 10000);

-- ❌ Blocks all writes until the index is built
CREATE INDEX idx_orders_status ON orders (status);
 
-- ✅ Builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- Takes longer but doesn't lock the table

async function backfillInBatches(
  db: Kysely<any>,
  batchSize = 5000,
) {
  let updated = batchSize;
 
  while (updated === batchSize) {
    const result = await db
      .updateTable("orders")
      .set({ shipped_at: sql`created_at` })
      .where("shipped_at", "is", null)
      .where(
        "id",
        "in",
        db
          .selectFrom("orders")
          .select("id")
          .where("shipped_at", "is", null)
          .limit(batchSize),
      )
      .executeTakeFirst();
 
    updated = Number(result.numUpdatedRows);
 
    // Yield to other queries between batches
    await new Promise((resolve) => setTimeout(resolve, 100));
  }
}

// Migration with explicit rollback verification
export async function up(db: Kysely<any>) {
  // Step 1: Add new column
  await db.schema
    .alterTable("products")
    .addColumn("sku", "varchar(50)")
    .execute();
 
  // Step 2: Create unique index
  await sql`CREATE UNIQUE INDEX CONCURRENTLY idx_products_sku ON products (sku)`.execute(db);
}
 
export async function down(db: Kysely<any>) {
  await sql`DROP INDEX CONCURRENTLY IF EXISTS idx_products_sku`.execute(db);
  await db.schema.alterTable("products").dropColumn("sku").execute();
}
 
// Rollback test: run in staging before production
// 1. Apply migration (up)
// 2. Verify application works
// 3. Rollback migration (down)
// 4. Verify application still works
// 5. Re-apply migration (up)