PostgreSQL with Next.js and Prisma

First of all, what is Prisma and why do we need it?

Prisma is an awesome libarary if you are like me, bad at write SQL queries and is used to other databases such as Dynamo. It provides a really similar way in querying your data and reduce the stress of debugging SQL queries. It has a clean and type-safe API for submitting database queries which returns plain old JavaScript objects.

In this article, I will be going through the following:

• Set up PostgreSQL
  - Installation
  - Data Visualization
• Set Up Next Js with Prisma
  - Create new project
  - Initialize Prisma
• Configure Prisma
  - Connect to Database
  - Define Database Schema
• Use Prisma in Next.js
  - Generate Client
  - Create Client Instance
  - Query Database

Set Up PostgreSQL on Mac

Installation

We will first set up postrgreSQL, if you already have your database, skip this section.

If you like the interactive way of installing, you can dowload the installer here. However, I found that downloading using this method makes it really hard to make customization as all the config files are initally locked and you will have to manually change access permision for all.

In this article, we will install using homebrew. You can find out more about other installation methods here.

Run the following command from your terminal.

brew install postgresql@16

This will install PostgreSQL 16, expose it to Port:5432, as well as creating a default database with name postgres at the following location /opt/homebrew/var/postgresql@16.

To create a new database, you can use the follwoing command.

initdb --locale=C -E UTF-8 path_to_your_directory

This will create a new database at path_to_your_directory with locale setting to C and encoding setting to UTF-8.

You can find out more about the argument you can pass into initdb here.

We will be using the default database created by brew initially in this artical.

Here are couple really useful commands .

• To start postgresql@16 now or restart at login: brew services start postgresql@16
• To stop the service: brew services stop postgresql@16
• To get the list of service you have: brew services list
• To connect to the server and enter SQL command: psql postgres

DataBase Visualization

I have also installed DBeaver for database visualisation.

Let’s first create a new connection by click on the icon below.

This will take us to Select your database. Choose PostgreSQL and let’s configure our connection setting.

The basic schema for your database URL will be look like the following.

jdbc:postgresql://{Host}:{Port}/{Database}

And in my case, this will be:

jdbc:postgresql://localhost:5432/postgres

You can find out the Username by running brew services list and it is the User.

Name          Status  User   File
postgresql@16 started itsuki ~/Library/LaunchAgents/homebrew.mxcl.postgresql@16.plist

And for the Password, you can leave it empty.

Choose Finish.

Double click on the database name on the left side panel and you will be able to connect to it. You should see the green check back under the elephant icon.

Having our PostgreSQL database set up, we can now moving onto creating and configuring our Next.js project and Prisma.

Set Up Next Js with Prisma

Create new project

First of all create a new next js project like following.

npx create-next-app db-prisma-demo

Here is how our directory look like initially.

.
├── .git
├── .gitignore
├── .next
├── README.md
├── next-env.d.ts
├── next.config.js
├── node_modules
├── package-lock.json
├── package.json
├── public
├── src
└── tsconfig.json

Initialize Prisma

Go to your project directory and install the following libraries.

npm install prisma @prisma/client

After the installation, we can then initialize Prisma with

npx prisma init

This command will

• create a new directory called prisma with a new file called schema.prisma that contains the Prisma schema, database connction variables and schema models. We will take a more detail look at it later.
• creates the .env file in the root directory to which we will be adding the database connection URL.

Below is our new directory structure.

.
├── .env
├── .git
├── .gitignore
├── .next
├── README.md
├── next-env.d.ts
├── next.config.js
├── node_modules
├── package-lock.json
├── package.json
├── prisma
├── public
├── src
└── tsconfig.json

Configure Prisma for PostgreSQL

Connect to Database

We will first add our connection URL to our .env file. It is similar to the URL we used when setting up DBeaver with just couple extra things.

The basic schema for the Base URL will be look like the following.

postgres://{username}:{password}@{hostname}:{port}/{database-name}

In addtion, you can also add Arguments to it. We will be adding schema to ours. If schema is not set, it will automatically set to the default, ie: public, but let’s just add it anyway. The URL scheme will become the following.

