How To Build A CRUD App With NextJS And Prisma + PostgreSQL
Tags: webdev, nextjs, postgressql, javascript
Published: 2023-11-27 15:30:37

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.

- [Install PostgreSQL Using Docker Compose](https://dev.to/skipperhoa/install-postgresql-using-docker-compose-44jn)
- [Connecting To PostgreSQL Databases In Prisma](https://dev.to/skipperhoa/connecting-to-postgresql-databases-in-prisma-3f20)

Gitlab : [how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql](https://gitlab.com/skipperhoa/how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql)

Demo: 
{% youtube p6MdxupigsA %}

The Article : [How To Build A CRUD App With NextJS And Prisma + PostgreSQL](https://hoanguyenit.com/how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.html)

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

```yml
# 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:

```javascript
docker-compose up -d
docker-compose ps
```

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

![How To Build A CRUD App With NextJS And Prisma + PostgreSQL - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/5-how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.jpg)

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**

```javascript
npx create-next-app@latest
```
We choose the requirements we want

```javascript
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 ](https://dev.to/skipperhoa/create-a-project-with-nextjs-1gi6)

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.

```javascript
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

```javascript
// 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

```javascript
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.

```javascript
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](https://dev.to/skipperhoa/connecting-to-postgresql-databases-in-prisma-3f20)  

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

```
npx prisma studio
```

![How To Build A CRUD App With NextJS And Prisma + PostgreSQL](https://hoanguyenit.com/public/upload/images/6-how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.jpg)

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

```javascript
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** : 

```javascript
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](https://www.prisma.io/docs/concepts/components/prisma-client/crud)

```javascript
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** : 

```javascript
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_**

```javascript
'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] " 

```javascript
'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

```javascript
'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 

```javascript
'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 "

```javascript
'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
```
Okay to run the project you can use the following command

```javascript
npm run dev
```

To view the tables in postgresql you can run the following command:

```javascript
npx prisma studio
```
Demo:
![How To Build A CRUD App With NextJS And Prisma + PostgreSQL - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql(1).jpg)

![How To Build A CRUD App With NextJS And Prisma + PostgreSQL - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/2-how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.jpg)

![How To Build A CRUD App With NextJS And Prisma + PostgreSQL - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/3-how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.jpg)

![How To Build A CRUD App With NextJS And Prisma + PostgreSQL - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/4-how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.jpg)
The Article : [How To Build A CRUD App With NextJS And Prisma + PostgreSQL](https://hoanguyenit.com/how-to-build-a-crud-app-with-nextjs-and-prisma-postgresql.html)