✍️ 필사 모드: Flyway完全ガイド -- DBマイグレーション自動化のすべて

app-code    v1.0 -> v1.1 -> v1.2 -> v2.0
             |       |       |       |
db-schema   V1   -> V2   -> V3   -> V4

# macOS
brew install flyway

# Linux (tar.gz)
wget -qO- https://download.red-gate.com/maven/release/com/redgate/flyway/flyway-commandline/10.x/flyway-commandline-10.x-linux-x64.tar.gz | tar xz
sudo ln -s $(pwd)/flyway-10.x/flyway /usr/local/bin

# バージョン確認
flyway --version

docker run --rm flyway/flyway:10 -url=jdbc:postgresql://host:5432/mydb -user=admin -password=secret migrate

services:
  flyway:
    image: flyway/flyway:10
    command: migrate
    volumes:
      - ./sql:/flyway/sql
      - ./conf:/flyway/conf
    depends_on:
      db:
        condition: service_healthy
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
      interval: 5s
      timeout: 5s
      retries: 5

<!-- Maven pom.xml -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.15.0</version>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/mydb</url>
        <user>admin</user>
        <password>secret</password>
    </configuration>
</plugin>

// Gradle build.gradle
plugins {
    id "org.flywaydb.flyway" version "10.15.0"
}

flyway {
    url = 'jdbc:postgresql://localhost:5432/mydb'
    user = 'admin'
    password = 'secret'
}

<!-- 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
    baseline-version: '0'

V2__add_email_column.sql
|  |  +-- 説明（アンダースコア2つで区切り）
|  +-- バージョン番号
+-- タイプ (V=Versioned, U=Undo, R=Repeatable)

SELECT installed_rank, version, description, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;

 installed_rank | version |      description      |  checksum   |     installed_on     | success
----------------+---------+-----------------------+-------------+----------------------+---------
              1 | 1       | init                  |  1234567890 | 2026-04-01 10:00:00  | t
              2 | 2       | add email column      | -987654321  | 2026-04-05 14:30:00  | t
              3 | 3       | create orders table    |  1122334455 | 2026-04-10 09:15:00  | t

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_username ON users(username);

-- U1__create_users_table.sql
DROP TABLE IF EXISTS users;

-- R__refresh_user_statistics_view.sql
CREATE OR REPLACE VIEW user_statistics AS
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

sql/
  beforeMigrate.sql      -- マイグレーション開始前
  afterMigrate.sql       -- マイグレーション完了後
  beforeEachMigrate.sql  -- 各マイグレーションファイル実行前
  afterEachMigrate.sql   -- 各マイグレーションファイル実行後
  beforeValidate.sql     -- バリデーション前

-- afterMigrate.sql
ANALYZE;
SELECT schemaname, tablename, last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';

flyway migrate

flyway info

+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Category  | Version | Description         | Type     | Installed On        | State    | Undoable |
+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Versioned | 1       | init                | SQL      | 2026-04-01 10:00:00 | Success  | No       |
| Versioned | 2       | add email column    | SQL      | 2026-04-05 14:30:00 | Success  | No       |
| Versioned | 3       | create orders table | SQL      |                     | Pending  | No       |
+-----------+---------+---------------------+----------+---------------------+----------+----------+

flyway validate

flyway repair

flyway clean

flyway baseline -baselineVersion=5 -baselineDescription="existing schema"

# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=admin
flyway.password=secret
flyway.schemas=public
flyway.locations=filesystem:sql
flyway.baselineOnMigrate=true
flyway.baselineVersion=0
flyway.connectRetries=3
flyway.cleanDisabled=true
flyway.outOfOrder=false
flyway.validateOnMigrate=true

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: admin
    password: secret
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: '0'
    clean-disabled: true
    validate-on-migrate: true
    out-of-order: false
    connect-retries: 3
    table: flyway_schema_history

# application-dev.yml
spring:
  flyway:
    clean-disabled: false
    locations: classpath:db/migration,classpath:db/seed

# application-prod.yml
spring:
  flyway:
    clean-disabled: true
    locations: classpath:db/migration
    validate-on-migrate: true

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uq_users_username UNIQUE (username)
);

COMMENT ON TABLE users IS 'ユーザー情報';
COMMENT ON COLUMN users.username IS 'ログインID';

-- V2__add_email_unique_constraint.sql
-- 既存の重複データ整理
DELETE FROM users a
USING users b
WHERE a.id > b.id
  AND a.email = b.email
  AND a.email IS NOT NULL;

-- NOT NULL制約追加
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- UNIQUEインデックス追加
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users(email);
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX idx_users_email;

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    currency VARCHAR(3) NOT NULL DEFAULT 'KRW',
    shipping_address TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT chk_orders_status CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

COMMENT ON TABLE orders IS '注文情報';

