Day 3: Prisma ORM: Database Integration | Next.js Full-Stack

What You'll Build

A Next.js app backed by a real SQLite database (upgradeable to PostgreSQL), with Prisma models for users and posts, full CRUD API routes, and type-safe queries throughout.

Section 1 · 10 min

Set Up Prisma

Prisma is the TypeScript-first ORM. You define your database schema in a .prisma file, run migrations, and Prisma generates a fully typed client. You get autocomplete on every query.

bashterminal

# Install Prisma
npm install prisma @prisma/client
npx prisma init --datasource-provider sqlite
# Creates: prisma/schema.prisma and .env

textprisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

bashterminal

# Run migration (creates the database tables)
npx prisma migrate dev --name init

# View your DB in the browser
npx prisma studio

Section 2 · 10 min

The Prisma Client Singleton Pattern

In Next.js dev mode, hot reloading creates new Prisma connections on every reload. This exhausts your connection pool. Use a singleton pattern to reuse the client.

typescriptsrc/lib/prisma.ts

import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma =
  globalForPrisma.prisma ?? new PrismaClient()

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

Import this singleton everywhere instead of creating new PrismaClient() directly.

Section 3 · 10 min

CRUD with Prisma in Route Handlers

Prisma's query API is intuitive and fully typed. Every model gets findMany, findUnique, create, update, delete, and more.

typescriptsrc/app/api/posts/route.ts

import { prisma } from '@/lib/prisma'
import { NextResponse } from 'next/server'

export async function GET() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: { author: { select: { name: true } } },
    orderBy: { createdAt: 'desc' },
    take: 10
  })
  return NextResponse.json(posts)
}

export async function POST(request: Request) {
  const { title, content, authorId } = await request.json()

  const post = await prisma.post.create({
    data: { title, content, authorId, published: false },
    include: { author: true }
  })
  return NextResponse.json(post, { status: 201 })
}

Section 4 · 10 min

Seed Data and Database Utilities

A seed script populates your database with test data. Essential for development and for running tests in CI.

typescriptprisma/seed.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const user = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: { email: '[email protected]', name: 'Demo User' }
  })

  await prisma.post.createMany({
    data: [
      { title: 'Hello World', content: 'First post!', published: true, authorId: user.id },
      { title: 'Getting Started', content: 'Second post', published: true, authorId: user.id },
    ]
  })

  console.log('Seeded!')
}

main().catch(console.error).finally(() => prisma.$disconnect())

jsonpackage.json (add this)

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}
// Run with: npx prisma db seed

What You Learned Today

Defined a Prisma schema with User and Post models with a one-to-many relationship
Ran migrations to create database tables and explored data with Prisma Studio
Used the singleton pattern to avoid connection pool exhaustion in Next.js dev mode
Performed CRUD operations with findMany, create, and upsert in API route handlers

Your Challenge

Go Further on Your Own

Add pagination to your GET /api/posts endpoint using Prisma's skip and take
Migrate from SQLite to PostgreSQL by changing the provider and DATABASE_URL
Add a Prisma middleware that logs slow queries (over 100ms) to the console

Day 3 Complete

Nice work. Keep going.

Day 4 is ready when you are.

Continue to Day 4

← Day 2 Course Overview Day 4 →

Course Progress

60%

Want live instruction and hands-on projects? Join the AI bootcamp — 3 days, 5 cities.