Migration

// Prisma 마이그레이션 예제 - Node.js / TypeScript

// ==============================================
// 1. Prisma 스키마 정의 (prisma/schema.prisma)
// ==============================================

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

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

model User {
  id          Int       @id @default(autoincrement())
  email       String    @unique
  username    String
  passwordHash String   @map("password_hash")
  isActive    Boolean   @default(true) @map("is_active")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  // 관계
  posts       Post[]
  profile     Profile?

  @@map("users")
  @@index([email])
  @@index([username])
}

model Profile {
  id        Int     @id @default(autoincrement())
  userId    Int     @unique @map("user_id")
  phone     String?
  address   String?
  avatarUrl String? @map("avatar_url")
  bio       String?

  user      User    @relation(fields: [userId], references: [id])

  @@map("user_profiles")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int      @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")

  author    User     @relation(fields: [authorId], references: [id])

  @@map("posts")
  @@index([authorId])
}
*/


// ==============================================
// 2. 마이그레이션 명령어
// ==============================================

// 마이그레이션 생성 및 적용
// npx prisma migrate dev --name init

// 프로덕션에 마이그레이션 적용
// npx prisma migrate deploy

// 마이그레이션 상태 확인
// npx prisma migrate status

// 스키마 리셋 (개발 환경만!)
// npx prisma migrate reset


// ==============================================
// 3. 생성된 마이그레이션 파일 예시
// ==============================================

// prisma/migrations/20240115143022_init/migration.sql
/*
-- CreateTable
CREATE TABLE "users" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "password_hash" TEXT NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "user_profiles" (
    "id" SERIAL NOT NULL,
    "user_id" INTEGER NOT NULL,
    "phone" TEXT,
    "address" TEXT,
    "avatar_url" TEXT,
    "bio" TEXT,

    CONSTRAINT "user_profiles_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
CREATE INDEX "users_email_idx" ON "users"("email");
CREATE INDEX "users_username_idx" ON "users"("username");

-- CreateIndex
CREATE UNIQUE INDEX "user_profiles_user_id_key" ON "user_profiles"("user_id");

-- AddForeignKey
ALTER TABLE "user_profiles" ADD CONSTRAINT "user_profiles_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
*/


// ==============================================
// 4. 프로그래밍 방식 마이그레이션 (Knex.js)
// ==============================================

// knexfile.js
module.exports = {
    development: {
        client: 'postgresql',
        connection: process.env.DATABASE_URL,
        migrations: {
            directory: './migrations',
            tableName: 'knex_migrations'
        }
    }
};

// migrations/20240115143022_create_users.js
exports.up = function(knex) {
    return knex.schema
        .createTable('users', (table) => {
            table.increments('id').primary();
            table.string('email', 255).notNullable().unique();
            table.string('username', 100).notNullable();
            table.string('password_hash', 255).notNullable();
            table.boolean('is_active').defaultTo(true);
            table.timestamps(true, true);  // created_at, updated_at

            table.index('email');
            table.index('username');
        })
        .createTable('user_profiles', (table) => {
            table.increments('id').primary();
            table.integer('user_id').unsigned().unique()
                .references('id').inTable('users');
            table.string('phone', 20);
            table.text('address');
        });
};

exports.down = function(knex) {
    return knex.schema
        .dropTableIfExists('user_profiles')
        .dropTableIfExists('users');
};

// 실행: npx knex migrate:latest
// 롤백: npx knex migrate:rollback

-- ============================================
-- Flyway 마이그레이션 예제 (Raw SQL)
-- ============================================

-- 파일명: V1__create_users_table.sql
-- V{버전}__{설명}.sql 형식 (언더스코어 2개)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ix_users_email ON users(email);
CREATE INDEX ix_users_username ON users(username);

-- 트리거: updated_at 자동 갱신
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();


-- ============================================
-- 파일명: V2__add_user_profiles.sql
-- ============================================

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    phone VARCHAR(20),
    address TEXT,
    avatar_url VARCHAR(500),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- users 테이블에 컬럼 추가
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
ALTER TABLE users ADD COLUMN login_count INTEGER DEFAULT 0;


-- ============================================
-- 파일명: V3__create_posts_table.sql
-- ============================================

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    slug VARCHAR(255) UNIQUE,
    published BOOLEAN DEFAULT FALSE,
    author_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP
);

CREATE INDEX ix_posts_author ON posts(author_id);
CREATE INDEX ix_posts_published ON posts(published) WHERE published = TRUE;
CREATE INDEX ix_posts_slug ON posts(slug);


-- ============================================
-- 무중단 마이그레이션 패턴 (대용량 테이블)
-- ============================================

-- V4__add_status_column_safe.sql
-- 1단계: nullable 컬럼 추가 (락 최소화)
ALTER TABLE posts ADD COLUMN status VARCHAR(20);

-- V5__backfill_status_column.sql
-- 2단계: 기존 데이터 백필 (배치로 처리)
UPDATE posts SET status = 'published' WHERE published = TRUE AND status IS NULL;
UPDATE posts SET status = 'draft' WHERE published = FALSE AND status IS NULL;

-- V6__add_status_constraints.sql
-- 3단계: NOT NULL 및 제약 조건 추가
ALTER TABLE posts ALTER COLUMN status SET DEFAULT 'draft';
ALTER TABLE posts ALTER COLUMN status SET NOT NULL;
ALTER TABLE posts ADD CONSTRAINT chk_posts_status
    CHECK (status IN ('draft', 'published', 'archived'));


-- ============================================
-- Flyway 명령어
-- ============================================

-- 마이그레이션 적용
-- flyway -url=jdbc:postgresql://localhost/mydb -user=postgres migrate

-- 현재 상태 확인
-- flyway info

-- 마이그레이션 검증
-- flyway validate

-- 히스토리 정리 (주의!)
-- flyway repair