📚 Panduan Prisma ORM

📋 Rangkuman Method Prisma
▼
💡 Cara Membaca Tabel:
  • Method - Nama method Prisma
  • Fungsi - Deskripsi kegunaan method
  • Return - Tipe data yang dikembalikan
  • SQL Equivalent - Perbandingan dengan SQL tradisional

✏️ CREATE Methods

Method Fungsi Return SQL Equivalent
create() Buat 1 record baru Object (record yang dibuat)
INSERT INTO table (col1, col2)
VALUES (val1, val2)
RETURNING *;
createMany() Buat banyak records sekaligus { count: number }
INSERT INTO table (col1, col2)
VALUES (val1, val2), (val3, val4);
createManyAndReturn() Buat banyak records dan kembalikan data Array of objects
INSERT INTO table (col1, col2)
VALUES (val1, val2), (val3, val4)
RETURNING *;
upsert() Update jika ada, create jika tidak ada Object
-- PostgreSQL
INSERT INTO table (id, col1)
VALUES (1, 'val')
ON CONFLICT (id)
DO UPDATE SET col1 = 'val';

📖 READ Methods

Method Fungsi Return SQL Equivalent
findUnique() Cari record by unique field (@id, @unique) Object atau null
SELECT * FROM table
WHERE unique_field = value
LIMIT 1;
findUniqueOrThrow() Cari record unique, throw error jika tidak ketemu Object
-- Manual error handling
SELECT * FROM table
WHERE unique_field = value;
findFirst() Cari record pertama yang memenuhi kondisi Object atau null
SELECT * FROM table
WHERE conditions
LIMIT 1;
findFirstOrThrow() Cari record pertama, throw error jika tidak ketemu Object
-- Manual error handling
SELECT * FROM table
WHERE conditions
LIMIT 1;
findMany() Cari banyak records dengan filter Array of objects
SELECT * FROM table
WHERE conditions
ORDER BY column
LIMIT count OFFSET skip;
aggregate() Agregasi data (count, avg, sum, min, max) { _count, _avg, _sum, _min, _max }
SELECT
COUNT(*), AVG(column),
SUM(column), MIN(column),
MAX(column) FROM table;
groupBy() Group data dengan agregasi Array of grouped objects
SELECT column, COUNT(*), AVG(column)
FROM table
GROUP BY column
HAVING condition;
count() Hitung jumlah records Number
SELECT COUNT(*) FROM table
WHERE conditions;

🔄 UPDATE Methods

Method Fungsi Return SQL Equivalent
update() Update 1 record Object (record yang diupdate)
UPDATE table
SET col1 = val1, col2 = val2
WHERE id = 1
RETURNING *;
updateMany() Update banyak records { count: number }
UPDATE table
SET col1 = val1
WHERE conditions;
upsert() Update atau create record Object
-- PostgreSQL
INSERT INTO table (id, col1)
VALUES (1, 'val')
ON CONFLICT (id)
DO UPDATE SET col1 = 'val'
RETURNING *;

🗑️ DELETE Methods

Method Fungsi Return SQL Equivalent
delete() Hapus 1 record Object (record yang dihapus)
DELETE FROM table
WHERE id = 1
RETURNING *;
deleteMany() Hapus banyak records { count: number }
DELETE FROM table
WHERE conditions;

📊 AGGREGATION Methods

Method Fungsi Return SQL Equivalent
aggregate() Operasi agregasi dasar { _count, _avg, _sum, _min, _max }
SELECT
COUNT(*), AVG(col),
SUM(col), MIN(col), MAX(col)
FROM table;
groupBy() Group data dengan agregasi Array of grouped objects
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category
HAVING AVG(price) > 100;
_count Hitung jumlah relasi Number
SELECT u.*,
(SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) as post_count
FROM users u;

🔍 FILTER Operators

Operator Fungsi Contoh Prisma SQL Equivalent
equals Sama dengan { name: { equals: 'John' } }
WHERE name = 'John'
not Tidak sama dengan { name: { not: 'John' } }
WHERE name != 'John'
in Ada dalam array { role: { in: ['ADMIN', 'USER'] } }
WHERE role IN ('ADMIN', 'USER')
notIn Tidak ada dalam array { role: { notIn: ['ADMIN'] } }
WHERE role NOT IN ('ADMIN')
lt Less than (kurang dari) { age: { lt: 18 } }
WHERE age < 18
lte Less than or equal { age: { lte: 18 } }
WHERE age <= 18
gt Greater than (lebih dari) { age: { gt: 18 } }
WHERE age > 18
gte Greater than or equal { age: { gte: 18 } }
WHERE age >= 18
contains Mengandung string { name: { contains: 'John' } }
WHERE name LIKE '%John%'
startsWith Dimulai dengan string { name: { startsWith: 'Jo' } }
WHERE name LIKE 'Jo%'
endsWith Diakhiri dengan string { name: { endsWith: 'hn' } }
WHERE name LIKE '%hn'
AND Logical AND { AND: [{ age: { gt: 18 } }, { role: 'USER' }] }
WHERE age > 18 AND role = 'USER'
OR Logical OR { OR: [{ role: 'ADMIN' }, { role: 'MODERATOR' }] }
WHERE role = 'ADMIN' OR role = 'MODERATOR'
NOT Logical NOT { NOT: { role: 'ADMIN' } }
WHERE NOT role = 'ADMIN'

