Database Integration: Prisma + PostgreSQL—The Complete Process

Q: What is the difference between prisma migrate dev and prisma db push ?

migrate dev generates traceable SQL migration files (suitable for team collaboration), while db push directly synchronizes the schema to the database (suitable for rapid prototyping; no history is retained). migrate deploy must be used in production environments.

Q: How do I ensure that database operations are transactional?

Prisma supports nested writes ( create: { post: { create: {...} } } ), which automatically wrap transactions. Use prisma.$transaction([...]) or prisma.$transaction(async (tx) => {...}) when you need to explicitly define a transaction.

Prisma is the most popular ORM in the Node.js ecosystem—it lets you write data models in TypeScript and automatically generates type-safe database clients.

1. What You'll Learn

Installing, Initializing, and Setting Up the Project Structure for Prisma ORM
Schema Data Modeling (User / Post / Comment Relationships)
Database Migration (prisma migrate dev) and Prisma Studio Visualization
Read data in the Server Component; write data in the Server Action
CRUD Operations in Route Handlers
Prisma Connection Pool Management and Next.js Compatibility

2. A True Story of a Full-Stack Engineer

(1) Pain Point: Using SQL alone causes a sharp drop in development efficiency

Bob is the Technical Lead of the TaskFlow team. The team uses native SQL to work with PostgreSQL:

"You have to write 20 lines of SQL template code for each API endpoint. With JOIN, it’s easy to miss fields in queries, and you have to manually maintain migration scripts when changing the table structure. The most frustrating part is that TypeScript types aren’t synchronized with the database fields—you only realize you’ve written the column names incorrectly at runtime."

Issue	Time Spent Per Week	Impact
Handwritten SQL Templates	8h	Repetitive Work
Debug type mismatch	4h	Runtime error
Manual migration script	3h	Prone to oversights
Documentation not up to date	2h	Newcomers take a while to get up to speed

(2) Prisma ORM's Solution

Define a model using Prisma Schema → Automatically generate TypeScript types → Type-safe CRUD.

PRISMA

// prisma/schema.prisma — Data Model as Documentation
model User {
  id        String   @id @default(cuid())
  name      String
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
}

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

(3) Revenue

Dimension	Handwritten SQL	Prisma
Code volume per API CRUD operation	25 lines	3 lines
Type Safety	❌ Manually Defined	✅ Automatically Generated
Migration Management	Manual SQL Files	`prisma migrate dev`
Development Experience	Switching IDEs/DB Clients	Embedded in Prisma Studio
Document Synchronization	Plug-ins	Schema as Document

3. Prisma Installation and Project Initialization

(1) Installation Process

BASH

# 1. Installation Prisma CLI and the client
npm install prisma @prisma/client --save-dev
# Or all at once
npx prisma init --datasource-provider postgresql

graph LR
    A[prisma init] --> B[Generate prisma/schema.prisma]
    A --> C[Generate .env DATABASE_URL]
    B --> D[Define the Data Model]
    D --> E[prisma migrate dev]
    E --> F[Generate Prisma Client]
    F --> G[Import and Use]

    style A fill:#cce5ff
    style D fill:#d4edda
    style F fill:#fff3cd

Generate File	Purpose
`prisma/schema.prisma`	Data Model Definition
`.env`	Database connection string (`DATABASE_URL`)

(2) Initialize the Client singleton

// lib/prisma.ts — Global Singleton(Prevent the creation of multiple connections during a hot reload)
import { PrismaClient } from '@prisma/client'

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

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

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

💡 Tip: Next.js hot reloading creates a new PrismaClient instance every time the page is refreshed. The global singleton pattern reuses existing connections in the development environment, preventing "Too many connections" errors.

▶ Example: Verifying the database connection

// app/api/health/route.ts
import { prisma } from '@/lib/prisma'
import { NextResponse } from 'next/server'

export async function GET() {
  try {
    await prisma.$connect()
    return NextResponse.json({ status: 'ok', db: 'connected' })
  } catch (e) {
    return NextResponse.json({ status: 'error', message: (e as Error).message }, { status: 500 })
  }
}

💻 Output:

JSON

{ "status": "ok", "db": "connected" }

4. Schema Data Modeling

(1) Model Relationship Types

