End of week 2 - giving the API its real shape with complex queries

The week 1 API is still at toy stage - separate, simple queries. Today you will give it a real working shape: an endpoint where many ideas come together at once - joins or includes, filters, calculations, pagination. Just like in a real project.

Today's goal - an order list endpoint

Build an order list where each order comes with its user's name, its items list and its total money. Along with it: filtering by status, a date range, and pagination. Just building this one endpoint will show you almost everything you learned this week being put to work:

• Fetching relations together - without N+1 (include or JOIN)
• Filters - WHERE conditions by status, date, user
• Total calculation - either SUM from the database, or a stored total field
• Checking whether the columns being filtered on have indexes
• The whole order-creation flow inside a transaction

// With Prisma, in one pull
const orders = await prisma.order.findMany({
  where: {
    status: status ?? undefined,
    createdAt: { gte: from, lte: to },
  },
  include: { user: { select: { name: true } }, items: true },
  orderBy: { createdAt: "desc" },
  skip: (page - 1) * limit,
  take: limit,
});

Notice - with select, only the user's name was fetched, not the whole user object. Do not fetch what you do not need - that is also part of performance. Finally, test yourself: will this endpoint survive 1 million orders? Are the indexes right? Did you count the number of queries?

The mistake everyone makes

Fetching all the data and filtering it in code. 'First bring all orders, then match status with JavaScript' - that works while data is small, and ends when it grows. Filtering always belongs to the database - that is why WHERE and $match exist.

Today's practice

Build the order list endpoint above completely - with filters, pagination, relations. Then say the week's three interview questions out loud: what is the N+1 query problem and how do you fix it, SQL vs NoSQL when to use which, what is the harm of too many indexes.

How to say it in an interview

Test yourself: 'In a complex query I first think about which data is needed together, I put filtering on the database side, I avoid N+1 by including relations, and I keep indexes on the columns being filtered on. Then I verify the guess with EXPLAIN or the query log.'