3 min read

How to set up Prisma with Next.js and Postgres

How to set up Prisma with Next.js and Postgres
Photo by Joshua Reddekopp / Unsplash

When building a SaaS application, you will quickly need to interface with a database. You can either use an ORM abstraction layer or write SQL queries and execute them. The best ORMs will allow you to quickly write  SQL where you need to while removing boilerplate code.

ORM is an object-relational mapping layer. It takes the data in the database and transforms it into easily managed objects for the programmer. I've found a thin, simple abstraction layer best for productivity and shipping code.

Currently, one of the best ORMs for TypeScript is Prisma. TypeScript is the best language to use when writing web apps, but ORMs historically have been tricky. In the past, JavaScript ORMs used a lot of dynamic programming to function. And types with that sort of black magic are hard to manage.

Prisma takes a different approach where it defines the schema in its agnostic language. On the one hand, it is something else to learn. On the other hand, it generates the types for your exact model perfectly. TypeScript only works as well as the types, so this is a tradeoff I find acceptable.

Run Postgres Container

Postgres is my preferred database and is very easy to run locally with docker. Just run:

docker run --rm --publish 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust -e POSTGRES_DB=databasename postgres
Start the Postgres container

With the image running, you can connect on port 5432 with the username postgres and no password. Make sure you connect to the database name you used above and not the default database.

Setup Prisma

I start with the Next.js + TypeScript starter. If you already have a project, you can skip this and just use your project.

yarn create next-app --example with-typescript project-name

Then install the new dependencies. Only install the client for production.

yarn add -SED prisma
yarn add -SE @prisma/client
npx prisma init

The last line will do the initial setup for the Prisma client. Then, to connect to the database and start using Prisma, you need to add the DATABASE_URL to your .env file:

DATABASE_URL="postgresql://postgres@localhost:5432/databasename?schema=public"
.env

The client set up adds a string in that file by default. Make sure you change it to match the credentials above. This will also need to be updated for your production environment.

Open up the new prisma/schema.prisma file, and you can see the connection information. Next, let's add a user model to make sure it is all working:

model User {
  id       Int     @id @default(autoincrement())
  email    String  @unique
  password String
  name     String?
}
prisma/schema.prisma

You can find the schema reference here.

Now you can create your first migration, which will write the SQL for the database. It also runs it.

npx Prisma migrate dev --name init

You're now ready to query your database!

Using the Client

Create a new file at lib/prisma.ts. If you don't have a lib folder, create one. This file should not be exported in a barrel rollup file (index.ts ) since the Prisma client can't be used in the browser. Instead, import it as:

import Prisma from 'lib/prisma'


In the file, add:

import { PrismaClient } from '@prisma/client'

// PrismaClient is attached to the `global` object in development to prevent
// exhausting your database connection limit.
//
// Learn more:
// https://pris.ly/d/help/next-js-best-practices

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
lib/prisma.ts

The above adds a global variable not to exhaust connections.

Fix the types on global for Prisma

TypeScript doesn't have a type for global that has the Prisma client on it. You need to update the types to remove the TypeScript warnings. The best way to fix this is to add the types instead of casting global to any.

Create a new file called types.d.ts in the root directory and add the following contents to it:

export declare global {
  var prisma: PrismaClient | undefined
}
types.d.ts

You might need to restart VS Code for the types to work. Now you are type-safe throughout!

Using the Prisma client

When you want to query with the client (such as in getServerSideProps), do:

export const getServerSideProps: GetServerSideProps = async (context) => {
  const user = await prisma.user.findFirst({
    where: {
      email: 'example@test.com'
    }
  })

  return {
    props: {
      user
    }
  }
}

And there you go! You can now setup, manage, and query your database with Prisma.


I'm building a suite of internet products to find a better alternative to the next tech job. The first product is a SaaS app that helps manage localizations and copy for your app.

If you want to follow along, please find me on Twitter. It means a lot!