Coordinating database schema changes with application deployments is a genuinely tricky problem — get it wrong and you risk downtime or data issues. This guide covers a practical, safe approach.
Why This Is Trickier Than It Seems
Unlike stateless application code, database migrations are often irreversible (or at least costly to reverse) and affect live data — a naive "run migrations then deploy new code" approach can cause errors if old application code is still running against a changed schema during the transition window.
The Backward-Compatible Migration Pattern
The safest general approach: design migrations to be backward-compatible, so both old and new application code can work with the migrated schema during a rolling deployment — avoids the tight coupling between "exact migration timing" and "exact deployment timing."
Example: Adding a New Column Safely
ALTER TABLE users ADD COLUMN new_field VARCHAR(255) NULL;Adding a nullable column is backward-compatible — old code that doesn't know about the column continues working fine, while new code can start using it once deployed.
Example: Renaming a Column (Requires Multiple Steps)
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Step 2 (separate deployment): Backfill and dual-write
-- Step 3 (later deployment): Remove old columnA rename genuinely isn't backward-compatible if done in a single step — breaking it into multiple deployments (add new, migrate/dual-write, remove old) avoids a window where old or new code breaks.
Integrating Migrations into Your CI/CD Pipeline
stages:
- name: Run Migrations
script: npm run migrate
- name: Deploy Application
script: ./deploy.shRunning migrations as an explicit pipeline stage before application deployment, rather than relying on manual execution, ensures migrations are consistently applied and tracked as part of your deployment history.
Testing Migrations Before Production
See How to Manage Multiple Environments (Dev/Staging/Prod) on a VPS — always test migrations against a staging environment with realistic data volume before applying to production; a migration that runs quickly on a small dev database can behave very differently against a large production table.
Handling Long-Running Migrations
See How to Back Up a Large Database Without Locking It for related locking concerns — some schema changes on large tables can take significant time and potentially lock the table; research your specific database's approach for online/non-blocking schema changes if this is a concern for your table sizes.
Having a Rollback Plan for Migrations
Not every migration can be cleanly reversed — understand which of your migrations are genuinely reversible versus one-way, and factor this into your overall rollback strategy (see How to Roll Back a Bad Deployment Quickly) for deployments including schema changes.
Backing Up Before Migrating in Production
See How to Set Up Automated VPS Backups — always ensure a recent, verified backup exists before running production migrations, regardless of how confident you are in the migration's correctness.
Common Errors
Migration succeeds but application errors occur during rolling deployment — usually indicates the migration wasn't genuinely backward-compatible; review whether old application code instances (still running during a rolling deploy) can actually handle the new schema state.
Continue Reading
- How to Back Up a Large Database Without Locking It
- How to Manage Multiple Environments (Dev/Staging/Prod) on a VPS
- How to Roll Back a Bad Deployment Quickly
Browse more articles in DevOps & CI/CD.
