HeyBinyang

Part 2: Build Your First Prisma Project from Scratch with PostgreSQL

·Tech·visibility3 reads ·Development

In the first article, we got to know Prisma from a macro perspective. Now, in this one, we'll start directly from a real project: using Prisma 7 + Next.js 16.2.2 + PostgreSQL + pnpm, from an empty directory to "being able to see the first User record from the database on the page".

You will complete:

  • Create a Next.js 16.2.2 project (App Router);

  • Configure pnpm as the package manager;

  • Set up the PostgreSQL database;

  • Initialize Prisma 7 (including prisma.config.ts);

  • Define the first User model and migrate to the database;

  • Use Prisma Client in a Next.js Server Component to query and render data.

Complete Command Overview (Quick Reference)

Here's a quick look at all the key commands used in this article:

# 1. Confirm / Install pnpm
corepack enable
corepack prepare pnpm@latest --activate

# 2. Create Next.js 16.2.2 project
pnpm create next-app@16.2.2 prisma-next-demo

cd prisma-next-demo

# 3. Install Prisma 7 + PostgreSQL dependencies
pnpm add -D prisma tsx @types/node @types/pg
pnpm add @prisma/client @prisma/adapter-pg pg dotenv

# 4. Initialize Prisma 7
pnpm prisma init

# 5. Generate and apply the first migration based on schema
pnpm prisma migrate dev --name init

# 6. Check migration status (optional)
pnpm prisma migrate status

# 7. Open Prisma Studio (visual data insertion)
pnpm prisma studio

# 8. Start Next.js development server
pnpm dev

The main text will explain step by step what each command does.

1. Prepare Environment: Node.js, pnpm, PostgreSQL

1.1 Node.js and pnpm

Make sure you have Node.js installed (v18+ recommended), then enable pnpm via Corepack:

corepack enable
corepack prepare pnpm@latest --activate
pnpm -v

If you see a version number, pnpm is ready to use.

1.2 Prepare PostgreSQL

You can provide PostgreSQL in any way. Here's a common Docker example:

docker run -itd \
  -e POSTGRES_USER=prisma \
  -e POSTGRES_PASSWORD=prisma \
  -e POSTGRES_DB=next_prisma_demo \
  -p 5432:5432 \
  --name next-prisma-postgres \
  postgres

Our conventions:

  • User: prisma

  • Password: prisma

  • Database: next_prisma_demo

  • Port: 5432

The .env DATABASE_URL will be:

DATABASE_URL="postgresql://prisma:prisma@localhost:5432/next_prisma_demo?schema=public"

If you're using a cloud PostgreSQL (Neon / Supabase / Railway / Prisma Postgres), simply replace the connection string.

2. Create Next.js 16.2.2 Project with pnpm

In your prepared directory, run:

pnpm create next-app@16.2.2 prisma-next-demo
cd prisma-next-demo

