How to set up Prisma with Next.js and Postgres

Quick Links

• Official Next.js + Prisma example
• Official Prisma setup
• Docker Postgres
• Full GitHub example code

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

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.

pnpm create next-app

# Or

yarn create next-app

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

pnpm i -D prisma 
pnpm i @prisma/client

# Or

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

Once Prisma has been installed, run the following:

npx prisma init

The command prisma init 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"

The client setup adds a string in that file by default. Make sure you change it to match the credentials above or whatever credentials you are using for your database. 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 basic user model to make sure it is all working:

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

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 the client generation.

npx prisma migrate dev --name init

You're now ready to query your database!

A note on deploying

The Prisma client keeps its type definitions in the node_modules folder. This keeps them out of the way and doesn't clutter your codebase. However, when you do a deployment, such as to Vercel, the node_modules folder is reinstalled during the build process.

The type definitions for your client won't be there.

This will cause the build to fail later when the type definitions for your models are not there. To fix this, you should update the build command in your package.json file.

"build": "prisma generate && next build",

By updating that command, when doing a production build, the first thing that is generated is the client types. Then the rest of the build will work as expected.

Seeding the Database

If you want to seed the database, you need to install ts-node which will run a seed script for you.

pnpm i -D ts-node

# Or

yarn add -SED ts-node

Then update your package.json to include a new Prisma key. This will be at the top level with the seed command in it:

"prisma": {
  "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
},

Lastly, you can create the seeding script. Create a new file located at prisma/seed.ts. Here's an example creating a user:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const user = await prisma.user.upsert({
    where: { email: 'test@test.com' },
    update: {},
    create: {
      email: 'test@test.com',
      name: 'Test User',
	    password: `$2y$12$GBfcgD6XwaMferSOdYGiduw3Awuo95QAPhxFE0oNJ.Ds8qj3pzEZy` //password
    },
  })
  console.log({ user })
}
main()
  .then(() => prisma.$disconnect())
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

To execute the seed, you can run:

npx prisma db seed

Seeding also runs when you run prisma migrate dev or npx prisma migrate reset.

Now that you have some data, we can use it!

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 the following:

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

const globalForPrisma = global as unknown as { prisma: PrismaClient }

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
  })

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

The above adds a global variable not to exhaust connections.

Using the Prisma client

You can use the Prisma client anywhere on the server. You'll often use it in API routes to mutate data and in your components to fetch data.

App Directory

When you want to fetch data in a Server Component you can read it directly in the component:

import { prisma } from '@/lib/prisma'

export default async function Home() {
  const user = await prisma.user.findFirst({
    where: {
      email: 'test@test.com'
    }
  })

  return (
    <main>
      <div>Hello, {user?.name}</div>
    </main>
  )
}

Pages Directory

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

import { prisma } from '@/lib/prisma'
import { User } from '@prisma/client'
import { GetServerSideProps } from 'next'

type Props = {
  user: User
}

export default function Page(props: Props) {
  return <main>Hello, {props.user.name}</main>
}

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

  return {
    props: {
      user
    }
  }
}

And there you go! You can now set up, 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!