Database Migration dengan Flyway

# Linux/macOS
curl -s https://repo.flywaydb.org/flyway-cli-latest.tar.gz | tar xz
sudo ln -s $(pwd)/flyway-*/flyway /usr/local/bin/flyway

# Windows (PowerShell)
# Download dari https://flywaydb.org/download
# Extract dan tambahkan ke PATH

# Docker
docker pull flyway/flyway:10

# Verifikasi
flyway -v

# Maven (untuk Java projects)
# pom.xml:
# <dependency>
#     <groupId>org.flywaydb</groupId>
#     <artifactId>flyway-core</artifactId>
#     <version>10.15.0</version>
# </dependency>

# Gradle
# implementation 'org.flywaydb:flyway-core:10.15.0'
# implementation 'org.flywaydb:flyway-database-postgresql:10.15.0'

# flyway.conf — konfigurasi Flyway

# Database connection
flyway.url=jdbc:postgresql://localhost:5432/myapp_db
flyway.user=postgres
flyway.password=secret_password

# Lokasi migration files
flyway.locations=filesystem:./sql/migrations

# Nama tabel untuk tracking
flyway.table=flyway_schema_history

# Encoding
flyway.encoding=UTF-8

# Placeholder replacement
flyway.placeholders.environment=development
flyway.placeholders.schema_name=public

# Behavior
flyway.cleanDisabled=true          # Disable flyway clean di production
flyway.validateOnMigrate=true      # Validasi checksum saat migrate
flyway.baselineOnMigrate=true      # Auto-baseline jika tabel belum ada
flyway.outOfOrder=false            # Tidak izinkan out-of-order migration
flyway.repeatableSqlMigrationPrefix=R
flyway.sqlMigrationPrefix=V
flyway.sqlMigrationSeparator=__
flyway.sqlMigrationSuffixes=.sql

# Target version (opsional, kosongkan untuk migrate ke latest)
# flyway.target=5.0

my-project/
├── sql/
│   └── migrations/
│       ├── V1__create_users_table.sql
│       ├── V2__create_posts_table.sql
│       ├── V3__add_email_column_to_users.sql
│       ├── V4__create_indexes.sql
│       ├── R__create_view_user_posts.sql
│       └── U1__create_users_table_undo.sql
├── flyway.conf
├── pom.xml (atau build.gradle)
└── docker-compose.yml

-- V1__create_users_table.sql
-- Migration pertama: membuat tabel users

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Index untuk pencarian
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

COMMENT ON TABLE users IS 'Tabel utama untuk data pengguna';

-- V2__create_posts_table.sql
-- Menambah tabel posts dengan foreign key ke users

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status) WHERE status = 'published';
CREATE INDEX idx_posts_slug ON posts(slug);

COMMENT ON TABLE posts IS 'Artikel dan postingan blog';

-- V3__add_tags_system.sql
-- Sistem tagging untuk posts

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

-- R__create_view_user_stats.sql
-- View yang di-update setiap kali file ini berubah
CREATE OR REPLACE VIEW user_stats AS
SELECT
    u.id,
    u.username,
    u.email,
    COUNT(p.id) AS post_count,
    COUNT(p.id) FILTER (WHERE p.status = 'published') AS published_count,
    MAX(p.published_at) AS last_published_at
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username, u.email;

-- R__create_function_update_timestamp.sql
-- Function untuk auto-update timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- R__create_triggers.sql
-- Trigger untuk tabel users
CREATE OR REPLACE TRIGGER trigger_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Trigger untuk tabel posts
CREATE OR REPLACE TRIGGER trigger_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- R__seed_default_tags.sql
-- Data default yang selalu di-sync
INSERT INTO tags (name, slug) VALUES
    ('Pemrograman', 'pemrograman'),
    ('Database', 'database'),
    ('DevOps', 'devops'),
    ('Tutorial', 'tutorial')
ON CONFLICT (slug) DO NOTHING;

-- U1__create_users_table_undo.sql
-- Membatalkan V1: menghapus tabel users
DROP TRIGGER IF EXISTS trigger_users_updated_at ON users;
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users;

-- U2__create_posts_table_undo.sql
-- Membatalkan V2: menghapus tabel posts
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS posts;

-- Perintah CLI untuk undo:
-- flyway undo
-- (Hanya tersedia di Flyway Teams/Enterprise)

-- V5__revert_tags_system.sql
-- "Undo" manual: buat migration baru yang membatalkan perubahan

-- Drop yang dibuat di V3
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;

-- ATAU: gunakan CREATE TABLE IF NOT EXISTS dan
-- ALTER TABLE untuk approach yang lebih aman

-- Jika butuh data lama:
-- 1. Buat backup table
CREATE TABLE tags_backup AS SELECT * FROM tags;

-- 2. Drop tabel lama
DROP TABLE post_tags;
DROP TABLE tags;

-- 3. Buat ulang dengan skema yang benar
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    category VARCHAR(30) DEFAULT 'general'  -- kolom baru
);

-- beforeMigrate.sql
-- Dijalankan sebelum seluruh migration batch
-- Berguna untuk backup atau pre-check

