flyway-migrations

Flyway 迁移

文件命名约定

src/main/resources/db/migration/

V{version}__{description}.sql       ← versioned (run once)
R__{description}.sql                ← repeatable (run when checksum changes)
U{version}__{description}.sql       ← undo (requires Flyway Teams)

Examples:
V1__create_users_table.sql
V2__create_orders_table.sql
V2.1__add_order_status_index.sql
V3__add_customer_email_to_orders.sql
R__create_reporting_views.sql

规则：

• 版本号和描述之间使用双下划线 __
• 描述中的空格使用下划线 _
• 版本号按顺序递增——不要回头补填缺失的版本号
• 绝不修改已在任何环境中运行过的迁移

迁移示例

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

CREATE INDEX idx_users_email ON users(email);

-- V2__create_orders_table.sql
CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id),
    status          VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    total_amount    NUMERIC(12, 2) NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- V3__add_shipping_address_to_orders.sql
-- Adding a column — always nullable or with default (safe for existing rows)
ALTER TABLE orders
    ADD COLUMN shipping_address TEXT,
    ADD COLUMN shipped_at TIMESTAMPTZ;

安全的迁移模式

-- ✅ Safe: add nullable column
ALTER TABLE orders ADD COLUMN notes TEXT;

-- ✅ Safe: add column with default
ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;

-- ✅ Safe: rename via expand → migrate → contract, spread across deploys
-- Step 1 (V5): add new column (nullable — old instances keep inserting without it)
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
-- Step 2 (V5): backfill
UPDATE orders SET customer_email = (SELECT email FROM users WHERE users.id = orders.user_id);
-- Step 3 (V7, only after EVERY writer populates customer_email): add the check as NOT VALID —
-- instant, no table scan, but new rows are already enforced
ALTER TABLE orders ADD CONSTRAINT orders_customer_email_nn CHECK (customer_email IS NOT NULL) NOT VALID;
-- Step 4 (V8 — a SEPARATE migration, so the V7 lock is already released): validate without
-- blocking writes, then SET NOT NULL reuses the validated check and skips the scan (PG 12+)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_email_nn;
ALTER TABLE orders ALTER COLUMN customer_email SET NOT NULL;
-- Step 5 (V9, after no deployed code reads user_id): drop old column
ALTER TABLE orders DROP COLUMN user_id;

-- ✅ Safe: add index CONCURRENTLY (no table lock in Postgres) — TWO rules, both required:
-- 1. Put it ALONE in its own migration file. Flyway detects CONCURRENTLY as
--    non-transactional; mixed with any other statement the migration is rejected:
--    "Detected both transactional and non-transactional statements".
-- 2. Set spring.flyway.postgresql.transactional-lock=false. Flyway's default
--    advisory lock holds an open transaction on a second connection, and
--    CONCURRENTLY waits for every open transaction — including Flyway's own.
--    The migration hangs forever. Session-level locks still block concurrent migrators.
-- V6__index_orders_email.sql (after V5 adds the column)
CREATE INDEX CONCURRENTLY idx_orders_email ON orders(customer_email);

-- ❌ Dangerous: rename column directly (breaks running app)
ALTER TABLE orders RENAME COLUMN user_id TO customer_id;

-- ❌ Dangerous: NOT NULL without default on large table (locks table)
ALTER TABLE orders ADD COLUMN priority INT NOT NULL; -- will fail on existing rows

application.yml

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false  # reject non-empty schemas without migration history
    validate-on-migrate: true
    out-of-order: false         # enforce sequential execution
    postgresql:
      transactional-lock: false # required for CREATE INDEX CONCURRENTLY (see above)

为现有数据库接入 Flyway

在正常的应用配置中，保持自动基线设置为禁用状态。对于已有数据表但没有 Flyway 历史记录的数据库：

1. 备份数据库，并验证目标 URL、数据库、架构和凭据。
2. 将其架构与迁移进行比较，明确选择当前已有的版本。
3. 使用该目标运行一次性命令 flyway baseline -baselineVersion=<verified-version>。
4. 检查 flyway info，然后仅迁移后续版本。切勿仅为消除启动错误，就为未知或部分迁移的架构设置基线。

这与面向新环境的基线迁移脚本不同。自动设置基线会移除 Flyway 防止连接到错误数据库的安全检查： 官方的 baseline-on-migrate 文档。

种子数据（仅限测试/开发环境）

// Use Spring profiles, not Flyway, for seed data
@Component
@Profile("dev")
@RequiredArgsConstructor
public class DevDataSeeder implements ApplicationRunner {
    private final UserRepository userRepository;

    @Override
    public void run(ApplicationArguments args) {
        if (userRepository.count() == 0) {
            userRepository.save(User.createAdmin("[email protected]", "password123"));
        }
    }
}

团队工作流：并发迁移

• 多位开发者同时创建迁移会导致版本冲突
• 解决方案：使用共享跟踪工具（Slack 频道、wiki 页面）或基于时间戳的版本号（V20260414_1__）
• 如果两个迁移使用相同的版本号，其中一位开发者必须提高自己的版本号
• 提交前运行 flyway info，检查是否存在版本号缺口或重复
• 在 CI/CD 中：将 flyway validate 作为部署前步骤，尽早发现冲突
• 生产环境中绝不要设置 out-of-order: true——这会掩盖迁移顺序错误

注意事项

• Agent 只添加了 flyway-core——从 Flyway 10（Boot 3.3+）开始，数据库支持由 flyway-database-<db> 模块提供（例如 flyway-database-postgresql）；缺少该模块会导致启动失败，并报错“Unsupported Database”
• Agent 将文件命名为 V1_create_users.sql（单下划线）——必须使用双下划线 __
• Agent 修改了现有迁移文件——绝不要编辑已运行过的迁移
• Agent 添加了没有默认值的 NOT NULL 列——应使用可空列或提供默认值
• Agent 直接重命名列——应分多个步骤，在不同部署中依次添加、回填并删除
• Agent 在 Flyway 迁移中植入种子数据——应改用 @Profile("dev") 种子数据加载器
• Agent 在同一个迁移中混用 CREATE INDEX CONCURRENTLY 和其他语句——Flyway 会拒绝执行（“both transactional and non-transactional statements”）；应将其放入单独的文件
• Agent 发布了 CONCURRENTLY 迁移，却没有设置 spring.flyway.postgresql.transactional-lock=false——索引构建会等待 Flyway 自身的 advisory-lock 事务，导致部署挂起
• Agent 跳过了索引——始终为外键以及用于 WHERE/ORDER BY 的列创建索引
• Agent 创建的迁移以 DROP TABLE 或 DROP COLUMN 作为第一步——始终先添加新列、部署代码，然后在之后的迁移中删除旧列