Prisma ORM Setup: Connecting Next.js to a PostgreSQL Database

Step 4 of 31 — Next.js Tutorial Series | Source code for this step

← Prev: Layouts & NavigationNext: Schema Design →

Commands in This Step

Command	Purpose
`pnpm add @prisma/client @prisma/adapter-pg`	Install Prisma client and adapter
`pnpm add -D prisma tsx`	Install Prisma CLI and tsx runner
`pnpm approve-builds`	Approve package build scripts
`pnpm rebuild prisma @prisma/engines esbuild`	Manually trigger build scripts
`npx prisma init`	Initialize Prisma in the project
`npx prisma init --db`	Create a Prisma Postgres database
`node -e "console.log(require('crypto').randomBytes(32).toString('base64url'))"`	Generate a random AUTH_SECRET
`npx prisma generate`	Generate the Prisma Client
`npx prisma studio`	Open database GUI in browser

What You Will Build

↑ Index

By the end of this step you will have Prisma installed, a cloud-hosted PostgreSQL database created, and your project configured to connect to it. No tables yet — that comes in Step 5.

Goal: Run npx prisma studio and see an empty database dashboard in your browser.

The Problem: How Do Web Apps Talk to Databases?
Core Concept: What is an ORM?
Core Concept: What is Prisma?
Install Prisma
Create a Prisma Postgres Instance
Core Concept: Environment Variables in Next.js
Set Up the .env File
Understand the Generated Files
Configure prisma.config.ts
Verify the Connection
Summary & Key Takeaways

The Problem: How Do Web Apps Talk to Databases?

↑ Index

Right now our Superblog shows placeholder text like "No posts yet." To display real blog posts, we need a database — a place to store and retrieve data.

But databases speak SQL (Structured Query Language), not JavaScript. Here is what a raw SQL query looks like:

SELECT posts.id, posts.title, posts.content, users.name AS author_name
FROM posts
INNER JOIN users ON posts.author_id = users.id
WHERE posts.published = true
ORDER BY posts.created_at DESC
LIMIT 6;

Writing raw SQL in a JavaScript application creates several problems:

No type safety — TypeScript cannot check if your column names are correct
SQL injection risk — if you build queries by concatenating user input, attackers can manipulate your database
No autocomplete — your editor cannot help you with table names and column names
Database-specific syntax — switching from PostgreSQL to MySQL means rewriting queries

Core Concept: What is an ORM?

↑ Index

An ORM (Object-Relational Mapping) is a library that lets you interact with a database using your programming language instead of raw SQL.

With an ORM, the same query becomes:

const posts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  take: 6,
  include: { author: { select: { name: true } } },
})

What the ORM does for you:

Problem	ORM Solution
No type safety	Generates TypeScript types from your database schema
SQL injection	Parameterizes queries automatically
No autocomplete	Full IntelliSense — `prisma.post.` shows all available methods
Database differences	Same code works across PostgreSQL, MySQL, SQLite

Think of it this way: An ORM translates between the JavaScript/TypeScript world (objects, arrays, functions) and the database world (tables, rows, columns). You write TypeScript, the ORM writes SQL.

Core Concept: What is Prisma?

↑ Index

Prisma is the most popular ORM for Node.js and TypeScript. It has three main parts:

Part	What it does
Prisma Schema	A file where you define your data models (like `User`, `Post`). It looks similar to TypeScript interfaces but uses Prisma's own syntax.
Prisma Client	An auto-generated TypeScript library that gives you type-safe methods to query the database (`prisma.post.findMany()`, `prisma.user.create()`, etc.)
Prisma Migrate	A tool that takes your schema changes and generates SQL migration files to update the database structure.

Coming from Laravel? In Laravel, you define your schema inside migration files (PHP classes with up() / down() methods) and query the database through Eloquent models. In Prisma, the schema lives in a single schema.prisma file, and Prisma Client is the equivalent of Eloquent — it gives you methods like prisma.post.findMany() instead of Post::all(). Prisma Migrate generates the SQL migrations for you automatically from the schema diff.

The workflow with Prisma looks like this:

1. You define models in schema.prisma
       ↓
2. prisma migrate creates SQL and updates the database
       ↓
3. prisma generate creates the TypeScript client
       ↓
4. You use prisma.post.findMany() in your code

