Safe Database Migrations with an AI Pair Programmer
Give your AI reviewer a migration boundary
I want my AI pair programmer writing migration drafts, not holding production credentials. This walkthrough adds display_name to public.users while keeping name available for older application versions. Assume PostgreSQL 18, Bash, PostgreSQL client tools, and an existing users table with id as its primary key and name as non-null text.
My Fluxo setup keeps the editor workflow consistent: one key and one base URL across Claude, GPT, Gemini, DeepSeek, Kimi, GLM, Qwen, Grok, and more. It works with Claude Code, Codex, Gemini CLI, and any OpenAI SDK. For this task, I would ask Claude Sonnet 5.5 for a draft and GPT-6.1 Sol for an independent review.
1. Export schema without handing over customer records
Set DATABASE_URL to your development database and SCRATCH_URL to a separate, disposable database. Keep both outside the agent's environment and terminal permissions. Run these commands yourself. A schema-only export excludes table data, but inspect identifiers and comments before sharing it. PostgreSQL documents the schema-only export option. ([postgresql.org](https://www.postgresql.org/docs/18/app-pgdump.html?utm_source=openai))
git switch -c db/add-display-name — mkdir -p migrations
pg_dump --schema-only --no-owner --no-privileges "$DATABASE_URL" > /tmp/schema.sql
Give the assistant the sanitized users definition and this instruction: “Draft an additive migration only. Preserve name. Explain locks, deployment order, batch backfill, verification, and recovery. Do not execute commands.”
2. Write the smallest additive migration
Review the draft, then save this exact migration. A nullable column without a default avoids an immediate table rewrite, but ALTER TABLE still needs a lock; additive does not mean lock-free. ([postgresql.org](https://www.postgresql.org/docs/18/sql-altertable.html))
printf '%s\n' 'BEGIN;' "SET LOCAL lock_timeout = '2s';" "SET LOCAL statement_timeout = '15s';" 'ALTER TABLE public.users ADD COLUMN display_name text;' 'COMMIT;' > migrations/001_add_display_name.sql
git diff --no-index -- /dev/null migrations/001_add_display_name.sql — Those timeouts are rehearsal starting points, not universal production settings. lock_timeout limits lock waits; statement_timeout bounds statement execution. Adjust them against measured workload behavior. ([postgresql.org](https://www.postgresql.org/docs/18/runtime-config-client.html?utm_source=openai))
3. Rehearse against a disposable users table
First confirm SCRATCH_URL points somewhere expendable. The following command deliberately fails if users already exists, rather than deleting anything.
psql -X -v ON_ERROR_STOP=1 "$SCRATCH_URL" -c "CREATE TABLE public.users (id bigint PRIMARY KEY, name text NOT NULL); INSERT INTO public.users VALUES (1, 'Ada'), (2, 'Lin');"
psql -X -v ON_ERROR_STOP=1 "$SCRATCH_URL" -f migrations/001_add_display_name.sql
psql -X -v ON_ERROR_STOP=1 "$SCRATCH_URL" -c "SELECT id, name, display_name FROM public.users ORDER BY id;"
Expect two rows with unchanged names and null display names. ON_ERROR_STOP makes script errors stop execution and produce a nonzero exit status. ([postgresql.org](https://www.postgresql.org/docs/18/app-psql.html?utm_source=openai))
- Test the old application against the expanded schema before release.
- Repeat rehearsal on representative sanitized data before choosing production timings.
- Verify backup restoration separately before approving any production schema change.
4. Deploy compatible writes before backfilling
Ask the assistant to patch application writes so inserts and name changes update both columns atomically. Keep reads on name initially. Test that behavior, then deploy it after the additive migration. Drain every older writer, including background jobs, before starting the backfill.
Run this batch repeatedly on scratch until it reports UPDATE 0. Each invocation commits separately. PostgreSQL supports using a limited CTE to select rows for an UPDATE. ([postgresql.org](https://www.postgresql.org/docs/18/sql-update.html?utm_source=openai))
PGOPTIONS='-c lock_timeout=2s -c statement_timeout=15s' psql -X -v ON_ERROR_STOP=1 "$SCRATCH_URL" -c "WITH batch AS (SELECT id FROM public.users WHERE display_name IS NULL ORDER BY id LIMIT 1000 FOR UPDATE) UPDATE public.users AS u SET display_name = u.name FROM batch WHERE u.id = batch.id AND u.display_name IS NULL;"
In production, pause between batches and watch latency, replication lag, and dead tuples. Stop on errors; do not hide failures behind automatic retries.
5. Verify the cutover and preserve recovery options
psql -X -v ON_ERROR_STOP=1 "$SCRATCH_URL" -c "SELECT count(*) AS missing FROM public.users WHERE display_name IS NULL; SELECT count(*) AS mismatched FROM public.users WHERE display_name IS DISTINCT FROM name;"
Both counts should be zero. Test application behavior before switching reads. Keep dual writes and the original column throughout the rollback window; revert application reads if needed, rather than dropping data during an incident.
Fluxo bills real tokens, including cache reads and writes, without a monthly fee. If this fits your terminal workflow, consider creating a Fluxo account. Inviting teammates also earns you a referral share when they pay.