Working with databases in Next.js using Prisma

Next.js is a database-agnostic web-based framework. This way, you can use any database with Next.js. You can use ORM to model your database data structure when working with databases. Next.js uses Prisma as an ORM to manage your database with no hustle. This article will integrate Next.js with Prisma to build a post with the comments app.

For this article, it is helpful to have:

• Node.js installed on your computer.
• Prior knowledge of JavaScript.
• Basic understanding of Prisma ORM.
• Basic knowledge of how to create applications using Next.js

Setting up the application

In your preferred working directory, run the following command to bootstrap the application:

npx create-next-app posts_app

Note: The above command will create your Next.js using Yarn as the default package manager. To use an alternative package manager, use the following command:

• For NPM add --use-npm flag:

npx create-next-app --use-npm posts_app

• For pnpm add --use-pnpm flag:

npx create-next-app --use-pnpm posts_app

To test if the app is working as expected, navigate to the newly created directory:

cd posts_app

Start the development server:

npm run dev

Setting up Prisma

First, install the Prisma dependencies to your Next.js project:

npm install prisma --save-dev

To use Prisma, you need to initialize Prisma Client for your project. This will Bootstrap a basic Prisma setup: To do so, run the following command:

npx prisma init

The above command will create two files:

• prisma/schema.prisma: The main Prisma configuration file will host the database configuration.
• .env: Will host the database connection URL and other environment variables.

Connecting Prisma with your Database

Prisma supports databases such as PostgreSQL, MySQL, SQLite, SQL server, MongoDB, and cockroach DB. Prisma comes in handy when running such databases with your application. With just a few lines of code, you switch to any database of your choice without affecting your codebase. Let’s see how that works with Prisma.

Open .env your file. This contains the connection URL of your database. Note by default, Prisma creates PostgreSQL to connect to your Postgres database.

Now to connect to any database of your choice, all you need is the database connection URL that accesses your database server host. Each database has its connection URL format. Check this guide and learn how you would structure a URL for your database. Once you have your connection URL reader, use DATABASE_URL inside the .env file and paste it into your URL.

In this case, we will use MySQL database, so go ahead and add the URL as follows:

DATABASE_URL = "mysql://your_username:your_password@localhost:3306/your_db_name"

For example:

DATABASE_URL = "mysql://root:kimafro@localhost:3306/posts"

When using Prisma, you don’t have to create your_db_name. Just add it to the URL, and Prisma will auto-handle this.

Note: Because Prisma is an ORM, you can use any database. All you have to do is ensure the connection string reflects as such.

Next, navigate to the prisma/schema.prisma file. To use your database with Prisma, you need to set the provider of the data source block. A data source determines how Prisma connects your database. It takes the parameter provider of the Prisma schema. The provider defines the database you are using. Therefore, based on your connection string, the provider should reflect the database name you are using.

Check this guide and learn more about Prisma Providers.

Prisma comes with the postgresql as the default provider. In this case, we are using MySQL, and the provider should reflect as such:

// schema.prisma
datasource db {
  provider = "mysql"
  url     
= env("DATABASE_URL")
}

Creating Prisma Models

Models Represent the entities of your application domain. A model describes how data will be represented in your database. Prisma uses a Model to create a database, tables, and all the required fields. This way, you can create tables and insert fields in your databases without manually creating any tables and inserting fields in your databases. Prisma handles this for you and minimizes any error that you could unknowingly introduce when doing such operations manually.

In this guide, we will create two tables, Post and Comment. We will use a Prisma schema to describe these tables using a few lines of code. Define this database schema as below in the prisma/schema.prisma right after the datasource block:

model Post {
  id       
String    @id @default(cuid())
  title    
String
  content  
String?   @db.LongText
  published Boolean   @default(false)
  comments 
Comment[]

  @@map(name: "posts")
}

model Comment {
  id       
String  @id @default(cuid())
  content  
String?
  post     
Post?   @relation(fields: [postId], references: [id])
  postId   
String?
  published Boolean @default(false)
}

