Home/Learn/Spring Boot/Database Migrations

Database Migrations

Intermediate

Data & JPA

Flyway manages database schema changes through versioned SQL scripts. Every schema change is a numbered migration file — repeatable, auditable, and automatically applied on startup.

Overview

Flyway tracks which migration scripts have been applied using a flyway_schema_history table. On each startup, Spring Boot auto-runs Flyway before the application starts serving traffic, applying any pending migrations in version order. Scripts are named V{version}__{description}.sql. Never modify an already-applied migration — create a new one instead. This makes schema changes as version-controlled as your code.

Flyway Setup and Migration Files

Add spring-boot-starter-flyway and place SQL scripts in src/main/resources/db/migration. Spring Boot auto-configures Flyway with your datasource. Migrations run in order at startup — before any JPA entity validation.

XML + YAML — Flyway dependency and config

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-flyway</artifactId>
</dependency>

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true   # safe for existing DBs with no flyway history
    out-of-order: false         # strictly ordered (recommended)

# File naming convention:
# V{version}__{description}.sql   — versioned (applied once, never re-run)
# R__{description}.sql            — repeatable (re-runs when checksum changes)
# U{version}__{description}.sql   — undo (Enterprise edition)

# Migration files:
src/main/resources/db/migration/
├── V1__create_users_table.sql
├── V2__create_orders_table.sql
├── V3__add_status_to_orders.sql
└── V4__create_order_items_table.sql

Writing Migration Scripts

Write migrations as plain SQL — no ORM abstractions. Each script should be idempotent where possible (IF NOT EXISTS, IF EXISTS). Test migrations on a copy of production data before deploying. For large tables, prefer additive changes: add nullable columns first, backfill in batches, then add constraints.

SQL — migration scripts with safe additive-change pattern

-- V1__create_users_table.sql
CREATE TABLE users (
    id          VARCHAR(36) PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(255),
    password    VARCHAR(255),
    role        VARCHAR(50)  NOT NULL DEFAULT 'USER',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- V2__create_orders_table.sql
CREATE TABLE orders (
    id          VARCHAR(36)    PRIMARY KEY,
    customer_id VARCHAR(36)    NOT NULL REFERENCES users(id),
    total       NUMERIC(12, 2) NOT NULL,
    status      VARCHAR(50)    NOT NULL DEFAULT 'PENDING',
    created_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- V3__add_shipping_address_to_orders.sql
-- ✅ Safe: add nullable column first, backfill, then add NOT NULL constraint
ALTER TABLE orders ADD COLUMN shipping_address TEXT;

-- V4__backfill_shipping_address.sql
UPDATE orders SET shipping_address = 'Unknown' WHERE shipping_address IS NULL;

-- V5__set_shipping_address_not_null.sql
ALTER TABLE orders ALTER COLUMN shipping_address SET NOT NULL;

Key Points to Remember

1Migration files are named V{version}__{description}.sql — double underscore between version and name.
2Never modify an applied migration — Flyway checksums files and will fail on any change.
3baseline-on-migrate = true allows Flyway to take over an existing schema without migrating from scratch.
4For large production tables: add nullable column → backfill in batches → add NOT NULL — never in one migration.
5Repeatable migrations (R__name.sql) re-run when their content changes — useful for stored procedures and views.
6Run Flyway validate in CI to catch any tampering with already-applied migration files.

Interview Questions

How does Flyway know which migrations have already been applied?

EasyAtlassian

What happens if you modify an already-applied Flyway migration script?

EasyThoughtWorks

What is the safe way to add a NOT NULL column to a large production table?

HardGoldman Sachs

What is baseline-on-migrate and when would you use it?

MediumRazorpay

What is the difference between versioned and repeatable migrations in Flyway?

MediumAmazon

Ask Aria about Database Migrations

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Transactions

Bean Validation