Prisma ORM: Database Modern untuk JavaScript

# Buat folder proyek baru
mkdir belajar-prisma && cd belajar-prisma

# Inisialisasi package.json
npm init -y

# Instal Prisma dan Prisma Client
npm install prisma --save-dev
npm install @prisma/client

# Inisialisasi Prisma (membuat folder prisma/ dengan schema.prisma)
npx prisma init

# Output:
# ✔ Your Prisma schema was created at prisma/schema.prisma
#   You can now open it in your favorite editor.

// prisma/schema.prisma

// Generator: menghasilkan Prisma Client
generator client {
  provider = "prisma-client-js"
}

// Datasource: konfigurasi database
datasource db {
  provider = "postgresql"    // Bisa: "mysql", "sqlite", "sqlserver", "mongodb"
  url      = env("DATABASE_URL")
}

# .env — letakkan di root proyek

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/belajar_prisma?schema=public"

# MySQL
# DATABASE_URL="mysql://user:password@localhost:3306/belajar_prisma"

# SQLite (untuk development)
# DATABASE_URL="file:./dev.db"

// prisma/schema.prisma

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

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

// Model User
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  nama      String
  umur      Int?
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]   // Relasi one-to-many dengan Post
}

// Model Post
model Post {
  id        Int      @id @default(autoincrement())
  judul     String
  konten    String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  tags      Tag[]    @relation("PostTags")
}

// Model Tag (many-to-many)
model Tag {
  id    Int    @id @default(autoincrement())
  nama  String @unique
  posts Post[] @relation("PostTags")
}

# Buat dan jalankan migrasi pertama
npx prisma migrate dev --name init

# Output:
# Prisma schema loaded from prisma/schema.prisma
# Datasource "db": PostgreSQL database "belajar_prisma"
#
# Applying migration `20260625120000_init`
#
# The following migration(s) have been created and applied:
#   migrations/
#     └─ 20260625120000_init/
#        └─ migration.sql
#
# Your database is now in sync with your schema.
#
# ✔ Generated Prisma Client (v5.x.x)

# Migrasi setelah perubahan schema
npx prisma migrate dev --name tambah-model-tag

# Reset database (hapus semua data dan migrasi ulang)
npx prisma migrate reset

# Deploy migrasi di production
npx prisma migrate deploy

-- migrations/20260625120000_init/migration.sql

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "nama" TEXT NOT NULL,
    "umur" INTEGER,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "judul" TEXT NOT NULL,
    "konten" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "authorId" INTEGER NOT NULL,

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

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey"
    FOREIGN KEY ("authorId") REFERENCES "User"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

// Singleton pattern untuk mencegah banyak koneksi di development
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

import { prisma } from './lib/prisma';

// Membuat satu user
const user = await prisma.user.create({
  data: {
    email: 'budi@example.com',
    nama: 'Budi Santoso',
    umur: 25,
  },
});
console.log('User dibuat:', user);

// Membuat user beserta post sekaligus (nested create)
const userDenganPost = await prisma.user.create({
  data: {
    email: 'sari@example.com',
    nama: 'Sari Dewi',
    umur: 28,
    posts: {
      create: [
        {
          judul: 'Belajar Prisma ORM',
          konten: 'Prisma adalah ORM modern yang sangat powerful...',
          published: true,
        },
        {
          judul: 'Tips TypeScript',
          konten: 'TypeScript membantu menulis kode yang lebih aman...',
          published: false,
        },
      ],
    },
  },
  include: {
    posts: true, // Sertakan relasi posts di response
  },
});
console.log('User dengan posts:', userDenganPost);

// Membuat banyak data sekaligus (bulk create)
const count = await prisma.user.createMany({
  data: [
    { email: 'andi@example.com', nama: 'Andi Wijaya', umur: 30 },
    { email: 'maya@example.com', nama: 'Maya Putri', umur: 22 },
    { email: 'rizki@example.com', nama: 'Rizki Pratama', umur: 27 },
  ],
  skipDuplicates: true, // Lewati jika email sudah ada
});
console.log(`${count.count} user berhasil dibuat`);