🔗 RELATION Filters

Filter Fungsi Contoh Prisma SQL Equivalent
some Minimal satu relasi memenuhi kondisi { posts: { some: { published: true } } }
WHERE EXISTS (
  SELECT 1 FROM posts p
  WHERE p.user_id = users.id
  AND p.published = true
)
every Semua relasi memenuhi kondisi { posts: { every: { published: true } } }
WHERE NOT EXISTS (
  SELECT 1 FROM posts p
  WHERE p.user_id = users.id
  AND p.published = false
)
none Tidak ada relasi yang memenuhi kondisi { posts: { none: { published: false } } }
WHERE NOT EXISTS (
  SELECT 1 FROM posts p
  WHERE p.user_id = users.id
  AND p.published = false
)
is Relasi one-to-one memenuhi kondisi { profile: { is: { bio: { contains: 'developer' } } } }
SELECT u.* FROM users u
INNER JOIN profiles p ON p.user_id = u.id
WHERE p.bio LIKE '%developer%'
isNot Relasi one-to-one tidak memenuhi kondisi { profile: { isNot: { bio: { contains: 'developer' } } } }
SELECT u.* FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
WHERE p.bio NOT LIKE '%developer%'
OR p.bio IS NULL

💰 TRANSACTION Methods

Method Fungsi Return SQL Equivalent
$transaction() Batch transaction (array of operations) Array of results
BEGIN;
INSERT INTO ...;
UPDATE ...;
COMMIT;
$transaction() Interactive transaction (callback) Callback return value
BEGIN;
-- multiple operations
COMMIT;

⚡ RAW QUERY Methods

Method Fungsi Return SQL Equivalent
$queryRaw() Execute raw SELECT query Array of objects
SELECT * FROM table
WHERE custom_condition;
$executeRaw() Execute raw UPDATE/DELETE query { count: number }
UPDATE table SET col = val
WHERE custom_condition;
$queryRawUnsafe() Execute raw query (unsafe) Array of objects
-- Dynamic SQL query

🔧 CLIENT Methods

Method Fungsi Return Keterangan
$connect() Connect ke database Promise<void> Biasanya otomatis
$disconnect() Disconnect dari database Promise<void> Penting untuk cleanup
$on() Event listener untuk query, error, info void Untuk logging dan debugging
$use() Middleware untuk Prisma Client void Untuk modifikasi query/result
⚠️ Tips Penggunaan Method:
  • Gunakan findUniqueOrThrow() untuk menghindari null check manual
  • createMany() lebih efisien untuk bulk insert
  • Gunakan transaction untuk operasi yang harus atomic
  • Hindari $queryRawUnsafe() jika mungkin untuk mencegah SQL injection
  • Selalu $disconnect() di akhir aplikasi
📦 Instalasi & Setup
▼

1. Instalasi

npm install -D prisma
npm install @prisma/client

npx prisma init
💡 Yang Terjadi:
  • Folder prisma/ dibuat
  • File .env dibuat
  • File schema.prisma dibuat
  • Siap untuk setup database

2. Setup Database Connection

// .env
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

// atau MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

// atau SQLite
DATABASE_URL="file:./dev.db"

3. Generate Prisma Client

npx prisma generate

// Install otomatis di:
// node_modules/.prisma/client

4. Inisialisasi Prisma Client

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

const globalForPrisma = global as unknown as { prisma: PrismaClient }

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query', 'error', 'warn'],
  })

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

5. Gunakan di Aplikasi

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

async function main() {
  const users = await prisma.user.findMany()
  console.log(users)
}

main()
  .catch(console.error)
  .finally(async () => {
    await prisma.$disconnect()
  })
📐 Anatomy Model Prisma
▼

Struktur Dasar Model

model NamaModel {
  // 👉 Field name | Type | Modifiers | Attributes
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?   // ❓ = nullable/opsional
  posts     Post[]    // 🔗 = relasi one-to-many
  createdAt DateTime  @default(now())
  
  // Index & constraints
  @@unique([field1, field2])
  @@index([field])
}

