ChatGPT Prompt to Write a Database Migration Script
StagePass sells tickets for 600 live venues, and its 2.1-million-row user table has become the bottleneck. Every profile read in the ticketing service drags a password_hash and a marketing flag along with it, and the table is now too wide to index cheaply. The plan is standard: split one fat table into accounts and profiles, backfill the data, and cut over with zero downtime while the PHP service keeps serving traffic.
The risk is not the idea, it is the order of operations. One missing step locks the table for four minutes on a Friday night. This guide gives you the prompt that makes ChatGPT produce a safe, ordered migration script, the schema facts you must feed it, and how to verify before you touch production.
Why You Must Give the Model the Whole Schema
A migration prompt without the current columns, the row count, and the traffic profile produces a textbook script that would break a real system. ChatGPT cannot guess that the users table is read and written 24 hours a day, or that 2.1 million rows make a single ALTER TABLE a lock risk. Feed it four things and the output changes completely:
- The exact current columns and their types.
- The exact target tables and the foreign key between them.
- The row count, so batch sizes are sized for reality.
- The traffic pattern, so every step is judged on whether it can run live.
The Migration Prompt (Give It the Full Picture)
Paste this block, swapping in your own tables and numbers.
You are a senior backend engineer writing a safe MySQL 8 schema migration for a ticketing platform called StagePass.
Current state:
- One table, users, with about 2.1 million rows.
- Columns: id, email, password_hash, full_name, phone, avatar_url, marketing_opt_in, created_at.
- The users table is read and written around the clock by a live PHP service.
Target state:
- accounts (id, email, password_hash, created_at)
- profiles (account_id, full_name, phone, avatar_url, marketing_opt_in), with account_id as a foreign key to accounts.id.
Requirements:
1. Zero downtime. No step may lock the users table for more than a few seconds.
2. Deliver the migration in ordered steps: create the new tables, backfill in batches, keep new writes in sync, verify, then cut over.
3. Use MySQL 8 syntax, and show a batched backfill using a WHERE id > last_id ORDER BY id LIMIT 5000 pattern.
4. Include a verification query that proves the row counts match before cut over, and one that checks for orphaned profile rows.
5. Add a rollback note next to every step.
Output plain SQL for each step, each preceded by a one-line comment explaining why that step is safe to run in production.Three constraints carry the weight here: “zero downtime”, the batch pattern, and the rollback note on every step. Remove any one and the script becomes a weekend outage waiting to happen.
Variables to Replace Before You Run It
- Table names and the foreign key. accounts and profiles are the StagePass names; your target may be different. The foreign key direction tells the model which inserts must happen first.
- The exact column list. Every column that leaves users must land in exactly one new table. Miss one and the cut over silently drops data.
- Row count and batch size. 2.1 million rows with 5000-row batches is about 420 batches, small enough to avoid long locks. On a 40-million-row table you would lower or raise this deliberately.
- Traffic pattern. “Read and written around the clock” is what forces the sync step. A nightly-batch system needs a different plan.
- MySQL version and engine. MySQL 8 with InnoDB gives you online DDL behavior. MySQL 5.6 or another engine changes what is safe.
What a Safe Output Looks Like
A usable result reads close to this:
-- Step 2: create the two target tables. Safe because both are new and empty, so no live traffic touches them yet.
CREATE TABLE accounts (
id BIGINT UNSIGNED NOT NULL,
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_email (email)
);
-- Step 4: backfill in batches. Safe because each batch touches 5000 rows by primary key and never holds a table lock.
INSERT INTO accounts (id, email, password_hash, created_at)
SELECT id, email, password_hash, created_at FROM users
WHERE id > :last_id ORDER BY id LIMIT 5000;
-- Step 7: verify before cut over. Expect both counts equal and the orphan check to return 0.
SELECT
(SELECT COUNT(*) FROM users) AS legacy_rows,
(SELECT COUNT(*) FROM accounts) AS new_rows;
SELECT COUNT(*) FROM profiles p LEFT JOIN accounts a ON a.id = p.account_id WHERE a.id IS NULL;Notice the comment on every step. When the on-call engineer runs this at 2am, the comment is what keeps them from skipping a verification they do not understand.
Ask for the Rollback and the Red Flags
A first draft stops at cut over. Ask for the return path too:
Now add a rollback plan. If row counts do not match after backfill, tell me exactly which rows to inspect and how to stop the sync job. Then list three signs that the migration is going wrong in production and the metric to watch for each.That second question is the one most people forget, and it is the one that saves a release. Typical red flags it returns: a growing lag in the sync job, a rising count of orphaned profile rows, and p99 latency on the accounts insert climbing past its baseline.
Variations Worth Asking For
- Down migration. Ask for the reverse script that restores users from accounts and profiles, useful when the change ships behind a feature flag, a habit covered in the legacy refactor workflow.
- Query rewrite. Ask the model to rewrite the app’s SELECT statements against the new tables, the same skill behind the SQL query generator prompt.
- Dual-write code. Ask for the PHP function that writes to users and the new tables at once, so nothing is lost during the sync window.
The Checklist Before You Touch Production
Even a good script needs a rehearsal. Walk through these items with the team before the maintenance window, not during it.
- Restore a production copy and run the whole migration on it. Time each phase. If the backfill takes 40 minutes on the copy, expect the same live.
- Confirm the sync step is idempotent. Ask ChatGPT to make each sync write safe to run twice, so a retried job cannot create a duplicate profile.
- Freeze schema changes elsewhere. No other deploy touches users during the backfill, or two migrations collide.
- Name the rollback owner. One engineer holds the decision to reverse, and the threshold for that decision is agreed in advance.
- Watch the right metric, not the dashboard. During backfill the number that matters is sync lag, not request volume.
Splitting Without Dropping a Column
The most common mistake when splitting one table into two is deleting the old column on the same night. Do not. Keep users intact through the cut over, route all reads to the new tables behind a flag, and drop the old table only after a full week of clean traffic. If a report still queries users at day three, you will be glad the columns are still there.
Two smaller habits round it out. First, seed profiles so that account_id and id never drift apart, because a misaligned key is the hardest bug to find after the fact. Second, backfill marketing_opt_in as its own small batch job, since a boolean column on 2.1 million rows is cheap to copy but easy to get wrong in a single bulk statement.
Sources & Notes
StagePass, the 600 venues, the 2.1-million-row figure, and all column names in this article are fictional examples used to demonstrate the prompt structure. The method follows standard online schema-change practice: create before backfill, batch by primary key, keep writes synchronized, verify counts, then cut over, with a rollback path defined before the change starts.
Run the final script once on a restored copy of production first. If the verification queries return matching counts there, the same script will return matching counts when it matters.
