0
SQL migration scripts with a tested rollback path
⁂auto-checked, 4 days oldAauraNovice
The prompt
Write a database migration for: change_description in database. I need both the forward migration and a working rollback, not just a comment saying 'reverse the above'. Format:
1. Forward migration SQL, in the smallest safe steps (see note on locking below).
2. Rollback SQL that actually restores the prior state, including data if the forward migration transforms or deletes anything (not just structural revert). If perfect rollback is impossible (e.g. a dropped column's data is gone), say so explicitly and propose a mitigation (e.g. rename instead of drop, keep for one release cycle).
3. State which statements take a table lock and roughly how that scales with table size, using database's actual locking behavior, not a generic claim.
4. Idempotency: write the migration so re-running it after a partial failure doesn't error out (use IF NOT EXISTS / IF EXISTS guards where the dialect supports them).
5. A one-line comment above each statement explaining its purpose, for the next engineer reading the migration file.
Current schema: schema
Change needed: change_description
Where it has been run
| Model | Version | People | Broke | Auto | Last |
|---|---|---|---|---|---|
| Claude | Haiku 4.5 | 0 | 0 | 1/1 | 4 days ago |
Example output
```sql
-- forward: rename legacy column, don't drop, to allow safe rollback and give app code a release cycle to migrate
ALTER TABLE users RENAME COLUMN full_name TO display_name;
-- forward: backfill new normalized email column
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_normalized text;
UPDATE users SET email_normalized = lower(trim(email)) WHERE email_normalized IS NULL;
```
```sql
-- rollback
ALTER TABLE users RENAME COLUMN display_name TO full_name;
ALTER TABLE users DROP COLUMN IF EXISTS email_normalized;
```
Note: the rename is metadata-only in Postgres, near-instant lock regardless of table size. The UPDATE backfill does take row locks and should be batched separately if this table exceeds a few hundred thousand rows, batching script available on request. Rollback is fully lossless here since we renamed instead of dropping, and the normalized column is purely derived data safe to discard.
0 comments
Sign in to comment or report what this prompt did for you.
Sign inNo comments yet. Run the prompt and report what you got.