Skip to main content

Schema Migration Automation With AI

5 min read
Dba

Dba

AI can draft migrations. You own the rollback plan and the order of operations.

Schema Migration Automation With AI

TL;DR

  • AI can draft migration scripts from a schema diff or description. It will get ordering wrong, miss backfills, and sometimes suggest unsafe operations.
  • Use AI for first drafts. You review for: order, locking, rollback, and production safety.
  • Migrations are one of the highest-risk DB operations. AI doesn't share the consequences. You do.

Schema migrations are high-stakes. A bad migration can lock tables for hours, corrupt data, or take down the app. AI can generate migration scripts quickly. It will also generate scripts that look right and fail in prod. Your job: use AI for speed, enforce safety yourself.

What AI Can Help With

Drafting from a spec:

  • "Add column X to table Y, nullable, default Z." AI can write the ALTER TABLE. Often correct for simple cases.
  • Verify syntax for your DB engine. Verify the semantics. "Nullable" vs. "NOT NULL with default" — different implications.

Generating from a diff:

  • "Here's the current schema. Here's the target. Generate migrations." AI can produce a sequence.
  • Order matters. Additive before subtractive. Dependencies first. AI might get it wrong. You check.

Rollback scripts:

  • "Write the reverse migration." AI can draft. You verify it actually reverses. Edge cases (data loss, constraints) need your eye.
  • Always have a rollback. Always test it.

What AI Gets Wrong

Order of operations:

  • Add column before dropping the old one. Create new table before backfilling. AI might suggest dropping a column that's still in use.
  • You know the app. You know what's live. Order correctly.

Locking and downtime:

  • "ALTER TABLE ADD COLUMN" — in some DBs this locks the whole table. AI might not consider that. You need to.
  • Large tables need special handling: online DDL, pt-online-schema-change, or batched approaches. AI rarely suggests these.

Data backfills:

  • New column needs values for existing rows. AI might write a simple UPDATE. On 100M rows that can be a disaster.
  • Batched backfills, background jobs, or phased rollout — you design that. AI might draft; you refine.

Cross-DB compatibility:

  • PostgreSQL, MySQL, SQL Server — syntax differs. AI mixes them. Always verify for your engine.
  • If you use a migration tool (Flyway, Liquibase, etc.), AI might not know its format. Adapt.

The Safe Workflow

  1. Draft — Use AI to generate initial migration(s). Include context: engine, table sizes, downtime tolerance.
  2. Review — Order, locking, rollback. Would this lock prod? For how long?
  3. Test — Run in staging with production-like data. Measure duration. Verify rollback.
  4. Execute — With a runbook. With monitoring. With a rollback plan you've tested.

Building Institutional Knowledge

  • Migration playbook. Document patterns that work for your stack. "For large tables we do X." AI can suggest; you codify.
  • Post-mortems. When a migration goes wrong, document why. AI won't read it. Future you will.

Hand-write migration. Manually sequence. Test rollback. Pray. Hours of careful work.

Click "Schema Migration With AI" to see the difference →

Quick Check

AI generated a migration to add a NOT NULL column to a 100M row table. What could go wrong?

Do This Next

  1. Draft one migration with AI — A simple change. Review it. List every assumption AI made that could be wrong. That's your review checklist.
  2. Document your migration safety rules — What must always be true? (e.g., "rollback tested," "no long locks without approval.") Share with the team.
  3. Run a dry-run — Take an upcoming migration. Have AI draft it. Compare to what you'd write. Close the gap.