CLI Agents for Database Migrations

Here's the uncomfortable truth about letting an agent handle your schema changes: the agent is genuinely excellent at writing a migration and genuinely dangerous at running one. Those are two completely different acts, and conflating them is how teams end up restoring from backup at 2 a.m. A cli agent database migration workflow that treats "write the migration" and "apply the migration to real data" as the same step is the single riskiest agent pattern in this whole series — because a migration is the one place where "undo" often doesn't exist. You can revert a code change. You cannot always revert a DROP COLUMN that already deleted a million rows.

Let's separate those two acts and build a workflow where the agent does the part it's brilliant at and never the part that can't be undone.

What Is Agent-Assisted Database Migration?

Agent-assisted migration means using the agent to author schema-change code — the CREATE TABLE, the ALTER, the backfill script, the down-migration — while keeping the execution against real data firmly under human control and behind the usual safeguards. The agent is a fast, knowledgeable migration author. It is not, and must never be, the thing that runs destructive DDL against production.

The distinction matters because migrations are stateful in a way code isn't. When you deploy bad code, you roll back to the previous version and the system is restored. When you run a bad migration that drops or transforms data, rolling back the code doesn't bring the data back. The change already happened to the state of the world.

[object Object],
,[object Object], users ,[object Object], ,[object Object], email_verified ,[object Object], ,[object Object], ,[object Object],;

What this does: Adds a nullable-safe column with a default — an additive, reversible change the agent can write correctly and quickly. This is exactly the kind of migration authoring agents excel at, because it's a well-understood pattern with clear rules.

Why CLI Agent Database Migration Needs Extra Care

The stakes are asymmetric in a way most agent tasks aren't. A wrong code edit costs you a revert; a wrong migration can cost you data you can't recover. That asymmetry should change how much autonomy you grant. Everywhere else in this series, the argument has been "contain the blast radius so a mistake is cheap." With migrations, some mistakes have no cheap version — the blast radius is permanent — so the containment has to be stricter.

There's a second reason care matters: agents optimize for the task as stated, and "make this schema change" doesn't automatically include "and make it safe to run on a live table with ten million rows under load." An agent might write a migration that's correct in isolation and catastrophic in production — a change that locks a huge table for minutes, or rewrites every row in a single transaction. The correctness the agent optimizes for and the operational safety production needs are different properties.

⚡ Pro tip: Ask the agent explicitly about lock behavior and table size. "Write this migration so it doesn't lock the table, assuming ten million rows and live traffic" produces a fundamentally different, safer migration than "add this column." The agent knows the safe patterns — online schema changes, batched backfills — but only applies them when the operational constraint is part of the prompt.

The Two Rules That Keep Migrations Safe

The first rule: additive and reversible, always. Use the expand/contract pattern — also called parallel change — so every migration is backward-compatible and can be rolled back. Add the new column before you use it; migrate readers and writers over; only remove the old column in a much later, separate migration once nothing depends on it.

[object Object],
,[object Object], users ,[object Object], ,[object Object], full_name text;
,[object Object],
,[object Object],
,[object Object],

What this does: Splits a rename into safe stages — add the new column, backfill and migrate consumers, and only much later drop the old one. At no point is the system in a broken or unrecoverable state, and every stage can be rolled back because the old column still exists until the very end.

The second rule: the agent writes, a human runs, and it runs against a staging clone first. The migration is authored by the agent, reviewed by a human, dry-run against a copy of production data to catch lock and performance problems, and only then applied to production through your normal deployment process — never by the agent directly.

[object Object],
claude -p ,[object Object], --allowedTools ,[object Object], ,[object Object],
,[object Object],

What this does: Scopes the agent to authoring only — reading and editing migration files — with no permission to execute anything against a database. The human takes the reviewed migration through staging and then production. The agent's speed is captured; its access to irreversible actions is zero.

⚠️ Common mistake: Letting the agent run migrations against a real database as part of an autonomous task. Granting an agent database execution access is granting it the ability to make unrecoverable changes to your data. Migrations run through your deployment pipeline with human review and a staging dry-run — the same as any high-risk change — not from an agent's terminal session.

