Building Full-Stack Apps with Next.js, Prisma, and PostgreSQL
Next.js with the App Router lets you write server-side database logic directly alongside your UI, no separate backend required. Pair it with Prisma's type-safe ORM and you have a full-stack setup that is both productive and maintainable. This guide walks through the complete setup from schema to deployment.
Why Prisma?
Prisma solves three problems with database access in Node.js:
- Type safety: queries return fully typed objects matching your schema, no casting needed
- Migrations: schema changes are tracked in version-controlled migration files
- Readable query API: no raw SQL strings scattered through your codebase
Setup
npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql
This creates a prisma/ directory with schema.prisma and adds DATABASE_URL to your .env.
# .env
DATABASE_URL="postgresql://user:password@localhost:5432/myapp_dev"
For local development, the easiest PostgreSQL setup is Docker:
docker run --name myapp-pg \
-e POSTGRES_USER=user \
-e POSTGRES_PASSWORD=password \
-e POSTGRES_DB=myapp_dev \
-p 5432:5432 \
-d postgres:16-alpine
Define Your Schema
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id String @id @default(cuid())
email String @unique
name String?
image String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
articles Article[]
comments Comment[]
}
model Article {
id String @id @default(cuid())
title String
slug String @unique
abstract String
body String
topic String
published Boolean @default(false)
publishedAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
authorId String
author User @relation(fields: [authorId], references: [id])
tags Tag[]
comments Comment[]
@@index([topic])
@@index([published, publishedAt(sort: Desc)])
}
model Tag {
id String @id @default(cuid())
name String @unique
articles Article[]
}
model Comment {
id String @id @default(cuid())
body String
createdAt DateTime @default(now())
authorId String
author User @relation(fields: [authorId], references: [id])
articleId String
article Article @relation(fields: [articleId], references: [id], onDelete: Cascade)
}
Run your first migration:
npx prisma migrate dev --name init
This creates a SQL migration file in prisma/migrations/ and applies it to your database. Commit this file. It is the authoritative history of your schema.
After changing the schema, always run migrate dev to create a new migration rather than editing existing ones.
The Prisma Client Singleton
In Next.js, hot-reload during development creates multiple Prisma Client instances if you instantiate it naively. Use the singleton pattern:
// lib/db.ts
import { PrismaClient } from "@prisma/client";
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };
export const db =
globalForPrisma.prisma ??
new PrismaClient({
log: process.env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
});
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db;
Import db wherever you need database access:
import { db } from "@/lib/db";
Reading Data in Server Components
// app/articles/page.tsx
import { db } from "@/lib/db";
export default async function ArticlesPage({
searchParams,
}: {
searchParams: { topic?: string };
}) {
const articles = await db.article.findMany({
where: {
published: true,
topic: searchParams.topic ?? undefined,
},
include: {
author: { select: { name: true, image: true } },
tags: { select: { name: true } },
},
orderBy: { publishedAt: "desc" },
take: 20,
});
return (
<ul>
{articles.map(article => (
<li key={article.id}>
<h2>{article.title}</h2>
<p>By {article.author.name}</p>
<ul>
{article.tags.map(tag => (
<li key={tag.name}>{tag.name}</li>
))}
</ul>
</li>
))}
</ul>
);
}
Prisma's include and select keep your queries precise. You only fetch the columns you need.
Writing Data with Server Actions
Server Actions are async functions that run on the server, called directly from client components or forms. They replace API routes for most mutations.
// app/articles/actions.ts
"use server";
import { db } from "@/lib/db";
import { auth } from "@/auth";
import { revalidatePath } from "next/cache";
import { redirect } from "next/navigation";
import { z } from "zod";
const CreateArticleSchema = z.object({
title: z.string().min(5).max(200),
abstract: z.string().min(20).max(500),
body: z.string().min(100),
topic: z.string(),
tags: z.array(z.string()).max(5),
});
export async function createArticle(formData: FormData) {
const session = await auth();
if (!session?.user?.id) redirect("/login");
const parsed = CreateArticleSchema.safeParse({
title: formData.get("title"),
abstract: formData.get("abstract"),
body: formData.get("body"),
topic: formData.get("topic"),
tags: formData.getAll("tags"),
});
if (!parsed.success) {
return { error: parsed.error.flatten().fieldErrors };
}
const { title, abstract, body, topic, tags } = parsed.data;
const slug = title
.toLowerCase()
.replace(/[^a-z0-9]+/g, "-")
.replace(/(^-|-$)/g, "");
const article = await db.article.create({
data: {
title,
slug,
abstract,
body,
topic,
authorId: session.user.id,
tags: {
connectOrCreate: tags.map(name => ({
where: { name },
create: { name },
})),
},
},
});
revalidatePath("/articles"); // bust the articles list cache
redirect(`/articles/${article.slug}`);
}
Usage in a Client Component form:
"use client";
import { createArticle } from "@/app/articles/actions";
export function CreateArticleForm() {
return (
<form action={createArticle} className="flex flex-col gap-4">
<input name="title" placeholder="Title" required />
<textarea name="abstract" placeholder="Abstract" required />
<textarea name="body" placeholder="Body" required />
<select name="topic">
<option value="React">React</option>
<option value="Next.js">Next.js</option>
<option value="Python">Python</option>
</select>
<button type="submit">Publish</button>
</form>
);
}
Pagination with Cursor-Based Queries
Offset pagination (skip/take) is simple but gets slow on large tables. Cursor-based pagination scales better:
// lib/articles.ts
export async function getArticlesCursor(cursor?: string, limit = 10) {
const articles = await db.article.findMany({
take: limit + 1, // fetch one extra to know if there's a next page
cursor: cursor ? { id: cursor } : undefined,
skip: cursor ? 1 : 0, // skip the cursor itself
where: { published: true },
orderBy: { publishedAt: "desc" },
select: { id: true, title: true, slug: true, abstract: true, publishedAt: true },
});
const hasNextPage = articles.length > limit;
const items = hasNextPage ? articles.slice(0, -1) : articles;
const nextCursor = hasNextPage ? items[items.length - 1].id : null;
return { items, nextCursor };
}
Database Transactions
Use $transaction for operations that must succeed or fail together:
export async function publishArticle(articleId: string, authorId: string) {
return db.$transaction(async (tx) => {
const article = await tx.article.findUnique({ where: { id: articleId } });
if (!article) throw new Error("Article not found");
if (article.authorId !== authorId) throw new Error("Not authorised");
if (article.published) throw new Error("Already published");
const updated = await tx.article.update({
where: { id: articleId },
data: { published: true, publishedAt: new Date() },
});
await tx.user.update({
where: { id: authorId },
data: { articlesCount: { increment: 1 } },
});
return updated;
});
}
If any operation inside $transaction throws, everything rolls back automatically.
Seeding the Database
Create a seed script for development data:
// prisma/seed.ts
import { db } from "../lib/db";
async function main() {
const user = await db.user.upsert({
where: { email: "muneer@example.com" },
update: {},
create: { email: "muneer@example.com", name: "Muneer Ahmed" },
});
await db.article.createMany({
data: [
{
title: "Getting Started with Prisma",
slug: "getting-started-with-prisma",
abstract: "Learn how to set up Prisma with PostgreSQL.",
body: "...",
topic: "Next.js",
authorId: user.id,
published: true,
publishedAt: new Date(),
},
],
skipDuplicates: true,
});
console.log("Seeded successfully");
}
main()
.catch(console.error)
.finally(() => db.$disconnect());
Add the seed command to package.json:
{
"prisma": {
"seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
}
}
Run with:
npx prisma db seed
Deployment Checklist
# 1. Use connection pooling in production (PgBouncer or Prisma Accelerate)
DATABASE_URL="postgresql://...?pgbouncer=true&connection_limit=1"
# 2. Run migrations as part of your deploy pipeline (not on app startup)
npx prisma migrate deploy
# 3. Generate the client after install (Vercel does this automatically)
npx prisma generate
Add to package.json for Vercel/Railway:
{
"scripts": {
"build": "prisma generate && next build",
"db:migrate": "prisma migrate deploy"
}
}
Conclusion
Next.js + Prisma + PostgreSQL is a mature, production-ready full-stack stack. Prisma's type-safe query API eliminates a class of runtime errors, migrations keep your schema versioned alongside your code, and Server Actions let you write mutations with minimal boilerplate. The key habits: always migrate (never db push in production), use the singleton client pattern, validate all inputs in Server Actions before they touch the database, and use transactions for multi-step writes.
References: