ts-db-perf

TypeScript Database Optimization

Optimize: $ARGUMENTS

1. N+1 Query Elimination

The classic trap: fetching a list, then querying per item in a loop.

// N+1: one query for the list, one per item
const orders = await db.order.findMany();
for (const order of orders) {
  order.customer = await db.customer.findUnique({ where: { id: order.customerId } });
}

// Resolved: single query with join/include
const orders = await db.order.findMany({
  include: { customer: true },
});

If the ORM doesn't support include, use a WHERE id IN (...) or a JOIN.

2. Select Only What You Need

// Over-fetching
const users = await db.user.findMany();

// Tight select
const users = await db.user.findMany({
  select: { id: true, email: true },
});

Applies to raw SQL too -- avoid SELECT * when you only need a few columns.

3. Pagination

Always paginate list endpoints. Cursor-based for large/real-time datasets, offset-based for simple cases.

// Offset-based
const [data, total] = await Promise.all([
  db.user.findMany({ skip: (page - 1) * limit, take: limit }),
  db.user.count(),
]);

// Cursor-based (no count query, stable under writes)
const items = await db.message.findMany({
  take: limit,
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: "desc" },
});

4. Caching

Cache where data is read-heavy and stale reads are tolerable.

async function getExchangeRate(from: string, to: string): Promise<number> {
  const key = `rate:${from}:${to}`;
  const cached = await cache.get(key);
  if (cached !== null) return Number(cached);

  const rate = await fetchRateFromAPI(from, to);
  await cache.set(key, String(rate), { ttl: 60 }); // 60s TTL
  return rate;
}

For repository-level caching, wrap the lookup:

async findById(id: string): Promise<User | null> {
  const cached = await cache.get(`user:${id}`);
  if (cached) return JSON.parse(cached);

  const user = await db.user.findUnique({ where: { id } });
  if (user) await cache.set(`user:${id}`, JSON.stringify(user), { ttl: 300 });
  return user;
}