-- Cek koneksi database
SELECT 1;

-- Log migration start
DO $$
BEGIN
    RAISE NOTICE 'Starting Flyway migration at %', now();
END $$;

-- afterMigrate.sql
-- Dijalankan setelah semua migration selesai

-- Analisis tabel yang diubah
ANALYZE users;
ANALYZE posts;

-- Log completion
DO $$
BEGIN
    RAISE NOTICE 'Flyway migration completed at %', now();
END $$;

-- beforeEachMigrate.sql
-- Dijalankan sebelum SETIAP migration file
DO $$
BEGIN
    RAISE NOTICE 'About to execute migration...';
END $$;

-- afterEachMigrate.sql
-- Dijalankan setelah SETIAP migration file
DO $$
BEGIN
    RAISE NOTICE 'Migration executed successfully.';
END $$;

-- afterVersioned.sql
-- afterRepeatable.sql
-- beforeClean.sql, afterClean.sql
-- beforeBaseline.sql, afterBaseline.sql
-- beforeValidate.sql, afterValidate.sql
-- beforeUndo.sql, afterEachUndo.sql
-- beforeRepair.sql, afterRepair.sql

import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.api.callback.Context;
import org.springframework.stereotype.Component;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Component
public class MigrationCallback implements Callback {

    private static final Logger log = LoggerFactory.getLogger(MigrationCallback.class);

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.AFTER_EACH_MIGRATE ||
               event == Event.AFTER_MIGRATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return true;
    }

    @Override
    public void handle(Event event, Context context) {
        if (event == Event.AFTER_EACH_MIGRATE) {
            log.info("Migration executed: {} - {}",
                context.getMigrationInfo().getVersion(),
                context.getMigrationInfo().getDescription());
        }
        if (event == Event.AFTER_MIGRATE) {
            log.info("All migrations completed successfully");
            // Kirim notifikasi Slack, email, dll.
        }
    }
}

# Baseline: tandai database existing sebagai versi 1
flyway baseline -baselineVersion=1

# Atau auto-baseline dengan mengatur di config:
# flyway.baselineOnMigrate=true

# Info: lihat status semua migration
flyway info
# Output:
# +-----------+---------+-------------+------+---------------------+---------+----------+
# | Category  | Version | Description | Type | Installed On        | State   | Undoable |
# +-----------+---------+-------------+------+---------------------+---------+----------+
# | SQL       | 1       | create users| SQL  | 2026-06-29 10:00:00 | Success | No       |
# | SQL       | 2       | create posts| SQL  | 2026-06-29 10:00:01 | Success | No       |
# | SQL       | 3       | add tags    | SQL  |                     | Pending | No       |
# | Repeatable|         | view stats  | SQL  | 2026-06-29 10:00:02 | Success | No       |
# +-----------+---------+-------------+------+---------------------+---------+----------+

# Validate: cek apakah migration files sudah berubah
flyway validate

# Repair: perbaiki metadata yang rusak
flyway repair
# - Hapus migration yang gagal dari history
# - Perbaiki checksum yang tidak cocok
# - Re-align migration yang pending

# Clean: HAPUS SEMUA objek di database (HANYA untuk development!)
flyway clean

# Migrate: jalankan semua migration yang pending
flyway migrate

# Migrate ke versi spesifik
flyway migrate -target=2

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp_db
    username: postgres
    password: ${DB_PASSWORD}
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    validate-on-migrate: true
    clean-disabled: true       # SELALU true di production
    out-of-order: false
    table: flyway_schema_history
    placeholders:
      environment: ${SPRING_PROFILES_ACTIVE:development}

# Profile-specific
---
spring:
  config:
    activate:
      on-profile: production
  flyway:
    clean-disabled: true
    out-of-order: false

# .github/workflows/db-migrate.yml
name: Database Migration
on:
  push:
    paths:
      - 'sql/migrations/**'
      - 'db/migration/**'

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Start PostgreSQL
        run: docker run -d --name pg-test \
          -e POSTGRES_PASSWORD=test \
          -e POSTGRES_DB=testdb \
          -p 5432:5432 postgres:16

      - name: Wait for PostgreSQL
        run: sleep 5

      - name: Flyway Migrate
        uses: flyway/flyway-action@v1
        with:
          url: jdbc:postgresql://localhost:5432/testdb
          user: postgres
          password: test
          locations: filesystem:./db/migration
          command: migrate

      - name: Flyway Info
        uses: flyway/flyway-action@v1
        with:
          url: jdbc:postgresql://localhost:5432/testdb
          user: postgres
          password: test
          command: info

  deploy-staging:
    needs: validate
    runs-on: ubuntu-latest
    if: github.ref == 'refs/heads/main'
    steps:
      - uses: actions/checkout@v4
      - name: Migrate Staging Database
        uses: flyway/flyway-action@v1
        with:
          url: ${{ secrets.STAGING_DB_URL }}
          user: ${{ secrets.STAGING_DB_USER }}
          password: ${{ secrets.STAGING_DB_PASS }}
          locations: filesystem:./db/migration
          command: migrate