Let’s digest what’s happening here. From above, we will have two tables: Post and Comment. We are defining every field that each table will have. Using the Prisma schema, we can introduce the relationship between these tables.

The relations between the tables will be one-to-many relations. This means a Post can have more than one comment. This is denoted by the comments Comment[] field. It defines a relation type of another model.

Each comment can have one post. Therefore post Post? @relation(fields: [postId], references: [id]) field handles that. This mapping will be done on the postId field on the Post model referencing the id on the Comment model.

We will use this relation within Next.js and Prisma Client to create the application.

It’s good to note that there is a difference between NoSQL and a relational database enforcement integrity constraint. The Models can be slightly different if you use a NoSQL MongoDB database. MongoDB is JSON based, and it uses collections and documents. To better understand how to write MongoDB models using Prisma schemas, check out this guide and learn more.

Sync Database with Prisma Model

Once you have the Prisma Model ready, it’s time to Sync it to your database. Sync the database with the above-defined models by running this command:

npx prisma db push

Running prisma db push to turn your Prisma schema into a database schema.

You can confirm these changes right in your database.

Alternatively, you can visualize your database using Prisma Studio.

npx prisma studio

Connecting Prisma to Next.js

First, to connect to Prisma using Next.js, we need Prisma Client dependencies ready. This way, we can create a Next.js server to access Prisma. Install the Prisma client:

npm install @prisma/client

Generate the client from the Prisma schema file:

npx prisma generate

Once you run the above command, you can use Prisma Client in the Next.js code.

On the project root directory, create a lib folder. Inside it, create a prisma.js file. The file will host the client connection. Add the following to the file:

import { PrismaClient } from '@prisma/client';

let prisma;

if (process.env.NODE_ENV === 'production') {
    prisma = new PrismaClient();
} else {
    if (!global.prisma) {
        global.prisma = new PrismaClient();
    }
    prisma = global.prisma;
}

export default prisma;

Using this connection, we will access the Prisma client and perform database operations using Prisma as the ORM. Let’s node dive and consume this connection to the Next.js app.

Setting up Next.js Components

In the project’s root directory, create a components directory. Inside the components directory, create two files:

• Navbar.js : Navigation bar:

import React from 'react';
export default function Navbar() {
    return (
        <div>
            <nav className="navbar">
                <div className='navbar-brand'>
                    Posts App
                </div>
                <div className='nav-links'>
                    <ul>
                        <li>
                            <a href="/">Posts</a>
                        </li>
                        <li>
                            <a href="/drafts">Drafts</a>
                        </li>
                        <li>
                            <a href="/create">Add Post</a>
                        </li>
                    </ul>
                </div>
            </nav>
            <style jsx>{`
                    .navbar{
                        width:100%;
                        display:flex;
                        padding:10px;
                       
justify-content:space-between;
                        width:500px;
                        margin:0px auto;
                    }
                    .navbar-brand{
                        font-weight:bold;
                        padding-left:20px;
                        margin-top:14px;
                    }
                    .nav-links ul{
                        padding:0px;
                        list-style-type:none;
                        display:flex;
                    }
                    .nav-links ul li{
                        margin-right:15px;
                        margin-top:0px;
                        padding:0px;
                    }
                    `}</style>
        </div>
    );
}

• Layout.js : The general app layout:

import React from "react";
import Navbar from "./Navbar";

export default function Layout(props) {
    return (
        <div>
            <Navbar />
            <div className="container">
                {props.children}
            </div>
            <style jsx>{`
                    .container{
                        width:500px;
                        margin:10px auto;
                        padding:22px;
                    }
                    `}</style>
        </div>
    );
}

These components will allow us to navigate through the application front end and access different routes to the application. These include:

• "/" - Accessing the application home page
• `“/drafts``` - accessing added posted waiting to be published
• "post/create" - accessing a page for adding new posts.

