Hòa Nguyễn Coder

Posted on Nov 27, 2023

How To Build A CRUD App With NextJS And Prisma + PostgreSQL

#webdev #nextjs #postgressql #javascript

Today I will make an example of CRUD in NextJS + Prisma + PostgreSQL . Recently, I also shared small things about how to use PostgreSQL by running Docker and how to use Prisma to connect to PostgreSQL , everyone can review it here.

Gitlab : how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql

Demo:

The Article : How To Build A CRUD App With NextJS And Prisma + PostgreSQL

Okay, let's start building

INSTALL POSTGRESQL USING DOCKER
I will briefly talk about using PostgreSQL in Docker. Because I have already shared the previous article, here I will just take the previous code and use it.

First you need to create a docker-compose.yml file, put the file anywhere, ask to cd to the directory containing that file and run docker-compose up -d , here I configure ( user , password , database ) so we can use Prisma connect Postgresql



# docker-compose.yml

version: '3.8'
services:
    postgres_db:
        image: postgres:13.5
        container_name: PostgresCount
        restart: always
        environment:
            - POSTGRES_USER=hoadev
            - POSTGRES_PASSWORD=hoadev123
            - POSTGRES_DB=hoadev_db
        volumes:
            - postgres_db:/var/lib/postgresql/data
        ports:
            - '5432:5432'
volumes:
    postgres_db:
        driver: local

To run the above file, you need to install Docker on your computer, then run the following:



docker-compose up -d
docker-compose ps

If you have Docker installed on Windows you can see the following image:

On Mackbook, you can also install Docker for management

If you don't want to use the management interface, you have to use the command ::), for example, use docker-compose ps to show a list of existing containers,...

That's it, running PostgreSQL is done

INSTALL A PROJECT NEXTJS



npx create-next-app@latest

We choose the requirements we want



What is your project named? my-app
Would you like to use TypeScript? No / Yes
Would you like to use ESLint? No / Yes
Would you like to use Tailwind CSS? No / Yes
Would you like to use `src/` directory? No / Yes
Would you like to use App Router? (recommended) No / Yes
Would you like to customize the default import alias? No / Yes
What import alias would you like configured? @/*

You can review how to create a Nextjs project: Create A Project With Next.Js

After we have created a project, we will continue to install Prisma

INSTALL PRISMA IN PROJECT NEXTJS
Okay, let's open the command line and run the commands below to install the prisma library and at the same time, the command below also configures the Prisma + PostgreSQL connection for us.



cd my-app
npm install typescript ts-node @types/node --save-dev
npx tsc --init
npm install prisma --save-dev
npx prisma init --datasource-provider postgresql

The code runs the above command, creating a prisma folder , you see in that folder there is a file named schema.prisma , you open the file and configure the Model tables for the database so that we can run the generate command later . It will create those tables to the database that we configured in postgresql



// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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?
  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
}

If you look at the file below, you will see env("DATABASE_URL") , we will see the .env file in our project directory, when we run the above commands it will also create the .env file available for us, open the file. And update your username, password, and database correctly. My example is the following link



DATABASE_URL="postgresql://hoadev:hoadev123@localhost:5432/hoadev_db?schema=public"

After configuring Prisma, it's time to run the generate command to let Prisma connect to PostgreSQL to create tables for us.



npx prisma migrate dev --name init

After running the above command you will see a migrations folder in the prisma folder , you can review the article here: Connecting To PostgreSQL Databases In Prisma

To know if there is a table in the database, you can use the database management interface in prisma as follows:



npx prisma studio

Okay, that's it, Prisma + Postgresql is done, next we install a few small messages in the nextjs project and we're done.

In our original my-app directory , run the command



npm install @prisma/client
npx prisma generate

With the two commands above, the first command helps us to be able to use prisma in Nextjs, the second command runs migration to properly configure our configuration application.

So the sword is delicious, Men ::)

Now it's time to configure each file in the nextjs project to be able to use Postgresql + Prisma

_lib/prisma.ts : Configure PrismaClient , to connect Prisma to NextJS, we use this file to use operations such as ( create , read , edit , delete ) in general, query statements to Postgresql
(route)/api/post/route.ts : build Methods, to request api, in this file we install 2 methods ( GET , POST ), used to get all data using GET method, also POST method we use to add a post
(route)/api/post/[id]/route.ts : As for this file, configure the methods ( GET , PUT , DELETE ) used to request API for functions (Read, Update, Delete)
(route)/post/page.tsx : build an interface to display all posts, now we need to request " api/post " (using " GET " method) to get all products
(route)/post/create/page.tsx : Used to add a new post, request " api/post " (use method " POST ")
(route)/post/edit/[id]/page.tsx : Displays post information according to the ID to be edited. We need to request " api/route/[id] " , for example: https://localhost:3000/api/post/123 , then continue to request " api/post/123 " (using the " PUT " method) to update the article
(route)/post/read/[id]/page.tsx : Read posts by ID, we need to request " api/post/[id] " using method " GET "
(route)/post/delete/[id]/page.tsx : Delete posts by ID, we need to request " api/post/[id] " using method " DELETE "

Above are comments on the files that we need to go through next in the Nextjs project

_lib/prisma.ts :



import { PrismaClient } from '@prisma/client';
let prisma: PrismaClient;
if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}
export default prisma;

(route)/api/post/route.ts : Configure methods (GET,POST), to get data information and add data, you will find yourself importing the _lib/prisma library , to use the functions (findMany, create ,...) in Prisma , you can see more here: https://www.prisma.io/docs/concepts/components/prisma-client/crud



import { NextRequest, NextResponse } from 'next/server';
import prisma from '../../../_lib/prisma';
export async function GET() {
    const posts = await prisma.post.findMany({
      /*   where: { published: true }, */
        include: {
          author: {
            select: { name: true },
          },
        },
      });

    return NextResponse.json(posts);
}

