Drizzle ORM dengan Claude Code: PostgreSQL, Migration, Zod, dan CI

Drizzle ORM adalah ORM yang mengutamakan TypeScript dan tetap dekat dengan SQL. Kita mendefinisikan tabel di TypeScript, lalu menulis query yang masih mudah dibaca oleh orang yang paham SQL. Pola ini cocok untuk Claude Code karena agent bisa membaca schema, menjelaskan relation, membuat migration, dan mengaitkan index dengan query tertentu.

Yang berbahaya adalah merasa pekerjaan database sudah selesai hanya karena schema.ts bisa compile. Migration yang dihasilkan bisa saja menghapus kolom. Seed bisa berjalan sekali tetapi gagal saat dijalankan lagi. Transaction bisa menahan koneksi database terlalu lama ketika menunggu email, Stripe, atau webhook. Karena itu, minta Claude Code mengerjakan alur lengkap: schema, migration, query, transaction, seed, validasi Zod, dan CI.

Untuk referensi terkini, gunakan dokumen resmi Drizzle ORM, Drizzle Kit, Transactions, Drizzle Zod, dan Claude Code docs. Artikel internal yang relevan: Prisma ORM, database migration, dan Zod validation.

Alur Kerja

Prompt yang baik harus menyebut aturan bisnis, bukan hanya nama tabel.

Implementasikan layer database dengan Drizzle ORM.

Stack:
- PostgreSQL
- drizzle-orm, drizzle-kit, node-postgres
- tabel: User, Post, Category, Comment, AuditLog
- status Post: draft, published, archived
- email dan slug unique
- daftar artikel difilter oleh status, publishedAt, author, category, search
- saat Post dihapus, hanya Comment dan join table yang cascade
- saat User dihapus, Post tidak boleh ikut terhapus

Berikan schema, drizzle.config.ts, query, transaction, seed, Zod, dan CI.

flowchart LR
  A["Prompt"] --> B["schema.ts"]
  B --> C["drizzle-kit generate"]
  C --> D["review SQL"]
  D --> E["query, transaction, seed"]
  E --> F["Zod validation"]
  F --> G["CI checks"]

Schema dan Migration

Schema berikut menampilkan bagian yang paling penting untuk review: unique, index, relation, dan aturan delete.

// db/schema.ts
import { relations } from "drizzle-orm";
import {
  boolean,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const postStatus = pgEnum("post_status", ["draft", "published", "archived"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
    role: varchar("role", { length: 40 }).default("editor").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("users_email_unique").on(table.email),
    index("users_role_idx").on(table.role),
  ],
);

export const categories = pgTable(
  "categories",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
  },
  (table) => [uniqueIndex("categories_slug_unique").on(table.slug)],
);

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 160 }).notNull(),
    title: varchar("title", { length: 160 }).notNull(),
    body: text("body").notNull(),
    status: postStatus("status").default("draft").notNull(),
    authorId: uuid("author_id").notNull().references(() => users.id, { onDelete: "restrict" }),
    viewCount: integer("view_count").default(0).notNull(),
    featured: boolean("featured").default(false).notNull(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("posts_slug_unique").on(table.slug),
    index("posts_status_published_at_idx").on(table.status, table.publishedAt),
    index("posts_author_id_idx").on(table.authorId),
  ],
);

export const postCategories = pgTable(
  "post_categories",
  {
    postId: uuid("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
    categoryId: uuid("category_id").notNull().references(() => categories.id, { onDelete: "cascade" }),
  },
  (table) => [primaryKey({ columns: [table.postId, table.categoryId] })],
);

export const comments = pgTable(
  "comments",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    postId: uuid("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
    authorId: uuid("author_id").notNull().references(() => users.id, { onDelete: "restrict" }),
    body: text("body").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    index("comments_post_created_at_idx").on(table.postId, table.createdAt),
    index("comments_author_id_idx").on(table.authorId),
  ],
);

export const auditLogs = pgTable(
  "audit_logs",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    action: varchar("action", { length: 80 }).notNull(),
    targetId: uuid("target_id").notNull(),
    metadata: jsonb("metadata").$type<Record<string, unknown>>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index("audit_logs_action_created_at_idx").on(table.action, table.createdAt)],
);

