Turning Database Schema Changes into Release Artefacts with Flyway

Turning Database Schema Changes into Release Artefacts with Flyway

The setup

A regulated financial services platform was running a Java and Spring Boot microservice estate on Kubernetes, backed by PostgreSQL. The application layer was already managed through a controlled release process, with container images built, scanned, promoted, and deployed through automated pipelines.

The database layer had not caught up.

The platform used several logical PostgreSQL schemas. Each schema owned a different part of the application estate: operational data, audit data, workflow state, reference data, and reporting-style access patterns. Over time, each schema had accumulated tables, indexes, views, functions, and stored procedures. The changes were sensible in isolation, but the way they were applied was not consistent enough for a growing release cycle.

In lower environments, engineers could usually apply scripts themselves. In more controlled route-to-live environments, database changes often required handover, manual execution, or coordination with a database administrator. That worked while the volume of change was small. It became risky once multiple services and schemas were moving together.

The goal was simple: database changes should behave like application changes. They should be versioned, reviewed, repeatable, visible in deployment logs, and applied before the application version that depends on them is rolled forward.

Flyway gave us a way to do that without introducing a heavy platform around database change management.

The problem

The main issue was not that database changes were failing every release. The issue was that the process had too many places for small differences to appear.

A table could be created in one environment but missed in another. An index could be added manually during a performance investigation and never make it back into source control. A stored procedure could be patched in a test environment, then overwritten later by a release script. None of these are dramatic failures. They are the kind of quiet drift that makes incidents harder to diagnose.

There was also a sequencing problem. Database changes are often coupled to application changes, but that coupling is about timing. A column must exist before a new service version starts writing to it. A table must be created before a batch job inserts into it. A constraint may need to wait until historical data has been cleaned.

Manual processes make sequencing harder because the release plan lives partly in people's heads, partly in documents, and partly in the pipeline.

We wanted the database to tell us what had already run, what version it was at, and what needed to happen next.

The approach

We introduced a small database management service built with Spring Boot and Flyway. The service did not expose business APIs. Its job was to package the SQL migrations, connect to the relevant PostgreSQL schemas, and execute them as a release-time Kubernetes Job.

That choice gave us a few useful properties.

First, the migration scripts lived in the same engineering workflow as the service code. A schema change was raised as a pull request, reviewed by engineers, and merged only after the usual checks had passed.

Second, Flyway handled ordering and idempotency. Each schema had a flyway_schema_history table that recorded which migrations had run, whether they succeeded, how long they took, and which script was applied.

Third, the database management service ran before the application deployment. If the migrations failed, the release stopped before the dependent application workloads were rolled forward.

The rejected alternative was to keep database changes as a separate operational step. That would have avoided adding another service and Kubernetes Job, but it would have preserved the problem we were trying to remove: database change as a handover rather than a release artefact.

The other option was Liquibase. It has strong cross-database support and a richer change-set model, so it is worth considering when a platform spans several database engines. For this case, the estate was mainly PostgreSQL, the team was comfortable with SQL scripts, and Flyway's simplicity was a better fit.

The implementation

At a high level, the release flow looked like this:

%% title: Database migration release gate
%% description: Shows reviewed migration scripts becoming a Kubernetes Job that gates application deployment.
%% kind: release-gate
flowchart LR
    A[Engineer raises PR] --> B[Migration scripts reviewed]
    B --> C[Build database management image]
    C --> D[Release gate: Kubernetes Job]
    D --> E[Load schema config]
    E --> F[Run Flyway per schema]
    F --> G[Application deployment continues]

The service kept migration scripts in a predictable structure under src/main/resources. Each schema had its own folder, which made ownership and review easier than placing every SQL file into one shared directory.

src/main/resources
├── db
│   └── migration
│       └── tenant-a
│           ├── reference-data-db
│           ├── event-log-db
│           ├── audit-event-log-db
│           ├── workflow-db
│           └── recovery-db
├── db-scripts
│   └── db-permission-scripts
│       └── tenant-a
│           ├── dev
│           ├── test
│           ├── preprod
│           └── prod
└── application.yml

Each migration file followed Flyway's naming convention:

V1__create_case_tables.sql
V1_1__add_status_index.sql
V1_2__create_audit_log_table.sql
V2__add_retention_procedure.sql

The prefix tells Flyway the type of migration. Most scripts were versioned migrations using V. The version controls ordering, the double underscore separates the version from the description, and the description gives engineers a readable explanation of the change.

A simplified migration looked like this:

-- V1__create_case_tables.sql

