中文
devexamples

Drizzle Migration Schema Diff

Evolve a Drizzle PostgreSQL schema with generated SQL migrations, reviewing column additions as nullable first, then backfilling safely.

TypescriptNodeData Integrity

Code

snippet
import { pgTable, text, integer, timestamp, index } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

// 初始 schema（drizzle/0000_init.sql 由 drizzle-kit generate 产出）
const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  customer: text('customer').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// 增量迁移：新增可空列 → 回填 → 收紧约束
const ADD_COLUMN = sql`
  ALTER TABLE invoices
    ADD COLUMN due_date timestamp;
`;

const BACKFILL = sql`
  UPDATE invoices
    SET due_date = created_at + interval '30 days'
    WHERE due_date IS NULL;
`;

const TIGHTEN = sql`
  ALTER TABLE invoices
    ALTER COLUMN due_date SET NOT NULL;
`;

async function applySteps(db, steps: ReturnType<typeof sql>[]) {
  for (const step of steps) {
    await db.execute(step);
  }
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS invoices_due_date_idx
      ON invoices (due_date);
  `);
}

console.log('three-step migration prepared: add, backfill, tighten');

Related Examples