In the interactive guide, it's recommended to select:

  • TypeScript: Yes

  • ESLint: Yes

  • Tailwind CSS: Yes (it will make the UI much nicer later)

  • Use src/ directory: optional; the examples below assume no src, meaning app/ is at the root; nextjs

  • App Router: Yes

  • Import alias: Keep default @/*.

After this step, you have a Next.js 16.2.2 App Router project skeleton.

3. Install Prisma 7 and PostgreSQL Dependencies

According to the official Prisma 7 Postgres Quickstart, we need: CLI + Client + PostgreSQL adapter + driver.

Run in the project:

pnpm add -D prisma tsx @types/node @types/pg
pnpm add @prisma/client @prisma/adapter-pg pg dotenv

Role explanation:

  • prisma: CLI tool (pnpm prisma ...);

  • @prisma/client: TypeScript client generated from schema;

  • @prisma/adapter-pg: Prisma 7 recommended PostgreSQL driver adapter;

  • pg: node-postgres driver;

  • dotenv: reads .env;

  • tsx: convenient for writing seed scripts later.

4. Initialize Prisma 7: Generate Config and Schema

Prisma 7 recommends using prisma init to generate a set of default configurations including prisma.config.ts.

Run in the project root:

pnpm prisma init

After execution, you'll see these new files:

  • prisma/schema.prisma

  • prisma.config.ts

  • .env

These three files handle:

  • Schema: describes data models;

  • Config: tells the Prisma CLI about schema/migrations/datasource configuration;

  • .env: provides environment variables like DATABASE_URL.

4.1 Configure .env DATABASE_URL

Open .env and fill in the connection string based on the PostgreSQL you prepared:

DATABASE_URL="postgresql://prisma:prisma@localhost:5432/next_prisma_demo?schema=public"

If using Prisma Postgres, fill in the PostgreSQL connection URL provided in the Prisma console. Make sure it's in the postgresql:// format.

4.2 Configure prisma.config.ts

The configuration method given in the official Prisma Next.js guide is roughly as follows:

// prisma.config.ts
import 'dotenv/config'
import { defineConfig, env } from 'prisma/config'

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

This configuration explains:

  • Prisma CLI uses prisma/schema.prisma as the schema file;

  • Database migrations are stored in prisma/migrations;

  • The datasource URL is read from the environment variable DATABASE_URL;

  • Uses dotenv/config to automatically load .env.

5. Write the First Prisma Schema: User Model

Open prisma/schema.prisma and change it to a minimal usable version:

// prisma/schema.prisma

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

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

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
}

A few points here:

  • Using provider = "prisma-client" with a custom output directory ../generated/prisma is one of the recommended approaches in Prisma 7;

  • datasource is explicitly set to postgresql;

  • User model includes:

  • Auto-increment Integer primary key id;

  • Simple string field name;

  • Unique constraint on email;

  • Auto-fill current time createdAt.

All subsequent Prisma Client types and safety features originate from this schema.

6. Generate and Apply the First Migration (Development)

The official Prisma recommended workflow is: use migrate dev for development, and migrate deploy for production.

Run in the project root:

pnpm prisma migrate dev --name init

It will:

  • Generate a migration based on the current schema.prisma and database state;

  • Write the migration SQL into prisma/migrations/*/migration.sql;

  • Apply it to your PostgreSQL database;

  • Generate or update Prisma Client.

If successful, you can optionally check the migration status:

pnpm prisma migrate status

It will show which migrations have been applied and whether the current database is in sync with the schema.

Production reminder (will be covered in detail later):
Using migrate dev in development is fine, but in production, only use migrate deploy and put it into your CI/CD pipeline, not manually on the server.

7. Create a Prisma Client Singleton in Next.js

To use Prisma Client safely in Next.js (especially with hot reloading in development), the official Next.js guide recommends using a singleton pattern with an adapter.

Create a new file lib/prisma.ts in the project:

// lib/prisma.ts
import { PrismaClient } from '../generated/prisma'
import { PrismaPg } from '@prisma/adapter-pg'

const connectionString = process.env.DATABASE_URL
if (!connectionString) {
  throw new Error('DATABASE_URL is not set')
}

const adapter = new PrismaPg({ connectionString })

const globalForPrisma = globalThis as unknown as {
  prisma?: PrismaClient
}

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    adapter,
  })

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

This code illustrates several key concepts:

  • Use the PrismaPg adapter to connect Prisma Client to PostgreSQL;

  • In development, attach the PrismaClient instance to globalThis to avoid creating too many connections during hot reload;

  • In production, create a single instance normally without polluting the global scope.

Throughout the project, you only need to import prisma from lib/prisma without creating a new instance again.

8. First Database Query on the Next.js Home Page

Next, we'll use Prisma to query the User table in app/page.tsx and render the results on the page.

Edit app/page.tsx:

// app/page.tsx
import { prisma } from '@/lib/prisma'

export default async function Home() {
  const users = await prisma.user.findMany({
    orderBy: { createdAt: 'desc' },
  })

  return (
    <main style={{ padding: '32px' }}>
      <h1>Prisma 7 + Next.js 16.2.2 + PostgreSQL</h1>
      <p>Current number of users: {users.length}</p>

      <ul>
        {users.map((u) => (
          <li key={u.id}>
            {u.name} - {u.email}
          </li>
        ))}
      </ul>
    </main>
  )
}

Since this is a Server Component (default), we can directly use await prisma.user.findMany in the function body. This is a very natural combination of Next.js App Router and Prisma.

The only problem now is that there is no user data in the database. We'll add one right away.

9. Insert the First User with Prisma Studio

Prisma Studio is the official Web UI to view and edit data directly in the browser.

Run in the project root:

pnpm prisma studio

A browser interface will open. On the left side, you'll see the User table:

  1. Click User;

  2. Click "Add Record";

  3. Fill in, for example:

  • name: Alice

  • email: alice@example.com;

  1. Save.

