Automating Database Migrations with Flyway and Spring Boot in a CI/CD Pipeline

Introduction

Production outages caused by *database* changes outnumber outages caused by *code* changes in most teams I've worked with. Flyway brings the same discipline to schema changes that Git brings to source: versioned, repeatable, auditable, and automated through CI/CD.

Why database migrations matter

• Reproducibility — every environment is built from the same ordered scripts.
• Auditability — flyway_schema_history is a permanent record.
• Safety — every script runs exactly once, in order, in a transaction.
• Automation — schema changes ship with the code that needs them.

Step 1 — Add Flyway to Spring Boot

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-database-postgresql</artifactId>
</dependency>

spring:
  datasource:
    url: jdbc:postgresql://db:5432/orders
    username: app
    password: ${DB_PASSWORD}
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    validate-on-migrate: true

Flyway runs automatically on application startup.

Step 2 — Folder structure

src/main/resources/db/migration/
  V1__init_schema.sql
  V2__add_orders_status_index.sql
  V3__add_customers_email_unique.sql
  R__refresh_reporting_views.sql

• V<version>__description.sql — versioned, applied once, in order.
• R__<name>.sql — repeatable, re-applied whenever the file's checksum changes (good for views).
• U<version>__ — undo migrations (paid tier only — don't rely on them; design forward).

Step 3 — A migration

```sql
-- V1__init_schema.sql
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id), total_cents BIGINT NOT NULL, status TEXT NOT NULL DEFAULT 'PENDING', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ```

-- V2__add_orders_status_index.sql
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

CONCURRENTLY avoids locking the table — essential for production. It also means the statement can't run in a transaction; configure that script with -- flyway:executeInTransaction=false.

Step 4 — Zero-downtime migration pattern

Never combine schema + code change in one deploy. Use expand / migrate / contract:

1. Expand — add the new column/table. Deploy. Code still uses the old shape. 2. Migrate — backfill data. Deploy code that reads/writes *both* shapes. 3. Contract — drop the old column. Deploy.

Example: renaming orders.total_cents to orders.amount_cents:

-- V10__add_amount_cents.sql
ALTER TABLE orders ADD COLUMN amount_cents BIGINT;
UPDATE orders SET amount_cents = total_cents WHERE amount_cents IS NULL;

-- V12__drop_total_cents.sql  (deployed AFTER all instances read amount_cents)
ALTER TABLE orders DROP COLUMN total_cents;

Step 5 — Multi-environment handling

```yaml
# application-dev.yml
spring:
  flyway:
    locations: classpath:db/migration,classpath:db/dev-seed

# application-prod.yml spring: flyway: locations: classpath:db/migration clean-disabled: true # CRITICAL — prevents flyway:clean wiping prod ```

Always set clean-disabled: true for non-dev environments.

Step 6 — Dockerized local Postgres

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: orders
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
    ports: ["5432:5432"]

docker compose up then ./mvnw spring-boot:run — Flyway migrates the schema on boot.

Step 7 — GitHub Actions pipeline

```yaml
name: ci
on: [push]
jobs:
  build:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_DB: orders, POSTGRES_USER: app, POSTGRES_PASSWORD: app }
        ports: ["5432:5432"]
        options: >-
          --health-cmd pg_isready --health-interval 5s --health-retries 10
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-java@v4
        with: { distribution: temurin, java-version: 21, cache: maven }
      - name: Validate migrations
        run: ./mvnw -B flyway:validate
      - name: Apply migrations (CI db)
        run: ./mvnw -B flyway:migrate
      - name: Tests
        run: ./mvnw -B test

deploy-prod: needs: build if: github.ref == 'refs/heads/main' runs-on: ubuntu-latest environment: production steps: - uses: actions/checkout@v4 - uses: actions/setup-java@v4 with: { distribution: temurin, java-version: 21 } - name: Migrate production DB env: FLYWAY_URL: ${{ secrets.PROD_DB_URL }} FLYWAY_USER: ${{ secrets.PROD_DB_USER }} FLYWAY_PASSWORD: ${{ secrets.PROD_DB_PASSWORD }} run: ./mvnw -B flyway:migrate - name: Deploy application run: ./deploy.sh ```

Note: migrations run *before* the new application version is deployed, so the schema is always at least as new as the running code.

Common migration mistakes

• Editing an applied V-script — Flyway detects the checksum mismatch and refuses to start. Never edit history; add a new script.
• Long-running ALTER TABLE in business hours — locks the table. Use CONCURRENTLY, batched updates, or run during a maintenance window.
• Mixing DDL and DML in one script on MySQL — DDL auto-commits, so a failure halfway leaves you in a half-applied state.
• Forgetting clean-disabled — one CLI mistake wipes prod.

Production best practices

• Use a dedicated migration user with only CREATE/ALTER rights — not the app user.
• Run flyway:migrate from CI/CD, not from the application on startup, for high-replica deployments.
• Keep migrations small — one logical change per V-script.
• Add a -- @author / @ticket header so the audit trail is meaningful.

Related tutorials

• Scaling Java Microservices on AWS EKS
• Spring Boot Microservices Architecture Explained
• Distributed Locking with Spring Boot and Redis