Migrating a legacy MySQL app to DynamoDB without a big-bang cutover

The trap: treating migration as an event

The default instinct is to write a script, schedule a Saturday night, put up a maintenance page, and run it. That plan has a fatal property: you only get to test it once, in production, at 2 AM. Anything you got wrong, you discover with users offline and a rollback plan you've never rehearsed.

We inverted it. The migration script became a piece of software we ran dozens of times, in dev and in prod, before the cutover meant anything. By the time it mattered, running it was boring.

That required designing for three properties from the start: it had to be idempotent, incremental, and scopeable.

Idempotent: PutItem, not INSERT

The single most important decision was preserving the source system's UUIDs as DynamoDB partition keys.

const items = members.map((member) => ({
  id: member.id,                    // the MySQL UUID, unchanged
  firstName: member.first_name,
  organizationId: member.organization_id,
  personalBalance: this.convertDecimal(member.personal_balance),
  active: member.active === 1,
  // ...
}));

Because the key is derived from the source row rather than generated, every write is an upsert. Run the migration once, you get the data. Run it fifty times, you get the same data. There's no "did this already run?" bookkeeping table, no partial-run cleanup, no duplicate records.

This one property is what turns a migration from a high-stakes event into a command you type without thinking. Everything else in this post is downstream of it.

Incremental: run it early, run it often

The CLI takes a mode and an optional watermark:

pnpm migrate --mode=initial
pnpm migrate --mode=incremental --since="2024-01-01"
pnpm migrate --mode=cutover
pnpm migrate --dry-run

--since pushes down to the SQL, using the legacy schema's updated_dt column that was already there. (booee_ was the platform's original name; the tables kept the old prefix through the rebrand, the way legacy tables always do.)

SELECT id, first_name, last_name, organization_id, personal_balance, ...
FROM booee_member
WHERE deleted_dt IS NULL AND organization_id IN (?, ?)
  AND updated_dt >= ?

So the real cutover sequence became:

1. Weeks before: run initial against production DynamoDB. The new system now has a full copy of live data, and we can use the real app with real data while the old one keeps serving users.
2. Throughout: run incremental --since=<last run> to catch drift. Each run takes seconds instead of minutes, because it only touches rows that changed.
3. Cutover day: point DNS at the new stack, run one final incremental, done.

The cutover mode clears every target table before seeding: the nuclear option for when you want a guaranteed-clean slate rather than an upsert over accumulated test data. It exists, but by the end we barely used it, because the incremental path had been exercised so many times we trusted it more.

--dry-run short-circuits before any write and reports what would have moved. It's the first thing you run against a new environment.

Scopeable: migrate two organizations, not two hundred

Every query is filtered by an explicit allowlist of organization IDs:

// Organizations to migrate:
// - Booee (admins)
// - a client booster club
export const ALLOWED_ORGANIZATION_IDS = [
  'bd41423d85343aad74f685e2aa0b3796',
  '34ba8f1c037011eb8ab47441e6bf0af4',
];

The legacy database had accumulated years of abandoned signups, test organizations, and orgs that had churned. Migrating all of it would have meant carrying a decade of junk into a brand-new system, and paying to store it.

An allowlist made the blast radius of every run explicitly finite. It also made the migration a product decision instead of a purely technical one: which customers are actually coming with us? That's a much better question to answer deliberately than by default.

The schema is where the real work is

Copying rows is mechanical. Deciding what the rows should become is not, and this is the part where a relational instinct will quietly ruin your DynamoDB design.

In MySQL, booee_member_transaction was a table with a foreign key to booee_member, and you'd JOIN to get a student's transaction history. DynamoDB has no joins, so the access pattern has to be baked into the key.

We used composite sort keys with type prefixes:

memberId=123 + sk=CONTACT#abc              → a parent contact record
memberId=123 + sk=TXN#2024-01-15#def       → a transaction
organizationId=456 + sk=META#ghi           → a custom field definition

Everything that belongs to a student lives under that student's partition, and begins_with(sk, 'TXN#') fetches the transaction history in one query. Embedding the date in the transaction sort key means the results come back already sorted chronologically, for free, with no index and no sort step.

