GraphQL 数据库与 ORM 集成：DataLoader、事务与查询下推

// ❌ 每个 resolver 各查一次 → N+1
const resolvers = {
  Query: {
    orders: () => db.order.findMany(),
  },
  Order: {
    buyer: (order) => db.user.findUnique({ where: { id: order.buyerId } }),
    items: (order) => db.orderItem.findMany({ where: { orderId: order.id } }),
  },
};

// 查 100 个订单 → 1 + 100 + 100 = 201 次查询

// Prisma 的批量查询天然适合 DataLoader
const users = await prisma.user.findMany({
  where: { id: { in: ids } },
  select: { id: true, fullName: true },   // 只取需要的列
});

// 按 id 建索引，保持与入参顺序对齐
const byId = new Map(users.map((u) => [u.id, u]));
return ids.map((id) => byId.get(id) ?? null);

import { inArray, eq } from 'drizzle-orm';

// Drizzle 生成的 SQL 更可控，便于分页与聚合下推
const rows = await db
  .select({ id: users.id, fullName: users.fullName })
  .from(users)
  .where(inArray(users.id, ids));

// ❌ 懒加载会在循环中触发 N 次查询
const orders = await repo.find();
for (const o of orders) {
  await o.buyer;   // 每个订单一次查询
}

// ✅ 显式 relation 加载
const orders = await repo.find({ relations: ['buyer', 'items'] });

import DataLoader from 'dataloader';

export function createLoaders(prisma: PrismaClient) {
  return {
    userById: new DataLoader<string, User | null>(async (ids) => {
      const users = await prisma.user.findMany({
        where: { id: { in: [...ids] } },
      });
      const byId = new Map(users.map((u) => [u.id, u]));
      return ids.map((id) => byId.get(id) ?? null);   // 顺序必须与 ids 一致
    }),
  };
}

const resolvers = {
  Order: {
    buyer: (order, _args, ctx) => ctx.loaders.userById.load(order.buyerId),
    items: (order, _args, ctx) => ctx.loaders.itemsByOrderId.load(order.id),
  },
};

// 一对多：按外键分组返回
new DataLoader<string, OrderItem[]>(async (orderIds) => {
  const items = await prisma.orderItem.findMany({
    where: { orderId: { in: [...orderIds] } },
  });
  const grouped = new Map<string, OrderItem[]>();
  for (const item of items) {
    if (!grouped.has(item.orderId)) grouped.set(item.orderId, []);
    grouped.get(item.orderId)!.push(item);
  }
  // 每个 key 都要有值（空数组而非 undefined）
  return orderIds.map((id) => grouped.get(id) ?? []);
});

// 顶层 mutation 开启事务，通过 context 传递 tx
const resolvers = {
  Mutation: {
    createOrder: async (_p, args, ctx) => {
      return ctx.prisma.$transaction(async (tx) => {
        const order = await tx.order.create({ data: { ...args.input } });
        await tx.inventory.updateMany({
          where: { sku: { in: args.input.items.map((i) => i.sku) } },
          data: { reserved: { increment: 1 } },
        });
        // 把 tx 挂到 context，供子 resolver 复用
        return orderService.create(tx, args.input);
      });
    },
  },
};

// 用 idempotency key 保证重试安全
async function createOrder(tx: Tx, input: CreateOrderInput, key: string) {
  const existing = await tx.idempotency.findUnique({ where: { key } });
  if (existing) return existing.result;   // 重复请求直接返回
  const order = await tx.order.create({ data: input });
  await tx.idempotency.create({ data: { key, result: order.id } });
  return order;
}

// Prisma 连接池（通过连接串参数）
// postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=10

// 无服务器环境用 Data Proxy / Accelerate 避免连接爆炸
const prisma = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL } },
});

# 每个 Lambda 实例一个池 → 连接数 = 并发实例数 × 池大小
# 解法：连接代理（PgBouncer / RDS Proxy / Prisma Accelerate）

// Prisma 中间件：记录每次查询，统计每请求查询数
prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const ms = Date.now() - start;
  metrics.dbQuery.observe({ model: params.model, action: params.action }, ms);
  return result;
});

-- 验证分页查询是否走索引
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE buyer_id = $1
ORDER BY created_at DESC
LIMIT 20;

-- 期望：Index Scan using idx_orders_buyer_created
-- 警惕：Seq Scan / Sort（内存排序）

// Drizzle 的 toSQL() 可在测试中断言生成的 SQL
const q = db.select().from(orders).where(eq(orders.buyerId, 'u-1')).limit(20);
console.log(q.toSQL());

// ❌ 内存分页
const all = await prisma.order.findMany();
return all.slice(offset, offset + limit);

// ✅ 下推 limit/offset
const page = await prisma.order.findMany({ skip: offset, take: limit });

// 游标分页：用 (created_at, id) 作为稳定排序键
const rows = await prisma.order.findMany({
  where: cursor
    ? {
        OR: [
          { createdAt: { lt: cursor.createdAt } },
          { createdAt: cursor.createdAt, id: { lt: cursor.id } },
        ],
      }
    : undefined,
  orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
  take: limit + 1,   // 多取一条判断 hasNextPage
});

// 永远不要把 GraphQL 参数直接拼进 where
// ❌ 注入风险 / 越权
db.user.findMany({ where: args.filter });

// ✅ 白名单化可过滤字段
const ALLOWED = new Set(['status', 'createdAt', 'total']);
const where = Object.fromEntries(
  Object.entries(args.filter ?? {}).filter(([k]) => ALLOWED.has(k)),
);