Where Agents Genuinely Help With Migrations

None of this means keep the agent away from migrations — it means aim it at the authoring work, which is substantial. There's a lot an agent does well here, and it's worth naming so the caution doesn't read as blanket avoidance.

Agents are strong at translating between migration frameworks. Moving from raw SQL to a framework's migration DSL, or between two ORMs, is exactly the kind of mechanical-but-fiddly translation an agent handles faster and more accurately than a human doing it by hand. Point it at an existing migration and ask for the equivalent in your target tool.

They're also good at writing complex, correct data transformations — the gnarly UPDATE ... FROM with a join and a CASE that you'd otherwise spend twenty minutes getting right. And they're excellent at generating the safety scaffolding: the down-migration, a post-migration verification query, a batched backfill script. That scaffolding is tedious enough that humans skip it, which is precisely why having the agent produce it improves safety.

The highest-value use might be review. Hand the agent a migration a human wrote and ask it to find the lock risks, the missing index, the non-reversible step. As a reviewer it has no execution access, so there's no risk, and it catches the operational problems humans miss.

[object Object],
,[object Object], migrations/0042_add_index.sql | claude -p ,[object Object],

What this does: Uses the agent purely to critique a human-written migration for operational hazards, with no database access at all. It's the safest possible use of an agent on migrations and one of the most valuable, because lock and reversibility problems are exactly what tired humans overlook.

⚡ Pro tip: Use the agent to review every migration before it ships, even ones a human wrote. Reviewing carries none of the execution risk of running, and the agent reliably flags the missing CONCURRENTLY, the un-batched backfill, the irreversible drop — the operational issues that don't show up until production. It's free safety.

⚡ Pro tip: Ask the agent to translate your migration into the exact SQL the framework will generate, and read that. Migration DSLs hide what actually runs against the database, and the generated SQL is where the lock behavior lives. Having the agent surface it turns an opaque migration into one you can actually assess for safety.

Real-World Migration Scenarios

A backend engineer at a SaaS company uses the agent to author every migration, always in expand/contract form, and has a standing rule that the agent's prompt must include the table's row count and whether it's under live traffic. The agent writes safe online migrations; the engineer runs them through CI against staging first.

A data platform team uses the agent to write large backfill scripts as batched, resumable operations — process ten thousand rows, commit, repeat — so a backfill that fails halfway can restart without redoing work or holding one enormous transaction. The agent is good at this pattern once told to use it.

A startup CTO handling a risky column-type change uses the agent to generate both the migration and a rollback plan, plus a verification query to confirm data integrity after each stage. The agent produces the whole safety kit; the human executes it deliberately, stage by stage.

⚡ Pro tip: Always have the agent write the down-migration, and actually test it. A migration without a working rollback is a one-way door, and the moment you most need the down-migration is the moment you're least able to write one calmly. Generate and verify the rollback while everything is fine, not during the incident.

Common Mistakes

⚠️ Common mistake: Combining a schema change and a data backfill in one migration. When schema and data change together in a single step, a failure leaves you in an ambiguous half-migrated state that's hard to reason about and hard to roll back. Separate them: change the schema in one reversible migration, backfill the data in another resumable one. Each is independently safe and independently recoverable.

The second mistake is trusting a migration because it succeeded on a small dev database. Dev data is tiny and idle; production data is large and under load. A migration that runs instantly on a thousand rows can lock a table for minutes on ten million while users wait. The dry-run against a production-sized staging clone is what surfaces that before your customers do.

Conclusion

A safe cli agent database migration workflow rests on one separation: the agent authors, a human executes, and execution always passes through staging and review before it touches production. Keep every migration additive and reversible with expand/contract, split schema changes from backfills, always generate and test the down-migration, and never grant the agent database execution access. The agent's speed at writing correct migrations is real value; its distance from the run button is what keeps that value from turning into an unrecoverable mistake.

The expand/contract prompts, the "assume live traffic and N rows" instruction, the down-migration-and-verify pattern — these apply to every migration you'll ever write. Save them in PromptABCD so your next schema change starts from a safe, reversible template instead of the one-way DROP that sends you to the backups.