export const usersRelations = relations(users, ({ many }) => ({ posts: many(posts), comments: many(comments) }));
export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  comments: many(comments),
  categories: many(postCategories),
}));
export const categoriesRelations = relations(categories, ({ many }) => ({ posts: many(postCategories) }));
export const postCategoriesRelations = relations(postCategories, ({ one }) => ({
  post: one(posts, { fields: [postCategories.postId], references: [posts.id] }),
  category: one(categories, { fields: [postCategories.categoryId], references: [categories.id] }),
}));
export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  author: one(users, { fields: [comments.authorId], references: [users.id] }),
}));

// drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
  verbose: true,
  strict: true,
});

npm run db:generate
npm run db:check
npm run db:migrate

Saat membaca SQL migration, cari DROP yang tidak disengaja, NOT NULL yang berbahaya untuk data lama, cascade yang terlalu luas, dan index yang tidak dipakai oleh query nyata.

Query, Seed, dan Zod

// db/client.ts
import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

export const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

// db/posts.ts
import { and, desc, eq, ilike, sql } from "drizzle-orm";
import { z } from "zod";
import { db } from "./client";
import { auditLogs, categories, comments, postCategories, posts, users } from "./schema";
import { createPostInputSchema } from "./validation";

type CreatePostInput = z.infer<typeof createPostInputSchema>;

export async function createPost(input: CreatePostInput) {
  const data = createPostInputSchema.parse(input);

  return db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values({
      slug: data.slug,
      title: data.title,
      body: data.body,
      authorId: data.authorId,
    }).returning();

    for (const slug of data.categorySlugs) {
      const [category] = await tx.insert(categories)
        .values({ slug, name: slug })
        .onConflictDoUpdate({ target: categories.slug, set: { name: slug } })
        .returning();
      await tx.insert(postCategories)
        .values({ postId: post.id, categoryId: category.id })
        .onConflictDoNothing();
    }

    await tx.insert(auditLogs).values({
      action: "post.create",
      targetId: post.id,
      metadata: { slug: post.slug },
    });

    return post;
  });
}

export async function listPublishedPosts(params: { page?: number; perPage?: number; search?: string } = {}) {
  const page = Math.max(params.page ?? 1, 1);
  const perPage = Math.min(Math.max(params.perPage ?? 20, 1), 50);
  const where = params.search
    ? and(eq(posts.status, "published"), ilike(posts.title, `%${params.search}%`))
    : eq(posts.status, "published");

  const [items, [{ total }]] = await Promise.all([
    db.select({
      id: posts.id,
      slug: posts.slug,
      title: posts.title,
      publishedAt: posts.publishedAt,
      authorName: users.name,
      commentCount: sql<number>`count(${comments.id})::int`,
    })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .leftJoin(comments, eq(comments.postId, posts.id))
      .where(where)
      .groupBy(posts.id, posts.slug, posts.title, posts.publishedAt, users.name)
      .orderBy(desc(posts.publishedAt), desc(posts.createdAt))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db.select({ total: sql<number>`count(*)::int` }).from(posts).where(where),
  ]);

  return { items, pagination: { page, perPage, total, totalPages: Math.ceil(total / perPage) } };
}

// db/validation.ts
import { createInsertSchema } from "drizzle-orm/zod";
import { z } from "zod";
import { posts } from "./schema";

