Building a Full-Stack App with Next.js, Prisma, and PostgreSQL

Modern full-stack development requires a seamless integration of frontend and backend technologies. In this guide, we'll walk through building a full-stack application using Next.js for the frontend, Prisma as an ORM (Object-Relational Mapping) tool, and PostgreSQL as the database. This stack offers a powerful combination of performance, type safety, and developer experience.

Why Next.js, Prisma, and PostgreSQL?

Next.js provides server-side rendering, API routes, and a seamless React-based frontend, making it an excellent choice for full-stack applications. Prisma simplifies database interactions with type-safe queries, migrations, and an intuitive schema definition. PostgreSQL, a robust relational database, ensures scalability and reliability for production applications.

Together, these tools enable:

• Type safety across the entire stack (TypeScript support).
• Efficient data fetching with Next.js API routes.
• Simplified database management with Prisma migrations.
• Scalability with PostgreSQL.

Setting Up the Project

First, create a new Next.js project with TypeScript:

npx create-next-app@latest fullstack-app --typescript  
cd fullstack-app

Next, install Prisma and initialize it:

npm install prisma @prisma/client  
npx prisma init

This creates a prisma directory with a schema.prisma file. Configure the database connection in prisma/schema.prisma:

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

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

Set up your PostgreSQL database (locally or via a cloud provider like Supabase or Neon) and update the .env file with your connection URL:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"

Defining the Data Model

Let’s create a simple blog application with Post and User models. Update prisma/schema.prisma:

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

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

Run the migration to create the database tables:

npx prisma migrate dev --name init

This generates SQL migrations and applies them to your PostgreSQL database.

Building the API with Next.js

Next.js API routes allow us to create backend endpoints easily. Let’s create a /api/posts endpoint to fetch and create posts.

Fetching Posts

Create pages/api/posts/index.ts:

import { PrismaClient } from '@prisma/client'  
import type { NextApiRequest, NextApiResponse } from 'next'  

const prisma = new PrismaClient()  

export default async function handler(  
  req: NextApiRequest,  
  res: NextApiResponse  
) {  
  if (req.method === 'GET') {  
    const posts = await prisma.post.findMany({  
      include: { author: true }  
    })  
    res.json(posts)  
  } else if (req.method === 'POST') {  
    const { title, content, authorId } = req.body  
    const post = await prisma.post.create({  
      data: {  
        title,  
        content,  
        authorId: Number(authorId)  
      }  
    })  
    res.json(post)  
  } else {  
    res.status(405).json({ message: 'Method not allowed' })  
  }  
}

Displaying Posts in the Frontend

Update pages/index.tsx to fetch and display posts:

import { useEffect, useState } from 'react'  

interface Post {  
  id: number  
  title: string  
  content: string | null  
  author: { name: string | null }  
}  

export default function Home() {  
  const [posts, setPosts] = useState<Post[]>([])  

  useEffect(() => {  
    fetch('/api/posts')  
      .then(res => res.json())  
      .then(data => setPosts(data))  
  }, [])  

  return (  
    <div>  
      <h1>Posts</h1>  
      <ul>  
        {posts.map(post => (  
          <li key={post.id}>  
            <h2>{post.title}</h2>  
            <p>{post.content}</p>  
            <p>By: {post.author.name}</p>  
          </li>  
        ))}  
      </ul>  
    </div>  
  )  
}

Adding Authentication (Optional)

For authentication, consider integrating NextAuth.js with Prisma. Install the required packages:

npm install next-auth @next-auth/prisma-adapter

Configure NextAuth in pages/api/auth/[...nextauth].ts:

import NextAuth from 'next-auth'  
import { PrismaAdapter } from '@next-auth/prisma-adapter'  
import { PrismaClient } from '@prisma/client'  

const prisma = new PrismaClient()  

export default NextAuth({  
  adapter: PrismaAdapter(prisma),  
  providers: [  
    // Configure providers (e.g., GitHub, Google, Email)  
  ],  
})

Conclusion

By combining Next.js, Prisma, and PostgreSQL, we’ve built a type-safe, scalable full-stack application with minimal boilerplate. Key takeaways:

1. Next.js API routes simplify backend logic.
2. Prisma provides a type-safe database layer with migrations.
3. PostgreSQL ensures reliability for production workloads.

To extend this project, consider adding:

• User authentication with NextAuth.js.
• Real-time updates with WebSockets or Server-Sent Events (SSE).
• Deployment on Vercel (Next.js) and a managed PostgreSQL service.

This stack empowers developers to build robust applications efficiently while maintaining type safety and scalability. Happy coding!