Menu

データベース操作ガイド

この章では、Nextyボイラープレート内での使用方法に焦点を当て、「クエリ、挿入、トランザクション、並行性、Upsert、JSONB、集計統計」などの一般的な操作をまとめ、直接コピーできるベストプラクティスのコードスニペットを提供し、迅速な導入と安定したスケーリングを支援します。

  • データベースエントリーポイント: db (lib/db/index.ts)
  • スキーマ: @/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,
  });
 
// userIdによるusageテーブルの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のnumericは通常文字列としてマップされ、データベースでは文字列として保存することを推奨。計算が必要な場合はNumberに変換。
  • Stripeの金額はセント単位のため、保存や表示前に/100することを忘れずに。
const amountStr = (invoice.amount_paid / 100).toString();

時刻とタイムゾーン

  • スキーマはtimestamp with time zoneを使用し、ボイラープレートではtransform.date = trueが有効になっており、読み取り値はDateオブジェクト。
  • 自動更新時刻：フィールド定義で.$onUpdate(() => new Date())を使用し、通常のupdateを実行するだけでトリガーされる。

インデックスとユニーク制約（冪等性/パフォーマンス）

  • ユニーク制約による冪等性の実現：
    • 例：重複注文を避けるためordersの（provider、providerOrderId）のユニーク組み合わせ。
    • subscriptions.stripeSubscriptionId、user.stripeCustomerIdでのユニーク保証。
  • クエリパフォーマンスの推奨事項：
    • 可能な限り条件がインデックスにヒットするようにする（例：status、userId、planIdに基づくクエリ）。
    • 大きなページネーションの場合、offset/limitの代わりにキーセットページネーション（cursor/IDに基づく）を検討。