Flyway Migrations

Flyway Migrations

Scope

Apply this skill when working on:

• Flyway migration scripts (V1__, V2__, etc.)
• schema evolution (DDL)
• data migrations (DML)
• production database changes
• PostgreSQL schema design

Core Principles

• Every database change must be implemented via a migration — never modify production databases manually
• Migrations are forward-only — never edit applied migrations
• Separate schema (DDL) and data (DML) migrations
• Migrations must be immutable once applied
• Always test migrations on production-like data

Flyway Naming & Structure

• Use versioned scripts:
  • V1__init.sql
  • V2__add_user_table.sql
• Use clear, descriptive names
• Keep each migration small and focused
• Avoid combining multiple concerns in one migration

Migration Safety Rules

Before applying a migration:

• No full table locks on large tables
• New columns must be:
  • nullable OR
  • have default values
• Indexes must be created safely (see below)
• Large updates must be batched
• Rollback strategy must be known (even if forward-only)

PostgreSQL Safe Patterns

Adding Columns

-- SAFE
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- SAFE (Postgres 11+)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- UNSAFE
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;