Setup : Postgressql with Prisma and Neon

A starter project demonstrating how to use Prisma ORM with a PostgreSQL database using the node-postgres (pg) driver adapter.

Prerequisites

• Node.js v18+

• pnpm v10+

• A running PostgreSQL instance (local or hosted)

Setup

1. Install dependencies

pnpm add prisma @types/pg typescript @types/node tsx --save-dev
pnpm add @prisma/client @prisma/adapter-pg pg dotenv

What each package does:

Package	Role
prisma	Prisma CLI — prisma init, prisma migrate, prisma generate, etc.
@prisma/client	Prisma Client library for querying your database
@prisma/adapter-pg	node-postgres driver adapter that connects Prisma Client to PostgreSQL
pg	node-postgres database driver
dotenv	Loads environment variables from .env
@types/pg	TypeScript type definitions for node-postgres
tsx	TypeScript executor — run .ts files directly without compiling
typescript / @types/node	TypeScript compiler and Node.js type definitions

2. Initialize Prisma

npx prisma init

This creates:

• prisma/schema.prisma — your schema file

• .env — with a DATABASE_URL placeholder

3. Configure environment

Edit .env with your PostgreSQL connection string:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"

Example for a local database:

DATABASE_URL="postgresql://postgres:password@localhost:5432/mydb"

Never commit .env to version control. Add it to .gitignore.

4. Define your schema

Edit prisma/schema.prisma:

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

datasource db {
  provider = "postgresql"
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

The output path in the generator block controls where the generated Prisma Client is written. Here it outputs to lib/generated/prisma, which is why the import path is ./generated/prisma/client.

5. Run migrations

npx prisma migrate dev

This creates the tables in your database and generates the Prisma Client automatically.

To regenerate the client without creating a new migration (e.g. after a manual schema edit):

npx prisma generate

6. Open Prisma Studio (optional)

A visual browser-based editor for your database:

npx prisma studio

Database client

Create lib/db.ts to set up a singleton Prisma Client instance:

import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

function createPrismaClient() {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL is not defined");
  }
  const adapter = new PrismaPg({ connectionString: url });
  const client = new PrismaClient({ adapter });
  return client;
}

export const prisma = globalForPrisma.prisma ?? createPrismaClient();

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

The global singleton pattern prevents creating multiple Prisma Client instances during hot reloads in development (e.g. Next.js).

Test the connection

Create script.ts at the project root:

import { prisma } from "./lib/db";

async function main() {
  // Create a user with a post
  const user = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@prisma.io",
      posts: {
        create: {
          title: "Hello World",
          content: "This is my first post!",
          published: true,
        },
      },
    },
    include: { posts: true },
  });
  console.log("Created user:", user);

  // Fetch all users with their posts
  const allUsers = await prisma.user.findMany({
    include: { posts: true },
  });
  console.log("All users:", JSON.stringify(allUsers, null, 2));
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

Run it:

pnpm dlx tsx script.ts

Project structure

.
├── lib/
│   ├── db.ts                    # Prisma Client singleton
│   └── generated/prisma/        # Auto-generated Prisma Client (do not edit)
├── prisma/
│   ├── schema.prisma            # Database schema
│   └── migrations/              # Migration history
├── prisma.config.ts             # Prisma CLI configuration
├── script.ts                    # Test / seed script
├── .env                         # Environment variables (not committed)
└── tsconfig.json

Common commands

Command	Description
npx prisma init	Initialize Prisma in a new project
npx prisma migrate dev	Create a migration and apply it (dev only)
npx prisma migrate deploy	Apply pending migrations (production)
npx prisma generate	Regenerate Prisma Client from schema
npx prisma studio	Open the visual database browser
npx prisma db push	Push schema changes without a migration file
npx prisma db seed	Run the seed script defined in package.json
pnpm dlx tsx script.ts	Run a TypeScript file directly