Incrementally Growing Schema Migrations with Flyway

Introduction

“I installed Flyway but I don’t know how to use it.”

That’s how it felt when I first touched Flyway.
The official documentation shows examples starting from V1__create_table.sql, but it was hard to figure out:

• How to start when you already have an existing DB (dvdrental)
• What unit to create files in when adding or modifying schema during development
• How to handle the production and local states getting out of sync

This article is a record of how I actually use Flyway in a real project.

---

What is Flyway?

A tool for managing DB schema change history with SQL files.

resources/db/migration/
  V1__initial_schema.sql
  V2__add_customer_app_schema.sql
  V3__add_taste_tags_to_film.sql

• Runs unapplied SQL in order when the app starts
• Applied SQL is recorded in the flyway_schema_history table
• Once applied, SQL cannot be changed (add a new SQL file instead)

---

Setup

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

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true  # Required when applying to an existing DB
    baseline-version: 0

---

Applying to an Existing DB (the dvdrental Case)

dvdrental is an existing sample DB. I added Flyway when its tables already existed.

The Importance of baseline-on-migrate

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

When applying Flyway to an existing DB for the first time, without this option
you get the problem where “it tries to run V1 SQL when the tables already exist, and fails.”

With baseline-on-migrate: true, Flyway treats the current DB as the baseline state (V0)
and only applies SQL from V1 onwards.

How to Write the Initial SQL

Since existing tables are outside Flyway’s management, V1 only needs the delta from the current state.

-- V1__baseline.sql (empty file or comments only)
-- dvdrental's existing tables are outside Flyway management
-- From V2 onwards, manage additions to the customer_app schema

---

Migration Files Used in Practice

V2: Creating the customer_app Schema

-- V2__create_customer_app_schema.sql
CREATE SCHEMA IF NOT EXISTS customer_app;

CREATE TABLE customer_app.app_user (
    user_id     BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    full_name   VARCHAR(255),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

V3: Adding the Film Tags Column

-- V3__add_taste_tags_to_film.sql
ALTER TABLE film ADD COLUMN IF NOT EXISTS taste_tags TEXT[];

COMMENT ON COLUMN film.taste_tags IS 'Mood tags auto-generated by LLM';

V4: Creating Cart Tables

-- V4__create_cart_tables.sql
CREATE TABLE customer_app.cart (
    cart_id     BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES customer_app.app_user(user_id),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_app.cart_item (
    cart_item_id    BIGSERIAL PRIMARY KEY,
    cart_id         BIGINT NOT NULL REFERENCES customer_app.cart(cart_id),
    film_id         INTEGER NOT NULL REFERENCES film(film_id),
    added_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

File Naming Convention

V{version}__{description}.sql

• Version is an integer or dot-separated (V1, V1.1, V2, etc.)
• Two underscores (__)
• Description in snake_case

V1__baseline.sql
V2__create_customer_app_schema.sql
V3__add_taste_tags_to_film.sql
V3_1__add_index_to_film_taste_tags.sql  ← When you want to add after V3

---

Common Failures and Fixes

① Accidentally Modified an Already-Applied SQL

ERROR: Detected failed migration to version 3
FlywayException: Validate failed: 
Detected applied migration not resolved locally: 3

Flyway records checksums for applied SQL.
Modifying it causes a checksum mismatch error.

Fix: Never modify it. If you need to fix something, create a new version SQL.

② Flyway State Out of Sync Between Local and Production

During development, you may want to fix V2 SQL after V3 has already been applied.
In that case, check the state in the flyway_schema_history table.

SELECT * FROM flyway_schema_history ORDER BY installed_rank;

③ Don’t Want to Use Flyway in Tests

# application-test.yml
spring:
  flyway:
    enabled: false
  jpa:
    hibernate:
      ddl-auto: create-drop  # Manage schema with Hibernate for tests

---

Development Flow

1. New feature requires DB schema changes
   ↓
2. Create V{next-number}__{description}.sql
   ↓
3. Start app locally → Flyway auto-applies
   ↓
4. Verify behavior
   ↓
5. Commit → also auto-applied on production deployment

The workflow becomes “schema change = add a SQL file”,
which means DB changes appear as Git diffs, making review and rollback easier.

---

Summary

Point	Details
Applying to existing DB	Set initial state with baseline-on-migrate: true
File naming	V{version}__{description}.sql (V is uppercase, __ is two underscores)
Never modify applied SQL	Fixes go in a new version
Granularity of additions	Separate files per operation: table create, column add, index add, etc.

With Flyway, DB schema changes can be handled the same way as code changes.
The frequency of logging directly into the DB to check “what’s the current production schema” drops dramatically.

---

Article Map for This Series

→ Building an End-User DVD Rental App — Vue 3 + Spring Boot Paired with the Admin App, with Article Map