export async function POST(request: NextRequest) {
  const body = await request.json();
  const { title, content,published ,authorId} = body;

  const newPost = await prisma.post.create({
    data: {
      title: title,
      content: content,
      author: { connect: { id:  authorId } },

    },
    include: {
      author: true,
    },
  });
  return NextResponse.json({"success":1,"message":"create success","post":newPost});
}

(route)/api/post/[id]/route.ts :



import { NextRequest, NextResponse } from 'next/server';
import prisma from '../../../../_lib/prisma';
export async function GET(request : NextRequest,{ params }: { params: { id: number } }) {
    const id =  params.id
    if (!id) {
        return NextResponse.error("Missing 'id' parameter");
      }

      const post = await prisma.post.findUnique({
        where: {
          id: parseInt(id),
        },
        include: {
          author: {
            select: { name: true },
          },
        },
      });

      return NextResponse.json(post);
}
export async function PUT(request : NextRequest,{ params }: { params: { id: number } }) {
    const id =  params.id
    if (!id) {
      return NextResponse.error("Missing 'id' parameter");
    }

   const post = await prisma.post.findUnique({
      where: {
        id: parseInt(id),
      },
    }) 

    const { title, content } = await request.json();
     const updatedPost = await prisma.post.update({
      where: {
        id: parseInt(id),
      },
      data: {
        title: title,
        content: content,

      },
    });

    return NextResponse.json({success:1,"post":updatedPost,"message":"Update success"});
}

export async function DELETE(request : NextRequest,{ params }: { params: { id: number } }) {
  const id =  params.id
  if (!id) {
    return NextResponse.error("Missing 'id' parameter");
  }

 const deletePost = await prisma.post.delete({
    where: {
      id: parseInt(id),
    },
  }) 

  return NextResponse.json({success:1,"message":"Delete success"});
}

(route)/post/page.tsx : set up the page to display the list of posts, use request api/posts , to get the list of posts, below the code below, I use the swr library, You can install via npm install swr



'use client'
import Link from 'next/link';
import useSWR from 'swr';
const fetcher = (url: string) => fetch(url).then((res) => res.json());

const PostPage = () => {
    const { data: posts, error, isLoading } = useSWR<any>(`/api/posts`, fetcher);
    if(error) return <div>failed to load</div>
    if(isLoading) return <div>loading...</div>
    return (
        <div className="w-full max-w-5xl m-auto">
            <h1 className='text-3xl text-blue-500 text-center pt-10 font-bold underline'>List Posts</h1>
            <Link href="/post/create" className='text-xl text-blue-500 text-center p-1 font-bold underline'>Create Post</Link>
           <table className='w-full mt-10 border-separate border-spacing-2 border border-slate-400'>
                <tr>
                <th className='border border-slate-300 p-2'>ID</th>
                    <th className='border border-slate-300 p-2'>Title</th>
                    <th className='border border-slate-300 p-2'>Content</th>
                    <th className='border border-slate-300 p-2'>User</th>
                    <th className='border border-slate-300 p-2'>Pushlish</th>
                    <th className='border border-slate-300 p-2'>Modify</th>
                </tr>
                {posts?.map(post => 
                    {
                        return (
                            <tr>
                                <td className='border border-slate-300 p-2'>{post.id}</td>
                                <td className='border border-slate-300 p-2'>{post.title}</td>
                                <td className='border border-slate-300 p-2'>{post.content}</td>
                                <td className='border border-slate-300 p-2'>{post.author.name}</td>
                                <td className='border border-slate-300 p-2'>{post.published?"Success":"pending"}</td>
                                <td className='border border-slate-300 p-2 flex flex-row gap-2'>
                                    <Link href={`/post/edit/${post.id}`} className='bg-yellow-500 font-bold p-1 inline-block rounded-md text-white'>Edit</Link>
                                    <Link href={`/post/delete/${post.id}`} className='bg-red-500 font-bold p-1 inline-block rounded-md text-white'>Delete</Link>
                                    <Link href={`/post/read/${post.id}`} className='bg-blue-500 font-bold p-1 inline-block rounded-md text-white'>View</Link>
                                </td>
                            </tr>
                        )
                    }
                )}


           </table>
        </div>
    );

}

export default PostPage;

(route)/post/read/[id]/page.tsx : Display posts by ID, request " api/post/[id] "