-- R__refresh_views.sql
CREATE OR REPLACE VIEW v_user_order_summary AS
SELECT
    u.id AS user_id,
    u.username,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(AVG(o.total_amount), 0) AS avg_order_amount,
    MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'CANCELLED'
GROUP BY u.id, u.username, u.email;

CREATE OR REPLACE VIEW v_daily_order_stats AS
SELECT
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status NOT IN ('CANCELLED')
GROUP BY DATE(created_at);

src/main/resources/
  db/
    migration/
      V1__create_users_table.sql
      V2__add_email_unique_constraint.sql
      V3__create_orders_table.sql
      V4__create_order_items_table.sql
      V5__add_user_profile_fields.sql
      R__refresh_views.sql
      R__update_functions.sql
    seed/
      V100__insert_test_users.sql
      V101__insert_test_orders.sql

V20260412_001__feature_a_create_table.sql   (開発者A)
V20260412_002__feature_b_add_column.sql     (開発者B)

spring:
  flyway:
    out-of-order: true

# 方法1: 連番
V1__create_users.sql
V2__add_index.sql

# 方法2: 日付ベース
V20260412.1__create_users.sql
V20260412.2__add_index.sql

# 方法3: チケット番号を含む
V1__JIRA_123_create_users.sql
V2__JIRA_456_add_index.sql

name: Database Migration
on:
  push:
    branches: [main]
    paths:
      - 'src/main/resources/db/migration/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway Validate
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            validate

      - name: Run Flyway Migrate
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            migrate

      - name: Run Flyway Info
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            info

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-app
spec:
  template:
    spec:
      initContainers:
        - name: flyway-migrate
          image: flyway/flyway:10
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              value: "jdbc:postgresql://postgres-service:5432/mydb"
            - name: FLYWAY_USER
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: username
            - name: FLYWAY_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: password
          volumeMounts:
            - name: migration-scripts
              mountPath: /flyway/sql
      containers:
        - name: app
          image: my-app:latest
      volumes:
        - name: migration-scripts
          configMap:
            name: flyway-migrations

-- V10__expand_add_new_column.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- 既存データのコピー
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- 両方のカラムに書き込むトリガー設定
CREATE OR REPLACE FUNCTION sync_user_name()
RETURNS TRIGGER AS '
BEGIN
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        IF NEW.name IS DISTINCT FROM OLD.name THEN
            NEW.full_name := NEW.name;
        END IF;
        IF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
            NEW.name := NEW.full_name;
        END IF;
    END IF;
    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_user_name
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_user_name();

-- V11__contract_remove_old_column.sql
DROP TRIGGER IF EXISTS trg_sync_user_name ON users;
DROP FUNCTION IF EXISTS sync_user_name();
ALTER TABLE users DROP COLUMN name;

-- V12__add_index_concurrently.sql
-- FlywayでCONCURRENTLYを使用するには、トランザクションを無効化する必要があります
-- flyway.confで設定: flyway.postgresql.transactional.lock=false
-- またはSpring Bootで別途non-transactional migrationを使用

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at_status
ON orders(created_at, status);

# 方法1: repairでチェックサムを更新（変更が意図的な場合）
flyway repair

# 方法2: 元のファイルを復元（誤って変更した場合）
git checkout -- src/main/resources/db/migration/V1__init.sql

# 1. 失敗状態の確認
flyway info

# 2. 失敗したマイグレーション記録の削除
flyway repair

# 3. SQLファイルを修正して再実行
flyway migrate

# 1. 現在のスキーマをV1としてダンプ
pg_dump --schema-only mydb > V1__baseline.sql

# 2. baseline設定
flyway baseline -baselineVersion=1

# 3. 以降のマイグレーションはV2から作成

sql/
  migration/
    V5__add_payment_table.sql
  rollback/
    V5__rollback_add_payment_table.sql

-- V5__rollback_add_payment_table.sql (手動実行用)
DROP TABLE IF EXISTS payments;
DELETE FROM flyway_schema_history WHERE version = '5';

-- V6__add_role_column.sql (DDL)
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'USER';

-- V7__migrate_admin_roles.sql (DML)
UPDATE users SET role = 'ADMIN' WHERE username IN ('admin', 'superadmin');
UPDATE users SET role = 'MODERATOR' WHERE username IN ('mod1', 'mod2');

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

-- db/seed/V1000__seed_test_users.sql
INSERT INTO users (username, email, password_hash, role) VALUES
('testuser1', 'test1@example.com', 'hashed_pw_1', 'USER'),
('testuser2', 'test2@example.com', 'hashed_pw_2', 'USER'),
('testadmin', 'admin@example.com', 'hashed_pw_3', 'ADMIN')
ON CONFLICT (username) DO NOTHING;