How to Add Prisma Postgres Database Adapter to Your Next.js App

Prisma is a Object Relational Mapping ORM library that supports any Node.js or Typescript backend applications. It's used to interact with databases using a type-safe and intuitive API. It simplifies database access by generating type-safe query builders and data models based on your database schema. Prisma ORM supports various database systems such as PostgreSQL, MongoDB, MySQL, SQLite, and many more. In this blog we will be learning how to add Prisma Postgres database adapter to your Next.js App.

Jordan Wu profile picture

Jordan Wu

9 min·Posted 

Mountains Under Red Skies Image.
Mountains Under Red Skies Image.
Table of Contents

What is Prisma?

Prisma ORM is a library that provides tools to make application developers more productive when working with databases. Everything revolves around data modeling, the process of defining the shape and structure of the objects in an application. The ORM handles this on two levels: on the database level and on the application level. The database level is where the data is stored depending on the database. The application level is representing the data from the database in your programming language.

Prisma ORM is a data mapper that decouple the application's in-memory representation of data from the database's representation. The decoupling is achieved by requiring separating the mapping responsibility into two types of classes:

Entity classes: The application's in-memory representation of entities which have no knowledge of the database.

Mapper classes: Transforming the data between the two representations and generating the SQL necessary to fetch data from the database and persist changes in the database.

The key characteristic of an ORM is that it lets you model your application data in terms of classes which are mapped to tables in the underlying database. These models are often called "application models" and are defined in your Prisma schema.

Prisma Schema

The Prisma schema file is the main configuration file for your Prisma ORM setup. It is typically called schema.prisma and consists of the following parts: Data sources, Generators, Data model definition. It allows developers to define their application models in an intuitive data modeling language. There are two ways to generate your schema:

Only Prisma Client: Application models in the Prisma schema are generated based on the introspection of your database schema. Data modeling happens primarily on the database-level.

Prisma Client and Prisma Migrate: Data modeling happens in the Prisma schema by manually adding application models to it. Prisma Migrate maps these application models to tables in the underlying database.

How to Add Prisma to Next.js App Router

In this blog post you are going to create your Prisma schema using Prisma Client and Prisma Migrate. Maintaining your Prisma schema at the application level is better and easier than at the database level.

Create Project Setup

First you would need to download prisma.

pnpmyarnnpm
pnpm add prisma

Now you can call the Prisma CLI using npx.

npx prisma init

It created a new directory called prisma that contains a file called schema.prisma, which contains the Prisma schema with your database connection variable and schema models. The data source determines how Prisma ORM connects your database, and is represented by the datasource block in the Prisma schema (you can only have one datasource). A generator determines which assets are created when you run the prisma generate command (you can have one or more generators).

File Imageprisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

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

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

And created a .env file in the root directory of the project, which is used for defining environment variables (such as your database connection).

.env
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

I recommend holding default values in this .env file and creating another file that holds your actual environment variable values called .env.development and git ignore all local .env.* files.

File Image.gitignore
# local env files
.env*

Database Connection URL

Make sure you have your database connection URL to connect to your Postgres database. It generally consists of the following components:

  • User: The name of your database user
  • Password: The password for your database user
  • Host: The IP or domain name of the machine where your database server is running
  • Port: The port on which your database server is running
  • Database name: The name of the database you want to use
PostgreSQL Connection URL
PostgreSQL Connection URL

Read How to Create a Postgres Database in AWS RDS on how to connect to a AWS RDS database instance. Or run a Postgres instance inside Docker by creating the following docker-compose.yml file:

File Imagedocker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:latest
    container_name: my-postgres-container
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: test
    volumes:
      - postgres-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  postgres-data:
    driver: local

Now you can run the Postgres instance by running the following command:

docker-compose up -d

Note: don't forget to stop your Postgres instance when you are done by running:

docker-compose down

If you went with docker compose your connection string would be:

postgresql://postgres:postgres@localhost:5432/test?schema=public

Update .env file with your database connection url.

