Menu

Database Operations Guide

This chapter focuses on the usage within the Nexty boilerplate, summarizing common operations such as "queries, insert, transactions, concurrency, Upsert, JSONB, aggregation statistics" and providing best practice code snippets that can be directly copied to help you quickly get started and scale stably.

  • Database Entry Point: db (lib/db/index.ts)
  • Schema: @/lib/db/schema
  • Command Line: e.g., npm run db:migrate, npm run db:studio

Getting db and schema

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

Reading/Querying

Basic Queries (Single Record/Lists/Pagination/Sorting/Search)

import { eq, ilike, or, desc } from 'drizzle-orm';
 
// Single record
const one = await db.select().from(user).where(eq(user.id, userId)).limit(1);
 
// List + pagination + sorting + search
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);
 
// Total count (for pagination)
const totalCount = (await db.select({ value: count() }).from(user).where(cond))[0].value;

Relational Queries (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));

Aggregate Statistics (COUNT/SUM + FILTER, Daily Aggregation)

import { sql, and, gte, lt } from 'drizzle-orm';
 
const start = new Date('2025-01-01');
const end = new Date('2025-02-01');
 
// Aggregation with 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)));
 
// Daily aggregation (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);

Insert/Updating/Deleting

Insert and Return Primary Key

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 type recommended to use string storage
  currency: 'usd',
};
 
const inserted = await db.insert(orders).values(orderData).returning({ id: orders.id });
const orderId = inserted[0]?.id;

Update

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

Delete

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

Upsert (Idempotent Writing)

  • Typical scenarios: Subscription synchronization, quota changes, and other "update if exists, insert if not" operations.
  • Depends on unique keys/unique indexes (e.g., subscriptions.stripeSubscriptionId, usage.userId).
// Subscription upsert
const { stripeSubscriptionId, ...updateData } = subscriptionData;
await db.insert(subscriptions)
  .values(subscriptionData)
  .onConflictDoUpdate({
    target: subscriptions.stripeSubscriptionId,
    set: updateData,
  });
 
// usage table upsert by userId (accumulate/overwrite)
await db.insert(usage)
  .values({ userId, oneTimeCreditsBalance: add })
  .onConflictDoUpdate({
    target: usage.userId,
    set: { oneTimeCreditsBalance: sql`${usage.oneTimeCreditsBalance} + ${add}` },
  });

Transactions and Concurrency Control

  • Scenarios: Credit allocation/revocation, balance deduction, multi-table log writing.
  • Note: Use row-level locks for critical read operations to avoid race conditions; implement brief retries when necessary.
await db.transaction(async (tx) => {
  // Row-level lock to ensure consistent balance read-update
  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',
  });
});
 
// Simple retry (handling brief lock conflicts/network jitter)
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 Field Operations (Merge/Overwrite/Delete Keys)

  • Scenarios: Recording "monthly quota/yearly quota" details, metadata extensions, etc.
  • Common operations: Remove old keys first, then merge to avoid dirty data remnants.
// Overwrite monthlyAllocationDetails field
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`,
    },
  });
 
// Delete a specific key (e.g., cancel yearly allocation info)
await db.update(usage).set({
  balanceJsonb: sql`coalesce(${usage.balanceJsonb}, '{}'::jsonb) - 'yearlyAllocationDetails'`,
}).where(eq(usage.userId, userId));

Amount and Numeric Types

  • numeric in Drizzle is typically mapped as string, recommended to store as string in database; convert to Number when calculations are needed.
  • Stripe amounts are in cents, remember to /100 before storing or displaying.
const amountStr = (invoice.amount_paid / 100).toString();

Time and Timezone

  • Schema uses timestamp with time zone, boilerplate has transform.date = true enabled, read values are Date objects.
  • Auto-update time: Use .$onUpdate(() => new Date()) in field definition, just perform normal update to trigger.

Indexes and Unique Constraints (Idempotency/Performance)

  • Achieve idempotency through unique constraints:
    • E.g., unique combination of (provider, providerOrderId) on orders to avoid duplicate orders.
    • Unique guarantees on subscriptions.stripeSubscriptionId, user.stripeCustomerId.
  • Query performance recommendations:
    • Conditions should hit indexes when possible (e.g., queries based on status, userId, planId).
    • For large pagination, consider keyset pagination (based on cursor/ID) instead of offset/limit.