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

In this tutorial, we'll build a full-stack CRUD (Create, Read, Update, Delete) application using Next.js 14, Prisma ORM, and PostgreSQL. We'll use Server Actions for data mutations and follow Next.js best practices.

Prerequisites

• Node.js installed on your machine
• Basic understanding of React and Next.js
• A code editor (VS Code recommended)

Step 1: Installing PostgreSQL
Download PostgreSQL for your operating system from postgresql.org

During installation:

• Remember the password you set for the postgres user
• Keep the default port (5432)
• Install the command line tools

Verify installation by opening Command Prompt/Terminal:

psql -U postgres
# Enter your password when prompted

Create a new database:

CREATE DATABASE mydb;
\q

Step 2: Setting Up Next.js Project
Create a new Next.js project:

npx create-next-app@latest user-management
cd user-management

Choose the following options:

• Would you like to use TypeScript? › No
• Would you like to use ESLint? › Yes
• Would you like to use Tailwind CSS? › Yes
• Would you like to use src/ directory? › Yes
• Would you like to use App Router? › Yes
• Would you like to customize the default import alias? › No

Install Prisma dependencies:

npm install prisma @prisma/client
npx prisma init

Step 3: Configure Prisma
Update your .env file:

DATABASE_URL="postgresql://postgres:yourpassword@localhost:5432/mydb?schema=public"

Replace yourpassword with your PostgreSQL password.

Create your Prisma schema in prisma/schema.prisma:

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

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

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

Push the schema to your database:

npx prisma db push

Step 4: Setting Up Server Actions
Create src/app/actions/users.js:

'use server'

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

export async function getUsers() {
  try {
    const users = await prisma.user.findMany()
    return { users }
  } catch (error) {
    return { error: 'Failed to fetch users' }
  }
}

export async function createUser(formData) {
  try {
    const name = formData.get('name')
    const email = formData.get('email')

    const user = await prisma.user.create({
      data: { name, email }
    })

    revalidatePath('/')
    return { user }
  } catch (error) {
    return { error: 'Failed to create user' }
  }
}

export async function updateUser(formData) {
  try {
    const id = parseInt(formData.get('id'))
    const name = formData.get('name')
    const email = formData.get('email')

    const user = await prisma.user.update({
      where: { id },
      data: { name, email }
    })

    revalidatePath('/')
    return { user }
  } catch (error) {
    return { error: 'Failed to update user' }
  }
}

export async function deleteUser(formData) {
  try {
    const id = parseInt(formData.get('id'))

    await prisma.user.delete({
      where: { id }
    })

    revalidatePath('/')
    return { success: true }
  } catch (error) {
    return { error: 'Failed to delete user' }
  }
}

Step 5: Create Components
Create src/app/components/UserForm.js:

'use client'

import { useFormStatus } from 'react-dom'
import { createUser } from '@/app/actions/users'

function SubmitButton() {
  const { pending } = useFormStatus()

  return (
    <button 
      type="submit"
      disabled={pending}
      className="bg-blue-500 text-white px-4 py-2 rounded hover:bg-blue-600 disabled:bg-blue-300"
    >
      {pending ? 'Adding...' : 'Add User'}
    </button>
  )
}

export default function UserForm() {
  return (
    <form action={createUser} className="space-y-4">
      <div>
        <label htmlFor="name" className="block mb-1">Name:</label>
        <input 
          type="text" 
          id="name" 
          name="name" 
          required
          className="border p-2 rounded w-full"
        />
      </div>
      <div>
        <label htmlFor="email" className="block mb-1">Email:</label>
        <input 
          type="email" 
          id="email" 
          name="email" 
          required
          className="border p-2 rounded w-full"
        />
      </div>
      <SubmitButton />
    </form>
  )
}

Create src/app/components/UserListItem.js:

'use client'

import { useState } from 'react'
import { updateUser, deleteUser } from '@/app/actions/users'
import { useFormStatus } from 'react-dom'

function Button({ children, ...props }) {
  const { pending } = useFormStatus()

  return (
    <button 
      {...props}
      disabled={pending}
      className={`px-3 py-1 rounded text-white disabled:opacity-50 ${props.className}`}
    >
      {pending ? 'Loading...' : children}
    </button>
  )
}

export default function UserListItem({ user }) {
  const [isEditing, setIsEditing] = useState(false)

  if (isEditing) {
    return (
      <li className="p-4 bg-gray-100 rounded">
        <form action={updateUser} className="space-y-2">
          <input type="hidden" name="id" value={user.id} />
          <input
            type="text"
            name="name"
            defaultValue={user.name}
            className="border p-2 rounded w-full"
            required
          />
          <input
            type="email"
            name="email"
            defaultValue={user.email}
            className="border p-2 rounded w-full"
            required
          />
          <div className="space-x-2">
            <Button 
              type="submit"
              className="bg-green-500 hover:bg-green-600"
            >
              Save
            </Button>
            <button
              type="button"
              onClick={() => setIsEditing(false)}
              className="bg-gray-500 text-white px-3 py-1 rounded hover:bg-gray-600"
            >
              Cancel
            </button>
          </div>
        </form>
      </li>
    )
  }

  return (
    <li className="p-4 bg-gray-100 rounded flex justify-between items-center">
      <div>
        {user.name} ({user.email})
      </div>
      <div className="space-x-2">
        <button
          onClick={() => setIsEditing(true)}
          className="bg-blue-500 text-white px-3 py-1 rounded hover:bg-blue-600"
        >
          Edit
        </button>
        <form action={deleteUser} className="inline">
          <input type="hidden" name="id" value={user.id} />
          <Button 
            type="submit"
            className="bg-red-500 hover:bg-red-600"
            onClick={(e) => {
              if (!confirm('Are you sure?')) {
                e.preventDefault()
              }
            }}
          >
            Delete
          </Button>
        </form>
      </div>
    </li>
  )
}

Step 6: Create Main Page
src/app/page.js:

import { getUsers } from './actions/users'
import UserForm from './components/UserForm'
import UserListItem from './components/UserListItem'

export default async function Home() {
  const { users, error } = await getUsers()

  if (error) {
    return <div className="p-4 text-red-500">Error: {error}</div>
  }

  return (
    <main className="p-4 max-w-4xl mx-auto">
      <h1 className="text-2xl font-bold mb-4">Users Management</h1>

      <div className="mb-8">
        <h2 className="text-xl mb-2">Current Users:</h2>
        {users?.length > 0 ? (
          <ul className="space-y-2">
            {users.map((user) => (
              <UserListItem key={user.id} user={user} />
            ))}
          </ul>
        ) : (
          <p className="text-gray-500">No users found.</p>
        )}
      </div>

      <div className="mt-8">
        <h2 className="text-xl mb-2">Add New User:</h2>
        <UserForm />
      </div>
    </main>
  )
}

Step 7: Create Prisma Instance
Create src/lib/prisma.js:

import { PrismaClient } from '@prisma/client'

const globalForPrisma = global

if (!globalForPrisma.prisma) {
  globalForPrisma.prisma = new PrismaClient()
}

export const prisma = globalForPrisma.prisma

Features Implemented

• Create new users with name and email
• Display list of all users
• Edit existing users with inline form
• Delete users with confirmation
• Server-side data validation
• Optimistic UI updates
• Loading states for all actions
• Error handling

Follow Github Code
https://github.com/abdur-rakib-rony/postgres-and-prisma-nextjs-crud-operation