Database Migrations in the Deploy Pipeline: Order and Safety

During a rolling deploy, from the moment the first new instance starts until the last old instance stops, your database serves two code versions. The old instance does not know about the new column, and it will insert rows without it. The new instance will insert rows with it. If either version breaks on the other's data, you have a failed deploy that is far worse than a failed build, because the failure only appears under live traffic.

PlanetScale's engineering write-up frames this as a reason never to couple application and schema changes in one atomic step: they cannot deploy atomically, so something will always be briefly inconsistent. The practical translation is a checklist every migration must pass before merge:

The first row is the one that gets most teams through their first hundred migrations. Prisma's guide to expand and contract is explicit about the requirement: new columns must be nullable or carry defaults so the current client's behavior cannot fail. That is the mechanical reason an additive change is safe and a destructive one is not.

The table's last two rows matter just as much. A DROP COLUMN is a one-way door for any instance still selecting * or referencing that name, and a long backfill in the same transaction as a DDL statement can hold locks far beyond the deploy window. Octopus's rollback analysis points out the trap directly: rollback scripts that drop a column have to answer who wrote the data in it, whether users will re-key it, and whether it is a foreign key elsewhere. If those questions have no answer, the column should not be dropped in the same release as the code change.

The pattern appears under several names (expand and contract, parallel change, expand/migrate/contract) and the substance is the same: introduce the new shape alongside the old, move data and readers across, then remove the old shape once nothing references it. Tim Wellhausen's paper states the goal cleanly: implement breaking changes in multiple steps, each of which does not break the system and can be reverted.

Worked example. A users table has email, and you want email_address with different constraints. The naive path is one ALTER TABLE ... RENAME, which breaks every old instance the moment it runs.

Release 1, expand. Add email_address as nullable. No application change required for the old code to keep working, because the old code never mentions the new column.

ALTER TABLE users ADD COLUMN email_address text NULL;

Release 2, dual write. Deploy code that writes to both columns on every insert and update, and reads from email as before. Old instances still write only email; that is fine, the backfill will catch them.

UPDATE users SET email_address = email, updated_at = now()
WHERE id BETWEEN :start AND :end AND email_address IS NULL;

Run that in batches (a few thousand rows at a time, committing between batches) until no rows match. On a table with hundreds of millions of rows, a single statement is not an option, which is why the zero-downtime walkthrough on The Backend Developer treats backfill as its own phase rather than part of the schema step.

Release 3, switch reads. Change reads to email_address, keep dual writes for safety, and add a check in code: if the two values diverge, log it and prefer the old column. Run it for a release. If no divergence shows up, you have evidence the migration is correct before you remove anything.

Release 4, stop writing old. Remove the write to email.

Release 5, contract. Drop email. This is the only irreversible step in the sequence, and by this point no code deployed in any environment references the column.

The same shape handles a column split (add first_name and last_name, dual write, backfill by splitting on the existing separator, switch, drop name) and a type change (add a new typed column, cast during backfill, switch, remove). The Medium overview of the pattern names the same three phases and emphasizes why it is worth the extra releases: at every intermediate point, a targeted fix or a rollback is possible without touching data that is still needed.

When application-level dual writes are not enough

Dual writing from application code assumes your application is the only writer. It usually is not. Cron jobs, event consumers, admin scripts, imports from a partner, and a report that runs an UPDATE nobody remembered all touch the same table. If even one writer does not know about the new column, the new column goes stale.

The standard answer is a database trigger that mirrors changes from the old column to the new one during the transition, and the trigger and shadow-read techniques described here cover the mechanics. Triggers are not free: they add write overhead and their behavior is invisible to application developers. Use them when you cannot enumerate every writer, and drop them as part of the contract release.

The first thing to accept: you almost never want to reverse a migration in production. Octopus makes the case bluntly: database rollbacks, whether by running reverse scripts or restoring a backup, will likely lose data. A backup restore loses every write since the backup was taken. A reverse script has to guess what to do with rows that arrived in the new column after the migration ran.

So the safe answer is structural, not procedural. If every migration is backward compatible, the previous code version still runs correctly against the new schema. Rolling back the deploy is then just redeploying the previous artifact, with no schema operation at all. This is the single biggest argument for expand and contract: it makes rollback a code problem, and code rollback is a solved problem.

That gives a simple decision rule for what to do when a migration is in flight and something breaks:

The bottom two rows are the ones that should be rare. If you find yourself in them, the fix is usually to split the migration into smaller pieces and re-run it as an additive change. Note also that a pipeline rollback configured as "roll back the entire pipeline" will re-run earlier stages by default in some tools, which can include a migration stage. Harness's rollback documentation describes that behavior for pipeline rollback, and it is worth checking your tool's semantics before you trust the button: you want rollback to reverse the deploy, not to reverse the migration.

Practically, that means three habits:

• Migrations need a "down" that is honest. For an additive change, down is DROP COLUMN or DROP TABLE, and that is fine because it only applies to a change you could reapply. For a data backfill, down is nothing, and the file should say so rather than pretend.
• Test rollback in staging, not in your head. Roll the previous artifact forward against the new schema in a staging environment and run the smoke suite. If old code plus new schema fails there, it will fail in production.
• Keep the deploy and the migration in separate blast radiuses. If your deployment tool supports stage level failure strategies, set migration failure to halt and alert, and deploy failure to roll back traffic. Do not let one failed operation trigger the other.

One more guard: cap the risk of a bad migration with a pre-flight check that refuses to run against production if the migration touches a table over some row count in a non-batched way. A very simple version lives in the migration file itself as a comment and a hard guard, checked by a lint step in CI:

if grep -E 'ALTER TABLE .* (DROP|RENAME RAW|MODIFY)' "$MIGRATION" \
   && [ "$TABLE_ROWS" -gt 1000000 ]; then
  echo "refusing: destructive migration on $TABLE_ROWS rows"
  exit 1
fi

The exact number is yours to pick; the point is that the pipeline, not a reviewer's attention, enforces it.

None of the above survives contact with a fast-moving team unless the pipeline enforces it. The following are cheap and catch the majority of mistakes.

Keep branches short. A migration that sits in a feature branch for three weeks will conflict with whatever landed on trunk and will be evaluated against a schema nobody has any more. Trunk-based development with same-day integration, described in the trunk based development guide, is not a philosophy requirement here; the practical benefit is that migrations merge in small, reviewable units.

Test against a schema built from scratch and a schema one version behind. The first catches migrations that only work on an empty database. The second simulates the rolling deploy: run the previous release's test suite against the new schema. This is the closest automated equivalent to "two versions live at once," and it is the highest-value test you can add to a CI pipeline.

Review migrations as code, with a second reviewer for destructive ones. The Harness CI/CD checklist includes the general principle that small, frequent, reviewable changes lower risk, and destructive migrations are exactly where a second pair of eyes earns its keep.

Log the applied version next to the deployed artifact version. When an incident lands, the first question is "what schema did this code expect?" If the deploy record and the migration record share a version tag, you answer in seconds.

Run migrations with the same credentials model as the deploy. Not the application's runtime credentials. Migration roles need DDL rights; the application usually should not have them, and separating the two means a compromised app cannot drop a table.

For teams building this out, there is an option beyond hand-rolling each piece. Quanta Intelligence can take a description of the pipeline, the migration flow and the environments, and have a specialist build the project with you, which is often faster than assembling generic templates that assume a different branching model.

If the concern behind all this is that the artifact is wrong for a reason you cannot see, that is a different problem, and an expert on the line of business can review the service and the pipeline together before you plan the next big schema change.