Let’s now consume Prisma and perform database CRUD operations using Next.js.

Adding posts

Next.js allows you to create serverless API routes without creating an entire backend server. API routes provide a solution to build your API with Next.js. Next.js uses server-side bundles that treat server code as an API endpoint instead of a page. It does that using alias pages/api path. This way, any file under api will be treated as an endpoint. This provides an easy solution to build your API within the same codebase.

pages/api will create all API handler functions using Next.js. This handler will act as the API endpoint to excite Prisma CRUD methods.

Create a ‘post’ directory in the pages/api directory. Inside the post directory, create an index.js file. In this index.js, add the handler for adding posts as follows:

import prisma from '../../../lib/prisma'

export default async function handle(req, res) {
    const { title, content, published } = req.body;
    const result = await prisma.post.create({
        data: {
            title: title,
            content: content,
            published: published
        },
    });
    res.json(result);
}

First, import the Prisma connection from the lib/prisma.js file. To add data, use the create() method and add the data you want to send when executing the POST payload to the API request body.

In the pages directory, create a posts directory. Inside the pages/posts directory, create a create.js file. Here we will create the page for adding posts that map to the "/add-post" we created earlier.

In the create.js file:

• Import the necessary packages:

import React, { useState } from "react";
import Head from "next/head";
import Router from 'next/router';
import Layout from "../../components/Layout";

• Render a function with a form and its POST API handler:

export default function Create() {

    const [title, setTitle] = useState("");
    const [content, setContent] = useState("");
    const [error, setError] = useState("");
    const [message, setMessage] = useState("");

    const handleSubmit = async e => {
        e.preventDefault();
        setError("");
        setMessage("");
        if (title && content) {
            // send a request to the server.
            try {
                const body = { title, content, published: false };
                await fetch(`/api/post`, {
                    method: "POST",
                    headers: {"Content-Type": "application/json"},
                    body: JSON.stringify(body),
                });
                await Router.push("/drafts");
            } catch (error) {
                console.error(error);
            }
        } else {
            setError("All fields are required");
            return;
        }
    }

    return (
        <Layout>
            <Head>
                <title>Create Post</title>
            </Head>
            <div>
                <form onSubmit={handleSubmit}>
                    {
                        error ? (
                            <div className=" error form-group">
                                {error}
                            </div>
                        ) : null
                    }
                    {
                        message ? (
                            <div className= "message form-group">
                                {message}
                            </div>
                        ) : null
                    }
                    <div className="form-group">
                       
<label>Title</label>
                        <input type="text" name="title" placeholder="Title" value={title} onChange={(e) => setTitle(e.target.value)} />
                    </div>
                    <div className="form-group">
                       
<label>Content</label>
                        <textarea
                            cols={50}
                           
name= "content"
                           
placeholder= "Content"
                            rows={8}
                            value={content}
                            onChange={(e) => setContent(e.target.value)}
                        />
                    </div>
                    <div className="form-group">
                        <button type="submit">Add Post</button>
                    </div>
                </form>
            </div>
            <style>{`
                    .form-group{
                        width:100%;
                        display:block;
                        margin-bottom:10px;
                    }
                    .form-group label{
                        display:block;
                        margin-bottom:10px;
                    }

                    .form-group input[type="text"]{
                        padding:10px;
                        width:100%;
                    }

                    .form-group textarea{
                        padding:10px;
                        width:100%;
                    }

                    .error{
                        color:red;
                        text-align:center;
                    }

                    .message{
                        color:green;
                        text-align:center;
                    }
                    `}</style>
        </Layout>
    )
}

When the above form is submitted, it will hit the api/post route. This will execute the Prisma create() method and instruct the database to add new records to the posts table.

Once the post is created, it will be saved as a draft. We will need to create a drafts page where you can view and publish it.

It’s good to note that publishing a post will involve an UPDATE request to the database. Therefore, execute an update() method to handle this operation as such. To do so, create edit.js inside the pages/api/post folder and add an API handle for sending update requests using Prisma as follows:

