💻 Coding
SQL Migration Reviewer: Expand, Contract, Rollback
Review a SQL migration for expand/contract safety, locks, data backfill, and rollback, without inventing dialect features.
0Reviews
Prompt
Act as a migration reviewer. Read the SQL and the stated engine. Do not invent functions or flags the dialect does not have. Prefer expand/contract over rewrite-in-place. Inputs: - Engine and version: [Postgres 16 / MySQL 8 / SQLite / other] - Migration SQL: [Paste] - ORM wrapper if any: [Prisma / Alembic / Rails / raw] - Table size / traffic notes: [Rows, hot path] - Expand or contract phase: [Expand / Backfill / Contract / Unsure] - Rollback plan I think I have: [Plan] - What must not go down: [SLA] Generate: 1. Dialect check: quote any statement that is not valid for Engine. 2. Lock and rewrite risk: table rewrites, full scans, missing CONCURRENTLY or ALGORITHM=INPLACE as applicable. 3. Expand/contract map: what should ship in expand, what must wait for contract, what backfill is missing. 4. Data safety: NOT NULL without backfill, drop column while readers exist. 5. Rollback: DOWN SQL only if Inputs support it. Else NO_DATA. 6. Annotated SQL: comments only. 7. Go / no-go: one sentence. Constraints: - Never invent a Postgres extension. - Do not recommend FORCE INDEX on Postgres. - If Engine is SQLite, do not suggest CONCURRENTLY. - No terraform, Next.js, regex, git, or README clones.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Examples
Example Input
Engine: Postgres 16.3 SQL: ALTER TABLE tide_windows ADD COLUMN source text; ALTER TABLE tide_windows ALTER COLUMN source SET NOT NULL; CREATE INDEX idx_tide_windows_station ON tide_windows (station_id); ORM: Alembic raw op.execute Size: 18 million rows, hot path reads station_id every request Phase: Unsure Rollback: alembic downgrade but we have no downgrade() SLA: reads must not wait on a table rewrite during 9-5 PT
Example Output
1. Dialect Postgres 16: nullable ADD COLUMN is metadata-only. SET NOT NULL on 18M is a scan plus lock risk. CREATE INDEX without CONCURRENTLY takes a write lock. 2. Locks Nullable add: OK for 9-5. SET NOT NULL in the same file: not OK. Blocking index: not OK. 3. Expand / contract Expand: ADD COLUMN source text NULL; CREATE INDEX CONCURRENTLY in its own migration (not in a transaction). Backfill: chunked UPDATE ... SET source = 'noaa' WHERE source IS NULL. Contract later: SET NOT NULL after 0 nulls. 4. Data safety No DEFAULT, so old rows stay NULL. SET NOT NULL will fail or lock. Hot path does not write source yet. 5. Rollback NO_DATA for downgrade(). DROP COLUMN on 18M is a rewrite; prefer leave the nullable column. DROP INDEX CONCURRENTLY IF EXISTS idx_tide_windows_station. 6. Annotated ALTER TABLE tide_windows ADD COLUMN source text; -- OK expand -- WAIT: ALTER COLUMN source SET NOT NULL; -- WAIT / split: CREATE INDEX CONCURRENTLY idx_tide_windows_station ON tide_windows (station_id); 7. Go / no-go No-go as written. Ship nullable add plus CONCURRENTLY index; backfill; NOT NULL later. 9-5 PT SLA is not met by this file.