postgres://{username}:{password}@{Host}:{Port}/{DatabaseName}?schema={schemaName}

Yes, you’re right! We have never set something called password. You can just use postgres and it will work fine.

In my case, the URL will be:

DATABASE_URL="postgresql://itsuki:postgres@localhost:5432/postgres?schema=public"

I actually don’t have to change anything in my schema.prisma . The provider is default to postgresql, and url being set to the environment variable. If yours looks different, change it to the following.

// schema.prisma

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Defining the Database Schema

This section is only for those of you who have an empty database or wish to add new tables to your database.

If that’s not you go, ie: you already have your database created and not going to add any new table, you can simply run the following command to turn your database schema into a Prisma schema and head to the next session.

npx prisma db pull

Suppose we want a (new) table storing User data in our database. Here is the model we will be adding to our schema.prisma.

model User {
  id Int        @id
  name String
}

The User model two columns.

• an id column of type Int which is the primary key
• a name column of type String.

By the way if you don’t have any schema from the beginning, this will create a new schema with User table.

After defining the model, we can deploy it to our database using

npx prisma db push

You can check your DBeaver and should observe something similar to the following with your User Table with defined columns showing up.

Let’s add couple rows to the column so that we can query on it later.

Double click on your User Table and select Data Tab at top. Right click anywhere on the Grid and choose Add row. I have add itsuki and itsuki2 as my user for demo purpose.

Use Prisma in Next.js

Generate Client

First of all, let’s generate a Prisma Client that is tailored to our models by running the following command.

npx prisma generate

This command is actually also invoked when we installed @prisma/client, and you might and should see the following already showing up at the top of your schema.prisma file. But let’s run it again since we have modified Prisma schema.

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

Create Client Instance

It is actually a little bit tricky when creating client instance within NextJs.

If you simply do const prisma = new PrismaClient() as what the tutorial for Node.js suggest, you might get the following error or stay in the loading state forever (that’s me, I am not getting the actual error on my console though).

warn(prisma-client) There are already 10 instances of Prisma Client actively running.

The problem is that in development environment, the command next run dev clears Node.js cache on run. This in turn initializes a new PrismaClient instance each time due to hot reloading that creates a connection to the database. This can quickly exhaust the database connections as each PrismaClient instance holds its own connection pool and you will get the following error. You can get more details about the issues here.

Here is the solution.

Let’s create a new lib folder under src and add prisma.ts to it.

// prisma.ts

import { PrismaClient } from '@prisma/client'

const prismaClientSingleton = () => {
  return new PrismaClient()
}

type PrismaClientSingleton = ReturnType<typeof prismaClientSingleton>

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClientSingleton | undefined
}

const prisma = globalForPrisma.prisma ?? prismaClientSingleton()

export default prisma

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

The idea here is to instantiate a single instance PrismaClient and save it on the globalThis object.

We will check globalThis to see if anything is stored on it. If nothing is on the object, we will create a new PrismaClient; otherwise, we will just reuse the one stored.

We can now import the client when we need it and start querying database.

Query Database

There are couple ways to use Prisma to query database within Next Js.

• getStaticProps
• getServerSideProps
• API Routes

Let’s start with a simple example where we will list all the users we have in the database. To do that we will be using the findMany() api in the format of prisma.$modelName.findMany() .

To use it in getServerSideProps:

// index.tsx

import prisma from '../lib/prisma'

export const getServerSideProps = async () => {
  const users = await prisma.User.findMany()
  console.log(users)

  return { props: { users } }
}

getStaticProps will be pretty much the same so I will skip it and move onto API route.

// api/fetchUser.ts

import prisma from '../lib/prisma'
import type { NextApiRequest, NextApiResponse } from 'next'

export default function handler(req: NextApiRequest, res: NextApiResponse) {
      const users = await prisma.User.findMany()
      res.status(200).json({ users: users });
}

There are many many more APIs provided by Prisma that really makes life working with relational database such as PostgreSQL so much easier so check out what you can do here.

That’s all I have for today!

Thank you for reading and Happy coding!