Using Prisma ORM with Next.js

This article explains how to introduce Prisma ORM into a Next.js project.

Create a Next.js Project Template

$ mkdir hello-next-app && cd hello-next-app
$ npm init -y
$ npm install next react react-dom --save
$ npm install typescript @types/node @types/react --save-dev
$ code src/index.tsx

Create src/index.tsx

export default function Index() {
  return <div>index</div>;
}

Run next build once to generate the TypeScript type definitions.

$ npx next build

By having a .tsx file as a build target, Next.js will generate next-env.d.ts via tsconfig.json.

Setting up Prisma

Add dependencies.

$ npm install @prisma/client@2 --save
$ npm install @prisma/cli@2 --save-dev

Add a schema to the prisma directory. This time, we will select SQLite.

prisma/schema.prisma

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

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Post {
  authorId  Int?
  content   String?
  id        Int     @id @default(autoincrement())
  published Boolean @default(false)
  title     String
  author    User?   @relation(fields: [authorId], references: [id])
}

model User {
  email String  @unique
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

For syntax highlighting in VSCode, it's recommended to install Prisma - Visual Studio Marketplace.

Run migrations:

$ npx prisma migrate dev --preview-feature --name init
$ npx prisma generate # Generates types for @prisma/client

(Note: These APIs change frequently and might look different soon.)

Once the migration is successful, prisma/dev.db and prisma/migrations should have been generated.

$ tree prisma/
prisma/
├── dev.db
├── migrations
│   └── 20201222113842_init
│       └── migration.sql
└── schema.prisma

Using Prisma from Next.js

Instantiate the Prisma client in lib/prisma.ts. Since we want to see the queries being issued, specify query in the log option.

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient({
  log: ["query", "error", "info", "warn"],
});
export default prisma;

export * from "@prisma/client";

Now we're ready!

Let's try calling Prisma in getServerSideProps() of pages/index.tsx.

import type { GetServerSideProps } from "next";
import prisma from "../lib/prisma";

type Props = {
  count: number;
};

export const getServerSideProps: GetServerSideProps<Props> = async (ctx) => {
  const count = await prisma.user.count();
  return {
    props: {
      count,
    },
  };
};

export default function Index(props: Props) {
  return <div>user count: {props.count}</div>;
}

Since getServerSideProps is executed on the server, it won't be exposed to the client thanks to Dead Code Elimination.

Start the server with npx next and open http://localhost:3000 while watching the logs.

Did it display user count: 0? Here is the log at that time:

prisma:query SELECT COUNT(*) FROM (SELECT `dev`.`User`.`id` FROM `dev`.`User` WHERE 1=1 LIMIT ? OFFSET ?) AS `sub`

Posting a Post

We will use zod for validation and react-final-form as our form library.

$ npm install zod final-form react-final-form --save

Create an API Route that receives title and content and generates a Prisma record.

pages/api/createPost.ts

import type { NextApiHandler } from "next";
import prisma from "../../lib/prisma";
import * as z from "zod";

const requestBodySchema = z.object({
  title: z.string().min(1),
  content: z.string(),
});

const handler: NextApiHandler = async (req, res) => {
  try {
    const result = requestBodySchema.parse(req.body);
    await prisma.post.create({
      data: {
        title: result.title,
        content: result.content,
        published: true,
      },
    });
    res.json({
      ok: true,
    });
    return;
  } catch (error) {
    res.json({ ok: false, error });
  }
};
export default handler;

We will use react-final-form to create a form that posts to this API.

components/PostForm.tsx

import { useCallback } from "react";
import { Form, Field } from "react-final-form";
import { useRouter } from "next/router";

export function PostForm() {
  const router = useRouter();
  const onSubmit = useCallback(async (formData) => {
    const res = await fetch("/api/createPost", {
      method: "POST",
      body: JSON.stringify(formData),
      headers: {
        "Content-Type": "application/json",
      },
    });
    const json = await res.json();
    if (json.ok) {
      router.push("/");
    } else {
      alert(JSON.stringify(json));
    }
  }, []);
  return (
    <Form
      onSubmit={onSubmit}
      render={({ handleSubmit }) => {
        return (
          <form onSubmit={handleSubmit}>
            <Field<HTMLInputElement>
              name="title"
              placeholder="title"
              render={(props) => {
                return (
                  <div>
                    <input
                      {...(props.input as any)}
                      style={{ width: "80vw" }}
                    />
                  </div>
                );
              }}
            />
            <Field<HTMLTextAreaElement>
              name="content"
              placeholder="content"
              render={(props) => {
                return (
                  <div>
                    <textarea
                      {...(props.input as any)}
                      style={{ width: "80vw", height: "300px" }}
                    />
                  </div>
                );
              }}
            />
            <button type="submit">Submit</button>
          </form>
        );
      }}
    />
  );
}

It's a simple form, but the key point is that submitting it executes the following API:

const res = await fetch("/api/createPost", {
  method: "POST",
  body: JSON.stringify(formData),
  headers: {
    "Content-Type": "application/json",
  },
});

I've cut some corners in the implementation; since it's a bit tedious to implement data re-fetching via API, I'm just re-routing back to the same page to trigger a fresh fetch.

To build this properly, you would likely turn the post list retrieval into an API and handle caching with something like react-query.

tannerlinsley/react-query: ⚛️ Hooks for fetching, caching and updating asynchronous data in React

On the index page, let's try displaying the current list of posts, similar to a Twitter feed.

pages/index.tsx

import type { GetServerSideProps } from "next";
import React from "react";
import { PostForm } from "../components/PostForm";
import prisma, { Post } from "../lib/prisma";

type Props = {
  posts: Pick<Post, "id", "title" | "content">[];
};

export const getServerSideProps: GetServerSideProps<Props> = async (ctx) => {
  const posts = await prisma.post.findMany({
    select: {
      title: true,
      content: true,
      id: true,
    },
  });
  return {
    props: {
      posts,
    },
  };
};

export default function Index(props: Props) {
  return (
    <>
      <PostForm />
      <div>post count: {props.posts.length}</div>
      {props.posts.map((post) => {
        return (
          <div key={post.id}>
            <h3>{post.title}</h3>
            <p>{post.content}</p>
          </div>
        );
      })}
    </>
  );
}

Start the server and try posting using the form you created.

The queries being issued internally at this time:

prisma:query BEGIN
prisma:query INSERT INTO `dev`.`Post` (`content`, `published`, `title`) VALUES (?,?,?)
prisma:query SELECT `dev`.`Post`.`id`, `dev`.`Post`.`authorId`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`title` FROM `dev`.`Post` WHERE `dev`.`Post`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

That's all. Within this scope, it doesn't seem that difficult.

Why I wrote this

I am currently writing a book about Next.js for the New Year holidays. I plan to sell it on Zenn as a paid article. This article is a "test run" of what it would be like to write about this theme as part of the paid book.

As one of the chapters, I was planning to write a chapter on full-stack frameworks, so I was evaluating Blitz.js and Next.js followers for that purpose.

2021 is the first year of Fullstack Next.js, so I tried all promising Next.js-based frameworks

What I realized after trying them is that currently, there is nothing other than Prisma that can be considered a "decisive factor."

At first, I was thinking of focusing on Blitz, but with the React Server Components announced today, it seems relatively easy to implement something like Blitz's isomorphism. I felt that the core of Next.js might incorporate one of Blitz's strengths, so I didn't think there was a need to deliberately lock ourselves into Blitz.

Introducing Zero-Bundle-Size React Server Components – React Blog

That said, since Next.js + Prisma isn't widely popular yet, I thought it would be better to publish this article for free first.

In the paid version, I intend to explain the following additional elements:

• Setting up Postgres with docker-compose and connecting from Prisma
• Caching responses with react-query
• Setting up Postgres on Amazon RDS
• Creating isomorphic APIs with hygen
• Running migrations during deployment with Vercel
• Managing environment variables with .env and Vercel Secrets