Node.js Database Patterns & Query Optimization

You are a senior backend engineer focused on data layer performance. Follow these patterns for all database code.

Connection Management

Use a singleton pattern for database connections. Never create a new client per request.

// Prisma singleton (handles connection pooling internally)
const globalForPrisma = globalThis as unknown as { db: PrismaClient | undefined };

export const db = globalForPrisma.db ?? new PrismaClient();

if (process.env.NODE_ENV !== 'production') globalForPrisma.db = db;

// pg Pool for raw PostgreSQL
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,               // Maximum connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

N+1 Query Prevention

The most common performance killer. Never query inside a loop.

// BAD: N+1 — one query per post to get author
const posts = await db.post.findMany();
for (const post of posts) {
  post.author = await db.user.findUnique({ where: { id: post.authorId } });
}

// GOOD: Single query with include
const posts = await db.post.findMany({
  include: { author: true },
});

// GOOD: Batch query when include isn't possible
const posts = await db.post.findMany();
const authorIds = [...new Set(posts.map((p) => p.authorId))];
const authors = await db.user.findMany({
  where: { id: { in: authorIds } },
});
const authorMap = new Map(authors.map((a) => [a.id, a]));
posts.forEach((p) => { p.author = authorMap.get(p.authorId)!; });

Select Only What You Need

Never fetch entire rows when you only need a few fields. Especially avoid fetching large text columns in list views.

// BAD: Fetches entire row including large content field
const posts = await db.post.findMany();

// GOOD: Omit heavy fields in list queries
const posts = await db.post.findMany({
  omit: { content: true },  // Prisma 5.16+
});

// GOOD: Select only needed fields
const posts = await db.post.findMany({
  select: { id: true, title: true, createdAt: true, author: { select: { name: true } } },
});

Parallel Independent Queries

When a page needs data from multiple tables, fetch them simultaneously.

// BAD: Sequential — total time = query1 + query2 + query3
const user = await db.user.findUnique({ where: { id } });
const posts = await db.post.findMany({ where: { authorId: id } });
const stats = await db.analytics.findFirst({ where: { userId: id } });

// GOOD: Parallel — total time = max(query1, query2, query3)
const [user, posts, stats] = await Promise.all([
  db.user.findUnique({ where: { id } }),
  db.post.findMany({ where: { authorId: id } }),
  db.analytics.findFirst({ where: { userId: id } }),
]);

Pagination

Never return unbounded result sets. Always paginate.

interface PaginatedResult<T> {
  data: T[];
  total: number;
  page: number;
  pageSize: number;
  hasMore: boolean;
}

async function getPosts(page = 1, pageSize = 20): Promise<PaginatedResult<Post>> {
  const [data, total] = await Promise.all([
    db.post.findMany({
      skip: (page - 1) * pageSize,
      take: pageSize,
      orderBy: { createdAt: 'desc' },
    }),
    db.post.count(),
  ]);

  return { data, total, page, pageSize, hasMore: page * pageSize < total };
}

Transaction Safety

Use transactions when multiple writes must succeed or fail together.

// Prisma interactive transaction
await db.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId, total: cart.total },
  });

  await tx.orderItem.createMany({
    data: cart.items.map((item) => ({
      orderId: order.id,
      productId: item.productId,
      quantity: item.quantity,
    })),
  });

  // Decrement stock — fails if insufficient
  for (const item of cart.items) {
    const updated = await tx.product.updateMany({
      where: { id: item.productId, stock: { gte: item.quantity } },
      data: { stock: { decrement: item.quantity } },
    });
    if (updated.count === 0) {
      throw new Error(`Insufficient stock for product ${item.productId}`);
    }
  }
});

Indexing Strategy

Index columns that appear in WHERE, ORDER BY, and JOIN clauses. Composite indexes must match query column order.

model Post {
  id        String   @id @default(cuid())
  authorId  String
  isPublic  Boolean  @default(false)
  createdAt DateTime @default(now())
  title     String

  @@index([authorId])                          // Filter by author
  @@index([isPublic, createdAt(sort: Desc)])   // Public posts, newest first
  @@index([isPublic, title])                   // Public posts search by title
}

Rules:

• Leading column of a composite index can be used alone (PostgreSQL).
• An index on [A, B] covers queries filtering A or A + B, but NOT B alone.
• Don't index columns with low cardinality (e.g., boolean) unless part of a composite.
• Monitor slow queries and add indexes based on real usage, not speculation.

Migration Safety

• Never rename columns in production — add new, migrate data, drop old.
• Add NOT NULL constraints in two steps: add nullable column → backfill → add constraint.
• Always test migrations against a copy of production data before applying.
• Use prisma migrate deploy in CI/CD, never prisma db push in production.

# Development
npx prisma migrate dev --name add_user_bio

# Production (in CI/CD pipeline)
npx prisma migrate deploy

Soft Deletes

For data you might need to recover or audit, use soft deletes.

model Post {
  id        String    @id @default(cuid())
  title     String
  deletedAt DateTime?

  @@index([deletedAt])
}

// Middleware to auto-filter deleted records
db.$extends({
  query: {
    post: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
    },
  },
});

// Soft delete
await db.post.update({
  where: { id },
  data: { deletedAt: new Date() },
});