Apply Expand-Contract for Backward-Compatible Schema

Add the new column, backfill it, switch reads, then drop the old column so no deploy sees a missing or extra column.

~25 mindatabasemigrationexpand-contract

Task

Apply ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract so the schema change is safe, reversible, and does not lock the table during peak traffic.

When to use this

A migration deploys ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract during peak hours, takes a 40-second lock, and causes a connection pool exhaustion.

Prerequisites

A database with migration tooling configured
A staging environment mirroring production schema

Steps

1. Configure ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract in the project configuration file with the recommended defaults.

ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract

Expected output: The configuration is accepted and ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract runs without errors on the first invocation.

2. Run the check locally to verify ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract produces the expected result before pushing.

pnpm test

Expected output: Local run matches CI behavior; no surprises after push.

3. Add the gate to the CI pipeline so ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract blocks merge on failure.

- name: Run add expand contract pattern
  run: pnpm test

Expected output: Pull requests with failing ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract are blocked from merging.

Troubleshooting

Problem

The check passes locally but fails in CI

Solution

Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.

Problem

Configuration is rejected with a schema error

Solution

Check the tool version matches the config schema; older versions may not support newer fields.

Limitations

The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
Configuration drift is possible if someone edits settings outside version control.

Make migrations safe

Start by measuring the problem. Add the new column, backfill it, switch reads, then drop the old column so no deploy sees a missing or extra column. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run ALTER TABLE users ADD COLUMN email_v2 TEXT; – expand, backfill, switch, DROP COLUMN email; – contract against the current codebase and inspect the output before wiring it into CI.

ALTER TABLE users ADD COLUMN email_v2 TEXT; -- expand, backfill, switch, DROP COLUMN email; -- contract

Verify and roll back

Once the gate is in place, make failures actionable. A failing ALTER TABLE users ADD COLUMN email_v2 TEXT; – expand, backfill, switch, DROP COLUMN email; – contract should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.