Migrations

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/lib/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    // Use unpooled connection for migrations
    url: process.env.DATABASE_URL_UNPOOLED!,
  },
  // Optional: customize migration table name
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
})

// src/lib/db/schema.ts
import { pgTable, text, timestamp, uuid, boolean } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  // NEW: Add a bio field
  bio: text('bio'),
  // NEW: Add email verification
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

# Generate migration SQL
npx drizzle-kit generate

# Output:
# [✓] Generated migration: 0001_add_bio_and_email_verified.sql

-- Generated by Drizzle Kit
ALTER TABLE "users" ADD COLUMN "bio" text;
ALTER TABLE "users" ADD COLUMN "email_verified" boolean DEFAULT false;

# Apply pending migrations
npx drizzle-kit migrate

# Output:
# [✓] Applied migration: 0001_add_bio_and_email_verified.sql
# [✓] Database schema is up to date

# Quick sync schema to database (development only!)
npx drizzle-kit push

# This directly applies schema changes without creating migration files
# WARNING: Don't use in production - changes aren't tracked

# Open Drizzle Studio to browse data
npx drizzle-kit studio
# Opens at https://local.drizzle.studio

// Option 1: Create a new migration that reverts changes

// 1. Modify schema to remove the problematic changes
// 2. Run: npx drizzle-kit generate
// 3. This creates a migration that reverses the previous one

// Option 2: Manual SQL rollback
// Create a file: drizzle/0002_rollback_bio.sql
/*
ALTER TABLE "users" DROP COLUMN "bio";
ALTER TABLE "users" DROP COLUMN "email_verified";
*/

import { platform } from '@/lib/platform'

// Before deploying risky migrations, create a branch
const branch = await platform.database.createBranch({
  name: 'pre-migration-backup',
  parentBranch: 'main',
})

// Run migrations on main
// If something goes wrong, restore from branch

// Or use point-in-time recovery
await platform.database.restore({
  branch: 'main',
  timestamp: '2024-01-15T10:30:00Z', // Before the migration
})

-- Safe: Adding a nullable column
ALTER TABLE "users" ADD COLUMN "bio" text;

-- Safe: Adding a column with default
ALTER TABLE "users" ADD COLUMN "status" text DEFAULT 'active';

-- Note: In PostgreSQL 11+, adding columns with defaults
-- doesn't rewrite the table, making it fast and safe

// WRONG: Removing a column directly breaks running code
// ALTER TABLE "users" DROP COLUMN "old_field"; // DON'T DO THIS

// RIGHT: Three-phase deployment

// Phase 1: Deploy code that stops WRITING to old_field
// (old_field still exists in DB, reads still work)

// Phase 2: Deploy code that stops READING old_field
// (code no longer depends on old_field)

// Phase 3: Drop the column
// ALTER TABLE "users" DROP COLUMN "old_field";

// WRONG: Direct rename breaks running code
// ALTER TABLE "users" RENAME COLUMN "name" TO "full_name"; // DON'T DO THIS

// RIGHT: Add new, migrate data, remove old

// Phase 1: Add new column, write to both
/*
ALTER TABLE "users" ADD COLUMN "full_name" text;
UPDATE "users" SET "full_name" = "name";
*/

// Deploy code that writes to BOTH columns

// Phase 2: Backfill and switch reads
// Deploy code that reads from "full_name"

// Phase 3: Stop writing to old column

// Phase 4: Drop old column
/*
ALTER TABLE "users" DROP COLUMN "name";
*/

-- Standard index creation (BLOCKS writes during creation)
-- CREATE INDEX "posts_title_idx" ON "posts" ("title");

-- Concurrent index creation (NO blocking, but slower)
CREATE INDEX CONCURRENTLY "posts_title_idx" ON "posts" ("title");

-- Note: CONCURRENTLY can't run inside a transaction
-- Drizzle migrations run in transactions by default
-- You may need to run this manually or configure migration behavior

import { db } from '@/lib/db'
import { sql } from 'drizzle-orm'

// For large tables, create indexes outside of migrations
async function createSearchIndex() {
  // This runs outside a transaction
  await db.execute(sql`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS "posts_search_idx"
    ON "posts" USING gin(to_tsvector('english', "title" || ' ' || "content"))
  `)
}

// Run this in a background job or deployment script

name: Deploy

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup Node
        uses: actions/setup-node@v4
        with:
          node-version: '20'

      - name: Install dependencies
        run: npm ci

      - name: Run migrations
        env:
          DATABASE_URL_UNPOOLED: ${{ secrets.DATABASE_URL_UNPOOLED }}
        run: npx drizzle-kit migrate

      - name: Deploy application
        run: npm run deploy

name: Check Migrations

on:
  pull_request:
    paths:
      - 'src/lib/db/schema.ts'
      - 'drizzle/**'

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup Node
        uses: actions/setup-node@v4
        with:
          node-version: '20'

      - name: Install dependencies
        run: npm ci

      - name: Check migration consistency
        run: npx drizzle-kit check

      - name: Verify schema matches migrations
        run: |
          npx drizzle-kit generate --dry-run
          # Fails if there are ungenerated schema changes

// Pre-deployment checklist:
// [ ] Migration tested on database branch
// [ ] Rollback plan documented
// [ ] Backup/snapshot created
// [ ] Off-peak deployment time scheduled (for large tables)
// [ ] Monitoring alerts configured
// [ ] Team notified of deployment

// Post-deployment verification:
// [ ] Migration completed without errors
// [ ] Application health checks passing
// [ ] No increase in error rates
// [ ] Query performance unchanged
// [ ] Rollback plan tested (optional)