Penjelasan Sintaks

Sintaks Penjelasan Contoh
model Keyword untuk definisi model model User { }
fieldName Type Nama field dan tipe data email String
? Field optional/nullable name String?
[] Array/List (untuk relasi) posts Post[]
@ Field-level attribute @id, @unique
@@ Model-level attribute @@index

Contoh Schema

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  password  String
  role      Role      @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  
  @@index([email])
  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?  @db.Text
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  tags      Tag[]
  createdAt DateTime @default(now())
  
  @@index([authorId])
}

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
💡 Tips Schema:
  • @map("name") - rename field di database
  • @@map("name") - rename tabel di database
  • @updatedAt - auto-update saat record berubah
  • @db.Text - native database type
🔤 Tipe Data Field
▼

Scalar Types

Prisma Database TypeScript Contoh
String VARCHAR, TEXT string name String
Int INTEGER number age Int
BigInt BIGINT bigint views BigInt
Float DOUBLE number price Float
Decimal DECIMAL Decimal amount Decimal
Boolean BOOLEAN boolean active Boolean
DateTime TIMESTAMP Date date DateTime
Json JSON any meta Json
Bytes BYTEA Buffer data Bytes

Native Database Types

// PostgreSQL
name     String   @db.VarChar(255)
content  String   @db.Text
price    Decimal  @db.Money
data     Json     @db.JsonB

// MySQL
name     String   @db.VarChar(255)
content  String   @db.LongText
number   Int      @db.UnsignedInt
⚠️ Perhatian:
  • Decimal lebih presisi untuk uang
  • Json tidak semua database support
  • @db.* berbeda per database
⚡ Attributes & Modifiers
▼

Field Attributes (@)

Attribute Fungsi Contoh
@id Primary key id Int @id
@unique Nilai harus unik email String @unique
@default() Nilai default role String @default("USER")
@updatedAt Auto-update timestamp updatedAt DateTime @updatedAt
@relation() Definisi relasi @relation(fields: [id])
@map() Rename field di DB @map("user_id")

Model Attributes (@@)

Attribute Fungsi Contoh
@@id() Composite primary key @@id([field1, field2])
@@unique() Composite unique @@unique([email, phone])
@@index() Database index @@index([email])
@@map() Rename tabel @@map("users")
💡 Best Practices:
  • Gunakan @id di setiap model
  • Tambahkan @@index untuk field sering di-query
  • Gunakan @updatedAt untuk tracking
✏️ Create - Membuat Data
▼

1. create() - Single Record

PRISMA
const user = await prisma.user.create({
  data: {
    email: 'john@mail.com',
    name: 'John Doe'
  }
});
SQL
INSERT INTO "User" 
(email, name)
VALUES 
('john@mail.com', 'John Doe')
RETURNING *;

2. createMany() - Multiple Records

const result = await prisma.user.createMany({
  data: [
    { email: 'user1@mail.com', name: 'User 1' },
    { email: 'user2@mail.com', name: 'User 2' }
  ],
  skipDuplicates: true
});

console.log(result.count); // 2

3. Create dengan Relasi

const user = await prisma.user.create({
  data: {
    email: 'john@mail.com',
    name: 'John',
    posts: {
      create: [
        { title: 'Post 1' },
        { title: 'Post 2' }
      ]
    }
  },
  include: { posts: true }
});

4. upsert() - Update atau Create

const user = await prisma.user.upsert({
  where: { email: 'john@mail.com' },
  update: { name: 'Updated' },
  create: { 
    email: 'john@mail.com',
    name: 'New' 
  }
});
📖 Read - Membaca Data
▼

1. findUnique() - By Unique Field

const user = await prisma.user.findUnique({
  where: { id: 1 }
});

const user2 = await prisma.user.findUnique({
  where: { email: 'john@mail.com' }
});

2. findMany() - Multiple Records

PRISMA
const users = await prisma.user.findMany({
  where: { age: { gte: 18 } },
  orderBy: { createdAt: 'desc' },
  take: 10
});
SQL
SELECT * FROM "User"
WHERE age >= 18
ORDER BY "createdAt" DESC
LIMIT 10;

3. Filter Operators

// Comparison
{ age: { gt: 18 } }           // greater than
{ age: { gte: 18 } }          // greater or equal
{ age: { lt: 65 } }           // less than
{ age: { lte: 65 } }          // less or equal

// String
{ name: { contains: 'John' } }      // LIKE '%John%'
{ name: { startsWith: 'J' } }       // LIKE 'J%'
{ name: { endsWith: 'Doe' } }       // LIKE '%Doe'