export const createPostInputSchema = createInsertSchema(posts, {
  slug: (schema) => schema.min(3).max(160).regex(/^[a-z0-9-]+$/),
  title: (schema) => schema.min(1).max(160),
  body: (schema) => schema.min(50),
})
  .pick({ slug: true, title: true, body: true, authorId: true })
  .extend({
    categorySlugs: z.array(z.string().min(1).max(120)).min(1).max(5),
  });

// db/seed.ts
import { db, pool } from "./client";
import { categories, postCategories, posts, users } from "./schema";

async function main() {
  const [user] = await db.insert(users)
    .values({ email: "masa@example.com", name: "Masa", role: "admin" })
    .onConflictDoUpdate({ target: users.email, set: { name: "Masa", role: "admin", updatedAt: new Date() } })
    .returning();

  const [category] = await db.insert(categories)
    .values({ slug: "drizzle", name: "Drizzle ORM" })
    .onConflictDoUpdate({ target: categories.slug, set: { name: "Drizzle ORM" } })
    .returning();

  const [post] = await db.insert(posts)
    .values({
      slug: "claude-code-drizzle-demo",
      title: "Claude Code Drizzle demo",
      body: "A seeded post for local verification.",
      status: "published",
      authorId: user.id,
      publishedAt: new Date(),
    })
    .onConflictDoUpdate({ target: posts.slug, set: { title: "Claude Code Drizzle demo", updatedAt: new Date() } })
    .returning();

  await db.insert(postCategories).values({ postId: post.id, categoryId: category.id }).onConflictDoNothing();
}

main().finally(async () => pool.end());

CI, Use Case, dan CTA

name: drizzle
on:
  pull_request:
jobs:
  db:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: app
          POSTGRES_DB: app_test
        ports:
          - 5432:5432
    env:
      DATABASE_URL: postgresql://app:app@localhost:5432/app_test
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: npm
      - run: npm ci
      - run: npm run db:generate
      - run: npm run db:check
      - run: npm run db:migrate
      - run: npm run db:seed
      - run: npm run typecheck
      - run: npm test

Checklist Review

Sebelum merge, minta Claude Code melakukan self-review yang spesifik. Setiap index harus dikaitkan dengan halaman atau API query tertentu. Setiap onDelete harus punya alasan bisnis, bukan sekadar pilihan default. Migration SQL harus dibaca untuk menemukan DROP, cascade yang terlalu luas, atau NOT NULL berbahaya untuk data lama. Seed perlu dijalankan dua kali. Zod dipakai untuk validasi input runtime, sedangkan constraint database tetap menjadi penjaga integritas terakhir. CI juga perlu dijelaskan batasnya: berhasil migrate di database test kosong tidak otomatis membuktikan aman untuk data produksi.

Use case yang paling umum ada empat. CMS membutuhkan slug unik, status publikasi, dan index publishedAt. Admin SaaS membutuhkan tenant boundary, role, dan audit log. Platform kursus membutuhkan progress update yang konsisten dengan access rule. Konten monetisasi membutuhkan event CTA, link produk, dan completion rate, tetapi penambahan analytics tidak boleh melemahkan constraint lama.

Kesalahan yang sering muncul: tidak membaca SQL migration, seed tidak idempotent, external API dipanggil di dalam transaction, Zod dianggap pengganti constraint database, pagination tanpa batas, dan Claude Code mengubah migration yang tidak terkait. Untuk tim, halaman bahasa Inggris Claude Code training and consultation adalah langkah berikutnya. Untuk belajar mandiri, lanjutkan ke Supabase integration dan CI/CD setup.

Dalam uji kecil pada blog API, hasil terbaik muncul saat Claude Code diminta menjelaskan SQL migration, bukan hanya menulis TypeScript. Draft pertama terlalu agresif pada aturan delete. Setelah onDelete, index, dan seed idempotency direview terpisah, diff menjadi jauh lebih mudah disetujui. Drizzle ORM terasa ringan, tetapi keamanan tetap datang dari kombinasi kecepatan Claude Code, review SQL oleh manusia, dan bukti CI.