/d1-create-migration

name: cloudflare-d1:create-migration
description: Guided migration creation for Cloudflare D1 with schema change validation, SQL generation, and testing workflow. Use when user wants to create migration, modify schema, add tables/indexes, or change database structure.

D1 Create Migration Command

Overview

Guided migration creation with validation, SQL generation, and safe testing workflow.

Prerequisites

• Existing D1 database configured in wrangler.jsonc
• migrations/ directory exists (created during setup)
• wrangler CLI available

Steps

Step 1: Migration Type

Use AskUserQuestion to determine what kind of migration to create.

**Prompt**: "What type of migration are you creating?"

**Options**: 1. "Add new table" 2. "Modify existing table (add/remove columns)" 3. "Add indexes" 4. "Data migration (update existing records)" 5. "Custom SQL"

**Store as**: `migrationType`

---

Step 2: Gather Migration Details

Based on `migrationType`, collect specific details:

If "Add new table":

**Ask**:

• Table name: `tableName`
• Columns (comma-separated): `columnsList`
• Example: "id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE"
• Add foreign keys? (yes/no): `hasForeignKeys`
• If yes, ask: "Which table.column?" → `foreignKeyReferences`

If "Modify existing table":

**Ask**:

• Table name: `tableName`
• Change type:
• "Add column"
• "Drop column"
• "Rename column"
• Store as: `changeType`
• Column details: `columnDetails`

If "Add indexes":

**Ask**:

• Table name: `tableName`
• Columns to index (comma-separated): `indexColumns`
• Index type:
• "Single column index"
• "Composite index (multiple columns)"
• "Unique index"
• Store as: `indexType`

If "Data migration":

**Ask**:

• Description of data change: `description`
• **Warning**: "Data migrations should be idempotent and tested in staging first. Proceed?"

If "Custom SQL":

**Ask**:

• "Provide SQL file path or paste SQL directly"
• Store as: `customSQL`

---

Step 3: Generate Migration File

Create migration file with timestamp:

# Generate timestamp
TIMESTAMP=$(date +%Y%m%d%H%M%S)

# Create migration filename
MIGRATION_FILE="migrations/${TIMESTAMP}_<description>.sql"

**Generate SQL based on migration type**:

Add Table Example:

-- Migration: Add <tableName> table
-- Created: <timestamp>

CREATE TABLE IF NOT EXISTS <tableName> (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  <columnsList>,
  created_at INTEGER DEFAULT (unixepoch())
);

-- Indexes for foreign keys and commonly queried columns
<for each indexed column>
CREATE INDEX IF NOT EXISTS idx_<tableName>_<column> ON <tableName>(<column>);
</for each>

-- Optimize query planner
PRAGMA optimize;

Add Column Example:

-- Migration: Add column <columnName> to <tableName>
-- Created: <timestamp>

-- SQLite doesn't support ADD COLUMN IF NOT EXISTS
-- Check if column exists first (defensive)
ALTER TABLE <tableName> ADD COLUMN <columnName> <type> <constraints>;

PRAGMA optimize;

Add Index Example:

-- Migration: Add index on <tableName>(<columns>)
-- Created: <timestamp>

CREATE INDEX IF NOT EXISTS idx_<tableName>_<columns> ON <tableName>(<columns>);

PRAGMA optimize;

Data Migration Example:

-- Migration: <description>
-- Created: <timestamp>
-- WARNING: Test in staging first!

-- Example: Update all users' status
UPDATE users
SET status = 'active'
WHERE status IS NULL;

PRAGMA optimize;

**Write Migration File**: Use Write tool to create the SQL file in migrations/

---

Step 4: Validation

Run automated checks on generated migration:

**1. Syntax Validation** (dry-run):

# Note: Dry-run not directly supported, but can test locally
wrangler d1 execute <database-name> --local --file=<migration-file>

**2. Best Practices Check**:

• ✓ Uses `IF NOT EXISTS` for CREATE statements?
• ✓ Has `PRAGMA optimize` at end?
• ✓ Includes indexes for foreign keys?
• ✓ Uses INTEGER for timestamps?
• ⚠ Has ALTER TABLE without IF NOT EXISTS? (warn user)
• ⚠ Has DROP operations? (destructive, warn user)

**3. Warn if Destructive**:

⚠️ Warning: This migration contains destructive operations:
- DROP TABLE <table>
- DROP COLUMN <column>

Recommendations:
1. Backup data first (Time Travel: wrangler d1 time-travel...)
2. Test in staging environment
3. Ensure data is no longer needed
4. Consider archiving instead of dropping

**Output Example**:

✓ Migration validation passed
  - Uses IF NOT EXISTS
  - Includes PRAGMA optimize
  - Indexes created for foreign keys

⚠️ Recommendation: Test in local database first

---

Step 5: Test in Local Database (Optional)

**Ask User**: "Test migration in local development database first? (Recommended)"

**If yes**:

# Apply to local D1
wrangler d1 migrations apply <database-name> --local

# Verify schema
wrangler d1 execute <database-name> --local --command ".schema <tableName>"

# Test query
wrangler d1 execute <database-name> --local --command "SELECT * FROM <tableName>"

**Show Results**:

✓ Local migration applied successfully

Schema after migration:
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  status TEXT DEFAULT 'pending',  ← New column
  created_at INTEGER DEFAULT (unixepoch())
);

Indexes:
- idx_users_email
- idx_users_status  ← New index

**Error Handling**:

❌ Local migration failed:
Error: UNIQUE constraint failed: users.email

Possible causes:
1. Duplicate emails exist in data
2. Column constraint conflicts with existing data

Solution:
1. Edit migration file to handle duplicates
2. Clean data first, then reapply migration

---

Step 6: Apply to Remote (Production)

**Warning Prompt**:

⚠️ Apply migration to remote (production) database?

This will modify your production database:
- Database: <database-name>
- Migration: <migration-file>

Ensure you have:
✓ Tested locally
✓ Reviewed SQL c