graph TB
    User -->|One-to-many| Post
    User -->|One-to-many| Comment
    Post -->|One-to-many| Comment
    Post -->|Many-to-many| Tag

    subgraph User
        U1[id String @id @default(cuid())]
        U2[name String]
        U3[email String @unique]
    end
    subgraph Post
        P1[id String @id @default(cuid())]
        P2[title String]
        P3[content String?]
    end
    subgraph Comment
        C1[id String @id @default(cuid())]
        C2[body String]
    end
    subgraph Tag
        T1[id String @id @default(cuid())]
        T2[name String @unique]
    end

    style User fill:#d4edda
    style Post fill:#cce5ff
    style Comment fill:#f8d7da
    style Tag fill:#fff3cd

Relationship Type	Prisma Syntax	Database Implementation
One-to-one	`User Profile`	Foreign key + unique
One-to-many	`User Post[]`	Foreign key
Many-to-many	`Post Tag[]` (implicit)	Intermediate table `_PostToTag`
Self-reference	`Category parentCategory`	Self-referencing foreign key

(2) Complete TaskFlow Diagram

PRISMA

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

enum Role {
  ADMIN
  EDITOR
  VIEWER
}

model User {
  id        String    @id @default(cuid())
  name      String
  email     String    @unique
  role      Role      @default(VIEWER)
  password  String?
  posts     Post[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Post {
  id        String    @id @default(cuid())
  title     String
  content   String?
  published Boolean   @default(false)
  author    User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  tags      Tag[]
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([authorId])
}

model Comment {
  id        String   @id @default(cuid())
  body      String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    String
  author    String
  createdAt DateTime @default(now())

  @@index([postId])
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]

  @@index([name])
}

▶ Example: Prisma Studio Visualization Operations

BASH

# Start Prisma Studio(Browser GUI View/Edit Data)
npx prisma studio

TEXT

1. Run `npx prisma studio`
2. Open in a browser http://localhost:5555
3. Select from the left User / Post / Comment / Tag tables
4. Click "Add Record" Add Test Data
5. Click "Save Changes" Persistence

💡 Tip: Studio supports direct editing of related data; when you edit a User, you can view their Posts and Comments.

5. Database Migration

(1) Migration Workflow

Step	Command	Function
Modify Schema	Edit `schema.prisma`	Add or Remove Models/Fields
Create Migration	`npx prisma migrate dev --name add_user_role`	Generate SQL Migration File
Application Migration	Automation	Update Database Structure
Reset Database	`npx prisma migrate reset`	Clear Data + Re-migrate
Generate Client	`npx prisma generate`	Update TypeScript Types

(2) Migrating the File Structure

BASH

prisma/migrations/
├── 20260706000001_init/
│   └── migration.sql          # Creating an Initial Table
├── 20260706000002_add_user_role/
│   └── migration.sql          # ALTER TABLE Add role column
└── migration_lock.toml        # Database Provider Lock-in

▶ Example: Add Role Migration

BASH

npx prisma migrate dev --name add_role_enum

Generated SQL:

SQL

-- prisma/migrations/20260706000002_add_role_enum/migration.sql
-- CreateEnum
CREATE TYPE "Role" AS ENUM ('ADMIN', 'EDITOR', 'VIEWER');

-- AlterTable
ALTER TABLE "User" ADD COLUMN "role" "Role" NOT NULL DEFAULT 'VIEWER';

💡 Tip: prisma migrate dev automatically detects schema changes and generates the corresponding SQL statements. There is no need to manually write ALTER TABLE statements.

6. Hands-On CRUD Operations

(1) Server Component Reads Data

TSX

// app/posts/page.tsx — RSC Query the database directly
import { prisma } from '@/lib/prisma'

export default async function PostsPage() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: { author: { select: { name: true } }, tags: true },
    orderBy: { createdAt: 'desc' },
    take: 20
  })

  return (
    <div>
      {posts.map((post) => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>Author: {post.author.name}</p>
          <p>{post.tags.map((t) => t.name).join(', ')}</p>
        </article>
      ))}
    </div>
  )
}

(2) Server Action: Write Data

// app/actions/posts.ts — Server Action CRUD
'use server'

import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'
import { redirect } from 'next/navigation'

export async function createPost(data: { title: string; content?: string; authorId: string }) {
  const post = await prisma.post.create({ data })
  revalidatePath('/posts')
  redirect(`/posts/${post.id}`)
}

export async function deletePost(id: string) {
  await prisma.post.delete({ where: { id } })
  revalidatePath('/posts')
}

▶ Example: Creating an Article Using a Form and a Server Action

TSX

