AI Code Generation for Database Migrations: Safety Patterns That Work

Why Migrations Are the Hard Case

Database migrations are the hardest case for AI code generation. They are:

• Irreversible in production. A wrong migration can lose data; a rollback is rarely as clean as the forward path.
• Variable in safety properties. A migration that's safe on a 1k-row table can lock production on a 100M-row table.
• Coupled to deployment timing. A schema change that's safe before a code deploy is fatal after one.
• Hard to test. Test environments rarely match production scale.

Despite that, AI can generate safe migrations with the right guardrails. This post is the rule set we apply.

The Non-Negotiables

Before letting AI generate any migration, these rules must be enforced by the validation pipeline:

• No DDL operation that takes a long-held exclusive lock on a production-sized table. ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT ... on Postgres is the canonical example before Postgres 11; on MySQL it can lock for hours on large tables depending on engine.
• No DROP of anything in the same migration as an ADD. Drops are a separate, later migration after dependent code is deployed.
• No migration without a forward-only flag. Reversible-down migrations create false confidence; they rarely work in production.
• No multi-statement migration without explicit transactional boundaries. Half-applied migrations are the worst recovery case.
• No data migration in the same step as a schema migration. Separate the structural change from the data backfill.

These five rules eliminate the majority of dangerous AI-generated migrations.

The Two-Migration Pattern

The safest pattern for non-trivial migrations is two migrations:

Migration 1 — Additive only. Add the new column nullable. Add the new index concurrently. Add the new table. Backfill data with a separate script. The application starts dual-writing.

Migration 2 — Cleanup, after dual-write is verified. Drop the old column. Drop the old index. Drop the old table. The application stops writing the old path.

AI agents handle this pattern well if explicitly told to use it. Without explicit instruction, AI generates the "obvious" single migration that is unsafe.

EnsureFix's per-repo config can declare "all schema changes use the two-migration pattern" and the SchemaAgent enforces it.

Postgres-Specific Patterns

Postgres has well-known unsafe operations:

• ALTER TABLE ... ADD COLUMN ... NOT NULL — locks unless default is volatile or column is nullable in 11+.
• CREATE INDEX (without CONCURRENTLY) — blocks writes.
• ALTER TABLE ... ADD CONSTRAINT ... CHECK — full table scan with lock.
• ALTER TABLE ... ALTER COLUMN ... TYPE — table rewrite.

Safe alternatives:

• Add column nullable, backfill, then add NOT NULL with ADD CONSTRAINT ... NOT VALID and VALIDATE CONSTRAINT separately.
• Always CREATE INDEX CONCURRENTLY.
• Add constraints NOT VALID first, validate separately.
• For type changes, add new column, dual-write, backfill, swap, drop old.

EnsureFix's SchemaAgent has Postgres-specific knowledge of safe and unsafe patterns and refuses to generate the unsafe ones unless escalated to human review.

MySQL-Specific Patterns

MySQL with InnoDB has different rules:

• Online DDL has improved (5.6+) but still has subtle behavior.
• Some operations require copy-and-rebuild (most index changes pre-8.0, some still in 8.0).
• Triggers and foreign keys interact in ways that can deadlock.

The safe pattern: use pt-online-schema-change or gh-ost for non-trivial changes on large tables. AI agents should generate the gh-ost / pt-osc invocation, not the raw ALTER TABLE.

ORM-Generated vs Hand-Written Migrations

Most enterprise codebases use ORM-generated migrations: Django, Rails, EF Core, Hibernate, Alembic, TypeORM. AI works well with these because the framework constrains the output:

• AI runs makemigrations (or equivalent) to let the framework generate the migration.
• AI inspects the generated migration for unsafe patterns.
• If unsafe, AI rewrites it to follow the safe pattern (often by splitting into two migrations).
• AI commits the migration file alongside the model change.

The validation pipeline then runs the migration against a snapshot of the production schema (not the production data) to verify it executes.

Data Migrations

Data migrations — backfilling values, transforming columns — are different from schema migrations and need different rules:

• Always batched. No UPDATE on a billion-row table without batching.
• Always idempotent. A re-run of the migration must produce the same end state.
• Always with a kill switch. A long-running data migration must be stoppable mid-run without leaving inconsistent state.
• Never in a synchronous deployment step. Data migrations run async, post-deploy, monitored.

AI agents generate data migrations following these patterns when the per-repo config declares the pattern. EnsureFix's MigrationAgent enforces batched-with-checkpoint structure for data migrations over a configurable row threshold.

Validation Before Merge

The validation pipeline for an AI-generated migration:

• Schema dry-run. Apply the migration to a schema-only snapshot of production. Verify no errors.
• Lock analysis. For Postgres, run pg_get_locks analysis to predict lock impact. For MySQL, check operation type against the known-unsafe list.
• Reversibility analysis. For migrations marked as reversible, verify the down direction actually works on the test schema.
• Test suite run. All existing tests must pass against the migrated schema.
• Generated query review. If the migration changes a column the app queries, the SchemaAgent flags affected queries for review.

Without these, AI-generated migrations are exactly as dangerous as human-generated migrations — which is to say, very. With them, AI migrations are safer than the median human migration because the rules are consistently enforced.

Where Humans Stay in the Loop

• Migration timing decisions. When to deploy the migration relative to the code change.
• Production scale judgment. "Is this safe on our 500M-row table?"
• Backup verification. Before any non-trivial migration, verify backup is fresh.
• Cross-region rollout decisions. Multi-region database changes need human orchestration.

AI handles the mechanical generation and the safety pattern enforcement. Humans handle the judgment about timing and scale.

Audit Requirements

For regulated environments, every migration must be auditable:

• Who proposed it (the AI, with the originating ticket).
• What it changes.
• Validation report (which gates passed).
• Who approved it.
• When it shipped.

EnsureFix's per-PR audit trail captures all of this. See [SOC 2 compliance for AI code generation](/blog/soc2-compliance-checklist-ai-code-generation).

Recovery Planning

Even with all the safety, AI migrations can go wrong (so can human ones). The recovery plan:

• Forward-fix preferred. A new migration that fixes the broken state.
• Snapshot restore as last resort. Document the recovery time objective.
• Migration log retention. Keep the AI's reasoning trace; if recovery is needed, the audit trail explains what was attempted.

What Not to Do

• Don't let AI auto-merge migrations. Even high-confidence migrations route through human review. The blast radius is too high to trust the validation pipeline alone.
• Don't let AI run migrations in production. Migrations execute through the team's existing migration workflow. AI generates the file; deployment runs it.
• Don't skip the dry-run. Even "obvious" migrations should run against the schema snapshot before merge.

Cost Economics

Per-migration cost is higher than per-PR cost because the validation is more thorough — schema dry-run, lock analysis, related-query review. Typical per-migration cost: $4-$8.

The value: a single avoided production outage from a bad migration pays for years of AI migration spend.

Summary

AI can generate safe database migrations when the validation pipeline enforces the non-negotiables: no risky DDL, two-migration pattern for non-trivial changes, batched data migrations, dry-run before merge, human review gate, full audit trail. Without these guardrails, AI-generated migrations are dangerous; with them, they are safer than the average hand-written migration because the rules are consistently applied.

For the cross-cutting validation pattern, see [enterprise safety layers](/blog/enterprise-safety-ai-generated-code). For the broader workflow, see [the autonomous PR pipeline](/blog/autonomous-pull-request-workflow-guide-2026).