Data Access Patterns

One Database Client, Built for Serverless

db/index.ts is short, and every line in it is there for a reason:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is not set");
}

const client = postgres(connectionString, { prepare: false });
export const db = drizzle(client, { schema });

{ prepare: false } is not a default left in by accident -- it is required when connecting through Supabase's pooled "Transaction mode" connection string (pgbouncer), which does not support prepared statements. Without it, queries fail with errors that do not obviously point back to the connection mode. Passing schema into drizzle() is what enables Drizzle's relational query API and keeps full TypeScript inference flowing from the schema in lesson 6 through every query that imports db from this one file. Every other file in the app imports this same db instance -- there is exactly one place a connection is configured.

Reusable Reads: db/queries.ts

Read logic lives in named, typed functions instead of being inlined into every page that needs data. getReviewsForBusiness takes a business id and an optional filter/sort object, builds its WHERE conditions dynamically, and only adds a condition when the caller actually asked for one:

export async function getReviewsForBusiness(
  businessId: string,
  filters: { status?: ReviewStatus; source?: ReviewSource; sort?: ReviewSort } = {},
): Promise {
  const conditions = [eq(reviews.businessId, businessId)];
  if (filters.status) conditions.push(eq(reviews.status, filters.status));
  if (filters.source) conditions.push(eq(reviews.source, filters.source));

  const orderBy = {
    newest: desc(reviews.postedAt),
    oldest: asc(reviews.postedAt),
    rating_asc: asc(reviews.rating),
    rating_desc: desc(reviews.rating),
  }[filters.sort ?? "newest"];

  return db.select().from(reviews).where(and(...conditions)).orderBy(orderBy);
}

Notice businessId is always the first condition, unconditionally -- every call to this function is already scoped to one business before any optional filter is considered. That ordering is not stylistic; it is the same authorization discipline lesson 5 and lesson 6 established, applied at the query layer: a caller cannot accidentally fetch another business's reviews by manipulating optional filters, because the mandatory scope is baked into the function's signature, not left to the caller to remember.

getReviewStats shows Drizzle dropping to raw SQL fragments via the sql template tag for aggregates Drizzle's query builder does not have first-class helpers for:

export async function getReviewStats(businessId: string): Promise {
  const [row] = await db
    .select({
      total: sql`count(*)::int`,
      averageRating: sql`coalesce(avg(${reviews.rating}), 0)::float`,
      newCount: sql`count(*) filter (where ${reviews.status} = 'new')::int`,
      flaggedCount: sql`count(*) filter (where ${reviews.status} = 'flagged')::int`,
    })
    .from(reviews)
    .where(eq(reviews.businessId, businessId));

  return row ?? { total: 0, averageRating: 0, newCount: 0, flaggedCount: 0 };
}

This still goes through Drizzle's query builder and parameter binding -- the sql tag interpolates schema columns safely, it is not string concatenation. The fallback object on the last line matters too: if a business has zero reviews, the aggregate query still returns one row (Postgres's COUNT and COALESCE guarantee that), but the explicit fallback documents the zero-state instead of leaving it to be inferred from SQL behavior.

Mutations Go Through Server Actions, Not Direct Calls

Reads are plain async functions called from Server Components. Writes are different: every mutation in this app is a Server Action (the "use server" directive from lesson 5) that repeats the same three-step shape every time. app/dashboard/reviews/[id]/actions.ts:

export async function respondToReview(
  reviewId: string,
  _prevState: RespondFormState,
  formData: FormData,
): Promise {
  const user = await getCurrentUser();
  if (!user) return { error: "You must be signed in." };

  const parsed = respondSchema.safeParse({ body: formData.get("body") });
  if (!parsed.success) {
    return { error: parsed.error.issues[0]?.message ?? "Invalid input" };
  }

  // Authorization boundary: confirm this review belongs to a business
  // owned by the current user before allowing a response.
  const [row] = await db
    .select({ reviewId: reviews.id, businessOwnerId: businesses.ownerId })
    .from(reviews)
    .innerJoin(businesses, eq(reviews.businessId, businesses.id))
    .where(eq(reviews.id, reviewId))
    .limit(1);

  if (!row || row.businessOwnerId !== user.id) {
    return { error: "Review not found." };
  }

  await db.insert(responses).values({ reviewId, body: parsed.data.body });
  await db.update(reviews).set({ status: "responded" }).where(eq(reviews.id, reviewId));

  revalidatePath(`/dashboard/reviews/${reviewId}`);
  revalidatePath("/dashboard");
  return { success: true };
}

Authenticate, validate, authorize, write, revalidate -- in that order, every time. The authorization step is the one most worth dwelling on: it does not just check "does this review id exist," it joins through reviews to businesses and confirms businessOwnerId === user.id. A signed-in user guessing or enumerating another business's review UUIDs gets the same generic "Review not found" response a truly invalid id would get -- the error message deliberately does not reveal whether the review exists but belongs to someone else.

Write a Server Action that lets a signed-in user flag a review, following the same pattern as respondToReview: authenticate, then join through to confirm business ownership, then write.

setReviewStatus repeats this exact shape for a smaller mutation, which is worth noticing on its own: even a one-line status update gets the full authenticate-then-authorize treatment, not a shortcut because it "seems low risk." Authorization checks that are conditional on perceived risk are the ones that get skipped under deadline pressure.

revalidatePath: Telling Next.js What's Stale

Both mutations end with two revalidatePath calls -- one for the specific review's detail page, one for the dashboard list. Server Actions in the App Router do not automatically know which cached pages a write affects; you tell Next.js explicitly which paths just became stale so the next render picks up fresh data instead of a cached one. Forgetting this call is a common, quiet bug: the write succeeds, the database is correct, and the UI still shows the old state until an unrelated navigation happens to refetch it.

What You Now Have

Auth from lesson 5, a schema from lesson 6, and now a read/write layer that enforces ownership on every query and mutation -- together these are the full data foundation the rest of the build sits on. Every API route and every page from here forward calls into db/queries.ts or follows the Server Action pattern from this lesson; none of them talk to the database directly. Lesson 8 builds the core feature -- the review dashboard itself -- on top of exactly this layer.