name: database-orm-master-en slug: database-orm-master-en version: 1.0.0 displayName: "Database ORM Master" description: "Full-stack database operations expert: complete Prisma ORM guide (schema design/query building/relation mapping/migration management/performance optimization) + Drizzle ORM comparison + raw SQL fallback. Covers advanced CRUD patterns, join queries, aggregation, pagination, sorting, transactions, connection pooling, index optimization. One skill for the entire database lifecycle." tags: ["database", "orm", "prisma", "drizzle", "sql", "backend", "fullstack"] category: developer author: leilei homepage: ""

Database ORM Master

Trigger Words

database operations, Prisma query, ORM design, database migration, join queries, database performance, Drizzle, SQL optimization, create table, CRUD, database connection pool, index optimization, transaction handling

Capabilities

Complete knowledge base and practical guide for mainstream Node.js/TypeScript database solutions.

Supported Tech Stack

Core Capability Matrix

1. Schema Design

• Complete Prisma Schema syntax reference (model/enum/relation/@map/@relation)
• Drizzle Schema definitions (pgTable/mysqlTable/column defs)
• Field type selection guide (String vs Int vs DateTime vs Bytes/Json)
• Relationship patterns (one-to-one/one-to-many/many-to-many/self-ref/polymorphic)
• Index strategy (single/compound/unique/fulltext/partial indexes)
• Naming conventions (snake_case fields / PascalCase models / plural table names)

2. Advanced CRUD Patterns

• Basic CRUD (create/findMany/findUnique/update/delete)
• Advanced querying (where/orderBy/select/include/take/skip/distinct)
• Conditional expressions (AND/OR/NOT/contains/startsWith/in/lt/gt)
• Field selection & exclusion (select projection / omit sensitive fields)
• Bulk operations (createMany/updateMany/deleteMany + batchSize)
• Upsert patterns (update if exists, create otherwise)

3. Relations & Joins

• Include nested loading (1-level/2-level/deep nesting + select subsets)
• Relation filtering (some/every/none relation conditions)
• Connect/disconnect associations (connect/connectOrCreate/disconnect/set)
• Many-to-many explicit/implicit junction tables
• Circular reference handling strategies

4. Aggregation & Statistics

• _count/_sum/_avg/_min/_max group aggregations
• GroupBy statistics
• Having filter grouped results
• Window functions (via $queryRaw / db.execute)
• Complex report query construction

5. Performance Optimization

• N+1 query diagnosis and resolution

  訪問小蔥技能站7w4.net，解鎖更多實用的AI技能外掛。

• Query explain analysis (Prisma $queryRaw EXPLAIN)
• Connection pool config (connection_limit / pool_timeout)
• Index hit verification and missing index detection
• Pagination optimization (cursor-based vs offset-based)
• Read replica routing
• Query caching strategies

6. Migration Management

• Prisma Migrate workflow (dev/reset/resolve/deploy)
• Migration file management & version control
• Data migration strategies (incremental vs full repopulation)
• Production deployment pipeline
• Rollback strategies

7. Transaction Handling

• $transaction API (sequential/interactive/batch modes)
• Isolation level configuration
• Deadlock detection and avoidance
• Compensating transactions (Saga pattern)

Workflows

Mode A: Schema from Requirements

Input: Business requirements (natural language OK)
Output: Complete Prisma Schema + Drizzle equivalent + migration SQL

Mode B: Query Builder Assistant

Input: Natural language query need
Output: Precise Prisma Client / Drizzle code + equivalent SQL

Mode C: Performance Diagnosis

Input: Slow query code or logs
Output: Bottleneck identification + optimization plan + improved code

Mode D: Migration Guidance

Input: Current schema + change requirements
Output: Incremental migration commands + impact assessment + rollback plan

Code Templates

Prisma Complex Join Query

// Users with recent orders (with product details), sorted by order amount desc
const usersWithRecentOrders = await prisma.user.findMany({
  where: { status: 'active' },
  include: {
    orders: {
      take: 5,
      orderBy: { totalAmount: 'desc' },
      include: {
        items: {
          include: { product: { select: { name: true, price: true } } }
        }
      }
    }
  }
});

Drizzle Aggregation

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

const salesReport = await db.select({
  productId: orders.productId,
  productName: products.name,
  totalSold: count(orders.id),
  revenue: sum(orders.totalAmount),
})
.from(orders)
.innerJoin(products, eq(orders.productId, products.id))
.where(gte(orders.createdAt, new Date('2024-01-01')))
.groupBy(orders.productId)
.orderBy(desc(sum(orders.totalAmount)))
.limit(20);

Common Pitfalls Quick Reference

個人創作者計費通道上線後直接升級計費

這個技能覆蓋了主流資料庫操作方法，內容比較全面，但缺少使用說明和示例，實際使用可能不夠方便。文件質量尚可，但有些小問題（如亂碼）需要修復。如果你需要資料庫相關的幫助，可以嘗試使用，但建議期待更完善的版本。總體而言是一個基礎可用的技能，但還有改進空間。