We also use Prisma Postgres — a cloud-hosted PostgreSQL database service by Prisma. It gives you a database with zero server setup.

Install Prisma

↑ Index

Run the following command in your project directory:

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

Then install the Prisma CLI and the tsx runner as dev dependencies:

pnpm add -D prisma tsx

Approve Build Scripts

pnpm v10+ blocks package build scripts by default for security. Prisma needs its build scripts to download the database engine binaries. You will see a warning like this:

╭ Warning ──────────────────────────────────────────────────────╮
│                                                               │
│   Ignored build scripts: @prisma/engines, esbuild, prisma.   │
│   Run "pnpm approve-builds" to pick which dependencies       │
│   should be allowed to run scripts.                           │
│                                                               │
╰───────────────────────────────────────────────────────────────╯

Run:

pnpm approve-builds

Use the arrow keys to navigate, press Space to select each package, and press Enter to confirm. Select all three: @prisma/engines, esbuild, and prisma.

If you accidentally pressed Enter without selecting anything: pnpm adds all packages to an ignoredBuiltDependencies list in pnpm-workspace.yaml, and running pnpm approve-builds again will say "There are no packages awaiting approval." To fix this, open pnpm-workspace.yaml and replace ignoredBuiltDependencies with onlyBuiltDependencies:
onlyBuiltDependencies:
  - '@prisma/engines'
  - esbuild
  - prisma
Then run pnpm rebuild prisma @prisma/engines esbuild to trigger the build scripts manually.

After approving, pnpm will run the postinstall scripts and download the necessary binaries. Without this step, Prisma CLI commands will fail.

What each package does:

Package	Purpose
`prisma`	The CLI tool — run migrations, generate client, open studio
`@prisma/client`	The runtime library you import in your code to query the database
`@prisma/adapter-pg`	A PostgreSQL adapter that connects Prisma Client to a PostgreSQL database
`tsx`	A TypeScript runner — used later to execute the seed script

Now initialize Prisma in your project:

npx prisma init

This command creates four things:

prisma/schema.prisma — the file where you define your data models
prisma.config.ts — a TypeScript configuration file in the project root that tells Prisma where to find the schema, where to store migrations, and how to connect to the database
.env — an environment file with a placeholder DATABASE_URL pointing to a local Prisma Postgres instance
An entry in .gitignore — adds /app/generated/prisma so the auto-generated Prisma Client is not committed to Git

Create a Prisma Postgres Instance

↑ Index

You need a PostgreSQL database. Prisma offers a free cloud-hosted database called Prisma Postgres. Run:

npx prisma init --db

This command is interactive. It will:

Ask you to select a region (choose one close to your location)
Ask you to give a name to your project (e.g., next16-superblog)

Once it finishes, it prints a Database URL in the terminal and updates your .env file with a DATABASE_URL that starts with postgres://.

The command also prints a list of Next steps — you can ignore them for now. We will cover the adapter setup and migrations in the upcoming steps.

You can verify your database was created by visiting the Prisma Console. Click on your project to see the database dashboard — it shows connection details, tables, and data.

Core Concept: Environment Variables in Next.js

↑ Index

Before we configure the database connection, let's understand environment variables — a concept that is critical in any real-world project.

An environment variable is a key-value pair that lives outside your code. It is used for values that:

Change between environments — local development uses one database, production uses another
Are secrets — API keys, passwords, and database URLs should never be in your source code
Should not be committed to Git — anyone who clones your repo should not get your secrets

In Next.js, environment variables are stored in a file called .env at the root of your project:

# .env
DATABASE_URL="postgres://[email protected]:5432/postgres?sslmode=verify-full"
AUTH_SECRET="your-secret-here"

How Next.js loads them:

File	When it is loaded	Committed to Git?
`.env`	Always (local dev + production)	❌ No (add to `.gitignore`)
`.env.local`	Always, overrides `.env`	❌ No
`.env.development`	Only in `next dev`	✅ Optional
`.env.production`	Only in `next build` / `next start`	✅ Optional

Important rule: Environment variables are available on the server by default (process.env.DATABASE_URL). To expose a variable to the browser (Client Components), you must prefix it with NEXT_PUBLIC_:

# Server only (default) — safe for secrets
DATABASE_URL="..."