import prisma from '../../../lib/prisma'

export default async function handle(req, res) {
    const { published } = req.body;
    const result = await prisma.post.update({
        where: {
            id: req.query.id,
        },
        data: {
            published: published,
        }
    });
    res.json(result);
}

Inside pages create a drafts.js and handle draft posts as such:

import Head from 'next/head';
import { useRouter } from 'next/router';
import prisma from '../lib/prisma';
import Layout from '../components/Layout';
import { useState } from 'react';

export default function Home({ feed }) {

    const [loading, setLoading] = useState(false);
    const router = useRouter();

    const publishPost = async postId => {
        try {
            setLoading(true);
            const body = {
                'published': true
            };
            await fetch('/api/post/edit?id=' + postId, {
                method: "POST",
                headers: {"Content-Type": "application/json"},
                body: JSON.stringify(body),
            });

            setLoading(false);
            await router.push("/");
        } catch (error) {
            console.log("error", error);
            setLoading(false);
        }
    }

    return (
        <Layout>
            <Head>
               
<title>Drafts</title>
                <meta name= "description" content=" Generated by create next app"/>
                <link rel="icon" href="/favicon.ico" />
            </Head>
            {
                feed.length > 0 ? (
                    feed.map((item, index) => (
                        <div className='post-card' key={index}>
                            <span style={{ fontWeight: 'bold' }}>{item.title}</span>
                           
<p>{item.content}</p>
                            <div className='post-card-action'>
                                <button onClick={() => publishPost(item.id)}>{loading ? "Loading...": "Publish"}</button>
                            </div>
                        </div>
                    ))
                ) : (
                    <div>
                        <p>No draft posts found.</p>
                    </div>
                )
            }
            <style jsx>{`
                    .post-card{
                    border:1px solid #d4d4d5;
                    padding:10px;
                    }
                    `}
            </style>
        </Layout>
    )
}

export const getStaticProps = async () => {
    const feed = await prisma.post.findMany({
        where: { published: false },
    });
    return {
        props: { feed },
        revalidate: 10,
    };
}

This will execute Prisma findMany() method and fetch the posts where the published value is false.

Once a post is submitted, you will be redirected to the drafts page. Publish it from there, and then you will be redirected to the home page. Let’s now create a home page for handling that.

Loading dynamic posts

In the pages/index.js, we will change it to load data from the database.

• Start by importing the Prisma client connection and the layout defined prior:

import Head from 'next/head';
import prisma from '../lib/prisma';
import Layout from '../components/Layout';
import { useState } from 'react';
import { useRouter } from 'next/router';

• Add a getStaticProps function as below to get the posts:

export const getStaticProps = async () => {
  const feed = await prisma.post.findMany({
    where: { published: true },
  });
  return {
    props: { feed },
    revalidate: 10,
  };
}

Once we get the published posts, we will create a simple method for executing delete posts. Add a function for deleting a post:

export default function Home({ feed }) {

  const [loading, setLoading] = useState(false);
  const router = useRouter();
  const deletePost = async postId => {
    try {
      setLoading(true);
      await fetch('/api/post/delete?id=' + postId, {
        method: "DELETE",
        headers: {"Content-Type": "application/json"}
      });

      setLoading(false);
      await router.push("/");
    } catch (error) {
      console.log("error", error);
      setLoading(false);
    }

  }
}

• Update the render function inside Home() to display the fetched posts:

return (
  <Layout>
    <Head>
      <title>Posts</title>
      <meta name= "description" content=" Generated by create next app"/>
      <link rel="icon" href="/favicon.ico" />
    </Head>
    {
      feed.length > 0 ? (
        feed.map((item, index) => (
          <div className='post-card' key={index}>
            <span style={{ fontWeight: 'bold' }}>{item.title}</span>
            <p>{item.content}</p>
            <div>
              <button onClick={() => deletePost(item.id)}>{
                loading ? "Loading": "Delete"
              }</button>
            </div>
          </div>
        ))
      ) : (
        <div>
          <p>No published posts found.</p>
        </div>
      )
    }
    <style jsx>{`
                .post-card{
                border:1px solid #d4d4d5;
                padding:10px;
                margin:10px;
                }
                `}
    </style>
  </Layout>
)