// List
{ age: { in: [18, 25, 30] } }       // IN
{ age: { notIn: [10, 15] } }        // NOT IN

4. Logical Operators

// OR
const users = await prisma.user.findMany({
  where: {
    OR: [
      { email: { contains: 'gmail' } },
      { email: { contains: 'yahoo' } }
    ]
  }
});

// AND + OR + NOT
const users = await prisma.user.findMany({
  where: {
    AND: [
      { age: { gte: 18 } },
      {
        OR: [
          { role: 'ADMIN' },
          { role: 'MODERATOR' }
        ]
      },
      {
        NOT: {
          email: { endsWith: 'banned.com' }
        }
      }
    ]
  }
});

5. Select & Include

// Select: pilih field tertentu
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true
  }
});

// Include: ambil relasi
const users = await prisma.user.findMany({
  include: {
    posts: true,
    profile: true
  }
});

6. Pagination

// Offset pagination
const page = 2;
const pageSize = 10;

const users = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { id: 'asc' }
});

// Cursor-based
const users = await prisma.user.findMany({
  take: 10,
  skip: 1,
  cursor: { id: 20 },
  orderBy: { id: 'asc' }
});
🔄 Update - Mengubah Data
▼

1. update() - Single Record

PRISMA
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Updated' }
});
SQL
UPDATE "User"
SET name = 'Updated'
WHERE id = 1
RETURNING *;

2. updateMany() - Multiple Records

const result = await prisma.user.updateMany({
  where: { age: { lt: 18 } },
  data: { role: 'MINOR' }
});

console.log(result.count); // Jumlah updated

3. Atomic Updates

// Increment/Decrement
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    views: { increment: 1 },
    likes: { decrement: 1 }
  }
});

// Multiply/Divide
data: {
  price: { multiply: 1.1 },
  discount: { divide: 2 }
}
🗑️ Delete - Menghapus Data
▼

1. delete() - Single Record

PRISMA
const user = await prisma.user.delete({
  where: { id: 1 }
});
SQL
DELETE FROM "User"
WHERE id = 1
RETURNING *;

2. deleteMany() - Multiple Records

const result = await prisma.user.deleteMany({
  where: { 
    createdAt: { 
      lt: new Date('2020-01-01') 
    }
  }
});

console.log(result.count);

3. Cascade Delete

// Di schema:
model Post {
  id       Int  @id
  authorId Int
  author   User @relation(
    fields: [authorId], 
    references: [id], 
    onDelete: Cascade
  )
}

// Delete user auto delete posts
const user = await prisma.user.delete({
  where: { id: 1 }
});

4. Soft Delete Pattern

model User {
  id        Int       @id
  deletedAt DateTime?
}

// Soft delete
const user = await prisma.user.update({
  where: { id: 1 },
  data: { deletedAt: new Date() }
});

// Query active only
const users = await prisma.user.findMany({
  where: { deletedAt: null }
});
🚨 Peringatan:
  • deleteMany({}) hapus SEMUA data!
  • Tidak bisa di-undo
  • Backup data penting
🔍 Konsep Dasar Relasi Database
▼

Apa itu Relasi Database?

Relasi adalah hubungan antara tabel-tabel dalam database. Prisma mendukung 3 jenis relasi utama:

👤

One-to-One (1:1)

Satu record di tabel A berhubungan dengan tepat satu record di tabel B, dan sebaliknya.

Contoh: User ↔ Profile (satu user punya satu profile, satu profile milik satu user)

👥

One-to-Many (1:N)

Satu record di tabel A bisa berhubungan dengan banyak record di tabel B, tapi satu record di tabel B hanya berhubungan dengan satu record di tabel A.

Contoh: User ↔ Posts (satu user bisa punya banyak post, tapi satu post hanya punya satu author)

🔄

Many-to-Many (N:M)

Satu record di tabel A bisa berhubungan dengan banyak record di tabel B, dan sebaliknya.

Contoh: Posts ↔ Tags (satu post bisa punya banyak tag, satu tag bisa dipakai banyak post)

💡 Kunci Penting:
  • @relation - mendefinisikan hubungan antara model
  • fields - field yang menyimpan kunci asing
  • references - field yang direferensikan di model lain
  • Gunakan ? untuk relasi opsional
  • Gunakan [] untuk relasi one-to-many atau many-to-many
👤 Relasi One-to-One (1:1)
▼
One-to-One

Konsep One-to-One

Setiap record di tabel A memiliki tepat satu hubungan dengan record di tabel B, dan sebaliknya.

📊 Diagram Relasi

User ──── (1:1) ──── Profile

Satu user memiliki satu profile, satu profile dimiliki oleh satu user

Schema One-to-One

