Database Migration Checklist: Plan, Test, Deploy, and Roll Back Schema Changes Safely
databasesmigrationsbackend-developmentdeploymentdevops

Database Migration Checklist: Plan, Test, Deploy, and Roll Back Schema Changes Safely

CCodeAcademy Editorial Team
2026-11-21
7 min read

A reusable database migration checklist for planning, testing, deploying, verifying, and rolling back schema changes safely.

A database migration can be a small column change or a coordinated rewrite of tables, indexes, and application code. This database migration checklist provides a repeatable way to review schema changes, protect data, test operational impact, deploy in stages, and decide whether a database migration rollback is safe.

Overview

A safe migration is more than a script that runs successfully. It is a controlled change with a known starting state, a tested forward path, observable effects, and a recovery plan. Before you begin, record the database engine and version, migration framework, deployment process, backup method, approximate data volume, and the application versions that read or write the affected tables.

Separate the migration into three questions:

  • Correctness: Will the new schema represent the intended data and preserve existing relationships?
  • Operational safety: Will the change create locks, long-running queries, excess storage use, or unacceptable load?
  • Recovery: If the release fails, can you restore service and preserve writes without making the data state more confusing?

Do not assume that every migration can be reversed with a single down command. A dropped column, destructive data transformation, or partially completed backfill may make a logical rollback impossible. In those cases, recovery may require restoring a backup, replaying captured writes, or deploying a compatibility version that works with the changed schema.

Migration types at a glance

Migration typeTypical riskRollback optionZero-downtime approach
Add a nullable columnLow to mediumDrop only if no code or data depends on itAdd first, deploy compatible code, populate later
Add an indexMediumRemove the index if it is not required by new behaviorUse the database's online or concurrent index method where supported; verify lock behavior
Rename a column or tableHighUsually requires a reverse rename and compatible application codeUse an expand-and-contract sequence with temporary dual reads or writes
Change a column typeHighPossible only when conversion is reversible and fully testedAdd a new column, backfill in batches, switch reads and writes, then remove the old column later
Delete or rewrite dataHighRestore or reconstruct from a verified sourcePrefer a staged copy, validation, and delayed cleanup

Checklist by scenario

Before writing the migration

  • Write the desired end state in plain language, including constraints, indexes, defaults, and data transformations.
  • Search application code, background jobs, reports, dashboards, ETL tasks, and external integrations for dependencies on the affected objects. An API documentation workflow can help keep database-facing contracts aligned: review the OpenAPI documentation workflow.
  • Identify foreign keys, triggers, generated values, views, permissions, replication rules, and scheduled jobs that may be affected.
  • Choose whether the change can run in a transaction. PostgreSQL commonly supports transactional DDL for many operations, while behavior varies by operation and database engine. MySQL behavior can also vary by statement and version. Confirm the exact operation rather than relying on a general rule.
  • Define success criteria: expected row counts, null counts, constraint status, query latency, lock duration, and application health indicators.

Before production deployment

  • Confirm that backups or snapshots exist and that the team knows how to restore them. A backup that has never been restored in a representative environment is not a complete recovery plan.
  • Run the migration against a production-like copy with similar table sizes, indexes, data distribution, and concurrent workload.
  • Measure the migration duration, transaction size, lock waits, temporary storage, replication or read-replica lag, and impact on important queries.
  • Review the generated SQL and the framework's migration ordering. Do not treat an ORM migration file as a substitute for understanding the database statements it creates.
  • Prepare a deployment owner, reviewer, maintenance or rollback decision maker, monitoring dashboard, and communication path.

For an expand-and-contract change

  1. Expand: Add the new table or column without removing the old path.
  2. Deploy compatibility code: Make the application able to read the old and new representations, or write both when required.
  3. Backfill: Copy existing data in bounded batches, using a stable key range and a pause or rate limit when load rises.
  4. Validate: Compare counts, checksums where appropriate, nullability, and representative records.
  5. Switch: Move reads and writes to the new representation behind a controlled release or feature flag.
  6. Contract: Remove the old path only after logs, metrics, jobs, and clients show that it is no longer used.

This staged pattern is often safer for zero-downtime database changes because application and schema compatibility is maintained during the transition. It also makes a database migration rollback less destructive: the team can switch traffic back before deleting the old representation.

Migration-plan template

