sql-migration://

[ sql-migration:// ] experimental

cat: code model: @cf/meta/llama-3.1-8b-instruct

Schema change you can roll out without downtime. Generates multi-step migrations (split into safe deploys) for renames, NOT NULL adds, type changes, backfills.

// system prompt

You design safe rolling-deploy database migrations. User describes a schema change + dialect + table size. You output the change as multiple deploy steps so old code and new code can run simultaneously during rollout.

For each step output:

  ## Step N — <one-line summary>
  Deploy with: <which code-change deploys with this step, e.g. "previous app version still reads/writes old column">

  -- UP
  <SQL>

  -- DOWN (rollback)
  <SQL>

  Notes:
  - <safety / lock / timing notes>

Rules:
- For NOT NULL additions on big tables: never use ALTER TABLE … SET NOT NULL directly (full-table rewrite/lock in older Postgres). Pattern: add CHECK constraint NOT VALID, VALIDATE separately, then ALTER … SET NOT NULL once validated.
- For renames: never single-step (breaks deployed code). Pattern: add new column, dual-write, backfill, deploy code reading new, drop old.
- For type changes that aren't binary-compatible: same dual-column pattern.
- Backfills > 100k rows: chunk in batches; provide a sample backfill loop.
- Always include the DOWN. If a step is truly irreversible (e.g. dropping a column with data), say so explicitly: "DOWN: irreversible after Step N+1 deploys — point of no return".
- Match the user's dialect (Postgres syntax for Postgres, MySQL for MySQL, etc.).

Describe the schema change + dialect + table size estimate

⚡ powered by Cloudflare Workers AI · quota deducted on success

// output

// sample output

## Step 1 — Add a NOT VALID CHECK constraint
Deploy with: existing app version (no code change yet).

-- UP
ALTER TABLE users
ADD CONSTRAINT users_email_verified_not_null
CHECK (email_verified IS NOT NULL) NOT VALID;

-- DOWN
ALTER TABLE users DROP CONSTRAINT users_email_verified_not_null;

Notes:
- NOT VALID skips the full-table scan, so this is essentially instant on 5M rows.
- Existing NULL rows are NOT rejected yet; new INSERTs/UPDATEs that produce NULL will be.

## Step 2 — Backfill the existing NULLs in batches
Deploy with: same existing app version. Run between Step 1 and Step 3.

-- UP (run repeatedly until 0 rows updated)
UPDATE users
SET email_verified = false
WHERE email_verified IS NULL
AND id IN (
SELECT id FROM users WHERE email_verified IS NULL LIMIT 10000
);

-- DOWN
-- Not meaningful — backfill is data-level. Roll back via Step 1 DOWN if needed.

Notes:
- Batches of 10k rows on a 5M table with ~30% NULLs ≈ 1.5M rows ≈ 150 batches.
- Run with a short sleep between batches to avoid lock contention.
- Watch replication lag if you have read replicas.

## Step 3 — Validate the constraint
Deploy with: existing app version (still no code change). Run only AFTER Step 2 completes.

-- UP
ALTER TABLE users VALIDATE CONSTRAINT users_email_verified_not_null;

-- DOWN
ALTER TABLE users DROP CONSTRAINT users_email_verified_not_null;

Notes:
- VALIDATE takes a SHARE UPDATE EXCLUSIVE lock — does not block reads or writes. Will scan the table once.
- If validation fails, a row with NULL still exists. Re-run Step 2 backfill.

## Step 4 — Replace constraint with proper NOT NULL
Deploy with: existing or new app version (the column is fully non-null at this point).

-- UP
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_verified_not_null;

-- DOWN
ALTER TABLE users ALTER COLUMN email_verified DROP NOT NULL;
ALTER TABLE users
ADD CONSTRAINT users_email_verified_not_null
CHECK (email_verified IS NOT NULL) NOT VALID;

Notes:
- In Postgres 12+ ALTER COLUMN … SET NOT NULL uses the validated CHECK constraint as proof, so it's near-instant. On older versions this would scan the table.

// powered by cloudflare workers ai · quota deducted on success ← back to catalog