📖 技能介绍

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

ORM/Tool	Version Range	Best For
Prisma	4.x - 6.x	Full-featured ORM, type-safe first choice
Drizzle ORM	0.x - 0.30+	Lightweight, SQL-like experience, TypeScript-native
Raw SQL	General	Complex queries / performance-critical fallback

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
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

Pitfall	Symptom	Fix
N+1 Queries	Many repeated SQL	Use include/select for eager loading
Deep Include Explosion	Very slow / OOM	Limit nesting depth, use select for needed fields only
Migration Conflicts	Team collaboration conflicts	Use prisma migrate resolve
Connection Pool Exhaustion	Timeouts / connection refused	Adjust connection_limit, check for leaks
Time Precision Loss	DateTime loses ms/timezone after storage	Use DateTime(tz) or store Unix timestamps
Soft Delete Leakage	Deleted data still showing	Add deletedAt field + default filter everywhere