Tujuan Pembelajaran

Bisa melakukan CRUD operations
Mengerti query builder Drizzle
Bisa menangani relasi antar tabel

Analogi

Diagram


      Query Types:
SELECT → db.select().from(table)
INSERT → db.insert(table).values({...})
UPDATE → db.update(table).set({...}).where(...)
DELETE → db.delete(table).where(...)

Drizzle query syntax mirip SQL — mudah dipelajari kalau sudah tahu SQL

Penjelasan Konsep

Setelah schema didefinisikan, saatnya belajar melakukan operasi CRUD (Create, Read, Update, Delete).

Drizzle menyediakan query builder yang syntax-nya sangat mirip SQL.

SELECT Queries

// Select all
const allUsers = await db.select().from(users);

// Select specific columns
const userNames = await db.select({ name: users.name, email: users.email }).from(users);

// With WHERE clause
const user = await db.select().from(users).where(eq(users.id, 1));

// Multiple conditions
const admins = await db.select().from(users)
  .where(and(eq(users.role,

'admin'), gte(users.id, 1)));

// LIKE / ILIKE
const results = await db.select().from(users)
  .where(like(users.name, '%Budi%'));

// ORDER BY
const sorted = await db.select().from(users)
  .orderBy(desc(users.createdAt));

// LIMIT and OFFSET
const paginated = await db.select().from(users)
  .limit(10).offset(20);

Notice: eq(), and(), gte(), like(), desc() adalah helper functions dari drizzle-orm.

Mereka menghasilkan SQL expression yang type-safe.

INSERT Queries

// Insert single
const newUser = await db.insert(users).values({
  name: 'Budi',
  email: 'budi@email.com',
}).returning(); // Return inserted row

// Insert multiple
await db.insert(users).values([
  { name: 'Budi', email: 'budi@mail.com' },
  { name: 'Ani', email: 'ani@mail.com' },
]);

UPDATE Queries

await db.update(users)
  .set({ name: 'Budi Santoso' })
  .where(eq(users.id, 1))
  .returning(); // Return updated row

DELETE Queries

await db.delete(users).where(eq(users.id,

1));

Relasi (Joins)

Drizzle support SQL joins dengan syntax yang natural:

import { eq } from 'drizzle-orm';

const result = await db.select()
  .from(users)
  .innerJoin(products, eq(users.id, products.userId))
  .where(eq(users.id, 1));

Aggregate Functions

import { count, avg, sum } from 'drizzle-orm';

const stats = await db.select({
  total: count(),
  avgPrice: avg(products.price),
  totalStock: sum(products.stock),
}).from(products);

Contoh Kode

typescript

// CRUD operations lengkap
import { eq, and, like, desc, count } from 'drizzle-orm';
import { db } from './db';
import { users, products } from './schema';

// CREATE
async function createUser(data: { name: string; email: string }) {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}

// READ with filters
async function getUsers(options: {
  search?: string;
  role?: string;
  page?: number;
  limit?: number;
} = {}) {
  const { search, role, page = 1, limit = 10 } = options;
  
  const conditions = [];
  if (search) conditions.push(like(users.name, `%${search}%`));
  if (role) conditions.push(eq(users.role, role));
  
  const where = conditions.length > 0 ? and(...conditions) : undefined;
  
  const [data, totalResult] = await Promise.all([
    db.select().from(users).where(where).orderBy(desc(users.createdAt)).limit(limit).offset((page - 1) * limit),
    db.select({ count: count() }).from(users).where(where),
  ]);
  
  return {
    data,
    pagination: {
      page,
      limit,
      total: totalResult[0].count,
      totalPages: Math.ceil(totalResult[0].count / limit),
    },
  };
}

// UPDATE
async function updateUser(id: number, data: Partial<{ name: string; email: string; role: string }>) {
  const [user] = await db.update(users).set(data).where(eq(users.id, id)).returning();
  return user;
}

// DELETE
async function deleteUser(id: number) {
  await db.delete(users).where(eq(users.id, id));
}

// Get user with their products
async function getUserWithProducts(userId: number) {
  const result = await db.select()
    .from(users)
    .leftJoin(products, eq(users.id, products.userId))
    .where(eq(users.id, userId));
  
  return result;
}

Penjelasan Kode

and() menggabungkan conditions dengan AND. Promise.all untuk parallel queries. Returning() mengembalikan row yang terpengaruh.

Prompt AI

Implementasikan full-text search dengan SQLite FTS5 extension melalui Drizzle. Buat virtual table untuk search.

Pertanyaan Reflektif

Benchmark query Drizzle-mu. Bandingkan dengan raw SQL untuk query yang sama. Apakah overhead-nya signifikan?