model User {
  id      Int     @id @default(autoincrement())
  email   String  @unique
  name    String?
  // 👇 Relasi one-to-one dengan Profile
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  userId Int    @unique  // 👈 Foreign key yang UNIQUE
  user   User   @relation(fields: [userId], references: [id])
}

Penjelasan Schema

Bagian Penjelasan
profile Profile? Di model User: relasi opsional ke Profile (bisa null)
userId Int @unique Di model Profile: foreign key yang HARUS unique
@relation(fields: [userId], references: [id]) Menghubungkan userId dengan id di model User

Query One-to-One

Create dengan Relasi

// Buat user dan profile sekaligus
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    profile: {
      create: {
        bio: 'Software developer from Indonesia'
      }
    }
  },
  include: {
    profile: true  // 👈 Include profile dalam response
  }
});

Read dengan Relasi

// Cari user dengan profile
const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true }
});

// Cari profile dengan user
const profileWithUser = await prisma.profile.findUnique({
  where: { id: 1 },
  include: { user: true }
});

Update dengan Relasi

// Update profile melalui user
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      update: {
        bio: 'Updated bio information'
      }
    }
  },
  include: { profile: true }
});
⚠️ Perhatian One-to-One:
  • Field foreign key HARUS memiliki @unique
  • Tanpa @unique, relasi akan menjadi one-to-many
  • Relasi bisa opsional (menggunakan ?)
👥 Relasi One-to-Many (1:N)
▼
One-to-Many

Konsep One-to-Many

Satu record di tabel A bisa berhubungan dengan banyak record di tabel B, tapi setiap record di tabel B hanya berhubungan dengan satu record di tabel A.

📊 Diagram Relasi

User ──── (1:N) ──── Post

Satu user bisa memiliki banyak post, setiap post hanya memiliki satu author

Schema One-to-Many

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String?
  // 👇 Relasi one-to-many dengan Post
  posts Post[]  // ❗ TANDA [] menandakan many
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  content  String?
  // 👇 Foreign key ke User
  authorId Int
  // 👇 Relasi many-to-one dengan User
  author   User   @relation(fields: [authorId], references: [id])
}

Penjelasan Schema

Bagian Penjelasan
posts Post[] Di model User: array of Post (banyak post)
authorId Int Di model Post: foreign key ke User (TANPA @unique)
author User @relation(...) Relasi many-to-one ke User

Query One-to-Many

Create dengan Relasi

// Buat user dengan beberapa post sekaligus
const user = await prisma.user.create({
  data: {
    email: 'author@example.com',
    name: 'Jane Author',
    posts: {
      create: [
        { title: 'My First Post', content: 'Hello world!' },
        { title: 'Second Post', content: 'Another great post' }
      ]
    }
  },
  include: {
    posts: true  // 👈 Include semua post
  }
});

Read dengan Filter Relasi

// Cari user yang memiliki post tertentu
const usersWithPosts = await prisma.user.findMany({
  where: {
    posts: {
      some: {  // 👈 Filter: minimal satu post memenuhi kondisi
        title: { contains: 'Tutorial' }
      }
    }
  },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5  // 👈 Ambil 5 post terbaru saja
    }
  }
});

Count Relasi

// Hitung jumlah post per user
const usersWithPostCount = await prisma.user.findMany({
  include: {
    _count: {
      select: {
        posts: true,     // 👈 Jumlah post
        comments: true   // 👈 Jumlah comment (jika ada relasi lain)
      }
    }
  }
});

Update dengan Relasi Bersarang

// Update user dan post bersamaan
const updated = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Updated Name',
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true }
      }
    }
  },
  include: { posts: true }
});
💡 Tips One-to-Many:
  • Gunakan some untuk filter "ada yang memenuhi"
  • Gunakan every untuk filter "semua memenuhi"
  • Gunakan none untuk filter "tidak ada yang memenuhi"
  • _count berguna untuk agregasi
🔄 Relasi Many-to-Many (N:M)
▼
Many-to-Many

Konsep Many-to-Many

Satu record di tabel A bisa berhubungan dengan banyak record di tabel B, dan sebaliknya. Biasanya memerlukan tabel penghubung (junction table).

📊 Diagram Relasi

Post ──── (N:M) ──── Tag

Satu post bisa memiliki banyak tag, satu tag bisa digunakan banyak post

Dua Jenis Many-to-Many

1. Implicit Many-to-Many (Otomatis)

Prisma secara otomatis membuat tabel penghubung tanpa perlu mendefinisikannya secara eksplisit. 

model Post {
  id    Int   @id @default(autoincrement())
  title String
  // 👇 Relasi many-to-many implicit dengan Tag
  tags  Tag[]
}

