database-orm-expert · diff
git:20260907.57a4b90 to git:20260911.16cf003
96 added, 235 removed. Audit A to A.
---
name: database-orm-expert
- description: "Expert guide for database schema design, ORM tools (Prisma 6, Drizzle ORM, TypeORM), migrations, query optimization, and type-safe SQL patterns in TypeScript / Panduan ahli untuk desain skema database, ORM tools (Prisma 6, Drizzle ORM, TypeORM), migrasi, optimasi query, dan pola SQL type-safe di TypeScript."
+ description: "Updated to be the unified database skill covering ORM, migrations, edge DBs, and Supabase CLI / Keahlian database terpadu untuk ORM, migrasi, edge DB, dan Supabase CLI."
author: "Roedy Rustam"
---
- # Database ORM Expert (Prisma 6 + Drizzle ORM Edition)
+ # Unified Database & ORM Expert
[English](#english) | [Bahasa Indonesia](#bahasa-indonesia)
---
<a name="english"></a>
## English
### Description
- Design schemas, select ORMs, execute migrations, optimize queries, and implement type-safe SQL patterns. Prioritize **Prisma 6** and **Drizzle ORM**. Implement connection pooling for production workloads.
+ Design schemas, select ORMs, execute zero-downtime migrations, optimize queries, and implement edge serverless databases. Covers Prisma 6, Drizzle ORM, TypeORM, Supabase CLI workflows, and Edge DBs (Neon, Cloudflare D1, Turso, Upstash).
### Trigger Conditions
- - Designing or migrating a database schema.
+ - Designing or migrating a production schema (blue-green, canary).
- Choosing between Prisma, Drizzle ORM, or TypeORM.
- Writing complex queries with joins, aggregations, or pagination.
- Optimizing slow queries or N+1 problems.
- - Setting up database migrations in CI/CD pipelines.
- - Implementing Row Level Security (RLS) patterns.
- - Working with PostgreSQL, MySQL, SQLite, or PlanetScale.
+ - Building edge-compatible serverless database connections.
+ - Executing Supabase migrations and RLS patterns.
+ - Managing backward-compatible massive data backfills.
## Orchestration & Integration
- `js-backend-expert`: For Node/Bun/Deno backend implementations integrating these ORMs.
- - `edge-serverless-db-expert`: For edge/serverless connections (e.g., Supabase, Neon, Turso).
- - `database-migration-versioning-expert`: For advanced migration strategies and CI/CD pipelines.
+ - `ci-cd-devops-architect`: For automated migration deployment steps.
+ - `supabase-security-expert`: For Supabase RLS and security.
+ - `cloud-hosting-expert`: For infrastructure integration.
---
- ### ORM Selection Guide
+ ### Core ORM & Query Strategies
+ #### ORM Selection Guide
| Criteria | Prisma 6 | Drizzle ORM | TypeORM |
|---|---|---|---|
| **Type Safety** | Schema-generated types | SQL-like, inferred types | Decorator-based |
| **Bundle Size** | Heavy (binary client) | Lightweight (<35KB) | Medium |
| **Query Style** | Fluent ORM API | SQL-first, composable | ActiveRecord / QueryBuilder |
| **Edge Runtime** | Prisma Accelerate needed | Native edge support | No |
| **Migrations** | `prisma migrate dev` | `drizzle-kit push/migrate` | `synchronize` (dev only) |
- | **Best For** | Rapid prototyping, teams | Production edge, monorepos | Legacy NestJS projects |
- **Recommendation**: Use **Drizzle ORM** for edge-compatible apps and performance-critical systems. Use **Prisma 6** for teams that prefer a schema-first DX and rich Studio tooling.
-
- ---
-
- ### Prisma 6 — Best Practices
-
- #### Schema Design
- ```prisma
- // schema.prisma
- generator client {
- provider = "prisma-client-js"
- previewFeatures = ["relationJoins", "nativeDistinct"]
- }
-
- datasource db {
- provider = "postgresql"
- url = env("DATABASE_URL")
- directUrl = env("DIRECT_URL") // for Supabase Pooler
- }
-
- model User {
- id String @id @default(cuid())
- email String @unique
- name String?
- role Role @default(USER)
- posts Post[]
- createdAt DateTime @default(now())
- updatedAt DateTime @updatedAt
-
- @@index([email])
- @@map("users")
- }
-
- model Post {
- id String @id @default(cuid())
- title String
- content String?
- published Boolean @default(false)
- authorId String
- author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
- publishedAt DateTime?
-
- @@index([authorId, published])
- @@map("posts")
- }
-
- enum Role {
- USER
- ADMIN
- SUPER_ADMIN
- }
- ```
-
- #### Avoiding N+1 with `include` vs `select`
- ```typescript
- // BAD: triggers N+1 queries
- const users = await prisma.user.findMany();
- for (const user of users) {
- const posts = await prisma.post.findMany({ where: { authorId: user.id } });
- }
+ **Recommendation**: Use **Drizzle ORM** for edge-compatible apps and performance-critical systems. Use **Prisma 6** for teams preferring a schema-first DX.
- // GOOD: single query with JOIN (Prisma 5.7+ relationJoins preview)
- const users = await prisma.user.findMany({
- select: {
- id: true,
- name: true,
- email: true,
- _count: { select: { posts: true } },
- posts: {
- where: { published: true },
- select: { id: true, title: true },
- take: 5,
- orderBy: { publishedAt: 'desc' },
- },
- },
- });
- ```
+ #### Prisma 6 Best Practices
+ - Use `$transaction` for atomic operations.
+ - Avoid N+1 queries by using `select` and `include` (with Prisma 5.7+ relationJoins preview) rather than looping over `findMany`.
- #### Optimistic Transactions
- ```typescript
- // Use $transaction for atomic operations
- const [updatedUser, newPost] = await prisma.$transaction([
- prisma.user.update({ where: { id }, data: { name } }),
- prisma.post.create({ data: { title, authorId: id } }),
- ]);
+ #### Drizzle ORM Best Practices
+ - Use `drizzle-kit generate` for generating SQL and `drizzle-kit migrate` for deployment.
+ - Utilize native edge support and type-safe query builders with `drizzle-orm`.
- // Interactive transaction for complex logic
- const result = await prisma.$transaction(async (tx) => {
- const user = await tx.user.findUniqueOrThrow({ where: { id } });
- if (user.role !== 'ADMIN') throw new Error('Unauthorized');
- return tx.post.updateMany({ where: { authorId: id }, data: { published: true } });
- });
- ```
+ #### Query Optimization Principles
+ 1. **Always index foreign keys** and columns used in `WHERE`, `ORDER BY`, and `JOIN`.
+ 2. **Use `EXPLAIN ANALYZE`** to detect sequential scans.
+ 3. **Cursor-based pagination** over offset for large datasets.
+ 4. **Avoid `SELECT *`**. Batch inserts where possible.
+ 5. **Connection Pooling**: Use PgBouncer, Supabase's built-in pooler, or Prisma Accelerate for standard TCP to avoid exhausting connection limits. Alternatively, use HTTP/WebSocket drivers for edge queries.
---
- ### Drizzle ORM — Best Practices
-
- #### Schema Definition (PostgreSQL)
- ```typescript
- // src/db/schema.ts
- import { pgTable, text, boolean, timestamp, pgEnum, index } from 'drizzle-orm/pg-core';
- import { createId } from '@paralleldrive/cuid2';
- import { relations } from 'drizzle-orm';
+ ### Zero-Downtime Migrations & Versioning
- export const roleEnum = pgEnum('role', ['USER', 'ADMIN', 'SUPER_ADMIN']);
+ #### 1. Zero-Downtime Migration Pattern (Expand and Contract)
+ Never make breaking changes in a single deployment.
+ - **Phase 1 (Expand)**: Add the new schema element (column, table) without removing the old one.
+ - **Phase 2 (Migrate)**: Update app to write to *both* and read from the new element (with fallback).
+ - **Phase 3 (Backfill)**: Run background script to backfill data to new element.
+ - **Phase 4 (Contract)**: Remove old application code.
+ - **Phase 5 (Cleanup)**: Drop old schema element in next deployment.
- export const users = pgTable('users', {
- id: text('id').primaryKey().$defaultFn(() => createId()),
- email: text('email').notNull().unique(),
- name: text('name'),
- role: roleEnum('role').default('USER').notNull(),
- createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
- updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull().$onUpdateFn(() => new Date()),
- }, (t) => [
- index('users_email_idx').on(t.email),
- ]);
+ #### 2. Backward Compatibility Rules
+ - **Never `DROP` or `RENAME`** a column/table in active use.
+ - **Add `DEFAULT` values** to new `NOT NULL` columns.
- export const posts = pgTable('posts', {
- id: text('id').primaryKey().$defaultFn(() => createId()),
- title: text('title').notNull(),
- content: text('content'),
- published: boolean('published').default(false).notNull(),
- authorId: text('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
- publishedAt: timestamp('published_at', { withTimezone: true }),
- }, (t) => [
- index('posts_author_published_idx').on(t.authorId, t.published),
- ]);
+ #### 3. Migration Mechanics & Safe Data Backfilling
+ - **Never run `prisma db push` or `synchronize: true`** in production. Always use immutable version-controlled scripts (e.g., `20260814_add_user_status.sql`).
+ - Write idempotent scripts (`CREATE TABLE IF NOT EXISTS`).
+ - **Chunking**: For large tables, perform updates in batches (using `LIMIT` and sleep intervals) to prevent table locking. Use background jobs (BullMQ/Inngest).
- export const usersRelations = relations(users, ({ many }) => ({
- posts: many(posts),
- }));
+ ---
- export const postsRelations = relations(posts, ({ one }) => ({
- author: one(users, { fields: [posts.authorId], references: [users.id] }),
- }));
- ```
+ ### Edge & Serverless Drivers
- #### Type-safe Queries with Drizzle
+ Exploit serverless DBs for ultra-low latency:
+ - **Neon & Cloudflare D1**: Serverless autoscaling Postgres with instant branching; Distributed edge SQLite.
+ - **Embedded Replicas**: Sync edge SQLite read-replicas with central cloud DBs.
+ - **HTTP/WebSocket Proxy Pooling**: Use `neon-http` or similar when querying databases from Edge Workers/Functions.
+ - **Upstash Redis Edge Caching**:
```typescript
- // src/db/index.ts
- import { drizzle } from 'drizzle-orm/postgres-js';
- import postgres from 'postgres';
- import * as schema from './schema';
-
- const client = postgres(process.env.DATABASE_URL!);
- export const db = drizzle(client, { schema });
-
- // Type-safe query with joins
- import { eq, and, desc, count } from 'drizzle-orm';
-
- const usersWithPosts = await db.query.users.findMany({
- with: {
- posts: {
- where: eq(posts.published, true),
- orderBy: desc(posts.publishedAt),
- limit: 5,
- },
- },
- extras: {
- postCount: db.$count(posts, eq(posts.authorId, users.id)).as('post_count'),
- },
- });
-
- // Paginated query
- async function getPaginatedPosts(page: number, pageSize = 20) {
- const offset = (page - 1) * pageSize;
- const [items, [{ total }]] = await Promise.all([
- db.select().from(posts).where(eq(posts.published, true))
- .orderBy(desc(posts.publishedAt))
- .limit(pageSize)
- .offset(offset),
- db.select({ total: count() }).from(posts).where(eq(posts.published, true)),
- ]);
- return { items, total, pages: Math.ceil(total / pageSize) };
+ import { Redis } from '@upstash/redis';
+ const redis = new Redis({ url: process.env.UPSTASH_REDIS_REST_URL!, token: process.env.UPSTASH_REDIS_REST_TOKEN! });
+ export async function getCachedData(key: string) {
+ let data = await redis.get(key);
+ if (!data) {
+ data = await fetchFromDB();
+ await redis.set(key, data, { ex: 3600 });
+ }
+ return data;
}
```
- #### Drizzle Migration Workflow
- ```bash
- # drizzle.config.ts defines connection + schema path
- npx drizzle-kit generate # generate migration SQL files
- npx drizzle-kit migrate # apply migrations to database
- npx drizzle-kit push # push schema directly (dev only)
- npx drizzle-kit studio # open Drizzle Studio GUI
- ```
-
---
- ### Query Optimization Principles
-
- 1. **Always index foreign keys** and columns used in `WHERE`, `ORDER BY`, and `JOIN`.
- 2. **Use `EXPLAIN ANALYZE`** to detect sequential scans and missing indexes.
- 3. **Cursor-based pagination** over offset for large datasets:
- ```typescript
- // Cursor pagination with Drizzle
- const items = await db.select().from(posts)
- .where(cursor ? lt(posts.createdAt, cursor) : undefined)
- .orderBy(desc(posts.createdAt))
- .limit(pageSize);
- ```
- 4. **Connection pooling**: Use PgBouncer or Supabase's built-in pooler. Set `DIRECT_URL` for migrations and `DATABASE_URL` for pooled reads/writes.
- 5. **Avoid `SELECT *`**: Always select only the columns you need.
- 6. **Batch inserts**: Use `db.insert(table).values([...items])` instead of looping.
-
- ---
-
- ### Migration Best Practices
+ ### Supabase CLI & MCP Workflow
- - **Never use `synchronize: true`** in production (TypeORM).
- - **Never run `prisma db push`** in production — always use `prisma migrate deploy`.
- - Store migration files in version control.
- - Run migrations as a separate step before deploying new app versions.
- - Use **advisory locks** or migration tools to prevent concurrent migration runs.
+ 1. **Check Environment**: Ensure `supabase/migrations` directory exists.
+ 2. **Review Available MCP Commands**:
+ - List existing migrations: `mcp_supabase-mcp-server_list_migrations` with `project_id`.
+ - Confirm SQL before executing via server directly.
+ 3. **Execute Command**:
+ - Write SQL script locally: `npx supabase migration new [name]` -> writes to `supabase/migrations/<timestamp>_[name].sql`.
+ - Test locally: `npx supabase migration up` or `npx supabase db reset`.
+ - Apply remote: `npx supabase db push` (or use remote MCP `apply_migration`).
+ 4. **Final Step**: If using RLS, explicitly add policies to new tables. Confirm completion with user.
---
<a name="bahasa-indonesia"></a>
## Bahasa Indonesia
### Deskripsi
- Rancang skema, pilih ORM, eksekusi migrasi, optimalkan query, dan implementasikan pola SQL type-safe. Prioritaskan **Prisma 6** dan **Drizzle ORM**. Implementasikan connection pooling untuk beban kerja produksi.
+ Rancang skema, pilih ORM, eksekusi migrasi tanpa downtime, optimalkan query, dan implementasikan edge serverless DB. Mencakup Prisma 6, Drizzle, TypeORM, Supabase CLI, dan Edge DBs (Neon, Cloudflare D1, Turso, Upstash).
### Kondisi Pemicu
- - Merancang atau memigrasikan skema database.
- - Memilih antara Prisma, Drizzle ORM, atau TypeORM.
- - Menulis query kompleks dengan join, agregasi, atau paginasi.
+ - Merancang atau memigrasikan skema produksi (blue-green, canary).
+ - Memilih antara Prisma, Drizzle, atau TypeORM.
- Mengoptimalkan query lambat atau masalah N+1.
- - Menyiapkan migrasi database dalam pipeline CI/CD.
- - Mengimplementasikan pola Row Level Security (RLS).
- - Bekerja dengan PostgreSQL, MySQL, SQLite, atau PlanetScale.
+ - Membuat koneksi serverless kompatibel dengan edge.
+ - Mengeksekusi migrasi Supabase dan pola RLS.
+ - Mengelola backfill data besar secara backward-compatible.
## Integrasi Orkestrasi
- - `js-backend-expert`: Untuk implementasi backend Node/Bun/Deno yang menggunakan ORM ini.
- - `edge-serverless-db-expert`: Untuk koneksi edge/serverless (mis. Supabase, Neon, Turso).
- - `database-migration-versioning-expert`: Untuk strategi migrasi lanjutan dan pipeline CI/CD.
-
- ### Panduan Pemilihan ORM
-
- Gunakan tabel di atas (lihat bagian English) sebagai referensi pemilihan ORM. Rekomendasi singkat:
- - **Drizzle ORM**: Untuk aplikasi edge-compatible dan sistem kritis performa.
- - **Prisma 6**: Untuk tim yang lebih menyukai DX schema-first dan tooling Studio yang kaya.
- - **TypeORM**: Hanya untuk proyek lama (legacy) berbasis NestJS.
+ - `js-backend-expert`: Untuk implementasi Node/Bun/Deno.
+ - `ci-cd-devops-architect`: Untuk otomatisasi langkah deployment migrasi.
+ - `supabase-security-expert`: Untuk RLS dan keamanan Supabase.
+ - `cloud-hosting-expert`: Untuk integrasi infrastruktur.
- ### Prinsip Utama
+ ### Strategi Inti ORM & Query
+ - **Drizzle ORM** direkomendasikan untuk aplikasi edge, **Prisma 6** untuk DX schema-first.
+ - Gunakan `$transaction` dan batasi `SELECT *`.
+ - Selalu indeks foreign key dan gunakan pagination berbasis cursor (Cursor-based pagination).
+ - **Connection Pooling**: Gunakan pooler bawaan Supabase, PgBouncer, Prisma Accelerate, atau driver HTTP (seperti `neon-http`) untuk request edge.
- 1. **Selalu index foreign key** dan kolom yang digunakan di `WHERE`, `ORDER BY`, dan `JOIN`.
- 2. **Gunakan `EXPLAIN ANALYZE`** untuk mendeteksi sequential scan dan index yang hilang.
- 3. **Cursor-based pagination** lebih baik dari offset untuk dataset besar.
- 4. **Connection pooling**: Gunakan PgBouncer atau Supabase pooler bawaan.
- 5. **Hindari `SELECT *`**: Selalu pilih hanya kolom yang dibutuhkan.
- 6. **Batch insert**: Gunakan insert massal, bukan looping satu per satu.
+ ### Migrasi Tanpa Downtime & Versioning
+ - **Pola Expand and Contract**: Jangan pernah melakukan breaking change dalam satu rilis. Tambah kolom baru, update kode untuk memakai keduanya, backfill data, hapus kode lama, lalu drop kolom lama.
+ - Jangan gunakan `DROP` atau `RENAME` pada kolom aktif. Kolom `NOT NULL` baru harus memiliki nilai `DEFAULT`.
+ - **Jangan jalankan `prisma db push` atau `synchronize: true`** di produksi. Gunakan skrip migrasi bertahap (idempoten).
+ - **Backfill Aman**: Gunakan pemrosesan batch/chunking dengan background job untuk tabel raksasa agar tidak menyebabkan table lock.
- ### Prinsip Migrasi
+ ### Driver Edge & Serverless
+ - Manfaatkan Neon Serverless Postgres, Cloudflare D1, Turso, atau Upstash Redis (caching).
+ - Gunakan driver berbasis HTTP/WebSocket di lingkungan Edge Workers untuk mencegah habisnya batas koneksi TCP.
- - Jangan gunakan `synchronize: true` di produksi (TypeORM).
- - Jangan jalankan `prisma db push` di produksi — selalu gunakan `prisma migrate deploy`.
- - Simpan file migrasi di version control.
- - Jalankan migrasi sebagai langkah terpisah sebelum deploy versi aplikasi baru.
+ ### Alur Kerja Supabase CLI & MCP
+ 1. Pastikan folder `supabase/migrations` ada.
+ 2. Periksa migrasi dengan `mcp_supabase-mcp-server_list_migrations`.
+ 3. Buat file migrasi lokal dengan `npx supabase migration new [nama]`.
+ 4. Uji lokal dengan `npx supabase migration up`.
+ 5. Deploy remote dengan `npx supabase db push` atau melalui MCP.
+ 6. Tambahkan kebijakan RLS jika diperlukan. Konfirmasikan sukses ke pengguna.