Change: [short description]
Owner and reviewer: [names or roles]
Affected objects: [tables, columns, indexes, constraints]
Preconditions: [backup, application version, feature flag]
Forward steps: [ordered commands or framework migrations]
Validation: [queries, counts, metrics, application checks]
Abort threshold: [lock time, error rate, lag, or duration]
Recovery: [revert release, switch reads, restore, or other action]
Cleanup: [deferred removal and follow-up date]

What to double-check

Locking and transactions: A migration can be logically correct and still disrupt users. Check whether an ALTER TABLE, index build, constraint validation, or type conversion blocks reads or writes. Avoid holding a large transaction open while unrelated application traffic continues. For PostgreSQL, investigate operation-specific options such as concurrent index creation where appropriate. For MySQL, verify the selected algorithm and lock behavior for the exact table and operation. Test on the same major database version and a similar storage layout as production.

Backfills: A backfill should be restartable and observable. Process predictable batches, commit regularly when safe, record progress, and make the transformation idempotent. Avoid a query that scans and updates the entire table in one unbounded transaction unless its impact has been demonstrated and accepted.

Constraints and existing data: Before adding a non-null constraint, uniqueness rule, or foreign key, find violating rows. Consider validating a constraint separately from enforcing it when the database supports that approach. Confirm that application defaults and database defaults agree; otherwise, different write paths may produce different records.

Queries and indexes: Schema changes can alter query plans even when the application code is unchanged. Check the queries most likely to use the changed table and review execution plans. For broader SQL investigation, use the SQL query debugging guide.

Application behavior: Verify writes, reads, background workers, exports, administrative screens, and API responses. Test both old and new application versions if a rolling deployment is used. API request and response checks can be organized with the REST API testing guide.

Post-deployment verification template

Migration status: [completed, paused, or failed]
Schema checks: [objects, constraints, indexes]
Data checks: [counts, nulls, duplicates, sample records]
Application checks: [read, write, job, API, and admin flows]
Operational checks: [errors, latency, locks, CPU, storage, replica lag]
Decision: [continue, monitor, pause, or recover]
Follow-up: [cleanup migration, ticket, owner, date]

Common mistakes

  • Dropping before replacing: Removing a column or table in the same release that introduces its replacement leaves little room for recovery. Add and migrate first; remove later.
  • Testing only on an empty database: Empty schemas do not reveal lock duration, duplicate data, skewed values, large indexes, or slow backfills.
  • Using an automatic rollback blindly: Reversing DDL does not necessarily restore deleted or transformed data. Classify each step as reversible, conditionally reversible, or recovery-only.
  • Ignoring non-application clients: Reports, scripts, data pipelines, and older services may not be included in the main deployment.
  • Making a migration depend on application code that is not deployed yet: Deploy additive schema changes before code that requires them.
  • Running without a stop condition: Define when to pause for lock waits, error rates, replication lag, storage pressure, or unexpected duration.
  • Leaving compatibility code forever: Record cleanup work as a separate task with an owner and a condition for removal.

Keep migration files in version control, review them through the normal pull-request workflow, and include the generated SQL when it is not obvious. The Git branching and pull request workflow offers a useful review structure. Run automated checks in CI, including migration application on a clean database and, where practical, upgrade testing from a representative prior state. For test organization, see the pytest testing workflow and the GitHub Actions troubleshooting guide.

When to revisit

Use this checklist before every production schema change, not only for high-risk migrations. Revisit the plan when the database engine, framework, hosting configuration, table size, replication design, deployment method, or application traffic pattern changes. It is also worth reviewing after a failed migration, a restore exercise, a major incident, or the introduction of a new reporting or integration workload.

As a practical final pass, ask the release owner to confirm five items:

  1. The forward migration was tested on representative data.
  2. Locks, duration, storage, and query impact have explicit thresholds.
  3. Backups and restoration steps are available and understood.
  4. The application supports the schema during a staged rollout.
  5. Verification queries and the recovery decision are written down.

After deployment, record what happened: actual duration, peak impact, validation results, and any manual intervention. Update the team’s migration template with those lessons before the next release. This turns a one-time database migration checklist into a durable developer utility for safer schema changes.

Related Topics

#databases#migrations#backend-development#deployment#devops
C

CodeAcademy Editorial Team

Developer Tools Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.