Why this matters
How to run database schema changes safely in CI comes down to one rule: treat the schema as code and test it like code. Application code rolls back cleanly. Schema changes usually do not. If a release adds a column and you roll the app back, old code has to keep working against the new shape. If a release drops a column, the rollback has nowhere to get the data back.
The other problem is time. A migration that runs in two seconds on a laptop with ten thousand rows can take twenty minutes on a table with fifty million, and hold a lock the whole time. CI is the only place you find that out without hurting anyone.
So the goal is not clever migrations. It is boring ones: small, ordered, tested against a database shaped like production, and checked before merge. Everything below makes that check routine.
Before you start
Pick one migration tool and keep it. Flyway, Liquibase, Alembic, Prisma Migrate, golang-migrate, Atlas and Rails migrations all do versioned, ordered files. What matters is that migrations live in the same repository as the code that depends on them, and that nobody applies schema changes by hand in a console.
Next, get a production-shaped schema into CI. A nightly schema-only dump, from pg_dump or mysqldump with the schema-only option, stored as a build artifact is enough for correctness checks. For timing you also want rows at realistic scale: a masked subset, or generated rows that match the size and shape of the biggest tables.
Write the policy before the first pull request. Additive changes such as new nullable columns, new tables and new indexes apply automatically. Drops, renames and type changes need a second reviewer and their own release. Put that in a short note in the repo so a review comment is not the first time anyone hears it.
How to do it
Every run does three passes. Create an empty database and apply every migration in order, oldest file first. That catches broken chains, duplicate versions, and files that assume a table someone created by hand. Then restore the production schema dump and apply only the branch's new migrations on top, which catches changes that only work on an empty database. Finally, run the integration tests against the migrated schema.
Add a linter between those passes. Squawk reads Postgres migrations and flags volatile defaults, index creation without CONCURRENTLY, column type changes, and NOT NULL without a validated check constraint. Atlas lints migrations for several engines with similar rules. Linters are cheap and they catch the expensive mistakes before a human reads the diff.
Make the database fail fast when it is stuck. Set lock_timeout to a few seconds and statement_timeout to something sensible at the top of any migration that touches a large table. Without lock_timeout, a migration waits behind one long query while every query behind it waits too.
- Version every schema change as a file. Create one migration file per change with a version prefix and a short description, and commit it with the code that needs it. Never edit a file that has already run in a shared environment.
- Build the database from zero on every run. Point the pipeline at an empty service container and apply all migrations in order. A failure here means new environments cannot be built from the repository.
- Replay migrations on a production schema dump. Restore the latest schema-only dump, then apply the branch migrations on top. This is where changes that assume an empty table show up.
- Add a migration linter and fail the build. Run squawk, Atlas lint or your tool's equivalent as its own step. Treat new warnings as blocking and clean up the old ones over time.
- Set timeouts inside the migration. Add a lock_timeout of a few seconds and a statement_timeout that matches your workload at the top of migrations that touch big tables. Never leave them unset.
- Time it against realistic row counts. Seed the largest affected table with close to production size, run the migration, and log the duration. Fail the job when it crosses a threshold you choose.
- Test the revert path. Apply the migration, then apply its down script or the documented manual reversal, and confirm the schema returns to the previous shape. If the tool has no down migration, rehearse the reversal in staging once.
- Gate destructive changes. Put drops, renames and type changes behind a protected environment or a required reviewer. Everything else runs on green.
How to check it worked
Open the migration history table after a run. flyway_schema_history or schema_migrations should list exactly the versions you expect, with no gaps and no checksum mismatches. A mismatch usually means someone edited a file that had already been applied.
Diff the resulting schema against your target. migra, atlas schema diff and liquibase diff all print added, removed and changed objects. The diff should contain only what the pull request intended. Anything else is drift that accumulated outside the migration chain.
Check the negative case. Run the branch tests without the migration applied and confirm they fail for the right reason. If they pass anyway, nothing is exercising the new column or table.
Before merge, run the migration against a staging copy at production size and watch pg_stat_activity or SHOW PROCESSLIST during the window. Confirm there are no long lock waits and that the app keeps serving traffic. Write the duration down so the next person has a baseline.
Mistakes to avoid
Running migrations from application startup. With three replicas, three processes try to migrate at once, and the tool's own lock may or may not save you. Keep migrations in a separate job that runs once, before the deploy.
Editing a migration that already ran. Flyway checksums fail the next run, and if yours do not, environments quietly diverge. Fix forward with a new file.
Dropping or renaming a column in the same release as the code change. Pods still running during a rolling deploy will hit a column that no longer exists. Add the new column, move reads and writes over, then drop in a later release.
Testing only on an empty database, wrapping a long backfill in the same transaction as the DDL, and leaving lock_timeout unset are the other repeat offenders. Each one turns a small migration into an outage. If building this yourself is not the best use of your team's time, Cloudverg's Delivery platform setup package covers pipelines, environments and a tested rollback process at a fixed price of $14,500 over four weeks, and it is one option among several worth comparing.
Frequently asked questions
Should database migrations run automatically on deploy?
Run them as a separate pipeline step, before the new application version starts, and run them once. Migrations fired from app startup collide across replicas and make the deploy depend on which process wins. Separating the steps also gives you a clean point to stop if the migration fails.
How do I test a migration without production data?
Use a schema-only dump for correctness and a masked or generated data set for timing. Row counts and column widths matter more than exact values for most DDL. Keep a small set for every pull request and a larger one for nightly builds.
What is the expand and contract pattern for schema changes?
You add the new structure first, keep old and new working side by side, move reads and writes across, then remove the old structure in a later release. Every intermediate state stays compatible with the code running at that moment. It is the pattern that makes rolling deploys and app rollbacks safe when the schema moves.