Using a Coding Agent for Database Migrations

Start by defining the database migration contract

A coding agent can inspect schemas, draft migration scripts, and identify missing tests. Its usefulness depends on the constraints you provide. For this walkthrough, assume a PostgreSQL application stores customer addresses in customers.email and needs a new email_normalized column for consistent lookups. The existing application must keep working throughout deployment.

Give the agent an explicit contract: preserve existing addresses, allow duplicate normalized values initially, avoid a table rewrite when adding the column, and keep production execution under human control. Ask it to inspect the repository before proposing changes. A useful first instruction is: “Find our migration framework, PostgreSQL version, customer schema, deployment process, and existing migration conventions. Cite the files supporting your findings.”

Inspect existing data before choosing migration behavior

Next, have the agent draft read-only queries that count rows, null addresses, and duplicate values after normalization. Define normalization precisely: this example uses lower(trim(email)) for application lookup behavior. Confirm that this matches your product’s address-handling rules before applying it.

Ask the agent to identify indexes, triggers, long-running transactions, and code paths that write customer addresses. Run the approved inspection queries through your normal database access process. Production findings should drive the implementation: a small table and a heavily written table may require different batching and deployment choices.

Configure the coding agent with appropriate access

Use a disposable database populated with synthetic or appropriately sanitized data. Give the agent the repository and local database access needed to reproduce the migration. Keep production credentials outside its environment, and explicitly identify which commands it may execute.

BestRelay, which publishes this walkthrough, provides a quality-first API relay that forwards each request to the official model named in it, without substitution. It covers Claude, GPT, Gemini, DeepSeek, Kimi, GLM, Qwen, Grok, and more, and works with Claude Code, Codex, Gemini CLI, and any OpenAI SDK. Its API base is https://api.bestapirelay.com; follow your client’s configuration instructions for authentication and endpoint setup.

Create a BestRelay account to try this workflow with your preferred compatible coding tool. Billing is pay-as-you-go using provider-reported token counts, with cache reads and writes priced separately. Members also earn a referral commission when people they invite top up.

Generate separate scripts for expansion and backfill

Ask the agent to produce a staged change with reviewable files. First, add email_normalized as a nullable text column without a default. PostgreSQL still needs a table lock for this schema change, so configure a short lock timeout and arrange a retry if the lock is unavailable.

Next, deploy application code that writes both columns whenever an address changes. Keep reads on the existing path until validation passes. Then backfill older rows in small transactions using stable primary-key ranges, updating only rows whose normalized value is null.

Require the agent to explain concurrent-write behavior. The backfill should derive each value from the current email inside the update statement. Every writer, including imports and administrative jobs, must participate in the transition. Add progress reporting and make interrupted batches safe to rerun.

Test failure paths before approving generated scripts

Have the agent run the migration against fixtures containing nulls, whitespace, mixed case, and duplicate addresses. Verify that original email values remain unchanged and populated normalized values match the agreed expression. Test inserts and updates while the backfill runs.

• Interrupt the backfill and confirm that restarting completes it correctly.
• Simulate lock contention and confirm that the schema change exits within its timeout.
• Run application tests against both the expanded schema and the completed backfill.
• Check query plans before adding an index for normalized lookups.

If an index is needed, assess CREATE INDEX CONCURRENTLY. It cannot run inside a transaction block, and a failed build can leave an invalid index. Ask the agent to account for your migration framework’s transaction behavior and document recovery.

Deploy incrementally and verify the resulting data

Review the SQL, application changes, test results, and operational instructions together. Deploy expansion first, then compatible writers, then the backfill. Compare expected and actual normalized values before switching reads, and monitor lock waits, replication lag, errors, and query latency.

Keep the original column through a defined observation period. Recovery can then return reads to the previous application path while preserving newly written addresses. Ask the agent to document that procedure and its prerequisites. Schedule any later constraint enforcement or column removal as a separate migration with its own evidence and review.