How to Build a CRUD App with NextJS and Prisma

How to Build a CRUD App with NextJS and Prisma

TL;DR

By the end of the tutorial, we will have created a "WishList App" that enables users to save their favorite links for items they wish to buy in the future. We'll also have understood how to build a full CRUD app with NextJs API and persist our data to the database using Prisma.

Introduction

NextJs is a powerful framework that adds superpowers to your React application. It offers amazing features like file routing, API handling, server-side rendering, static site generation and lots more are available by default without any extra configuration. Traditionally, we used to build full-stack JavaScript applications as separate services and different codebases. Where React powers the front end, and Express Js or any backend framework powers the logic.

Thanks to its API routes, we can build full-stack applications as quickly as possible. To keep the app as simple as possible, we will not implement authentication but use Local storage to generate unique sessions for our users.

In this tutorial, we'll be using:

  1. NextJs for building our application endpoints.

  2. Prisma: an Object Relational mapping (ORM) for setting up our data model, connecting, and interacting with the database. Prisma is a one-catch solution because it can connect with any database of your choice using the same APIs with almost no extra configuration on both the models and methods. You can work with both relational databases like MySQL, and Postgres and non-relational databases like MongoDB. View docs

  3. Railway: To provision an online PostgreSQL database instance. I chose Railway for this tutorial because you don't need to create an account to provision a DB and follow along.

Let's Get Started

Go ahead and install the necessary dependencies. On your terminal, run

npx create-next-app@latest

This will install a fresh NextJs application. Select the default prompts, including tailwind CSS. Open the folder in your favorite code editor. Clear out the boilerplate codes and you should have a clean /pages/index.js ready to tango!

Step 1: Database Section

Let's install Prisma to enjoy the amazing benefits:

npm install prisma --save-dev
npx prisma
npx prisma init

These commands will install Prisma, invoke the Prisma CLI and initialize Prisma in our project. The init command creates a prisma folder that contains the schema file. The schema file is the main configuration for our database setup. You should also see a .env file in the root folder, with the value DATABASE_URL.

Next, we need to get our database connection link from Railway. On the home page, click on "Start a New Project", then select "Provision PostgreSQL". It is important to note here, that we can use any database of our choice. For this tutorial, we'll be using PostgreSQL. You should have a screen like this.

Click on the connect tab, copy the DATABASE_URL, then replace the value in your .env file. Now we have our database connection set up, let's proceed to set up our data model.

Open up the schema.prisma file in the prisma folder. This is also called the schema file and is used by Prisma to handle database connection, data modeling, and migration. The Prisma CLI also uses it to generate SQL statements that run against our database when we perform a migration.

To create a model for the wishlist, add this code to your schema file.

model Wishlist {
  id  Int @id @default(autoincrement())
  title   String
  link   String?
  userId  Int 
  createdAt DateTime @default(now())
}

We just created a Wish model which has

  • id as the primary key. The id field autoincrements.

  • title field which is a string.

  • link field with an optional string value.

  • createdAt field, which is a DateTime type. The default value is the current date/time.

Prisma also supports database relationships, but our goal today is to keep things simple. Now we have our model ready, the next step is to migrate it to the database. To do this, we simply run

npx prisma migrate dev --name "Intialized database"

The --name flag is used to specify the name of the migration. Whenever you edit your schema file, you need to run this command with a name that describes the changes. The migrate command does two things:

  • Generates a new SQL migration file.

  • Runs the generated SQL against the database.

We should now have a migration folder in our prisma directory with a unique name. The command will also install prisma client the first time it is run. The prisma client is what allows us to interact with the database. Now, take a look at your Railway dashboard, you should see two new tables have been created.

  • A migration table to handle our migrations

  • A wishList table that models our schema.

To connect to our database, we need to instantiate a Prisma object. Create a "client" folder in the prisma directory and add file index.js

// prisma/client/index.js
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default prisma;

We imported the PrismaClient from the modules folder, then instantiated a new prisma object and exported it. We can now import this file wherever we wish and use Prisma to interact with our database. If this does not make sense right now, don't worry. Let's see it in action!

