Database

database — the brief's nouns, typed

Stage: Phase 7 (Backend) - Reads: design/BRIEF.md, design/SITEMAP.md - Writes: db/schema.ts, db/index.ts, db/seed.ts, drizzle.config.ts, drizzle/ migrations

Standard

The schema reads like the brief: every table a noun from BRIEF.md, every column earning its place, relations explicit, timestamps everywhere. Queries run directly in server components or a thin db/queries.ts — never behind a fetch to your own API. The database rebuilds from the repo alone: drizzle-kit migrate + seed, and the site looks inhabited, not test-data-empty.

Process

1. Extract the nouns. "A course platform with instructors and reviews" → users, courses, enrollments, reviews. Zero persistent nouns → stop; a brochure site gets no database.
2. Install: npm i drizzle-orm @neondatabase/serverless and npm i -D drizzle-kit. Pin deliberately: npm latest is drizzle-orm 0.45.x while the official docs install @rc (1.0.0-rc). Choose 0.45.x for the stable line or the rc to match current docs — record the choice and reason in BRIEF.md's backend section, and pin drizzle-orm and drizzle-kit from the same line, never mixed.
3. Wire client and config (exact shapes below). DATABASE_URL from the Neon dashboard connection string into .env — exactly that file, and gitignored; the env-loading rules below explain why .env.local silently breaks drizzle-kit.
4. Write db/schema.ts: one pgTable per noun, relations, an index on every column you filter or join by.
5. Iterate with npx drizzle-kit push (schema straight to DB, no files) ONLY while the data is disposable. The moment data matters — and always before first deploy — switch to npx drizzle-kit generate (emits SQL) + npx drizzle-kit migrate (applies it), and commit drizzle/.
6. Write and run db/seed.ts. Verify with a real query (db.query.<table>.findMany()) before building UI on top.

Locked wiring

// db/index.ts
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./schema";

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

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql", // mandatory — config fails without it
  dbCredentials: { url: process.env.DATABASE_URL! },
});

Commands are drizzle-kit generate / migrate / push — the old generate:pg variants are dead. The neon-http driver is stateless one-shot HTTP: ideal for serverless/RSC reads and single-statement writes; for interactive multi-statement transactions verify the websocket driver against current docs first.

Env loading for drizzle-kit — the one real trap in this wiring. drizzle-kit bundles dotenv and auto-loads .env from the project root before evaluating drizzle.config.ts — verified empirically on both this stack's pinned lines — so the config above needs no dotenv dependency and no import "dotenv/config" (the official Drizzle+Neon guide carries an explicit config({ path: '.env' }) from the dotenv package; for the config file it is belt-and-braces, load-bearing only for standalone scripts run under bare tsx). What IS load-bearing on the verified pins: drizzle-kit reads .env and nothing else — not .env.local, no NODE_ENV variants, no --env-file flag (discussed upstream, never shipped; re-check on a future major). Next.js loads .env.local for app code, so a project keeping secrets there — the create-next-app convention — has exactly one blind tool: drizzle-kit sees DATABASE_URL as undefined and every kit command fails while the app itself runs fine. Pick one and record it:

• DATABASE_URL in .env (gitignored) and the auto-load does the rest — the wiring above as-is, and the default; or

• secrets stay in .env.local: load them with Next's own loader at the top of drizzle.config.ts — install it DECLARED, npm i -D @next/env (it resolves transitively from next today, but an undeclared import breaks under pnpm/Yarn PnP and Next layout changes; Next's own docs say install it for ORM/test config outside the Next runtime):

  import { loadEnvConfig } from "@next/env";
  loadEnvConfig(process.cwd(), process.env.NODE_ENV !== "production"); // dev=true → .env.development/.env.local semantics
  

  Full .env.local/NODE_ENV semantics, no coupling to drizzle-kit's private file layout. One caveat: don't keep the SAME key in both .env and .env.local — drizzle-kit's earlier bundled load already populated it, @next/env won't overwrite, and the two tools silently disagree.

Already-set process env always wins over the auto-loaded file, so CI- and host-provided variables are respected either way.

Schema rules

import { pgTable, text, integer, boolean, timestamp, uuid, index } from "drizzle-orm/pg-core";

export const courses = pgTable("courses", {
  id: uuid("id").defaultRandom().primaryKey(),
  slug: text("slug").notNull().unique(),
  title: text("title").notNull(),
  priceCents: integer("price_cents").notNull().default(0),
  published: boolean("published").notNull().default(false),
  authorId: text("author_id").notNull().references(() => users.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow().$onUpdate(() => new Date()),
}, (t) => [index("courses_author_idx").on(t.authorId)]);

• Money is integer cents. Never float.
• text over varchar(n) — Postgres treats them identically; length limits live in zod at the boundary, not in DDL.
• Every table gets createdAt with .defaultNow(); mutable tables add updatedAt with .$onUpdate().
• Anything routed by URL (SITEMAP.md dynamic segments) gets a unique slug.
• Closed value sets are pgEnum, not free text.
• Auth tables come from the Better Auth CLI (ultraweb:auth) — merge them into this schema file and migrate through this workflow: one schema, one migration history.

RSC query patterns

// app/courses/page.tsx — server component: no fetch, no API hop
import { db } from "@/db";

export default async function CoursesPage() {
  const courses = await db.query.courses.findMany({
    where: (c, { eq }) => eq(c.published, true),
    orderBy: (c, { desc }) => desc(c.createdAt),
    limit: 24,
  });
  // render
}

• Independent queries in one component go through Promise.all — serial awaits add a full round trip each.
• DB calls have no fetch-cache semantics; they run every request. To cache: cacheComponents: true in next.config, wrap the query function with 'use cache' + cacheTag("courses"), then revalidateTag("courses") after mutations (Next 16 also accepts a cacheLife profile as second arg).
• Detail pages: await params before touching the slug — params are Promises in Next 16.
• An empty result is a designed state (ultraweb:ui-states), never a blank div.

Seed script

// db/seed.ts — run with: npx tsx --env-file=.env db/seed.ts
import { db } from "./index";
import { courses } from "./schema";

await db.insert(courses).values([/* 8–12 rows per listed table */]);

Seed data is what gate-visual screenshots. Write it in the brief's voice: real-sounding names, plausible prices, varied lengths — one short title, one two-liner, so layouts prove they survive both. Lorem in the seed becomes lorem on the site; banned either way.

Anti-patterns

• from "pg", new Pool( — wrong driver; Neon serverless uses @neondatabase/serverless
• drizzle(sql) positional wiring from pre-2025 tutorials — the locked form is drizzle({ client: neon(url) })
• generate:pg, push:pg — dead drizzle-kit commands
• defineConfig without dialect: "postgresql" — hard error
• DATABASE_URL only in .env.local — drizzle-kit reads .env and nothing else; the config evaluates with undefined and every kit command dies while next dev runs fine
• drizzle-kit push against a database whose data you keep
• fetch("/api/ inside an RSC that owns the db — self-HTTP round trip
• real(, doublePrecision( for money
• Seed rows named "Test 1", "Foo", or lorem anything
• drizzle-orm from latest with drizzle-kit from rc (or vice versa) — one line, both packages

Worked example — Kaffeewerk Ost, single-origin shop schema

Moved to references/example.md — read only when this build's case is genuinely ambiguous; the sections above are the decision material.

Composes with

Moved to references/composes.md — the handoff map; load it when orchestrating this skill against its neighbors.