// app/posts/new/page.tsx
import { createPost } from '@/app/actions/posts'
import { auth } from '@/auth'

export default async function NewPostPage() {
  const session = await auth()
  if (!session?.user) return <p>Please log in first</p>

  return (
    <form action={createPost}>
      <input name="title" placeholder="Article Title" required />
      <textarea name="content" placeholder="Article Content" rows={10} />
      <input type="hidden" name="authorId" value={session.user.id} />
      <button type="submit">Published</button>
    </form>
  )
}

// Correction Server Action:Receive FormData
'use server'
import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'
import { redirect } from 'next/navigation'

export async function createPost(formData: FormData) {
  const title = formData.get('title') as string
  const content = formData.get('content') as string
  const authorId = formData.get('authorId') as string

  if (!title || !authorId) throw new Error('Required fields are missing')

  const post = await prisma.post.create({
    data: { title, content, authorId }
  })
  revalidatePath('/posts')
  redirect(`/posts/${post.id}`)
}

(3) Route Handler: Complete CRUD

// 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({
    include: { author: true, comments: true },
    orderBy: { createdAt: 'desc' }
  })
  return NextResponse.json(posts)
}

export async function POST(req: Request) {
  const body = await req.json()
  const post = await prisma.post.create({ data: body })
  return NextResponse.json(post, { status: 201 })
}

// app/api/posts/[id]/route.ts
import { prisma } from '@/lib/prisma'
import { NextRequest, NextResponse } from 'next/server'

export async function GET(req: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params
  const post = await prisma.post.findUnique({
    where: { id },
    include: { comments: true, tags: true }
  })
  if (!post) return NextResponse.json({ error: 'not found' }, { status: 404 })
  return NextResponse.json(post)
}

export async function PATCH(req: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params
  const body = await req.json()
  const post = await prisma.post.update({ where: { id }, data: body })
  return NextResponse.json(post)
}

export async function DELETE(req: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params
  await prisma.post.delete({ where: { id } })
  return NextResponse.json({ deleted: true })
}

7. Connection Pool Management

(1) Connection Pool Configuration

Configuration Option	Default Value	Production Recommendation	Description
`connection_limit`	10	20–50	Maximum concurrent connections
`pool_timeout`	10s	30s	Connection timeout
`idle_timeout`	10s	30s	Idle connection retention time

(2) Configure connection pool parameters

// lib/prisma.ts — Production-Grade Connection Pool
import { PrismaClient } from '@prisma/client'

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=20&pool_timeout=30&idle_timeout=30'
    }
  }
})

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

⚠️ Note: In serverless environments (Vercel), the number of database connections is limited. We recommend using Prisma Accelerate (connection pool proxy) or the Supabase connection pool (?pgbouncer=true).

▶ Example: Supabase Connection Pool Configuration

ENV

# .env — Supabase Connection Pool
DATABASE_URL="postgresql://postgres:password@db.xxxxx.supabase.co:6543/postgres?pgbouncer=true&connection_limit=5"

// Serverless Optimization:Reuse the instance for each request
import { PrismaClient } from '@prisma/client'

let prisma: PrismaClient

export function getPrisma() {
  if (!prisma) {
    prisma = new PrismaClient({
      datasources: { db: { url: process.env.DATABASE_URL } }
    })
  }
  return prisma
}

8. Complete Example: Comprehensive Implementation of a Blog System CRUD

TSX

// app/posts/[id]/page.tsx — Article Details + Comment Feature
import { prisma } from '@/lib/prisma'
import { notFound } from 'next/navigation'
import { auth } from '@/auth'
import { revalidatePath } from 'next/cache'
import { redirect } from 'next/navigation'

async function addComment(formData: FormData) {
  'use server'
  const session = await auth()
  if (!session?.user) throw new Error('Please log in first')

  const body = formData.get('body') as string
  const postId = formData.get('postId') as string

  if (!body || !postId) throw new Error('Missing field')

  await prisma.comment.create({
    data: { body, postId, author: session.user.name! }
  })
  revalidatePath(`/posts/${postId}`)
}