On this home page, we are also executing a delete function. Therefore, go ahead and create an API handle to delete a post. To do so, create delete.js inside the pages/api/post folder and add an API handle for sending update requests using Prisma as follows:

import prisma from '../../../lib/prisma'

export default async function handle(req, res) {
    const result = await prisma.post.delete({
        where: {
            id: req.query.id,
        }
    });
    res.json(result);
}

Testing Posts

Let’s test what we have built so far. Ensure your development server is running (npm run dev) and check your home page. It should be similar to the following:

Great, we have no posts yet. Let’s navigate to Add Post and create one.

Click Submit to add the post. The posts will be added to the draft as unpublished posts.

At this point, the post should be available in your database. Go ahead and check that or use the:

npx prisma studio

To update the post, click the Publish button and update the post to published and displayed on the home page.

And if you want to erase the post, click the Delete button. All these changes should also reflect in the database as such.

Handling Post Comments

We have the posts ready; let’s now create a comment section for every post added. To add comments, we first need to access the single post. We have one too many relations here. Therefore, the user has to access each post to leave a comment. First, let’s create and handle the page for loading a single post.

Loading a single post

To access the content of a single post, we will create a page for handling that. In the pages/posts/ directory, create a [id] directory, and then inside it, create an index.js:

• Import the necessary modules:

import prisma from '../../../lib/prisma';
import Layout from '../../../components/Layout';

• On the getServerSideProps() function, change it to query the record from the database:

export default function PostPage(props) {

    return (
        <Layout>
            <div>
               
<h1>{props.title}</h1>
                <h5>{props.content}</h5>
                <h6>Comments</h6>
                <a href={`${props.id}/comment`}>Leave a comment</a>
                {
                    props.comments.length > 0 ? (
                        <ul>
                            {
                                props.comments.map((comment, index) => (
                                    <li key={index}>
                                       
{comment.content}
                                    </li>
                                ))
                            }
                        </ul>
                    ) : (
                        <p>No comments</p>
                    )
                }
            </div>
        </Layout>
    )
}

Each post has a unique id. We are mapping the post id to the page and executing the details of that single post. Prisma makes this possible by using the findUnique() method that allows you to pass the id parameter out of the box.

We have the single post ready. The user must click the post itself to access it so that Next.js can load this page. Head to the home page (pages/index.js) and add the following changes.

Update the findMany() as follows:

export const getStaticProps = async () => {
  const feed = await prisma.post.findMany({
      where: { published: true },
      include: {
        comments: {
              select: { content: true },
          },
      },
  });
  return {
      props: { feed },
      revalidate: 10,
  };
}

Add a href tag to the postcard. This way, the single post will be loaded as such when a post is clicked. Also, add a counter to the home page that should list the number of comments available to each post. To implement these changes, update the pages/index.js return function as follows:

return (
    <Layout>
        <Head>
            <title>Posts</title>
            <meta name= "description" content=" Generated by create next app"/>
            <link rel="icon" href="/favicon.ico" />
        </Head>
        {
            feed.length > 0 ? (
                feed.map((item, index) => (
                    <a href={`posts/${item.id}`}>
                        <div className='post-card' key={index}>
                            <span style={{ fontWeight: 'bold' }}>{item.title}</span>
                           
<p>{item.content}</p>
                           
<p>{item.comments.length} Comments</p>
                            <div>
                                <button onClick={() => deletePost(item.id)}>{
                                    loading ? "Loading": "Delete"
                               
}</button>
                            </div>
                        </div>
                    </a>
                ))
            ) : (
                <div>
                    <p>No published posts found.</p>
                </div>
            )
        }
        <style jsx>{`
                .post-card{
                border:1px solid #d4d4d5;
                padding:10px;
                margin:10px;
                }
                `}
        </style>
    </Layout>
)