// Mengambil semua user
const semuaUser = await prisma.user.findMany();
console.log('Semua user:', semuaUser);

// Mengambil satu user berdasarkan ID
const user = await prisma.user.findUnique({
  where: { id: 1 },
});
console.log('User ID 1:', user);

// Mengambil user berdasarkan email (unique field)
const userByEmail = await prisma.user.findUnique({
  where: { email: 'budi@example.com' },
});
console.log('User by email:', userByEmail);

// Mengambil dengan relasi (include)
const userDenganPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
    },
  },
});

// Mengambil hanya field tertentu (select)
const userSelect = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    nama: true,
    email: true,
    _count: {
      select: { posts: true },
    },
  },
});
console.log('Jumlah post:', userSelect?._count.posts);

// Pagination dengan take dan skip
const halaman2 = await prisma.user.findMany({
  skip: 10,    // Lewati 10 data pertama
  take: 10,    // Ambil 10 data berikutnya
  orderBy: { createdAt: 'desc' },
});

// Update satu user
const updateUser = await prisma.user.update({
  where: { id: 1 },
  data: {
    nama: 'Budi Santoso Updated',
    umur: 26,
  },
});

// UpdateMany — update beberapa data sekaligus
const updateManyResult = await prisma.user.updateMany({
  where: { isActive: false },
  data: { isActive: true },
});
console.log(`${updateManyResult.count} user diaktifkan`);

// Upsert — update jika ada, buat jika tidak ada
const upsertUser = await prisma.user.upsert({
  where: { email: 'baru@example.com' },
  update: { nama: 'Nama Diperbarui' },
  create: {
    email: 'baru@example.com',
    nama: 'User Baru',
    umur: 20,
  },
});

// Delete satu user
const hapusUser = await prisma.user.delete({
  where: { id: 5 },
});

// DeleteMany — hapus beberapa data
const hapusBanyak = await prisma.user.deleteMany({
  where: { isActive: false },
});
console.log(`${hapusBanyak.count} user dihapus`);

// Schema
model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique
}

// Query dengan relasi one-to-one
const userDenganProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true },
});

// Membuat user dengan profile sekaligus
const newUser = await prisma.user.create({
  data: {
    email: 'lisa@example.com',
    profile: {
      create: {
        bio: 'Full-stack developer yang suka kopi',
        avatar: 'https://example.com/lisa.jpg',
      },
    },
  },
  include: { profile: true },
});

// Schema (sudah didefinisikan sebelumnya)
// User memiliki banyak Post, Post milik satu User

// Query user beserta semua post-nya
const author = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});

// Menambah post baru ke user yang sudah ada
await prisma.post.create({
  data: {
    judul: 'Post Baru dari Prisma',
    konten: 'Ini adalah konten post...',
    author: {
      connect: { id: 1 }, // Hubungkan ke user ID 1
    },
  },
});

// Atau menggunakan authorId langsung
await prisma.post.create({
  data: {
    judul: 'Post Lainnya',
    konten: 'Konten lain...',
    authorId: 1,
  },
});

// Prisma otomatis membuat tabel junction untuk many-to-many eksplisit

// Query post beserta tags-nya
const postDenganTags = await prisma.post.findUnique({
  where: { id: 1 },
  include: { tags: true },
});

// Menghubungkan post dengan tag yang sudah ada
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      connect: [
        { id: 1 }, // tag "JavaScript"
        { id: 2 }, // tag "TypeScript"
      ],
    },
  },
});

// Membuat tag baru dan hubungkan sekaligus
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      create: [{ nama: 'Database' }, { nama: 'ORM' }],
    },
  },
});

// Query tag beserta semua post yang memiliki tag tersebut
const tagDenganPosts = await prisma.tag.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      include: { author: { select: { nama: true } } },
    },
  },
});

// Filter dasar
const activeUsers = await prisma.user.findMany({
  where: { isActive: true },
});