model Tag {
  id   Int    @id @default(autoincrement())
  name String @unique
  // 👇 Relasi many-to-many implicit dengan Post
  posts Post[]
}

2. Explicit Many-to-Many (Manual)

Kita mendefinisikan tabel penghubung secara manual untuk kontrol lebih detail. 

model Post {
  id         Int             @id @default(autoincrement())
  title      String
  // 👇 Relasi melalui tabel penghubung
  categories PostCategory[]
}

model Category {
  id    Int             @id @default(autoincrement())
  name  String
  // 👇 Relasi melalui tabel penghubung
  posts PostCategory[]
}

// 👇 Tabel penghubung (junction table)
model PostCategory {
  postId     Int
  categoryId Int
  // 👇 Relasi ke Post dan Category
  post       Post     @relation(fields: [postId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])
  
  // 👇 Composite primary key
  @@id([postId, categoryId])
  
  // Bisa tambahkan field tambahan
  assignedAt DateTime @default(now())
  assignedBy String
}

Perbandingan Implicit vs Explicit

Implicit Explicit
✅ Lebih sederhana ✅ Lebih fleksibel
✅ Otomatis oleh Prisma ✅ Bisa tambahkan field tambahan
❌ Tidak bisa tambah field di tabel penghubung ✅ Bisa query langsung ke tabel penghubung
✅ Cocok untuk relasi sederhana ✅ Cocok untuk relasi kompleks

Query Many-to-Many

Implicit Many-to-Many

// Buat post dengan tags
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    tags: {
      connectOrCreate: [
        {
          where: { name: 'programming' },
          create: { name: 'programming' }
        },
        {
          where: { name: 'javascript' },
          create: { name: 'javascript' }
        }
      ]
    }
  },
  include: { tags: true }
});

// Cari posts dengan tag tertentu
const programmingPosts = await prisma.post.findMany({
  where: {
    tags: {
      some: { name: 'programming' }
    }
  },
  include: { tags: true }
});

Explicit Many-to-Many

// Buat post dengan categories melalui tabel penghubung
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    categories: {
      create: [
        {
          assignedBy: 'admin',
          category: {
            connectOrCreate: {
              where: { name: 'tech' },
              create: { name: 'tech' }
            }
          }
        }
      ]
    }
  },
  include: {
    categories: {
      include: {
        category: true
      }
    }
  }
});

// Query complex dengan tabel penghubung
const postsWithCategories = await prisma.post.findMany({
  include: {
    categories: {
      include: {
        category: true
      },
      where: {
        assignedBy: 'admin'
      }
    }
  }
});
⚠️ Pilihan Many-to-Many:
  • Gunakan implicit untuk relasi sederhana tanpa data tambahan
  • Gunakan explicit jika perlu menyimpan metadata di relasi
  • Explicit memberi kontrol lebih tapi lebih kompleks
🔄 Self Relations (Relasi dengan Diri Sendiri)
▼

Konsep Self Relations

Model yang berelasi dengan dirinya sendiri. Berguna untuk struktur hierarki seperti:

  • Employee → Manager (yang juga Employee)
  • Category → Parent Category
  • Comment → Reply (yang juga Comment)

Contoh Self Relations

1. One-to-Many Self Relation

model User {
  id          Int     @id @default(autoincrement())
  name        String
  // 👇 Employee punya manager (relasi ke model yang sama)
  managerId   Int?
  manager     User?   @relation("Manager", fields: [managerId], references: [id])
  // 👇 Manager punya banyak subordinates
  subordinates User[] @relation("Manager")
}

2. Many-to-Many Self Relation

model User {
  id     Int    @id @default(autoincrement())
  name   String
  // 👇 User bisa follow banyak user, dan di-follow banyak user
  followedBy   User[] @relation("UserFollows", references: [id])
  following    User[] @relation("UserFollows", references: [id])
}

Query Self Relations

// Cari user dengan manager dan subordinates
const userWithHierarchy = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    manager: true,        // 👈 Siapa managernya
    subordinates: true    // 👈 Siapa saja yang dia manage
  }
});

// Cari semua top-level managers (yang tidak punya manager)
const topManagers = await prisma.user.findMany({
  where: { managerId: null },
  include: {
    subordinates: {
      include: {
        subordinates: true  // 👈 Nested include
      }
    }
  }
});
⚙️ Opsi dan Konfigurasi Relasi
▼

Referential Actions

Mendefinisikan apa yang terjadi ketika record di-referensi dihapus atau di-update. 

model Post {
  id       Int    @id
  authorId Int
  author   User   @relation(
    fields: [authorId], 
    references: [id],
    onDelete: Cascade,    // 👈 Hapus post jika user dihapus
    onUpdate: Cascade     // 👈 Update authorId jika id user berubah
  )
}