Ensure that the development server is running. This is the new look of the home page:

Click on any post. This should load the individual post as follows:

Note that the post URL has the post id. This created the pages that access each post. Great! Let’s now add comments.

Adding a comment

This consumes the api from pages/api/post/comment. Create the comment.js file in the pages/api/post directory and add the following:

import prisma from '../../../lib/prisma'

export default async function handle(req, res) {
    const { comment, postId } = req.body;
    const result = await prisma.comment.create({
        data: {
            content: comment,
            postId: postId
        },
    });
    res.json(result);
}

In the pages/posts/[id] directory, create a comment.js file. In this comment.js file:

• Import the necessary modules:

import React, { useState } from "react";
import Head from "next/head";
import { useRouter } from 'next/router';
import Layout from "../../../components/Layout";

• Get the post id from the getServerSideProps function:

export const getServerSideProps = async ({ params }) => {
    return {
        props: {
            id: params?.id
        },
    };
};

• Render a form in the view for adding a comment:

export default function Comment(props) {

    const [comment, setComment] = useState("");
    const [error, setError] = useState("");
    const [message, setMessage] = useState("");
    const router = useRouter();

    const handleSubmit = async e => {
        e.preventDefault();
        setError("");
        setMessage("");
        if (comment) {
            // send request to server.
            try {
                const body = { comment, postId: props.id, published: false };
                let res = await fetch(`/api/post/comment`, {
                    method: "POST",
                    headers: {"Content-Type": "application/json"},
                    body: JSON.stringify(body),
                });
                await router.push("/");
            } catch (error) {
                console.error(error);
            }
        } else {
            setError("All fields are required");
            return;
        }
    }

    return (
        <Layout>
            <Head>
                <title>Create Comment</title>
            </Head>
            <div>
                <form onSubmit={handleSubmit}>
                    {
                        error ? (
                            <div className=" error form-group">
                                {error}
                            </div>
                        ) : null
                    }
                    {
                        message ? (
                            <div className= "message form-group">
                                {message}
                            </div>
                        ) : null
                    }
                    <div className="form-group">
                       
<label>Comment</label>
                        <input type="text" name="comment" placeholder="Leave a comment" value={comment} onChange={(e) => setComment(e.target.value)} />
                    </div>
                    <div className="form-group">
                        <button type="submit">Submit</button>
                    </div>
                </form>
            </div>
            <style>{`
                    .form-group{
                        width:100%;
                        display:block;
                        margin-bottom:10px;
                    }
                    .form-group label{
                        display:block;
                        margin-bottom:10px;
                    }

                    .form-group input[type="text"]{
                        padding:10px;
                        width:100%;
                    }

                    .form-group textarea{
                        padding:10px;
                        width:100%;
                    }

                    .error{
                        color:red;
                        text-align:center;
                    }

                    .message{
                        color:green;
                        text-align:center;
                    }
                    `}</style>
        </Layout>
    )
}

Click Leave a comment on the single post page. This will load a form to add a comment as such:

Go ahead and add comments:

And indeed, the comments are available as you add them:

Conclusion

That’s a wrap for this guide. You can now comfortably execute any operations to your database exclusively using Prisma and Next.js. For code references, check the application on this GitHub repository.

I hope you found this helpful. Happy coding!

A TIP FROM THE EDITOR: For more on Prisma and Next.js, see how to manage authentication at Authentication And DB Access With Next, Prisma, And MongoDB.

Gain Debugging Superpowers

Unleash the power of session replay to reproduce bugs and track user frustrations. Get complete visibility into your frontend with OpenReplay, the most advanced open-source session replay tool for developers.

Check our GitHub Repo