Database Migrations with Flyway

Module 7 · ~8 min read

Flyway treats database schema changes as versioned code. Each migration is a SQL file with a version number. Flyway tracks which migrations have been applied in a flyway_schema_history table and runs only the new ones on startup. Schema evolution becomes reproducible and auditable.

Why Flyway?

Schema changes are in version control alongside the code that requires them
Every environment (dev, staging, prod) goes through the same migration sequence
Flyway validates checksums on startup — modified migrations are detected and rejected
Zero-config in Spring Boot — auto-runs on startup when spring.flyway.enabled=true

Naming Convention

Flyway migration files follow the pattern V{version}__{description}.sql (two underscores):

V1__init_schema.sql — initial tables
V2__document_ingestion.sql — document and chunk tables
V3__add_guardrail_flags.sql — guardrail logging table

Migrations run in version order. Gaps are allowed (V1, V3, V5 is valid).

Never modify an existing migration file after it has been applied to any environment. Flyway stores the checksum of each migration. Modifying a file will cause a startup failure on every environment that already ran it. To fix a mistake, create a new migration.

Example: V1 Initial Schema

V1__init_schema.sql — users table View source ↗

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username    VARCHAR(50) UNIQUE NOT NULL,
    password    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Idempotent Patterns

For migrations that might need to be re-run (e.g., data backfills), use idempotent SQL patterns:

Idempotent migration patterns

CREATE TABLE IF NOT EXISTS my_table (...);

INSERT INTO config_values (key, value)
VALUES ('max_results', '10')
ON CONFLICT (key) DO NOTHING;

ALTER TABLE my_table
  ADD COLUMN IF NOT EXISTS new_col VARCHAR(100);

Application Configuration

application.yml — Flyway configuration View source ↗

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false

Production Rules

Never use spring.jpa.hibernate.ddl-auto: create or update in production alongside Flyway. Hibernate's schema management and Flyway will conflict, potentially dropping tables or leaving the schema in an inconsistent state. Set ddl-auto: validate or none.

ddl-auto value	Environment	Effect
`none`	Production	Hibernate makes no schema changes — Flyway controls everything
`validate`	Staging / CI	Hibernate checks the schema matches entities — fails fast on mismatch
`create`	Never with Flyway	Drops and recreates schema — destroys data
`update`	Never with Flyway	Conflicts with Flyway's schema ownership

← Previous Next →