Step 3 — API Route + Drizzle ORM

DB wiring is the heart of fullstack. Drizzle ORM + PostgreSQL is type-safe and lightweight.

1. PostgreSQL

docker run --name pg-fullstack \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=my_app \
  -p 5432:5432 -d \
  postgres:16-alpine

Check:

docker exec -it pg-fullstack psql -U postgres -d my_app

2. Install

pnpm add drizzle-orm postgres
pnpm add -D drizzle-kit @types/pg

3. Env

.env.local:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/my_app

4. Schema (DDL)

src/db/schema.ts:

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

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  body: text("body").notNull(),
  published: boolean("published").notNull().default(false),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

TS types derived from DDL.

5. Singleton connection

src/db/index.ts:

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

const client = postgres(process.env.DATABASE_URL!, { max: 10, idle_timeout: 20 });
export const db = drizzle(client, { schema });

6. Migrations config

drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
  verbose: true,
  strict: true,
});

7. Generate · migrate

pnpm drizzle-kit generate
pnpm drizzle-kit migrate

In early dev you can skip files with:

pnpm drizzle-kit push

Use generate + migrate in production for history.

8. Studio

pnpm drizzle-kit studio

A browser GUI for the DB.

9. API route — GET / POST

src/app/api/posts/route.ts:

import { NextResponse } from "next/server";
import { db } from "@/db";
import { posts } from "@/db/schema";
import { desc } from "drizzle-orm";

export async function GET() {
  const rows = await db.select().from(posts).orderBy(desc(posts.created_at)).limit(20);
  return NextResponse.json(rows);
}

export async function POST(req: Request) {
  const body = await req.json();
  if (!body.title || !body.body) {
    return NextResponse.json({ error: "title/body required" }, { status: 400 });
  }
  const [row] = await db.insert(posts).values({ title: body.title, body: body.body }).returning();
  return NextResponse.json(row, { status: 201 });
}

10. Dynamic route

src/app/api/posts/[id]/route.ts:

import { NextResponse } from "next/server";
import { db } from "@/db";
import { posts } from "@/db/schema";
import { eq } from "drizzle-orm";

export async function GET(_req: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  const [row] = await db.select().from(posts).where(eq(posts.id, Number(id)));
  if (!row) return NextResponse.json({ error: "not found" }, { status: 404 });
  return NextResponse.json(row);
}

export async function DELETE(_req: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  await db.delete(posts).where(eq(posts.id, Number(id)));
  return NextResponse.json({ ok: true });
}

11. Call from client

"use client";
import { useEffect, useState } from "react";

type Post = { id: number; title: string; body: string };

export default function PostList() {
  const [posts, setPosts] = useState<Post[]>([]);
  useEffect(() => { fetch("/api/posts").then(r => r.json()).then(setPosts); }, []);
  return <ul>{posts.map(p => <li key={p.id}>{p.title}</li>)}</ul>;
}

From Server Components, call db.select() directly.

12. Transactions

await db.transaction(async (tx) => {
  await tx.insert(posts).values({ title: "A", body: "..." });
  await tx.insert(posts).values({ title: "B", body: "..." });
  // mid-failure → automatic ROLLBACK
});

13. Gotchas

• DATABASE_URL missing → restart dev server after .env edit
• Migration conflicts across branches → rebase and renumber
• Next 15+ params is a Promise
• Mutations in GET route → use POST/DELETE

Closing

Drizzle feels the most "TypeScript-native" among ORMs. Select results are fully typed, migrations + GUI + raw SQL coexist cleanly.

Next

• 04-deploy

References: Drizzle ORM · Next.js Route Handlers.