Two other deliberate un-relational choices:

Denormalized organizationId. Contacts and transactions each store the org ID even though it's derivable through the member. In SQL that's a normalization violation you'd be scolded for. In DynamoDB it's what lets us serve "all transactions for this organization, by date" from a GSI without loading every member first. Multi-tenant authorization checks also get to be a field comparison rather than a second read.

Denormalized personalBalance. Each member row carries the sum of their transactions. DynamoDB has no SUM(), and the member list, the app's most-viewed screen, shows a balance per student. Without the rollup, rendering that page would mean querying every student's full transaction history. The cost is that writes now have to maintain it, which is a real and permanent tax on every transaction mutation. We took the trade knowingly, and it's the one I'd re-examine first if the app's read/write mix ever shifts.

The lesson: don't port your schema, port your access patterns. Write down the screens your app actually renders, then design keys backwards from those.

Three things that bit us

mysql2 returns DECIMAL as a string

Money columns come back as "1234.50", not 1234.5. Left alone, every balance in the new system would be a string, and "100" + "50" in JavaScript is "10050". The kind of bug that shows up as a customer emailing about their child's account.

protected convertDecimal(value: unknown): number {
  if (value === null || value === undefined) return 0;
  if (typeof value === 'number') return value;
  return parseFloat(String(value)) || 0;
}

Worth being honest about the residue: this lands money in a float. It's the same representation the legacy app used, so migrating didn't make anything worse, and at booster-club balance magnitudes it's fine. But if I were designing the money model from scratch today, I'd store integer cents.

DynamoDB rejects undefined

MySQL is full of NULLs: optional middle names, blank phone numbers. Map a NULL through and you hand the SDK an undefined, and it throws rather than skipping the attribute. The fix is one client option, but you have to know to set it:

docClient = DynamoDBDocumentClient.from(client, {
  marshallOptions: { removeUndefinedValues: true },
});

Combined with a convertDate helper that returns undefined rather than null for empty timestamps, sparse legacy data lands as genuinely absent attributes, which is what you want, since DynamoDB bills by stored bytes.

BatchWriteItem silently doesn't write everything

This is the one that catches most people, because it fails quietly. BatchWriteItem takes up to 25 items and returns HTTP 200 even when it refused some of them: the rejects come back in UnprocessedItems. If you check the status code and move on, you get a migration that reports success and drops rows under load.

You have to loop, with backoff:

let unprocessed = request;
let retries = 0;

while (Object.keys(unprocessed.RequestItems).length > 0 && retries < 5) {
  const result = await client.send(new BatchWriteCommand(unprocessed));

  if (result.UnprocessedItems && Object.keys(result.UnprocessedItems).length > 0) {
    unprocessed = { RequestItems: result.UnprocessedItems };
    retries++;
    await new Promise((r) => setTimeout(r, Math.pow(2, retries) * 100));
  } else {
    break;
  }
}

Exponential backoff isn't politeness here. Throttling is precisely the condition that produces unprocessed items, so retrying immediately makes it worse.

What I'd add next time

Two gaps I'd close if I were starting over.

A real parity check. We verified with dry-run counts and spot checks against the running app. That catches missing rows; it does not catch wrong rows. What I actually want is a --verify mode that re-reads both sides and diffs field by field, with special attention to the aggregates: recompute every member's balance from their migrated transactions and assert it matches the migrated personalBalance. Row counts are the cheap check that makes you feel safe; value comparison is the one that makes you safe.

Migrator-level result codes. Each migrator returns { table, migrated, errors, duration }, which is good for a human reading the summary, but the process still exits zero when an individual table reports errors. Fine when a person is watching the output. Not fine the moment you want this in CI.

The takeaway

The thing that made this migration calm wasn't clever code. It was making every run repeatable, so that the number of times we'd executed the cutover procedure before cutover day was not one, but dozens.

The result is live at fundraisingaccounts.com: booster clubs are running their fundraising on it today, with balances that survived the trip intact.

Kyle Twogood is the founder of Magrathea Software. He’s been building production software since 1997.