model User {
  id    Int    @id
  posts Post[]
}

Opsi Referential Actions

Opsi onDelete onUpdate
Cascade Hapus record terkait Update foreign key
Restrict Block delete jika ada relasi Block update jika ada relasi
NoAction Sama seperti Restrict Sama seperti Restrict
SetNull Set foreign key menjadi NULL -
SetDefault Set foreign key ke nilai default -

Best Practices Relasi

💡 Tips Relasi:
  • Gunakan onDelete: Cascade untuk data dependen yang tidak berguna tanpa parent
  • Gunakan onDelete: SetNull untuk data yang bisa berdiri sendiri
  • Hindari onDelete: Cascade untuk data penting yang perlu diarsip
  • Selalu gunakan include dengan bijak - jangan include semua relasi
  • Gunakan select untuk memilih field spesifik jika tidak butuh semua data

Common Patterns

Soft Delete dengan Relasi

model User {
  id        Int       @id
  email     String
  deletedAt DateTime?
  posts     Post[]
}

model Post {
  id        Int       @id
  title     String
  deletedAt DateTime?
  author    User      @relation(fields: [authorId], references: [id])
  authorId  Int
  
  @@map("posts")
}

// Query hanya data yang tidak di-soft delete
const activePosts = await prisma.post.findMany({
  where: {
    deletedAt: null,
    author: {
      deletedAt: null  // 👈 Hanya post dari author yang aktif
    }
  },
  include: {
    author: {
      select: { id: true, email: true }  // 👈 Hanya ambil field yang diperlukan
    }
  }
});
🚀 Advanced Queries
▼

1. Raw SQL Queries

// Raw query dengan template literal
const result = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE age > ${18} 
  AND email LIKE ${'%@gmail.com'}
`;

// Raw query dengan Prisma.sql
const users = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM User WHERE status = ${'ACTIVE'}`
);

// Execute raw SQL (tanpa return)
await prisma.$executeRaw`
  UPDATE "User" 
  SET status = 'INACTIVE' 
  WHERE lastLogin < ${new Date('2023-01-01')}
`;
⚠️ Security Warning:
  • Selalu gunakan parameterized queries
  • Jangan concatenate string langsung
  • Gunakan Prisma.sql atau template literals

2. Aggregations

// Basic aggregations
const stats = await prisma.user.aggregate({
  _count: { _all: true },
  _avg: { age: true },
  _sum: { score: true },
  _min: { age: true },
  _max: { age: true }
});

// Group by aggregations
const userStats = await prisma.user.groupBy({
  by: ['country', 'role'],
  _count: { _all: true },
  _avg: { age: true },
  having: {
    age: {
      _avg: { gt: 25 }
    }
  }
});

// Multiple aggregations
const postStats = await prisma.post.aggregate({
  _count: {
    id: true,
    authorId: true
  },
  _avg: {
    views: true,
    likes: true
  },
  where: {
    published: true
  }
});

3. Full Text Search

// Di schema.prisma
model Post {
  id      Int    @id
  title   String
  content String
  
  @@fulltext([title, content])
  @@fulltext([content])
}

// Query full text search
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { 
        title: { 
          search: 'prisma OR database',
          mode: 'insensitive'
        }
      },
      {
        content: {
          search: 'tutorial AND guide'
        }
      }
    ]
  }
});

4. JSON Fields

model User {
  id   Int  @id
  meta Json // { preferences: {}, settings: {} }
}

// Query JSON fields
const users = await prisma.user.findMany({
  where: {
    meta: {
      path: ['preferences', 'theme'],
      equals: 'dark'
    }
  }
});

// Update JSON fields
await prisma.user.update({
  where: { id: 1 },
  data: {
    meta: {
      set: {
        preferences: { theme: 'dark' },
        settings: { notifications: true }
      }
    }
  }
});

5. Batch Operations

// Batch findMany
const [users, posts, comments] = await Promise.all([
  prisma.user.findMany({ where: { active: true } }),
  prisma.post.findMany({ where: { published: true } }),
  prisma.comment.findMany({ where: { approved: true } })
]);

// Conditional operations
const operations = [];

if (shouldUpdateUser) {
  operations.push(
    prisma.user.update({
      where: { id: 1 },
      data: { name: 'Updated' }
    })
  );
}

if (shouldCreatePost) {
  operations.push(
    prisma.post.create({
      data: { title: 'New Post' }
    })
  );
}

const results = await prisma.$transaction(operations);
💰 Transactions
▼

1. Interactive Transactions