export default async function PostDetailPage({ params }: { params: Promise<{ id: string }> }) {
  const { id } = await params

  const post = await prisma.post.findUnique({
    where: { id },
    include: {
      author: { select: { name: true } },
      tags: true,
      comments: { orderBy: { createdAt: 'desc' } }
    }
  })

  if (!post) notFound()

  return (
    <div>
      <h1>{post.title}</h1>
      <p>Author: {post.author.name}</p>
      <div>{post.content}</div>
      <p>Tags: {post.tags.map((t) => t.name).join(', ')}</p>

      <hr />
      <h2>Comments ({post.comments.length})</h2>
      {post.comments.map((c) => (
        <div key={c.id}>
          <strong>{c.author}</strong>: {c.body}
        </div>
      ))}

      <form action={addComment}>
        <input type="hidden" name="postId" value={post.id} />
        <textarea name="body" placeholder="Write a Review..." rows={3} required />
        <button type="submit">Submit</button>
      </form>
    </div>
  )
}

💡 Tip: This example demonstrates both RSC data reading (prisma.post.findUnique) and Server Action writing (addComment) on a single page, using a full-stack Next.js + Prisma setup.

❓ FAQ

Q Which should I choose, Prisma or Drizzle ORM?

A Prisma is more beginner-friendly (declarative schema + Studio visualization + automatic migrations), while Drizzle is closer to SQL syntax and offers slightly better performance. This tutorial uses Prisma because it has the largest ecosystem (43k⭐) and comprehensive documentation.

Q Why use the global singleton pattern to create a PrismaClient?

A In Next.js development mode, hot reloading frequently creates new instances, which can cause the number of database connections to skyrocket. The global singleton caches the instance in globalThis, ensuring that only one PrismaClient is created.

Q What is the difference between prisma migrate dev and prisma db push?

A migrate dev generates traceable SQL migration files (suitable for team collaboration), while db push directly synchronizes the schema to the database (suitable for rapid prototyping; no history is retained). migrate deploy must be used in production environments.

Q Will querying the database directly from the Server Component cause performance issues?

A No. Since the RSC runs on the server, querying the database directly eliminates one HTTP round trip compared to going through an API route. This works even better when combined with Next.js data caching (fetch automatic caching).

Q How do I ensure that database operations are transactional?

A Prisma supports nested writes (create: { post: { create: {...} } }), which automatically wrap transactions. Use prisma.$transaction([...]) or prisma.$transaction(async (tx) => {...}) when you need to explicitly define a transaction.

📖 Summary

Prisma ORM uses Schema to declaratively define models and automatically generates a type-safe TypeScript client
Three relationship models: one-to-one, one-to-many (foreign key), and many-to-many (implicit intermediate table)
Migration Workflow: Modify Schema → migrate dev → Automatically Generate SQL → Update Client
Prisma Studio provides a browser-based GUI for viewing and editing data directly
CRUD operations can be flexibly used in RSC (read), Server Action (write), and Route Handler (API)
The global singleton pattern prevents connection leaks in the development environment; connection pool parameters must be added for serverless environments.

📝 Exercises

Basic Exercise (⭐): Initialize Prisma in an existing project, define a one-to-one relationship between the User and Profile models, run the migrations, insert a record, and validate it in Prisma Studio.
Advanced Exercise (⭐⭐): Implement a complete CRUD API route for the article system—GET (list + pagination), POST (create), PATCH (update), and DELETE (delete)—all using Prisma operations.
Challenge (⭐⭐⭐): Use Prisma $transaction to implement a feature that allows you to create an article and associate it with tags at the same time. If a tag does not exist, create the tag first, then establish a many-to-many relationship between the article and the tag.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

Database Integration: Prisma + PostgreSQL—The Complete Process

1. What You'll Learn

2. A True Story of a Full-Stack Engineer

(1) Pain Point: Using SQL alone causes a sharp drop in development efficiency

(2) Prisma ORM's Solution

(3) Revenue

3. Prisma Installation and Project Initialization

(1) Installation Process

(2) Initialize the Client singleton

▶ Example: Verifying the database connection

4. Schema Data Modeling

(1) Model Relationship Types

(2) Complete TaskFlow Diagram

▶ Example: Prisma Studio Visualization Operations

5. Database Migration

(1) Migration Workflow

(2) Migrating the File Structure

▶ Example: Add Role Migration

6. Hands-On CRUD Operations

(1) Server Component Reads Data

(2) Server Action: Write Data

▶ Example: Creating an Article Using a Form and a Server Action

(3) Route Handler: Complete CRUD

7. Connection Pool Management

(1) Connection Pool Configuration

(2) Configure connection pool parameters

▶ Example: Supabase Connection Pool Configuration

8. Complete Example: Comprehensive Implementation of a Blog System CRUD

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好