CREATE TABLE IF NOT EXISTS case_record (
    case_id UUID PRIMARY KEY,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_case_record_status
    ON case_record(status);

One useful design choice was disabling Spring Boot's default Flyway auto-execution and controlling migration explicitly from the service. That mattered because the service needed to loop across multiple configured schemas rather than run a single default migration path.

spring:
  flyway:
    enabled: false
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    open-in-view: false
  datasource:
    url: ${SPRING_DATABASE_URL}
    username: ${DB_READ_WRITE_USERNAME}
    password: ${DB_READ_WRITE_PASSWORD}

database:
  tenant-id: ${DATABASE_TENANT_ID}

db-migration-config:
  createSchemaFlag: ${FLYWAY_CREATE_SCHEMA_FLAG:false}
  baselineOnMigrateFlag: ${FLYWAY_BASELINE_ON_MIGRATE_FLAG:false}
  repairMigrationFlag: ${FLYWAY_REPAIR_MIGRATION_FLAG:false}
  outOfOrderFlag: ${FLYWAY_OUT_OF_ORDER_FLAG:false}
  tenantIdSchemaInfoMap:
    tenant-a:
      tenantName: TENANT_A
      schemaInfoMap:
        reference-data-db:
          schemaName: reference_data_db
          location: db/migration/tenant-a/reference-data-db
        event-log-db:
          schemaName: event_log_db
          location: db/migration/tenant-a/event-log-db
        audit-event-log-db:
          schemaName: audit_event_log_db
          location: db/migration/tenant-a/audit-event-log-db

The migration service then loaded the schema configuration for the active tenant and executed Flyway once per schema. This is close to the shape of the production code, with names anonymised:

@Value("${database.tenant-id}")
private String databaseTenantId;

public void migrate() {
    Collection<DatabaseSchemaInfo> schemaInfoCollection =
        flywayConfigProperties.getAllDatabaseSchemaInfo(databaseTenantId);

    schemaInfoCollection.forEach(databaseSchemaInfo -> {
        String schemaLocation = "classpath:" + databaseSchemaInfo.getLocation();

        log.info(
            "Starting Flyway migration of {} schema from location {}",
            databaseSchemaInfo.getSchemaName(),
            schemaLocation
        );

        Flyway flyway = Flyway.configure()
            .dataSource(postgreSqlDataSource)
            .defaultSchema(databaseSchemaInfo.getSchemaName())
            .createSchemas(flywayConfigProperties.getCreateSchemaFlag())
            .baselineOnMigrate(flywayConfigProperties.getBaselineOnMigrateFlag())
            .locations(schemaLocation)
            .outOfOrder(flywayConfigProperties.getOutOfOrderFlag())
            .load();

        if (flywayConfigProperties.getRepairMigrationFlag()) {
            log.info(
                "Starting Flyway repair of {} schema from location {}",
                databaseSchemaInfo.getSchemaName(),
                schemaLocation
            );
            flyway.repair();
        }

        flyway.migrate();
    });
}

This gave each schema its own migration folder and its own flyway_schema_history table. It also made the deployment logs easier to read because each schema reported its own migration path and version.

The Kubernetes Job became the release gate:

apiVersion: batch/v1
kind: Job
metadata:
  name: database-management
spec:
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: database-management
          image: registry.example.com/database-management:${VERSION}
          envFrom:
            - secretRef:
                name: database-credentials

The important detail is not the YAML itself. The important detail is that database change became part of the same deployment system as everything else. It had logs, status, retries, versioned artefacts, and a clear failure point.

What happened in production

The biggest improvement was release confidence.

A representative release previously needed manual coordination for database objects across several schemas. After introducing the Flyway job, the same style of release could apply schema changes in a few minutes before application deployment started. The release team no longer needed a separate checklist for which SQL files had been run in which environment.

Environment rebuilds also became easier. A clean database could be brought up to the expected version by running the database management job, instead of replaying a manually curated set of scripts. That made lower-environment refreshes less fragile and reduced the number of "works in test but not in staging" conversations.

The flyway_schema_history table became useful during support. When a release issue appeared, engineers could check exactly which migrations had run and when. That did not solve every database problem, but it removed one common uncertainty: whether the target environment was actually at the version the application expected.

The indicative impact was:

• Database deployment steps moved from manual coordination to an automated release job.
• Schema drift between lower and higher environments reduced noticeably.
• Failed migrations stopped the release earlier, before dependent services were deployed.
• Engineers could review database changes in pull requests instead of treating them as late-stage operational scripts.

What surprised us

Rollback was the awkward part.

We were using Flyway Community Edition, which is very good at applying forward migrations, but does not provide the same automatic undo support available in Flyway's commercial editions. Because we did not have access to the paid licence, rollback could not simply be treated as a built-in Flyway operation. We had to treat it as a manual engineering activity: prepare the undo logic, review it carefully, and run it back through the migration process as a deliberate versioned change.

The workaround was manual. We created undo scripts using Flyway's U prefix, then converted the required undo script into a new versioned migration when we needed to roll a database change back.

For example:

U2__remove_retention_procedure.sql

could become:

V2_1__remove_retention_procedure.sql

That is not elegant, but it has one important advantage: the rollback is still forward-only from the database's point of view. It is recorded in schema history, reviewed like any other change, and applied consistently across environments.

The lesson was that rollback should be designed at the start, not discovered halfway through adoption. Some migrations are reversible. Some are not. Dropping a column, changing data types, and deleting historical data all need a different level of care from creating a new table.

What we'd do differently

The first change would be to define a migration policy before the first script is merged. That policy should say which changes are considered safe, which require a backout script, and which need a multi-release expand-and-contract pattern.

The second change would be to test migrations against realistic data volumes earlier. A script that works instantly on an empty local schema can behave very differently on a large table with live-style indexes and constraints.

The third change would be to make ownership clearer per schema. A shared database management service is useful, but it can become a dumping ground if teams do not know who owns which migration folders, naming conventions, and review standards.

The pattern, generalised

The useful pattern here is not "use Flyway". The useful pattern is to treat database change as a release artefact.

Once database migrations are versioned, reviewed, packaged, and deployed through the same route as application code, the operating model changes. Engineers stop asking whether someone has run the script. The release process has a single place to show what happened. Environments become easier to compare because the database records its own migration state.

Flyway worked well here because it stayed close to SQL and did not force the team into a new abstraction. For a mostly PostgreSQL estate with Java engineers and Spring Boot services, that simplicity was a strength.

Liquibase would be worth a closer look where database portability, richer change metadata, or multi-engine support is more important than keeping the model simple. But whichever tool is chosen, the discipline matters more than the product: put database change in source control, run it through the pipeline, and make the database version visible.

That is what turns schema management from a release risk into normal engineering work.