Database Migration Tools: Flyway, Alembic, Prisma Migrate

# 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