// Transaction dengan $transaction
const result = await prisma.$transaction(async (tx) => {
  // 1. Kurangi saldo pengirim
  const sender = await tx.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  });

  // Cek saldo cukup
  if (sender.balance < 0) {
    throw new Error('Saldo tidak cukup');
  }

  // 2. Tambah saldo penerima
  const recipient = await tx.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  });

  // 3. Buat record transaksi
  const transaction = await tx.transaction.create({
    data: {
      from: 1,
      to: 2,
      amount: 100,
      type: 'TRANSFER'
    }
  });

  return transaction;
});

2. Batch Transactions

// Multiple operations dalam satu transaction
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: {
      email: 'user@mail.com',
      name: 'New User'
    }
  }),
  prisma.post.create({
    data: {
      title: 'First Post',
      content: 'Hello World'
    }
  })
]);

3. Transaction Options

// Timeout dan isolation level
await prisma.$transaction([
  prisma.user.delete({ where: { id: 1 } }),
  prisma.post.deleteMany({ where: { authorId: 1 } })
], {
  maxWait: 5000,    // maksimal menunggu 5 detik
  timeout: 10000,   // timeout setelah 10 detik
  isolationLevel: 'Serializable' // PostgreSQL only
});

// Isolation levels:
// - ReadUncommitted
// - ReadCommitted  
// - RepeatableRead
// - Serializable

4. Error Handling

try {
  const result = await prisma.$transaction(async (tx) => {
    // Operations here...
    
    if (someCondition) {
      throw new Error('Rollback transaction');
    }
    
    return result;
  });
} catch (error) {
  console.error('Transaction failed:', error);
  // Semua operations di-rollback otomatis
}

// Retry logic
const MAX_RETRIES = 3;
let retries = 0;

while (retries < MAX_RETRIES) {
  try {
    const result = await prisma.$transaction(async (tx) => {
      // Operations...
    });
    break; // Success, keluar loop
  } catch (error) {
    retries++;
    if (retries === MAX_RETRIES) throw error;
    await new Promise(resolve => setTimeout(resolve, 1000));
  }
}
💡 Tips Transactions:
  • Gunakan untuk operasi yang harus atomic
  • Handle errors dengan baik
  • Pertimbangkan deadlocks
  • Test dengan scenario rollback
🔧 Migrations
▼

1. Migration Workflow

# 1. Buat perubahan di schema.prisma
# 2. Generate migration file
npx prisma migrate dev --name init

# 3. Apply migration ke database
npx prisma migrate deploy

# 4. Reset database (development)
npx prisma migrate reset

# 5. Status migrations
npx prisma migrate status

2. Migration Commands

Command Fungsi
migrate dev Buat dan apply migration
migrate deploy Apply pending migrations
migrate reset Reset database dan seed
migrate status Lihat status migrations
migrate resolve Mark migration sebagai applied/rolled back

3. Custom Migrations

// prisma/migrations/20231010123456_add_custom_index/migration.sql
-- Custom SQL migration
CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"(email);
CREATE INDEX "Post_published_created_at_idx" ON "Post"(published, created_at);

-- Complex data transformation
UPDATE "User" 
SET status = 'ACTIVE' 
WHERE last_login > NOW() - INTERVAL '30 days';

-- Add column dengan default value
ALTER TABLE "User" 
ADD COLUMN "preferences" JSONB NOT NULL DEFAULT '{}'::jsonb;

4. Seeding Database

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

const prisma = new PrismaClient()

async function main() {
  // Seed users
  await prisma.user.createMany({
    data: [
      {
        email: 'admin@mail.com',
        name: 'Admin User',
        role: 'ADMIN'
      },
      {
        email: 'user@mail.com', 
        name: 'Regular User',
        role: 'USER'
      }
    ],
    skipDuplicates: true
  })

  // Seed dengan relasi
  const user = await prisma.user.create({
    data: {
      email: 'author@mail.com',
      name: 'Author',
      posts: {
        create: [
          { title: 'First Post', content: 'Hello World' },
          { title: 'Second Post', content: 'Prisma is awesome' }
        ]
      }
    }
  })
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect())

5. Production Migrations

# Preview migration (tanpa apply)
npx prisma migrate dev --create-only

# Review generated SQL
cat prisma/migrations/xxx_migration_name/migration.sql

# Apply di production
npx prisma migrate deploy

# Rollback strategy
# 1. Buat migration revert
npx prisma migrate dev --name revert_change

# 2. Atau gunakan migrate resolve
npx prisma migrate resolve --rolled-back "migration_name"
🚨 Best Practices Migrations:
  • Selalu review SQL sebelum apply di production
  • Gunakan --create-only untuk preview
  • Backup database sebelum migration
  • Test migrations di staging environment
↑