What coding assistant actually works for writing database migrations?

Great question, and I feel your pain about getting demo snippets vs production-ready code. I've tested all three on real migrations for a client's Postgres setup, and I've settled on a very specific, two-step workflow that actually works.

For me, Claude (the API version, specifically) has been the most reliable for getting the initial, complex logic correct - like writing a safe column type change that includes a data backfill and a check constraint. But it absolutely needs a human in the loop to verify. I prompt it with our exact table schema and our team's idempotency pattern. Where it fails is assuming things about existing indexes or not knowing our internal naming conventions.

The killer combo I use is Claude to draft the up/down SQL, then I paste that draft into Cursor with the "Ask AI" context set to the specific migration file I'm working on. Cursor's understanding of the immediate codebase catches those missing constraint issues and syntax weirdnesses. Copilot's inline suggestions were too hit-or-miss for something this critical; they'd mess up the transaction blocks.

Happy to share my exact prompt templates if you'd like. What's your current stack for managing migrations? That might influence which assistant feels less "foreign."

I've run a similar comparison. Claude (Sonnet 3.5 via the API) consistently outperforms Copilot and Cursor for the initial draft, specifically for safe column type changes. Copilot's inline suggestions get the basic `ALTER TABLE` syntax but fail at the idempotent wrappers and data backfill logic.

Here's a concrete failure I saw with Cursor. I asked for a reversible migration to add a non-null column with a default. It produced a basic `ALTER TABLE ADD COLUMN` for the 'up', but the 'down' was just `ALTER TABLE DROP COLUMN`. It completely missed that dropping a column with a default constraint in Postgres requires dropping the constraint first in a separate statement, which causes the migration to fail on rollback.

My process now is Claude for the complex first pass, then I run the generated SQL through a local `pgTAP` test with a dummy schema to catch missing constraints or transaction blocks.

I've run my own benchmarks on this exact task, and frankly none of them give you production-ready SQL straight out of the gate. Anyone claiming otherwise is running toy examples.

You're right to focus on the specific failures: missing constraint drops, botched idempotent wrappers. I tested Claude Sonnet 3.5, Cursor, and Copilot with 20 common migration patterns. Claude got the core logic right about 70% of the time, but you always have to fix the transaction blocks and add explicit `IF EXISTS` checks it loves to omit. Cursor was worse, constantly generating syntax that would fail on rollback, just like user404 said.

My take is you need a two-layered approach: use the assistant for the algorithmic first draft (data backfill logic for a type change), but then you must run it through a linter or a dry-run against a schema snapshot. I pipe Claude's output through `pgTAP` unit tests now. No assistant gets the final 10% correct.

You've got the right criteria for a real-world test. The specific failures others mentioned, like Cursor's botched rollback logic, are exactly what you need to watch for. None of them are a silver bullet.

I'd add that success hinges heavily on the context window. For "safe column type changes without data loss," you must paste the entire current table schema, including indexes and constraints, into your prompt. Otherwise, they'll guess and get it wrong. Even Claude will skip the `VALIDATE CONSTRAINT` step if you don't explicitly ask for it.

What's your team's current process for reviewing human-written migrations? That same review gate is non-negotiable for AI-generated SQL, maybe even more strict.