# Available in the browser — NEVER put secrets here
NEXT_PUBLIC_APP_NAME="Superblog"

We will never put database URLs or secrets in NEXT_PUBLIC_ variables. They stay server-side only.

Set Up the .env File

↑ Index

The npx prisma init --db command already put the DATABASE_URL in your .env. Before doing anything else, change sslmode=require to sslmode=verify-full at the end of the URL, then add an AUTH_SECRET for later:

# Database connection (created by npx prisma init --db)
DATABASE_URL="postgres://YOUR_USER:[email protected]:5432/postgres?sslmode=verify-full"

# Auth secret — we will use this in Step 12 (generate with the command below)
AUTH_SECRET="placeholder-will-replace-in-step-12"

Generate a proper AUTH_SECRET for later use:

node -e "console.log(require('crypto').randomBytes(32).toString('base64url'))"

Copy the output and replace the placeholder value in .env.

Why verify-full instead of require? The npx prisma init --db command generates the connection string with sslmode=require, which encrypts the connection but does not verify the server's TLS certificate — leaving you open to man-in-the-middle attacks. Changing it to sslmode=verify-full tells the PostgreSQL driver to also verify that the server certificate is signed by a trusted certificate authority and that the hostname matches. Prisma Postgres fully supports verify-full, so there is no reason not to use the stricter option.

Create `.env.example`

Create a file called .env.example as a template for other developers (or your future self):

# Database connection (created by npx prisma init --db)
DATABASE_URL="postgres://YOUR_USER:[email protected]:5432/postgres?sslmode=verify-full"

# Generate with: node -e "console.log(require('crypto').randomBytes(32).toString('base64url'))"
AUTH_SECRET="RANDOM_32_CHARACTER_STRING"
NEXTAUTH_URL="http://localhost:3000"

Add `.env` to `.gitignore`

Open .gitignore and make sure .env is listed (Next.js usually adds it by default, but verify):

# env files
.env
.env*.local

Why two files? .env contains your actual secrets — it is never committed to Git. .env.example contains placeholder values — it is committed, so anyone who clones the project knows which variables they need to set up.

Understand the Generated Files

↑ Index

After running npx prisma init, Prisma generated two files and modified .gitignore. Let's go through each one.

`prisma/schema.prisma`

Open it. It should look like this:

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

// Get a free hosted Postgres database in seconds: `npx create-db`

generator client {
  provider = "prisma-client"
  output   = "../app/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

Let's understand each block:

The `generator` Block

generator client {
  provider = "prisma-client"
  output   = "../app/generated/prisma"
}

This tells Prisma to generate a TypeScript client from your schema. When you run npx prisma generate, Prisma reads your models schema and creates a type-safe client library.

provider = "prisma-client" — uses the modern Prisma Client generator (Prisma 7+)
output = "../app/generated/prisma" — the generated client goes into app/generated/prisma/ instead of node_modules. This makes the generated code visible in your project, which is useful for debugging and understanding what Prisma generates. The path is relative to the prisma/ folder, so ../app/generated/prisma resolves to app/generated/prisma/ from the project root.

The `datasource` Block

datasource db {
  provider = "postgresql"
}

This tells Prisma:

provider = "postgresql" — we are using a PostgreSQL database

Notice there is no url here — the database URL is configured in prisma.config.ts instead.

`prisma.config.ts` (project root)

Prisma also generated a configuration file at the root of your project:

import 'dotenv/config'
import { defineConfig } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: {
    path: 'prisma/migrations',
  },
  datasource: {
    url: process.env['DATABASE_URL'],
  },
})

Line by line:

`import "dotenv/config";`

This loads the .env file so that process.env["DATABASE_URL"] can read its value. Without this line, Prisma would not know your database URL.

`defineConfig({ ... })`

This is Prisma's type-safe configuration function. It validates that your configuration is correct at the TypeScript level.

`schema: "prisma/schema.prisma"`

Points to the schema file. Prisma needs to know where your models schema are defined.

`migrations: { path }`

path — where migration SQL files are stored

We need to add a seed command here so Prisma knows how to seed the database. Update the migrations block in your prisma.config.ts to:

migrations: {
  path: 'prisma/migrations',
  seed: 'tsx ./prisma/seed.ts',
},

