Database Schema Migrations in CI/CD: A Zero-Downtime Guide

Most teams automate application deployments long before they automate database changes. Code goes out through a polished pipeline, while schema changes are run by hand, late at night, by whoever has production access. It works until it doesn't: a missed script, a locked table, or an app version that no longer matches the database.

Treating database schema migrations as part of your CI/CD pipeline removes that risk. This guide explains how migrations work, how to make them safe for zero-downtime deployments, which tools to use, and how to handle rollbacks.

What Is a Database Schema Migration?

A schema migration is a versioned, repeatable change to the structure of a database, such as creating a table, adding a column, adding an index or changing a constraint. Each migration is stored as a file in version control alongside the application code and given a version number or timestamp.

A migration tool records which migrations have already been applied, usually in a special history table inside the database. When the pipeline runs, the tool applies only the pending migrations, in order. This gives every environment, from a developer's laptop to production, the same schema history.

Why Automate Migrations in CI/CD?

• Consistency: every environment gets exactly the same changes in the same order.
• Traceability: migrations are reviewed in pull requests and tied to the code changes that need them.
• Fewer human errors: nobody has to remember to run a script by hand.
• Faster delivery: database changes stop being a bottleneck that blocks frequent releases.
• Better testing: migrations run in test environments long before they reach production.

If you are still building your pipeline, start with how to build a CI/CD pipeline from scratch.

Two Approaches: Versioned vs Declarative

Versioned (migration-based)

You write each change as an ordered migration script. The tool applies scripts in sequence. This gives precise control over how changes happen, which matters for large tables and data transformations. Flyway, Liquibase, Alembic, Django migrations, Rails Active Record migrations and golang-migrate follow this model.

Declarative (state-based)

You describe what the final schema should look like, and the tool works out the changes needed to get there. Atlas, SQL Server Database Projects and similar tools use this approach. It is convenient, but you must review generated plans carefully, because the tool may choose an operation, such as dropping and recreating a column, that loses data or locks a table.

Many teams combine both: declarative tooling to generate a migration, which is then reviewed and committed as a versioned file.

The Core Challenge: Code and Schema Change at Different Times

In a zero-downtime deployment, old and new versions of your application run side by side for a period, whether through a rolling update, blue-green deployment or a canary. Both versions talk to the same database. That means every schema change must work with both the old code and the new code at the same time.

Renaming a column in one step breaks this immediately: the new code expects the new name while the old code still uses the old one. The solution is the expand and contract pattern.

The Expand and Contract Pattern

Also known as parallel change, this pattern splits a breaking change into several backwards-compatible steps. Here is how you would rename a column called "fullname" to "display_name":

1. Expand: add the new "display_name" column. Old code ignores it, so nothing breaks.
2. Dual write: deploy code that writes to both columns but still reads from the old one.
3. Backfill: copy existing data from the old column into the new one, in small batches to avoid long locks.
4. Switch reads: deploy code that reads from the new column. Keep writing to both for safety.
5. Stop old writes: deploy code that only uses the new column.
6. Contract: once you are confident, drop the old column in a later release.

It takes more releases than a single change, but every step is safe to deploy and safe to roll back. Feature flags make the switching steps even easier to control.

Rules for Safe Migrations

• Make additive changes first. Adding tables, nullable columns and new indexes is generally safe. Removing or renaming should come last.
• Avoid long locks. On large tables, some operations rewrite the whole table or block writes. Use your database's online options, such as creating indexes concurrently in PostgreSQL, or online schema change tools like gh-ost or pt-online-schema-change for MySQL.
• Set lock and statement timeouts so a migration fails fast instead of queuing behind a long transaction and blocking production traffic.
• Separate schema changes from large data changes. Run big backfills as background jobs in batches, not inside the deployment.
• Never edit a migration that has already run in a shared environment. Write a new migration instead.
• Keep migrations small and focused, so problems are easy to identify.

Where Migrations Fit in the Pipeline

1. Pull request: the migration file is reviewed alongside the code. Linters such as Squawk (for PostgreSQL) or the checks in Atlas can flag dangerous operations automatically.
2. CI: the pipeline spins up a temporary database, applies all migrations from scratch, and runs the test suite against it. This catches broken or out-of-order migrations early. See what is database testing for more test types.
3. Staging: migrations run against a staging database with realistic data volume to reveal slow or locking operations.
4. Production: migrations run as a dedicated pipeline step, or as a Kubernetes Job or init step, before the new application version receives traffic.
5. Verification: the pipeline checks the migration history table and application health before marking the deployment successful.

Make sure only one process runs migrations at a time. Most tools use a database lock for this, but avoid running migrations from every application replica at startup in large deployments.

Database credentials used by the pipeline should never be hard-coded. Follow how to secure secrets in your CI/CD pipeline.

Handling Rollbacks

Rolling back application code is easy. Rolling back a database is not, because data written after the migration may be lost. That is why the most reliable strategy is to roll forward:

• Design every migration to be backwards-compatible so you can roll back application code without touching the schema.
• If a migration itself is wrong, fix it with a new migration rather than reversing history.
• Write "down" migrations for development convenience, but test them before relying on them in production.
• Take a backup or snapshot before high-risk migrations, and make sure you have practised restoring it.

Read more about safe recovery in how to roll back a deployment safely in CI/CD.

Popular Migration Tools

• Flyway: simple, SQL-first versioned migrations; widely used with Java but works with any stack.
• Liquibase: supports SQL, XML, YAML and JSON changelogs, with rollback definitions and preconditions.
• Atlas: modern schema-as-code tool supporting both declarative and versioned workflows, plus migration linting.
• Alembic: the standard choice for Python applications using SQLAlchemy.
• Framework tools: Django, Rails, Laravel, Prisma and Entity Framework all include migration systems.
• golang-migrate: lightweight CLI and library popular in Go projects.

Common Mistakes

• Combining a breaking schema change and the code that depends on it in one release.
• Running a large backfill inside a single transaction.
• Testing migrations only on an empty database.
• Letting developers apply ad-hoc changes directly in production, causing schema drift.
• Not monitoring database performance during and after a migration.

For more pipeline pitfalls, see CI/CD pipeline best practices and common mistakes.

Conclusion

Database migrations deserve the same automation, review and testing as application code. Version every change, run migrations through your pipeline, use the expand and contract pattern to keep old and new code compatible, and prefer rolling forward over risky reversals. Once the process is in place, database changes stop being the scariest part of a release and become just another routine step.