Schema Migrations with Flyway

Versioned SQL migrations, ddl-auto pitfalls and zero-downtime schema changes.

Advanced10 min read

Read these first

Entities, Repositories, and Queries

Why this matters

ddl-auto: update is unpredictable in production — Flyway gives you repeatable, reviewable schema changes.
Migration files are the database equivalent of git commits: versioned, auditable, and reversible in principle.
Zero-downtime deployments require careful migration ordering — Flyway's versioning model supports this.

Author1

BookN

Order1

Author has many Books. Order has many OrderItems referencing Books.

Add Flyway

Java

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

Flyway auto-configures when on the classpath. Set Hibernate to validate only:

Java

spring:
  jpa:
    hibernate:
      ddl-auto: validate
  flyway:
    enabled: true
    locations: classpath:db/migration

Migration file naming

Files live in src/main/resources/db/migration/:

Java

V1__create_books_table.sql
V2__create_authors_table.sql
V3__add_author_id_to_books.sql
V4__create_orders_table.sql

Convention: V{version}__{description}.sql. Double underscore separates version from description. Versions must be unique and sequential.

V1: Initial schema

Java

-- V1__create_books_table.sql
CREATE TABLE books (
    id          BIGSERIAL PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    isbn        VARCHAR(13) NOT NULL UNIQUE,
    price       NUMERIC(10, 2) NOT NULL,
    genre       VARCHAR(50) NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_books_genre ON books(genre);
CREATE INDEX idx_books_isbn ON books(isbn);

V2: Add authors

Java

-- V2__create_authors_table.sql
CREATE TABLE authors (
    id   BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

ALTER TABLE books ADD COLUMN author_id BIGINT REFERENCES authors(id);

V3: Seed reference data

Java

-- V3__seed_genres.sql
INSERT INTO authors (name) VALUES ('Robert C. Martin'), ('Andrew Hunt');

INSERT INTO books (title, isbn, price, genre, author_id)
VALUES ('Clean Code', '9780132350884', 38.50, 'Programming', 1);

Flyway tracks applied migrations in a flyway_schema_history table — already-applied scripts are never re-run.

Flyway rules

Never modify an applied migration — create a new version instead.
Versions are immutable — V3 applied in staging must be identical in production.
Test migrations locally before pushing — broken SQL blocks all deployments.
One concern per migration — easier to review and rollback.

Repeatable migrations

For views and stored procedures that can be re-applied:

Java

R__book_summary_view.sql

Java

-- R__book_summary_view.sql
CREATE OR REPLACE VIEW book_summary AS
SELECT b.id, b.title, b.price, a.name AS author_name
FROM books b
JOIN authors a ON b.author_id = a.id;

Repeatable migrations re-run when their checksum changes.

Baseline for existing databases

When adopting Flyway on a database that already has tables:

Java

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 1

Flyway marks version 1 as applied without running V1, then applies V2+ normally.

Zero-downtime patterns

For adding a non-nullable column to a live BookStore:

Java

-- V5__add_stock_column.sql (step 1: add nullable)
ALTER TABLE books ADD COLUMN stock INTEGER;

-- V6__backfill_stock.sql (step 2: populate)
UPDATE books SET stock = 100 WHERE stock IS NULL;

-- V7__enforce_stock_not_null.sql (step 3: constrain)
ALTER TABLE books ALTER COLUMN stock SET NOT NULL;

Deploy each step separately so the running application never sees an inconsistent schema.

Flyway in CI

Java

./mvnw flyway:migrate -Dflyway.url=jdbc:postgresql://127.0.0.1:5432/bookstore_test

Run migrations against a test database in CI before deploying to production.

Quick recall

Everything you need if you only revisit this box.

Flyway applies versioned SQL scripts from src/main/resources/db/migration/.
File naming: V{version}__{description}.sql with unique, sequential versions.
Set ddl-auto: validate — Hibernate checks schema; Flyway manages changes.
Never modify applied migrations; create new versions for every schema change.
Repeatable migrations (R__) re-apply views and procedures when checksums change.
Multi-step migrations enable zero-downtime column additions in production.

Test yourself

Answer these before moving on — recall is what makes it stick.