Enable the driverAdapters Preview Feature Flag

At the core Prisma uses an engine that is the direct interface of the database, any higher-level interfaces always communicate with the database through this engine-layer. Instead of using the default built in database drivers, you can use the most popular driver for Postgres in the JavaScript ecosystem called node-postgres. Update your schema.prisma file to use Postgres and enable the driverAdapters preview feature flag.

File Imageprisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

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

Defining a User Model

As mentioned earlier you are generating the Prisma schema at the application level and that involves defining the application models in schema.prisma. Prisma models represents the entities of your application domain and map to the tables in your database. For example when creating a simple User model.

File Imageprisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

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

model User {
  uuid  String @id @default(uuid())
  email String @unique

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("update_at")

  @@map("users")
}

This model will represent your user record which consists of a unique identifier, their email address, and timestamps of when the record was created and updated. For more information about the data model definition check out Prisma schema reference.

Create the First Migration

Now that you have your model defined in schema.prisma file. You would need to run a database migration to create the table in your database. To run the migration you would use the migrate dev command.

npx prisma migrate dev --name init

After running this command your table should be created in Postgres and created a new migration file in prisma/migrations directory. To learn more about the migration commands check out Prisma Migrate.

You can access your database using Prisma Studio. This can be done by running the following command:

npx prisma studio

Generate Prisma Client Assets

You need to generate the Prisma Client assets before you can use it. To generate the assets you would run the following command:

npx prisma generate

By default, Prisma Client is generated into the node_modules/.prisma/client folder. Prisma Client uses a query engine to run queries against the database. This query engine is downloaded when prisma generate is invoked and stored in the output path together with the generated Client. By generating Prisma Client into node_modules, the query engine is usually kept out of version control by default since node_modules is typically ignored for version control.

Instantiate Prisma Client

Install the @prisma/client and @prisma/adapter-pg npm packages:

pnpmyarnnpm
pnpm add @prisma/client @prisma/adapter-pg

Now you can create the Prisma Client in your Next.js app. It will follow Best practice for instantiating Prisma Client with Next.js and Instantiate Prisma Client using the driver adapter guides.

File Imagesrc/lib/prisma.ts
import { Pool } from 'pg'
import { Prisma Pge } from '@prisma/adapter-pg'
import { PrismaClient } from '@prisma/client'

const connectionString = `${process.env.DATABASE_URL}`

const prismaClientSingleton = () => {
  const pool = new Pool({ connectionString })
  const adapter = new PrismaPg(pool)

  return new PrismaClient({ adapter })
}

declare global {
  var prismaGlobal: undefined | ReturnType<typeof prismaClientSingleton>
}

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton()

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

export default prisma

This will give you the flexibility on how to configure the database adapter to your use case and creates only one instance of the Prisma Client when running your Next.js app. Next would be to make sure to add DATABASE_URL environment variable to your deployment process, for example I'm using Vercel Environment Variables.

If you are using vercel/pkg to package your Node.js project you would want to include adding your Prisma query engine binary path to the pkg/assets in your package.json file, as mentioned in Solve package error with vercel/pkg.

File Imagepackage.json
{
  "pkg": {
    "assets": ["node_modules/.prisma/client/*.node"]
  }
}

Performing CRUD actions

CRUD is an acronym that stands for Create, Read, Update, and Delete. These are the operations used in all applications when interacting with a database. The generated assets will give you the ability to use the Prisma Client API to perform database operations. Check out Prisma CRUD for more examples.

Summary

That's all the steps you would need to add Prisma with the Postgres database adapter to your Next.js app. Having this will allow you to store all your application's data inside a Postgres database and allow you to add more features to your web application.

About the Author

Jordan Wu profile picture
Jordan is a full stack engineer with years of experience working at startups. He enjoys learning about software development and building something people want. What makes him happy is music. He is passionate about finding music and is an aspiring DJ. He wants to create his own music and in the process of finding is own sound.
Email icon image
Stay up to date

Get notified when I publish something new, and unsubscribe at any time.