Manual migrations break releases: where Flyway setup falls apart
While schema changes are applied by hand, the order and composition of migrations live in the release engineer's head, not in the repository.
On environments, different sets of files are applied: somewhere an index was missed, somewhere someone else's change was applied "for the future"—and the environments quietly diverge.
Schema divergence is discovered in production, when a query from fresh code fails on a missing column.
Worse is when a migration is applied partially. A script with several changes without a transactional wrapper fails midway: half the changes are already in the database, half are not, and the service table knows nothing about such a state.
Rolling back the code does not revert the schema, a repeated run hits "object already exists," and then everything is restored manually from logs and a backup.
Then comes the deployment block: the release is ready, but waits for the on-duty administrator to apply the changes, the deployment window stretches, and compatibility between the application version and schema version rests on agreements.
The cost of such a scheme is not the speed of one release, but the loss of controllability: you can neither roll back nor reproduce on a clean environment.
We break down how this is closed in Flyway configuration: versioned files from the repository, checksums of applied changes, a separate account with limited rights, and running migrations in the pipeline before the new application version starts.
What managed schema migration order gives
Managed schema migration order is needed not for tidiness in the repository, but so that schema state does not depend on manual edits on environments.
When changes live as separate numbered files, the database is assembled reproducibly, and deployment stops being an event of unknown duration. The effects are visible in measurable values.
- Unified environment. Test, pre-production, and production databases go through the same sequence of migrations—divergence surfaces during build, not at release time.
- Schema versioning. A migration has a number and a checksum; the database has a journal of what was applied. A tool like Flyway will not apply a file changed after it was applied: an after-the-fact edit does not go unnoticed.
- Predictable deployment. An incremental change applies in seconds and does not depend on the volume of accumulated data—the maintenance window is planned in advance.
- Rollback without restoring from a copy. Paired migrations and the expand-contract schema keep the previous application release working: returning is an operation, not emergency recovery.
- Downtime due to migrations goes away. Blocking rebuilds are moved to a separate step and run without stopping writes—no unplanned pauses remain.
Result: the same set of objects on all environments, deployment time within a known interval, and removal of a class of failures that manual schema edits used to cause.
Flyway configuration options: comparison of operating modes
The same Flyway migration description can be applied in different ways: the schema can be changed automatically at application startup, as a separate pipeline step before deployment, or manually by engineer command.
The mode determines not the migration syntax, but the moment of application, database permissions, and how quickly the team notices schema-code desynchronization.
| Mode | When appropriate | Limitations |
|---|---|---|
| Automatic migrations at startup | Local and test environments, single instance, frequent iterations | With multiple replicas, migration starts in parallel—a lock is needed, otherwise race; moment of application is not controlled |
| Separate pipeline step before deployment | Production environments, multiple replicas, predictable order required | Separate database access and time budget for long schema changes are needed |
| On-demand migrations | Regulated environments, change audit, critical tables | Human factor: schema lags behind deployment, an error causes downtime |
How to choose a mode for an environment
Migration description stays the same for all environments; only environment configuration differs: auto-apply on test environments, and in production a separate step under an account with rights only to modify the schema.
Schema version compliance check is moved to application startup: if migrations are not applied, the service does not come up.
This removes a class of incidents when new code works with an old schema, and destructive operations like schema cleanup in production are disabled at the settings level.
Implementation stages: from schema audit to handover of regulations
We build the order of work so that each next step relies on the previous one being verified. A versioning error should surface on a copy, not on a working environment—then it is an hour of rollback instead of a night of restoring from backup.
- Schema audit. We collect the structure of all environments, compare them with each other and with the change history in version control. We find divergences, manually applied edits, and objects that are not in any script.
- Versioning scheme. We agree on migration numbering, naming rules, and separation of one-time and repeatable changes. We fix who and how adds the next migration.
- History transfer. We bring the current database to a baseline state: mark the current structure as the starting point so past changes are not applied again.
- Check on a copy. We run the entire set on a full copy of the working environment—first from scratch, then on top of the current state. We measure time and catch conflicts.
- Control points. Migrations run as a separate step before application startup, with deployment blocked on error. Matching the structure with the expected one is a condition for proceeding further.
- Deployment regulations. Separately describe changes requiring service downtime: preparation, window, rollback procedure, responsible persons.
- Handover. We hand over configurations, schemas, instructions, and analysis of typical failures—the team runs migrations further on its own.
What remains in the repository and with the team after implementation
Implementation is considered complete not when a migration has passed on a test environment, but when the team has a set of artifacts by which any engineer can release the next release, not just the author of the changes.
Below is the minimum set that we hand over together with the working pipeline; we check each item at acceptance.
- Migration repository — flat versioned files with forward and reverse scenarios, one logical change per file. Already applied files are not edited: any schema change is a new file, otherwise checksums will diverge between environments.
- Review policy — mandatory review of the script by a second engineer, naming rules, and deployment order. We separately fix the ban on manual schema edits: all changes go only through the repository.
- Environment settings — connection profiles for development, test, and production environments, separate accounts with the minimum required rights. Locks and timeouts are set explicitly so a long migration does not bring down the service.
- Rollback regulations — what we do on a failed migration: recovery point, order of compensating scripts, who makes the decision to stop. We fix the target time to return to a working version and test it in drills.
- Schema documentation — an up-to-date data model collected from the storage itself after each deployment, with descriptions of key entities and relationships. A living schema instead of an outdated presentation from six months ago.
- Instructions for the on-duty shift — where to look at the migration journal, which errors mean stopping the pipeline, how to switch deployment to manual mode. Plus a 5–7 point release acceptance checklist.
The set remains in your repository and your task management system—it can be used without us.
Case: how the schema on several environments stopped diverging
The project is a service with three environments: development, acceptance, and production. Migrations there were applied by hand: scripts were in the repository, an engineer ran them one by one and monitored the output.
By the time of the schema review, the schemas had diverged by more than 40 objects—indexes, constraints, default values, and types of individual fields.
The cost of divergence manifested at acceptance: checks ran against a structure that did not exist in production, and some defects reached users.
The deployment itself took 40–60 minutes of manual work and required a window, while rollback turned into parsing logs and making edits in place.
The schema was moved to Flyway: each script received a version number and checksum, and state is recorded in the service table.
Migrations were built into the build pipeline—they are applied automatically on each environment, and an attempt to apply an already applied or modified script stops the build until review.
What changed after implementation
Schema deployment was reduced to 4–6 minutes and stopped requiring a separate window. Divergences between environments—zero: the structure at acceptance and in production is assembled from a single chain of versions.
Recovery time after a failed migration is about 10 minutes: reverse scenario plus return of the previous artifact. An entire class of failures disappeared, when the wrong script reached an environment or the same one was applied twice.
What to do if some migrations have already been applied manually
The schema developed through direct edits: a column was added via console, an index was built manually, some objects have long been in production.
There is no table of applied migration history, and the first Flyway run sees an empty journal—it tries to apply everything from the beginning and fails on already existing tables. The order is restored through a baseline, not through manual reassembly of the journal.
A baseline is a fixed current schema state from which managed history begins. Set baselineVersion to the version from which migrations will continue, and enable baselineOnMigrate: on first run, one baseline record is created, and existing objects remain untouched. Everything done by hand before it is considered part of the starting point.
One baseline is not enough. Manual edits were applied without control, so objects may diverge from what is described in migrations—up to column types and constraints. Run validate and look at divergences, then repair rebuilds checksums and removes records of failed attempts.
Then order is restored: changes go only through migration files, and direct production edits are prohibited or rolled back by a separate migration.
It is useful to compare the schema of test and working environments—so the baseline does not lock in an unnoticed divergence that will surface several releases later.
If the schema has diverged too much, it is more honest to set the baseline to a version below the current one and describe the remainder with a separate reconciliation script.
The decision is made by the volume of divergences, not by habit: sometimes it is cheaper to align the schema once than to drag a journal that never matched.
Common technical doubts about database migrations
Engineers' doubts around migrations boil down to four scenarios: simultaneous startup of several service instances, a change failing midway, dropping a column that has become unnecessary, and the approval order for schema edits. Below is how each is closed in Flyway configuration.
The service starts in several instances—will the migration be applied twice?
No: the tool takes a lock on the version history service table, so only the first node applies the change; the others wait for release and see that the version is already recorded.
The difficulty is elsewhere—when instances are updated one by one, the old code works with the new schema for some time, so we make changes backward compatible: first added, switched reads and writes, and only then removed the unnecessary.
What remains in the database if a migration fails midway?
It depends on whether the DBMS supports transactional schema changes. Where it does, the migration rolls back entirely, and a repeated run starts from a clean state.
Where it does not, some objects are already created, and the setup includes handling such a state: we compare the actual schema, mark the failed version, and repeat from the correct point. That is why large transformations are split into small steps.
How are columns dropped and dangerous changes approved?
Dropping a column or table is a two-phase operation: first we remove references in code and deploy the release, then with the next change we drop the object. That eliminates the risk of a running instance accessing a missing field.
Long operations that hold a table lock are moved to a separate window and we limit lock wait time so as not to build a queue of queries. Schema edits themselves go through general approval in the same order as code.
Let's discuss the task and come back with an estimate and plan
To come back with an estimate and work plan, rather than general wording, a short snapshot of your migration environment is enough. Below are the inputs by which we will see the scope and risks without entering your environments.
- DBMS and its version in production and on test environments, plus a schema structure dump without the data itself.
- Current change scripts: how they are named, where they are stored, whether any were already applied manually and not marked in the Flyway service table.
- How changes reach production now: who applies them and with what command, in what window, what they do on an error midway.
- Repository and build: where migrations are stored, what builds the application, whether there is a separate deployment pipeline.
- Environments and access: how many environments, how data sets differ, which accounts can be given to automation.
- Rollback and downtime requirements: acceptable downtime and what is considered a correct rollback—reverse script or restore from a copy.
Then we analyze what was provided, check the application order and repeatability of scripts, note steps that will have to remain manual, and compare the schema description with what is already deployed.
As a result, you get a stage plan, the composition of automatic checks before deployment, and the rollback procedure—with an estimate by timeline and scope of work.
The next step is a short business conversation. Describe the task: a couple of paragraphs about the current state and what is getting in the way are enough—we will come back with an estimate and plan.