The Database: Drizzle ORM

Website Rebuild with Astro

Chapter 6 · The Database: Drizzle ORM

Chapter 4's own getBreadcrumb was planted deliberately — one database query per ancestor level. This chapter catches it, resolved via Drizzle's own relational query API.

Enabling Relational Queries

// db/client.ts
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

const connection = await mysql.createConnection(process.env.DATABASE_URL);
export const db = drizzle(connection, { schema });

Passing the full schema module — including Chapter 2's own pagesRelations — into drizzle() is what unlocks db.query.pages's own relational API, distinct from the plain db.select() query builder used so far.

Fixing the Breadcrumb

const pageWithAncestors = await db.query.pages.findFirst({
  where: eq(pages.id, pageId),
  with: {
    parent: {
      with: {
        parent: {
          with: {
            parent: {
              with: { parent: true }
            }
          }
        }
      }
    }
  }
});

with eagerly loads the parent chain in place of Chapter 4's own repeated single-row query loop, resolved in a small, bounded number of queries rather than one per level.

Verified Honestly Against Prisma

Not a coincidence — a documented design goal

Drizzle's own relational query API — the with keyword, the nested-object shape — deliberately mirrors Prisma's own include, both in naming and in structure. This is a real, stated design choice from Drizzle's own documentation: offer a developer experience close to Prisma's while keeping the underlying query builder closer to raw SQL. The similarity between with: { parent: true } here and Prisma's own include: { parent: true } from the Next.js rebuild's own database chapter is genuine kinship, not an accident.

The Same Shared Limitation, a Fifth Time

Fixed depth, exactly like every sibling ORM before it

The nested with call above only reaches four levels deep — no more. This is the identical limitation already established across every ORM in this series: Prisma's own nested include, Django's chained select_related, Eloquent's dotted with(), and ActiveRecord's nested includes() all share this exact same fixed-depth requirement for a self-referencing, arbitrary-depth tree. None of the five ORMs used across this entire Website Rebuild series can express "load the whole ancestor chain, however deep it is" without a raw recursive SQL query — a real, consistent, honestly-repeated finding, not a Drizzle-specific shortcoming.

Five ORMs' Eager Loading, Compared

Framework	Syntax
Next.js (Prisma)	`include: { parent: { include: { parent: true } } }`
Django	`select_related('parent__parent__parent')`
Laravel (Eloquent)	`with('parent.parent.parent')`
Rails (ActiveRecord)	`includes(parent: { parent: :parent })`
Astro (Drizzle)	`with: { parent: { with: { parent: true } } }`

Hands-On Exercises

Exercise 1

Configure db/client.ts to pass the full schema (including pagesRelations) into drizzle(), enabling db.query.pages's own relational API.

📄 View solution

Exercise 2

Replace PageLayout's own getBreadcrumb loop with a db.query.pages.findFirst({ with: ... }) call, and confirm the same correct ancestor chain still renders.

📄 View solution

Exercise 3

Store a page six levels deep and confirm the nested with clause from this chapter (only four levels) fails to load the full ancestor chain — demonstrating the shared fixed-depth limitation directly.

📄 View solution

Chapter 6 Quick Reference

drizzle(connection, { schema }) — enables db.query's own relational API
db.query.pages.findFirst({ with: ... }) — replaces the deliberately-planted N+1 loop
Verified kinship — Drizzle's with deliberately mirrors Prisma's own include, a documented design goal
Shared limitation, a fifth time — every ORM in this series needs a fixed-depth nested call; none expresses unbounded ancestor depth natively
Next chapter: Rendering Content & the Kanji Edge Case