ORM Integration and Query Optimization with Drizzle

Concept: type-safe access

SQL you can see, types you can trust

Drizzle describes tables in TypeScript and builds queries that read like the SQL they compile to. The schema is the single source of truth: row types are inferred from it, a typo in a column name is a compile error, and Drizzle Kit turns schema changes into migrations.

In Next.js, Server Components and Server Actions can run those queries directly, so the browser never makes a second API request just to learn what the server already knew.

Like a kitchen's ordering system: it speaks the warehouse's language for you, but it won't tell you that you ordered the whole pallet.

Concept: selective queries

Ask for the fields you render, and no more

db.select() with no argument returns every column, including the long text the page never shows. Naming fields keeps rows small, where() filters in the database rather than in JavaScript, and limit() bounds the result so a table that grows tenfold doesn't make the page ten times heavier:

const [show] = await db
  .select({
    id: shows.id,
    name: shows.name,
    producerName: producers.name,
  })
  .from(shows)
  .innerJoin(producers, eq(shows.producerId, producers.id))
  .where(eq(shows.id, showId))
  .limit(1);

For lists that keep growing, prefer cursor pagination over a large offset(): the database can seek straight to the cursor instead of counting past every skipped row. And an index on the columns you filter and sort by — declared in the schema, like episodes_show_updated_idx in the demo — lets Postgres read rows already in order.

Concept: N+1

A query inside a loop is a round trip per item

The classic mistake is fetching a list, then looking up something related for each row. Every lookup is its own network hop to the database, and the cost grows with the list. A join brings the related data back in the same statement:

/* One statement per episode, plus the list itself */
for (const episode of episodes) {
  const [editor] = await db
    .select()
    .from(producers)
    .where(eq(producers.id, episode.editorId));
}

/* One statement in total */
await db
  .select({ title: episodes.title, editorName: producers.name })
  .from(episodes)
  .leftJoin(producers, eq(episodes.editorId, producers.id));

Drizzle's relational API, db.query.shows.findFirst({ with: { ... } }), loads nested relations without hand-written joins, and batching with inArray() covers the cases a join doesn't fit. Either way, the type system won't stop the loop version from compiling.

Accuracy note: Drizzle gives you type safety and SQL-shaped queries. It does not prevent N+1, choose indexes, or cache results — those are still your decisions.

Concept: connections

Serverless functions can't each hold a connection

Postgres connections are limited, and a burst of serverless invocations can exhaust them. With Neon, the HTTP driver sends each query as a request and holds no connection, which suits a function that runs one or two statements and exits. When you need an interactive transaction, the WebSocket driver with a pool does:

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";

export const db = drizzle(neon(process.env.DATABASE_URL!), { schema });

Pick the driver by what the code needs — one-shot reads or multi-statement transactions — not by habit.

Concept: caching above the query

Memoize within a request, cache across requests

Two components that need the same record shouldn't query for it twice. React's cache() shares one result across a single server render. With Cache Components, 'use cache' keeps a result across requests, cacheLife() sets how long, and cacheTag() labels it so a write can expire it with updateTag():

async function getCachedShowOverview(showId: string) {
  "use cache";
  cacheLife("hours");
  cacheTag("show");

  return getShowOverview(showId);
}

export const getShowOverviewCached = cache(getCachedShowOverview);

Keep user-specific data out of a shared entry unless the cache key scopes it to that user. Everything on the demo board is the same for every visitor, which is what makes sharing it safe.

Concept: layers

Repositories hold queries, services hold rules

Scattering SQL through components couples the UI to the schema. Instead, a repository owns the Drizzle queries, a service owns business rules and caching policy, and components just call the service. Writes that arrive through the GraphQL endpoint reach the same services and repositories, so authorization and rules live in one place.

Server Components
        ↓
Application services   (rules, caching)
        ↓
Repositories           (Drizzle queries)
        ↓
Drizzle ORM
        ↓
Neon Postgres

Then measure. Slow-query logs and EXPLAIN plans show what the database actually does; guessing from the TypeScript rarely does.

Implementation example

The podcast network demo's structure

The queries are real Drizzle, compiled to real SQL. Only the far end is stood in for: Drizzle's pg-proxy driver hands each statement to a small in-memory interpreter instead of a database, so the page can count genuine statements and run anywhere without credentials.

app/patterns/drizzle-query-optimization/
├── schema.ts          ← demo tables + composite index (no migration)
├── types.ts           ← shared row and result types
├── data.ts            ← fixtures, recording pg-proxy client
├── repository.ts      ← getShow (selective) and getShowNaive
├── service.ts         ← rules, 'use cache' + cache() wrappers
└── show/
    ├── layout.tsx     ← static shell: page chrome, no data access
    ├── page.tsx       ← cached board + strategy comparison
    └── actions.ts     ← Server Action calling updateTag("show")

components/patterns/drizzle-query-optimization/
├── show-overview.tsx      (header, shares the memoized lookup)
├── episode-list.tsx       (list, shares the memoized lookup)
├── query-comparison.tsx   (naive vs selective, real SQL shown)
├── format-sql.ts          (readable SQL for the panel)
└── show-controls.tsx      (publish-an-episode form)

There are three important pieces here.

The comparison panel is measured, not described. Both strategies return the same board; the naive one sends a statement per episode with an editor and pulls columns nothing renders.

The board is cached and tagged. Its revision only changes after you publish an episode, which expires the show tag.

Components never see Drizzle. They call the service, which calls the repository — swap the demo driver for Neon and nothing above the repository changes.

Publish a few episodes on the live demo: the board's revision climbs while the statement counts in the comparison panel stay put.