Step 3 — API Route + Drizzle ORM

Time to wire the DB. PostgreSQL + Drizzle ORM, kept simple.

Install

pnpm add drizzle-orm postgres
pnpm add -D drizzle-kit

Schema

src/db/schema.ts:

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

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

Connection

src/db/index.ts:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client);

API Route

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

import { db } from "@/db";
import { posts } from "@/db/schema";
import { NextResponse } from "next/server";

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

export async function POST(req: Request) {
  const body = await req.json();
  const [row] = await db.insert(posts).values(body).returning();
  return NextResponse.json(row, { status: 201 });
}

Migrate

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! },
});

pnpm drizzle-kit push

Next

Step 4: Deploy →