'use client'
import React from 'react'
import useSWR from "swr";
const fetcher = (url: string) => fetch(url).then((res) => res.json());
const ReadPage = ({params} :{params:{id:number}}) => {
    const { data: post, error, isLoading } = useSWR<any>(`/api/posts/`+params.id, fetcher);
    if(error) return <div>failed to load</div>
    if(isLoading) return <div>loading...</div>
    return <div className="w-full max-w-5xl m-auto">
        <h1 className="text-3xl font-bold">Read Post</h1>
        <p className="text-2xl">{post?.title}</p>
        <p className="text-2xl">{post?.content}</p>
    </div>
}
export default ReadPage

(route)/post/edit/[id]/page.tsx : Displays the article that needs to be edited by ID, and at the same time, updates the article



'use client'
import React,{useEffect, useState} from 'react'
import { useRouter } from 'next/navigation'
import useSWR from "swr";
const fetcher = (url: string) => fetch(url).then((res) => res.json());
const EditPage = ({params} :{params:{id:number}}) => {
    const router = useRouter();
    const [title, setTitle] = useState('')
    const [content, setContent] = useState('')
    const { data: post, error, isLoading } = useSWR<any>(`/api/posts/`+params.id, fetcher);
    useEffect(()=>{
        if(post){
            setTitle(post.title);
            setContent(post.content);
        }
    },[post])
    const saveData = (e)=>{
        e.preventDefault();
        if(title!="" && content !=""){
            var data = {
                "title":title,
                "content":content
            }
            console.log(data);
            fetch(`/api/posts/`+params.id, {
                method: 'PUT',
                headers: {
                    'Content-Type': 'application/json',
                },
                body:JSON.stringify(data),
            })
            .then((response) => response.json())
            .then((data) => {
                if(data.success>0){
                    alert(data.message);
                    router.push('/post')
                }
            })
        }

    }
    if(error) return <div>failed to load</div>
    if(isLoading) return <div>loading...</div>
    return <div className="w-full max-w-5xl m-auto">
        <h1 className="text-3xl font-bold">Edit</h1>
        <form onSubmit={saveData}>
            <input type="text" name="title" id="title" className="border border-slate-300 p-1 m-1" value={title} onChange={e => setTitle(e.target.value)}/>
            <input type="text" name="content" id="content" className="border border-slate-300 p-1 m-1" value={content} onChange={e => setContent(e.target.value)}/>
            <input type="submit" value="submit" className="border border-slate-300 p-1 m-1" />
        </form>
    </div>
}
export default EditPage

(route)/post/create/page.tsx : Create form to add new posts



'use client'
import React,{ useState} from 'react'
import { useRouter } from 'next/navigation'
const CreatePage = ({params} :{params:{id:number}}) => {
    const route = useRouter()
    const [title, setTitle] = useState('')
    const [content, setContent] = useState('')
    const saveData = (e : any)=>{
        e.preventDefault();
        if(title!="" && content !=""){
            var data = {
                "title":title,
                "content":content,
                "published":true,
                "authorId":1
            }
            console.log(data);
            fetch(`/api/posts`, {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body:JSON.stringify(data),
            })
            .then((response) => response.json())
            .then((data) => {
                if(data.success>0){
                    alert(data.message);
                    route.push('/post')
                }
            })
        }

    }
    return <div className="w-full max-w-5xl m-auto">
        <h1 className="text-3xl font-bold">Create</h1>
        <form onSubmit={saveData}>
            <input type="text" name="title" id="title" className="border border-slate-300 p-1 m-1"  onChange={e => setTitle(e.target.value)}/>
            <input type="text" name="content" id="content" className="border border-slate-300 p-1 m-1"  onChange={e => setContent(e.target.value)}/>
            <input type="submit" value="submit" className="border border-slate-300 p-1 m-1" />
        </form>
    </div>
}
export default CreatePage

(route)/post/delete/[id]/page.tsx : Display posts to be deleted, by ID, request request to " api/posts/[id] " , using method " DELETE "



'use client'
import React from 'react'
import useSWR from "swr";
import { useRouter } from 'next/navigation'
const fetcher = (url: string) => fetch(url).then((res) => res.json());
const ReadPage = ({params} :{params:{id:number}}) => {
    const router = useRouter();
    const { data: post, error, isLoading } = useSWR<any>(`/api/posts/`+params.id, fetcher);
    const deletePost = (id)=>{
        fetch(`/api/posts/`+id, {
            method: 'DELETE',
        })
        .then((response) => response.json())
        .then((data) => {
            if(data.success>0){
                alert(data.message);
                router.push('/post')
            }
        })
    }
    if(error) return <div>failed to load</div>
    if(isLoading) return <div>loading...</div>
    return <div className="w-full max-w-5xl m-auto">
        <h1 className="text-3xl font-bold">Read Post</h1>
        <p className="text-2xl">{post?.title}</p>
        <p className="text-2xl">{post?.content}</p>
        <button className="bg-red-500 font-bold p-1 inline-block rounded-md text-white" onClick={()=>deletePost(params.id)}>Remove Post</button>
    </div>
}
export default ReadPage