Back in the terminal, start the Next.js development server:

pnpm dev

Open your browser and go to http://localhost:3000. You should see:

  • The title;

  • "Current number of users: 1";

  • A list item with Alice - alice@example.com.

At this point, the full chain is working: Browser → Next.js App Router → Prisma Client → PostgreSQL → Return Data → Page Render.

10. Example package.json (Prisma + Next.js + pnpm)

For your reference, here is a typical package.json snippet (only showing relevant parts from this article). You can adjust as needed:

{
  "name": "prisma-next-demo",
  "private": true,
  "scripts": {
    "dev": "next dev",
    "build": "next build",
    "start": "next start",
    "lint": "next lint",
    "prisma:generate": "prisma generate",
    "prisma:migrate": "prisma migrate dev",
    "prisma:deploy": "prisma migrate deploy",
    "prisma:studio": "prisma studio"
  },
  "dependencies": {
    "@prisma/adapter-pg": "^7.0.0",
    "@prisma/client": "^7.0.0",
    "dotenv": "^16.0.0",
    "next": "16.2.2",
    "pg": "^8.0.0",
    "react": "18.3.1",
    "react-dom": "18.3.1"
  },
  "devDependencies": {
    "@types/node": "^22.0.0",
    "@types/pg": "^8.0.0",
    "prisma": "^7.0.0",
    "tsx": "^4.0.0",
    "typescript": "^5.0.0"
  }
}

  • You can adjust version numbers based on actual installation (^7.0.0 indicates Prisma 7 major version);

  • Adding prisma:migrate / prisma:deploy scripts makes CI/CD calls more convenient.

11. Common Errors and Troubleshooting

11.1 Error: P1001 Cannot connect to database

Typical errors contain "The database server was reached but timed out" or "Cannot reach database server". Common causes:

  • Docker container not started / PostgreSQL not running;

  • .env DATABASE_URL is incorrect (username/password/database name/port);

  • Database firewall rules restricting access (for remote cloud databases).

Troubleshooting suggestions:

  • Confirm Docker container is running: docker ps;

  • Use psql / a GUI tool to test the connection and verify the connection string;

  • Ensure the Next.js project and database are network-accessible to each other.

11.2 Error: DATABASE_URL is not set

In lib/prisma.ts, we explicitly check for process.env.DATABASE_URL and throw an error if missing.

Common causes:

  • .env file not created;

  • .env is in the wrong path (not the project root);

  • The start command was not executed from the project root.

Troubleshooting suggestions:

  • Confirm .env is in the same directory as package.json;

  • Confirm you run pnpm dev from the project root;

  • Restart the development server to reload environment variables.

11.3 relation "User" does not exist

This means the table does not exist in the database, but you have already called a query. Common cause: forgot to run migrate dev or migrate deploy.

Solution:

pnpm prisma migrate dev --name init

Then restart pnpm dev.

11.4 Next.js Development: Too many connections / Warnings

If you frequently see Postgres errors like "too many clients already" during development, it's usually because PrismaClient is being instantiated multiple times, leading to connection pool accumulation.

Solution:

  • Ensure lib/prisma.ts uses the global singleton pattern (globalThis.prisma);

  • Make sure all imports use import { prisma } from '@/lib/prisma', and no place manually does new PrismaClient.

12. Summary: After This Article, You Have an "Extensible" Project Starting Point

In this article, we deliberately set up Prisma from scratch within the Prisma 7 + Next.js 16.2.2 + pnpm + PostgreSQL stack, rather than in a standalone script project. This ensures that all future practical articles (Next.js, MCP, AI-assisted development) can connect seamlessly.

By now, you have:

  • A modern Next.js App Router project skeleton;

  • A PostgreSQL database with trackable migration history;

  • A Prisma 7-based Client singleton adapted for PostgreSQL;

  • The first type-safe database query from a page.

Reference Links

Prisma Documentation

  1. https://www.prisma.io/docs/guides/nextjs

  2. https://www.prisma.io/docs/guides/frameworks/nextjs

  3. https://www.prisma.io/docs/getting-started/prisma-orm/quickstart/postgresql

  4. https://www.prisma.io/docs/cli/migrate/deploy

  5. https://www.prisma.io/docs/guides/use-prisma-in-pnpm-workspaces

  6. https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production

GitHub

  1. https://github.com/prisma/prisma/discussions/24571

Share

XRedditFacebookInstagramLinkedIn