// Filter dengan operator perbandingan
const dewasa = await prisma.user.findMany({
  where: {
    umur: {
      gte: 18,    // Greater than or equal
      lte: 60,    // Less than or equal
    },
  },
});

// Filter dengan string operator
const cariUser = await prisma.user.findMany({
  where: {
    nama: {
      contains: 'Budi',     // Mengandung kata "Budi"
      mode: 'insensitive',  // Tidak case-sensitive
    },
  },
});

// Filter dengan operator IN
const userTertentu = await prisma.user.findMany({
  where: {
    id: { in: [1, 3, 5, 7] },
  },
});

// Filter dengan NOT
const nonActive = await prisma.user.findMany({
  where: {
    NOT: { isActive: true },
  },
});

// Filter kombinasi AND / OR
const complexFilter = await prisma.user.findMany({
  where: {
    AND: [
      { isActive: true },
      {
        OR: [
          { umur: { gte: 25 } },
          { nama: { startsWith: 'Admin' } },
        ],
      },
    ],
  },
});

// Filter relasi
const postsDariUserAktif = await prisma.post.findMany({
  where: {
    author: {
      isActive: true,
    },
    published: true,
  },
  include: {
    author: {
      select: { nama: true, email: true },
    },
  },
});

// Sorting ascending
const ascUsers = await prisma.user.findMany({
  orderBy: { nama: 'asc' },
});

// Sorting descending
const descUsers = await prisma.user.findMany({
  orderBy: { createdAt: 'desc' },
});

// Multiple sorting
const sorted = await prisma.user.findMany({
  orderBy: [
    { umur: 'asc' },
    { nama: 'desc' },
  ],
});

// Cursor-based pagination (lebih efisien untuk data besar)
const cursorPagination = await prisma.user.findMany({
  take: 10,
  skip: 1, // Skip cursor
  cursor: {
    id: 50, // Mulai dari ID 50
  },
  orderBy: { id: 'asc' },
});

// Aggregation — count, avg, sum, min, max
const stats = await prisma.post.aggregate({
  _count: true,
  _avg: { authorId: true },
  where: { published: true },
});

// GroupBy
const groupByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: true,
  where: { published: true },
  having: {
    authorId: { gt: 0 },
  },
});

// Batch transaction — semua operasi dijalankan bersamaan
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: {
      email: 'transaksi@example.com',
      nama: 'User Transaksi',
    },
  }),
  prisma.post.create({
    data: {
      judul: 'Post dari Transaksi',
      konten: 'Dibuat dalam transaksi yang sama',
      authorId: 1,
    },
  }),
]);
console.log('User:', user.nama);
console.log('Post:', post.judul);

// Interactive transaction — bisa menjalankan logic di tengah transaksi
async function transferPost(authorFromId: number, authorToId: number, postId: number) {
  const result = await prisma.$transaction(async (tx) => {
    // 1. Cek apakah post milik authorFrom
    const post = await tx.post.findUnique({
      where: { id: postId },
    });

    if (!post || post.authorId !== authorFromId) {
      throw new Error('Post tidak ditemukan atau bukan milik author ini');
    }

    // 2. Update author post
    const updatedPost = await tx.post.update({
      where: { id: postId },
      data: { authorId: authorToId },
    });

    // 3. Catat log transfer
    const log = await tx.logTransfer.create({
      data: {
        postId,
        fromAuthorId: authorFromId,
        toAuthorId: authorToId,
      },
    });

    return { updatedPost, log };
  });

  return result;
}

// Contoh penggunaan
try {
  const result = await transferPost(1, 2, 10);
  console.log('Transfer berhasil:', result);
} catch (error) {
  console.error('Transfer gagal:', error.message);
}

// Query raw SQL
const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE "isActive" = true ORDER BY "createdAt" DESC
`;

// Raw SQL dengan parameter (mencegah SQL injection)
const userId = 1;
const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE "id" = ${userId}
`;

// Execute raw SQL (untuk INSERT, UPDATE, DELETE)
await prisma.$executeRaw`
  UPDATE "User" SET "isActive" = false WHERE "umur" < 18
`;