Step 2: API Endpoints

Create New Wish (CREATE)

NextJs lets us create REST endpoints that can be accessed via the API routes. All you need do is add your endpoints files in the /pages/api directory. You can access these endpoints by localhost:3000/api/{name_of_file} route. Let us create an endpoint to add a new wish to our wishlist table. Add a new file create-new.js in the API folder.

// pages/api/create-new.js
import prisma from "../../../prisma/client";

export default async function handler(req, res) {
  if (req.method === "POST") {
    const { title, titleLink, userId } =
      typeof req.body == "string" ? JSON.parse(req.body) : req.body;

    try {
      // we can access db records with prisma functions
      const wish = await prisma.wishlist.create({
        data: {
          title,
          link: titleLink,
          userId: parseInt(userId),
        },
      });
      res.status(200).json({ wish });
    } catch (e) {
      res.status(500).json(e);
    }
  }
}

We imported the prisma client, then called the create function on the wishlist model passing the data, and voila! Prisma handles the creation of a new wish in our database and returns the newly created wish. We then sent the wish as a response. We can test this endpoint with either Postman or Insomia.

run npm run dev to start up the NextJs development server. We can see it returns the newly created wish!

Get All User Wishes (READ)

// pages/api/get-wish.js
import prisma from "../../../prisma/client";

export default async function handler(req, res) {
  if (req.method === "GET") {
    const { user } = req.query;

    try {
      const list = await prisma.wishlist.findMany({
        where: {
          userId: parseInt(user),
        },
        orderBy: {
          createdAt: "desc",
        },
      });
      res.status(200).json({ list });
    } catch (e) {
      res.status(500).json(e);
    }
  }
}

Just like in the previous file, here, we use the Prisma function findMany on our wishlist model to retrieve the user's wishlist and then return it to the caller. We also specified our collection by using orderBy

Update Wish (UPDATE)

// pages/api/edit-wish.js
import prisma from "../../../prisma/client";

export default async function handler(req, res) {
  if (req.method === "POST") {
    const { wishId, title, link } = JSON.parse(req.body);

    try {
      const updatedWish = await prisma.wishlist.update({
        where: {
          id: wishId,
        },
        data: {
          title,
          link,
        },
      });
      res.status(200).json({ updatedWish });
    } catch (e) {
      res.status(500).json(e);
    }
  }
}

Next, we create an endpoint for editing a single wish. The endpoint accepts a POST request that receives the wishId, title, and link, updates the record, and returns the updated list to the caller. We use the update function on the Prisma client to update the database record.

Delete Wish (DELETE)

To delete a wish record, we call the delete function on the wishlist model with Prisma.

// pages/api/delete-wish.js
import prisma from "../../../prisma/client";

export default async function handler(req, res) {
  if (req.method === "POST") {
    const { wishId } = JSON.parse(req.body);

    try {
      await prisma.wishlist.delete({
        where: {
          id: wishId,
        },
      });
      res.status(200).json({ message: "deleted" });
    } catch (e) {
      res.status(500).json(e);
    }
  }
}

Step 3: Frontend

Now we have a set of API endpoints for our app. We can then proceed to build our frontend, to see it come alive. The example code below is more of an abstraction to show concept. To see the full working code, check out the git repo. You can run it locally on your device. Just replace the DATABASE_URL variable in the .env file with yours from Railway.

export default function Home() {
  return (
    <div className="max-w-4xl min-h-screen mx-auto">
      <WishProvider>
        <Navbar />
        <NewWishList />
        <AllWishList />
      </WishProvider>
    </div>
  );
}

// AllWishList 
// ...
const getUserWishes = async () => {
    setIsLoading(true);
    const res = await fetch(`/api/get-wishes?user=${userId}`);
    const data = await res.json();

    data.list && initializeList(data?.list);
    setIsLoading(false);
 };
// ...

CONCLUSION

We have seen how to create database models, migrate, and interact with our database using Prisma with NextJs endpoints. Prisma is a game changer for how we build full-stack applications with NextJs.

Feel free to drop questions in the comments or hit me up on Twitter. Thanks for reading.