The seed property tells Prisma which command to run when you execute npx prisma db seed. We use tsx (TypeScript runner) to execute a TypeScript seed file. We will create this file in Step 6.

`datasource: { url }`

The database connection URL, read from the .env file.

Why configure the URL here instead of in the schema? The prisma.config.ts file is a TypeScript module that supports imports and environment variable loading. The schema.prisma file uses Prisma's own syntax which is more limited. Putting the URL in the config file gives you more control.

`.gitignore` entry

Prisma added this line to your .gitignore:

/app/generated/prisma

This ensures the auto-generated Prisma Client is not committed to Git — it is regenerated from the schema whenever you run npx prisma generate.

Verify the Connection

↑ Index

Now let's check everything is wired up correctly. Run:

npx prisma generate

This reads your schema.prisma file and generates the Prisma Client into app/generated/prisma/. You should see output like:

✔ Generated Prisma Client to ./app/generated/prisma

If it succeeds, your schema is valid and the generator is configured correctly.

Now let's verify the database connection by opening Prisma Studio:

npx prisma studio

This opens a browser window at http://localhost:5555 showing your database. It will be empty — no tables, no data. That is expected! We have not created any models yet.

If Studio opens without errors, your database connection is working.

You can also verify your database in the browser by visiting the Prisma Console and clicking on your project's dashboard.

Press Ctrl+C in the terminal to stop Prisma Studio when you are done.

Your project structure now

nextjs-16-crud/
├── .env                    ← Your secrets (not committed to Git)
├── .env.example            ← Template for other developers
├── prisma.config.ts        ← Prisma configuration
├── prisma/
│   └── schema.prisma       ← Data model definitions (empty for now)
├── app/
│   ├── generated/
│   │   └── prisma/         ← Auto-generated Prisma Client
│   ├── layout.tsx
│   ├── Header.tsx
│   ├── page.tsx
│   ├── not-found.tsx
│   ├── login/
│   └── posts/
└── package.json

Summary & Key Takeaways

↑ Index

Concept	What it means
ORM	A library that translates between TypeScript objects and SQL database queries
Prisma	The most popular TypeScript ORM — gives you a schema, a migration tool, and a type-safe client
Prisma Schema	A file (`schema.prisma`) where you define your data models using Prisma's syntax
Prisma Client	Auto-generated TypeScript code that lets you query the database with full type safety
Prisma Migrate	A tool that creates SQL migration files from your schema changes
Prisma Postgres	A cloud-hosted PostgreSQL database service — zero server setup
Environment variables	Key-value pairs stored in `.env` — used for secrets and configuration
`.env` vs `.env.example`	`.env` has real secrets (not committed). `.env.example` has placeholders (committed).
`prisma.config.ts`	TypeScript configuration for Prisma — where to find the schema, how to connect, how to seed

What We Have Not Done Yet

We installed Prisma and connected to the database, but we have not:

Defined any data models (no User, no Post)
Created any database tables
Written any queries

That is all coming in the next steps.

What is Next

In Step 5, we will define our data models — User and Post — in the Prisma schema, understand relationships between models, and run our first migration to create the actual database tables.

← Prev: Layouts & NavigationNext: Schema Design →

Commands in This Step

What You Will Build

Table of Contents

The Problem: How Do Web Apps Talk to Databases?

Core Concept: What is an ORM?

Core Concept: What is Prisma?

Install Prisma

Approve Build Scripts

Create a Prisma Postgres Instance

Core Concept: Environment Variables in Next.js

Set Up the .env File

Create .env.example

Add .env to .gitignore

Understand the Generated Files

prisma/schema.prisma

The generator Block

The datasource Block

prisma.config.ts (project root)

import "dotenv/config";

defineConfig({ ... })

schema: "prisma/schema.prisma"

migrations: { path }

datasource: { url }

.gitignore entry

Verify the Connection

Your project structure now

Summary & Key Takeaways

What We Have Not Done Yet

What is Next

Create `.env.example`

Add `.env` to `.gitignore`

`prisma/schema.prisma`

The `generator` Block

The `datasource` Block

`prisma.config.ts` (project root)

`import "dotenv/config";`

`defineConfig({ ... })`

`schema: "prisma/schema.prisma"`

`migrations: { path }`

`datasource: { url }`

`.gitignore` entry