Database Migration Best Practices

Design database transfers around verifiable invariants: read-only sources, explicit type rules, deferred indexes, and repeatable recovery tests.

Five engineering practices built into the engine

  • Never silently coerce unknown types: Treat unrecognized column types as a hard blocker until a human picks an explicit target mapping.
  • Defer secondary indexes and foreign keys: Load rows first, then build indexes and add foreign key constraints in Stage 6 for faster throughput and clean cycle handling.
  • Use keyset Primary Key checkpoints: Resume interrupted transfers strictly where the last batch ended with WHERE pk > last_pk. Refuse in-place appends on PK-less tables.
  • Preserve fixed-point precision and NULL semantics: Transfer DECIMAL values as exact strings and hash NULL differently from empty strings.
  • Keep production credentials out of plans and reports: Treat plan.json and report.html as shareable audit artifacts free of secrets.

Frequently asked questions

How should I handle tables without a Primary Key?

Add a primary key on the source before migration if possible. Otherwise treat partial transfer interruptions as a full reload into a clean target table.