Menu

数据库操作指南

本文围绕 Nexty 模板涉及的用法，总结「查询、写入、事务、并发、Upsert、JSONB、聚合统计」等常见操作，提供可直接复制的最佳实践代码段，帮助你快速上手与稳定扩展。

  • 数据库入口：db（lib/db/index.ts）
  • Schema：@/lib/db/schema
  • 命令行：例如 npm run db:migrate、npm run db:studio）

获取 db 与 schema

import { db } from '@/lib/db';
import { user, orders, pricingPlans, usage, creditLogs } from '@/lib/db/schema';

读取/查询

基础查询（单条/列表/分页/排序/搜索）

import { eq, ilike, or, desc } from 'drizzle-orm';
 
// 单条
const one = await db.select().from(user).where(eq(user.id, userId)).limit(1);
 
// 列表 + 分页 + 排序 + 搜索
const pageIndex = 0, pageSize = 20, keyword = 'john';
const cond = keyword
  ? or(ilike(user.email, `%${keyword}%`), ilike(user.name, `%${keyword}%`))
  : undefined;
 
const users = await db
  .select()
  .from(user)
  .where(cond)
  .orderBy(desc(user.createdAt))
  .offset(pageIndex * pageSize)
  .limit(pageSize);
 
// 总数（用于分页）
const totalCount = (await db.select({ value: count() }).from(user).where(cond))[0].value;

关联查询（left join）

import { eq } from 'drizzle-orm';
 
const rows = await db
  .select({
    orderId: orders.id,
    planInterval: pricingPlans.recurringInterval,
    amount: orders.amountTotal,
  })
  .from(orders)
  .leftJoin(pricingPlans, eq(orders.planId, pricingPlans.id));

聚合统计（COUNT/SUM + FILTER，按日汇总）

import { sql, and, gte, lt } from 'drizzle-orm';
 
const start = new Date('2025-01-01');
const end = new Date('2025-02-01');
 
// 带 FILTER 的聚合
const stats = await db
  .select({
    oneTimeCount: sql`COUNT(*) FILTER (WHERE ${orders.orderType} = 'one_time_purchase')`.mapWith(Number),
    monthlyRevenue: sql`COALESCE(SUM(${orders.amountTotal}) FILTER (WHERE ${pricingPlans.recurringInterval} = 'month'), 0)`.mapWith(Number),
  })
  .from(orders)
  .leftJoin(pricingPlans, eq(orders.planId, pricingPlans.id))
  .where(and(gte(orders.createdAt, start), lt(orders.createdAt, end)));
 
// 按日聚合（date_trunc）
const dt = sql`date_trunc('day', ${orders.createdAt})`;
const daily = await db
  .select({ date: dt, count: count(orders.id) })
  .from(orders)
  .where(and(gte(orders.createdAt, start), lt(orders.createdAt, end)))
  .groupBy(dt);

写入/更新/删除

新增并取回主键

import { InferInsertModel } from 'drizzle-orm';
 
type NewOrder = InferInsertModel<typeof orders>;
 
const orderData: NewOrder = {
  userId,
  provider: 'stripe',
  providerOrderId: 'pi_123',
  status: 'succeeded',
  orderType: 'one_time_purchase',
  amountTotal: '9.90', // numeric 类型建议用字符串存储
  currency: 'usd',
};
 
const inserted = await db.insert(orders).values(orderData).returning({ id: orders.id });
const orderId = inserted[0]?.id;

更新

await db.update(user)
  .set({ banned: true, banReason: 'By admin', banExpires: null })
  .where(eq(user.id, userId));

删除

await db.delete(orders).where(eq(orders.id, orderId));

Upsert（幂等写入）

  • 典型场景：订阅同步、配额变更等「有则更新、无则插入」。
  • 依赖唯一键/唯一索引（例如 subscriptions.stripeSubscriptionId、usage.userId）。
