Database Migration Tools: Flyway, Alembic, Prisma Migrate

# Install via Homebrew (macOS/Linux)
brew install flyway

# Install via Docker
docker pull flyway/flyway

# Download binary
# https://flywaydb.org/documentation/usage/commandline/

# Verifikasi instalasi
flyway --version

# flyway.toml — Konfigurasi Flyway
[flyway]
url = "jdbc:mysql://localhost:3306/toko_online"
user = "root"
password = "password_anda"
schemas = ["toko_online"]

# Lokasi file migration
locations = ["filesystem:sql/migrations"]

# Nama tabel tracking migration
table = "flyway_schema_history"

# Encoding file SQL
encoding = "UTF-8"

# Placeholder (variabel dalam file SQL)
[flyway.placeholders]
app_user = "sistem"
env = "development"

-- =============================================
-- File: sql/migrations/V1__create_tables.sql
-- Deskripsi: Membuat tabel awal toko online
-- =============================================

-- Prefix V = versioned migration (dijalankan sekali)
-- Prefix R = repeatable migration (dijalankan ulang jika berubah)
-- Prefix U = undo migration (rollback, Flyway Teams/Enterprise)

-- V1__create_tables.sql
CREATE TABLE pelanggan (
    id_pelanggan INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    telepon VARCHAR(20),
    kota VARCHAR(50) DEFAULT 'Jakarta',
    tanggal_daftar DATE DEFAULT (CURRENT_DATE),
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE produk (
    id_produk INT PRIMARY KEY AUTO_INCREMENT,
    nama_produk VARCHAR(200) NOT NULL,
    harga DECIMAL(12, 2) NOT NULL CHECK (harga > 0),
    stok INT NOT NULL DEFAULT 0,
    kategori VARCHAR(50),
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pesanan (
    id_pesanan INT PRIMARY KEY AUTO_INCREMENT,
    id_pelanggan INT NOT NULL,
    tanggal_pesanan DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan)
);

-- =============================================
-- File: V2__add_indexes.sql
-- Deskripsi: Menambah index untuk performa
-- =============================================

CREATE INDEX idx_pelanggan_kota ON pelanggan(kota);
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_pesanan_tanggal ON pesanan(tanggal_pesanan);
CREATE INDEX idx_pesanan_status ON pesanan(status);

-- =============================================
-- File: V3__add_pelanggan_phone_index.sql
-- =============================================

CREATE INDEX idx_pelanggan_telepon ON pelanggan(telepon);

-- =============================================
-- File: V4__alter_pesanan_add_catatan.sql
-- =============================================

ALTER TABLE pesanan ADD COLUMN catatan TEXT;

-- =============================================
-- File: R__create_views.sql (Repeatable)
-- Akan diulang setiap kali isi file berubah
-- =============================================

CREATE OR REPLACE VIEW v_laporan_penjualan AS
SELECT
    p.kategori,
    COUNT(DISTINCT ps.id_pesanan) AS jumlah_pesanan,
    SUM(dp.subtotal) AS total_penjualan
FROM detail_pesanan dp
JOIN produk p ON dp.id_produk = p.id_produk
JOIN pesanan ps ON dp.id_pesanan = ps.id_pesanan
WHERE ps.status <> 'batal'
GROUP BY p.kategori;

# Cek status migration (mana yang sudah/belum dijalankan)
flyway info

# Jalankan semua migration yang belum dijalankan
flyway migrate

# Validasi: cek apakah file migration sudah berubah
flyway validate

# Repair: fix migration history yang corrupt
flyway repair

# Clean: HAPUS SEMUA objek di database (HATI-HATI!)
flyway clean

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

# Undo: rollback migration terakhir (Enterprise)
flyway undo

+-----------+---------+---------------------------+------+---------------------+---------+----------+
| Category  | Version | Description               | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------------+------+---------------------+---------+----------+
| Versioned | 1       | create tables             | SQL  | 2026-06-26 10:00:00 | Success | No       |
| Versioned | 2       | add indexes               | SQL  | 2026-06-26 10:00:01 | Success | No       |
| Versioned | 3       | add pelanggan phone index | SQL  | 2026-06-26 10:00:02 | Success | No       |
| Versioned | 4       | alter pesanan add catatan | SQL  |                     | Pending | No       |
| Repeatable |        | create views              | SQL  | 2026-06-26 10:00:03 | Outdated|          |
+-----------+---------+---------------------------+------+---------------------+---------+----------+

<!-- pom.xml: Plugin Flyway untuk Maven -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.15.0</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/toko_online</url>
        <user>root</user>
        <password>password_anda</password>
    </configuration>
</plugin>

<!-- Jalankan: -->
<!-- mvn flyway:migrate -->
<!-- mvn flyway:info -->

# Install Alembic
pip install alembic

# Inisialisasi project migration
alembic init alembic

# Struktur yang dihasilkan:
# alembic/
#   versions/       -- file migration
#   env.py          -- konfigurasi environment
#   script.py.mako  -- template file migration
# alembic.ini       -- konfigurasi utama

# alembic.ini
# [alembic]
# sqlalchemy.url = postgresql://user:pass@localhost/toko_online

# alembic/env.py — Konfigurasi yang lebih fleksibel
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig

# Import model SQLAlchemy Anda
from app.models import Base

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata

def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode."""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online() -> None:
    """Run migrations in 'online' mode."""
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

# Auto-generate migration dari perubahan model
alembic revision --autogenerate -m "initial tables"

# Buat migration kosong (manual)
alembic revision -m "add custom indexes"

# Jalankan migration
alembic upgrade head

# Jalankan 1 migration ke depan
alembic upgrade +1

# Rollback 1 migration
alembic downgrade -1

# Rollback ke awal
alembic downgrade base

# Lihat history
alembic history --verbose

# Lihat current version
alembic current

# Lihat semua migration yang pending
alembic history | grep "(head)"

# alembic/versions/20260626_001_create_tables.py
"""create initial tables

Revision ID: a1b2c3d4e5f6
Revises:
Create Date: 2026-06-26 10:00:00.000000
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision: str = 'a1b2c3d4e5f6'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Tabel pelanggan
    op.create_table(
        'pelanggan',
        sa.Column('id_pelanggan', sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column('nama', sa.String(100), nullable=False),
        sa.Column('email', sa.String(150), nullable=False, unique=True),
        sa.Column('telepon', sa.String(20)),
        sa.Column('kota', sa.String(50), server_default='Jakarta'),
        sa.Column('tanggal_daftar', sa.Date(), server_default=sa.text('CURRENT_DATE')),
        sa.Column('is_active', sa.Boolean(), server_default='true'),
    )

    # Tabel produk
    op.create_table(
        'produk',
        sa.Column('id_produk', sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column('nama_produk', sa.String(200), nullable=False),
        sa.Column('harga', sa.Numeric(12, 2), nullable=False),
        sa.Column('stok', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('kategori', sa.String(50)),
        sa.Column('deskripsi', sa.Text()),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )

    # Tabel pesanan
    op.create_table(
        'pesanan',
        sa.Column('id_pesanan', sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column('id_pelanggan', sa.Integer(), sa.ForeignKey('pelanggan.id_pelanggan'), nullable=False),
        sa.Column('tanggal_pesanan', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('total', sa.Numeric(12, 2), nullable=False),
        sa.Column('status', sa.String(20), server_default='pending'),
        sa.Column('catatan', sa.Text()),
    )

    # Indexes
    op.create_index('idx_pelanggan_kota', 'pelanggan', ['kota'])
    op.create_index('idx_produk_kategori', 'produk', ['kategori'])
    op.create_index('idx_pesanan_tanggal', 'pesanan', ['tanggal_pesanan'])


def downgrade() -> None:
    op.drop_table('pesanan')
    op.drop_table('produk')
    op.drop_table('pelanggan')

# Install Prisma
npm install prisma --save-dev
npm install @prisma/client

# Inisialisasi Prisma
npx prisma init

# Struktur yang dihasilkan:
# prisma/
#   schema.prisma    -- definisi model
#   migrations/      -- file migration
# .env               -- DATABASE_URL

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Pelanggan {
  idPelanggan   Int        @id @default(autoincrement())
  nama          String     @db.VarChar(100)
  email         String     @unique @db.VarChar(150)
  telepon       String?    @db.VarChar(20)
  kota          String     @default("Jakarta") @db.VarChar(50)
  tanggalDaftar DateTime   @default(now()) @map("tanggal_daftar")
  isActive      Boolean    @default(true) @map("is_active")
  pesanan       Pesanan[]

  @@map("pelanggan")
}

model Produk {
  idProduk   Int              @id @default(autoincrement())
  namaProduk String           @map("nama_produk") @db.VarChar(200)
  harga      Decimal          @db.Decimal(12, 2)
  stok       Int              @default(0)
  kategori   String?          @db.VarChar(50)
  deskripsi  String?
  createdAt  DateTime         @default(now()) @map("created_at")
  detail     DetailPesanan[]

  @@map("produk")
}

model Pesanan {
  idPesanan      Int            @id @default(autoincrement())
  idPelanggan    Int            @map("id_pelanggan")
  tanggalPesanan DateTime       @default(now()) @map("tanggal_pesanan")
  total          Decimal        @db.Decimal(12, 2)
  status         String         @default("pending") @db.VarChar(20)
  catatan        String?
  pelanggan      Pelanggan      @relation(fields: [idPelanggan], references: [idPelanggan])
  detail         DetailPesanan[]

  @@map("pesanan")
}

model DetailPesanan {
  idDetail     Int     @id @default(autoincrement())
  idPesanan    Int     @map("id_pesanan")
  idProduk     Int     @map("id_produk")
  jumlah       Int
  hargaSatuan  Decimal @map("harga_satuan") @db.Decimal(12, 2)
  subtotal     Decimal @db.Decimal(12, 2)
  pesanan      Pesanan @relation(fields: [idPesanan], references: [idPesanan])
  produk       Produk  @relation(fields: [idProduk], references: [idProduk])

  @@map("detail_pesanan")
}

# Buat migration dari perubahan schema.prisma
npx prisma migrate dev --name add_phone_index

# Jalankan migration di production
npx prisma migrate deploy

# Reset database (HAPUS SEMUA DATA + re-run semua migration)
npx prisma migrate reset

# Generate Prisma Client setelah schema berubah
npx prisma generate

# Push schema langsung tanpa migration (prototyping)
npx prisma db push

# Buka Prisma Studio (GUI untuk data)
npx prisma studio

# Lihat status migration
npx prisma migrate status

# Resolve migration conflict
npx prisma migrate resolve --applied <migration_name>

-- prisma/migrations/20260626100000_init/migration.sql

-- CreateTable
CREATE TABLE "pelanggan" (
    "id_pelanggan" SERIAL NOT NULL,
    "nama" VARCHAR(100) NOT NULL,
    "email" VARCHAR(150) NOT NULL,
    "telepon" VARCHAR(20),
    "kota" VARCHAR(50) DEFAULT 'Jakarta',
    "tanggal_daftar" DATE DEFAULT CURRENT_DATE,
    "is_active" BOOLEAN DEFAULT true,

    CONSTRAINT "pelanggan_pkey" PRIMARY KEY ("id_pelanggan")
);

-- CreateIndex
CREATE UNIQUE INDEX "pelanggan_email_key" ON "pelanggan"("email");

-- CreateTable
CREATE TABLE "produk" (
    "id_produk" SERIAL NOT NULL,
    "nama_produk" VARCHAR(200) NOT NULL,
    "harga" DECIMAL(12,2) NOT NULL,
    "stok" INTEGER NOT NULL DEFAULT 0,
    "kategori" VARCHAR(50),
    "deskripsi" TEXT,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "produk_pkey" PRIMARY KEY ("id_produk")
);

-- CreateTable
CREATE TABLE "pesanan" (
    "id_pesanan" SERIAL NOT NULL,
    "id_pelanggan" INTEGER NOT NULL,
    "tanggal_pesanan" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "total" DECIMAL(12,2) NOT NULL,
    "status" VARCHAR(20) DEFAULT 'pending',
    "catatan" TEXT,

    CONSTRAINT "pesanan_pkey" PRIMARY KEY ("id_pesanan"),
    CONSTRAINT "pesanan_id_pelanggan_fkey"
        FOREIGN KEY ("id_pelanggan") REFERENCES "pelanggan"("id_pelanggan")
        ON DELETE RESTRICT ON UPDATE CASCADE
);

-- =============================================
-- LANGKAH 1: Tambah kolom baru (tidak locking tabel)
-- =============================================
ALTER TABLE pelanggan ADD COLUMN nomor_whatsapp VARCHAR(20);

-- =============================================
-- LANGKAH 2: Backfill data dari kolom lama
-- Jalankan dalam batch untuk tabel besar
-- =============================================
UPDATE pelanggan
SET nomor_whatsapp = telepon
WHERE nomor_whatsapp IS NULL
LIMIT 10000;
-- Ulangi sampai semua data terisi

-- =============================================
-- LANGKAH 3: Update aplikasi untuk menulis
-- ke kedua kolom (dual write)
-- =============================================
-- Di kode aplikasi:
-- INSERT INTO pelanggan (nama, email, telepon, nomor_whatsapp)
-- VALUES (?, ?, ?, ?);

-- =============================================
-- LANGKAH 4: Verifikasi data konsisten
-- =============================================
SELECT COUNT(*) FROM pelanggan
WHERE telepon IS NOT NULL AND nomor_whatsapp IS NULL;
-- Harusnya 0

-- =============================================
-- LANGKAH 5: Hapus kolom lama (setelah semua
-- aplikasi menggunakan kolom baru)
-- =============================================
ALTER TABLE pelanggan DROP COLUMN telepon;

# .github/workflows/deploy.yml
name: Deploy with Migration

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: toko_online
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      # Flyway migration
      - name: Run Flyway Migrations
        uses: flyway/flyway-action@v0.4.0
        with:
          url: jdbc:postgresql://localhost:5432/toko_online
          user: postgres
          password: postgres
          locations: filesystem:sql/migrations
          command: migrate

      # Alembic migration
      - name: Run Alembic Migrations
        run: |
          pip install alembic sqlalchemy psycopg2-binary
          alembic upgrade head
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/toko_online

      # Prisma migration
      - name: Run Prisma Migrations
        run: |
          npm install
          npx prisma migrate deploy
          npx prisma generate
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/toko_online