// 订阅 upsert
const { stripeSubscriptionId, ...updateData } = subscriptionData;
await db.insert(subscriptions)
  .values(subscriptionData)
  .onConflictDoUpdate({
    target: subscriptions.stripeSubscriptionId,
    set: updateData,
  });
 
// usage 表按 userId upsert（累加/覆盖）
await db.insert(usage)
  .values({ userId, oneTimeCreditsBalance: add })
  .onConflictDoUpdate({
    target: usage.userId,
    set: { oneTimeCreditsBalance: sql`${usage.oneTimeCreditsBalance} + ${add}` },
  });

事务与并发控制

  • 场景：发放/撤销积分、扣减余额、同时写多表日志。
  • 建议：关键读操作使用行级锁，避免竞态；必要时做短暂重试。
await db.transaction(async (tx) => {
  // 行级锁，确保余额读取-更新一致
  const rows = await tx.select().from(usage).where(eq(usage.userId, userId)).for('update');
  const u = rows[0];
  if (!u) throw new Error('usage row not found');
 
  const newSub = Math.max(0, u.subscriptionCreditsBalance - toRevoke);
 
  await tx.update(usage)
    .set({ subscriptionCreditsBalance: newSub })
    .where(eq(usage.userId, userId));
 
  await tx.insert(creditLogs).values({
    userId,
    amount: -toRevoke,
    oneTimeBalanceAfter: u.oneTimeCreditsBalance,
    subscriptionBalanceAfter: newSub,
    type: 'subscription_cancel_revoke',
  });
});
 
// 简单重试（处理短暂锁冲突/网络抖动）
for (let attempts = 1; attempts <= 3; attempts++) {
  try {
    await db.transaction(/* ... */);
    break;
  } catch (e) {
    if (attempts === 3) throw e;
    await new Promise(r => setTimeout(r, attempts * 1000));
  }
}

JSONB 字段读写（合并/覆盖/删除键）

  • 场景：记录「月配额/年配额」明细、元数据扩展等。
  • 常用操作：先移除旧键再合并，避免脏数据残留。
// 覆盖 monthlyAllocationDetails 字段
const details = { monthlyAllocationDetails: { monthlyCredits: 1000 } };
await db.insert(usage)
  .values({ userId, subscriptionCreditsBalance: 1000, balanceJsonb: details })
  .onConflictDoUpdate({
    target: usage.userId,
    set: {
      subscriptionCreditsBalance: 1000,
      balanceJsonb: sql`coalesce(${usage.balanceJsonb}, '{}'::jsonb) - 'monthlyAllocationDetails' || ${JSON.stringify(details)}::jsonb`,
    },
  });
 
// 删除某个键（例如取消年配额信息）
await db.update(usage).set({
  balanceJsonb: sql`coalesce(${usage.balanceJsonb}, '{}'::jsonb) - 'yearlyAllocationDetails'`,
}).where(eq(usage.userId, userId));

金额与数值类型

  • numeric 在 Drizzle 中通常映射为字符串，建议以字符串写库；需要运算时再转成 Number。
  • Stripe 金额单位为「分」，记得 /100 后再存或展示。
const amountStr = (invoice.amount_paid / 100).toString();

时间与时区

  • Schema 使用 timestamp with time zone，模板已开启 transform.date = true，读出即为 Date 对象。
  • 自动更新时间：字段定义里使用 .$onUpdate(() => new Date())，只需正常 update 即可触发。

索引与唯一约束（幂等/性能）

  • 通过唯一约束实现幂等：
    • 例如 orders 上（provider, providerOrderId）唯一组合，避免重复插单。
    • subscriptions.stripeSubscriptionId、user.stripeCustomerId 唯一保证。
  • 查询性能建议：
    • 条件尽量命中索引（如基于 status、userId、planId 的查询）。
    • 大分页可考虑 keyset pagination（基于游标/ID）替代 offset/limit。