Part 2: Setting Up Prisma with PostgreSQL

Introduction

In Part 1, we explored what ORMs are and why they matter. Now it's time to get our hands dirty. In this article, I'll walk you through setting up a TypeScript project with Prisma ORM and PostgreSQL—the same setup I use for my production applications.

I remember spending an entire evening struggling with database connections and TypeScript configurations when I first started. This guide will save you that time by showing you the exact steps I now follow for every new project.

Prerequisites

Before we start, ensure you have these installed:

# Check Node.js version (I use v18 or higher)
node --version  # Should be v18.0.0 or higher

# Check npm version
npm --version

# Check PostgreSQL (we'll also cover Docker option)
psql --version

If you don't have PostgreSQL installed, don't worry—I'll show you how to run it with Docker, which is what I use for local development.

Project Setup

Step 1: Initialize a TypeScript Project

Let me walk you through creating a new project from scratch:

# Create project directory
mkdir prisma-orm-tutorial
cd prisma-orm-tutorial

# Initialize npm project
npm init -y

# Install TypeScript and essential dependencies
npm install -D typescript ts-node @types/node nodemon

# Install Prisma CLI as dev dependency
npm install -D prisma

# Install Prisma Client (runtime)
npm install @prisma/client

Step 2: Configure TypeScript

Create a tsconfig.json file. This is the configuration I use for all my Node.js backend projects:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "commonjs",
    "lib": ["ES2022"],
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true,
    "moduleResolution": "node",
    "declaration": true,
    "declarationMap": true,
    "sourceMap": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

Step 3: Set Up PostgreSQL Database

I use Docker for local PostgreSQL because it keeps my machine clean and makes it easy to reset the database. Here's my docker-compose.yml:

version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: prisma-postgres
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: prisma_tutorial
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Start the database:

# Start PostgreSQL in the background
docker-compose up -d

# Check it's running
docker-compose ps

Alternative: Using Local PostgreSQL

If you have PostgreSQL installed locally, create a database:

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE prisma_tutorial;

# Exit psql
\q

Initializing Prisma

Step 4: Initialize Prisma in Your Project

This command creates the Prisma directory and configuration files:

npx prisma init

This creates two important files:

1. prisma/schema.prisma - Your database schema definition

2. .env - Environment variables (including database connection)

Step 5: Configure Database Connection

Open .env and update the database connection string:

# For Docker setup (default)
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/prisma_tutorial?schema=public"

# Format: postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

Connection String Breakdown:

• postgresql:// - Database type

• postgres:postgres - Username:password

• localhost:5432 - Host:port

• prisma_tutorial - Database name

• ?schema=public - PostgreSQL schema (default is public)

Step 6: Create Your First Schema

Open prisma/schema.prisma. This is where magic happens. Here's a real schema from one of my projects, simplified for learning:

// This is your Prisma schema file
// Learn more: https://pris.ly/d/prisma-schema

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

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

// User model - represents a users table
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  password  String
  role      String   @default("user")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relation: One user can have many posts
  posts     Post[]
}

// Post model - represents a posts table
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Foreign key to User
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

Schema Breakdown:

• generator client: Tells Prisma to generate TypeScript client code

• datasource db: Defines PostgreSQL as our database

• model User: Creates a users table (Prisma pluralizes by default)

• @id: Marks primary key

• @default(autoincrement()): Auto-incrementing integer

• @unique: Ensures email uniqueness

• @updatedAt: Automatically updates on record change

• posts Post[]: One-to-many relationship

• @relation(...): Defines foreign key constraint

Step 7: Create Your First Migration

Migrations track database schema changes over time. Here's how I create them:

# Create and apply migration
npx prisma migrate dev --name init

# What this does:
# 1. Generates SQL migration files
# 2. Applies migration to database
# 3. Generates Prisma Client

You'll see output like:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "prisma_tutorial"

PostgreSQL database created at localhost:5432

Applying migration `20240204120000_init`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20240204120000_init/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (5.x.x) to ./node_modules/@prisma/client

Let's look at the generated SQL in prisma/migrations/20240204120000_init/migration.sql:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "role" TEXT NOT NULL DEFAULT 'user',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "authorId" INTEGER NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" 
  FOREIGN KEY ("authorId") REFERENCES "User"("id") 
  ON DELETE CASCADE ON UPDATE CASCADE;

Creating Your First TypeScript File

Step 8: Set Up Prisma Client

Create src/index.ts:

import { PrismaClient } from '@prisma/client';

// Initialize Prisma Client
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'], // Useful for debugging
});

async function main() {
  // Test connection
  console.log('🔌 Connecting to database...');
  
  // Create a user
  const user = await prisma.user.create({
    data: {
      email: '[email protected]',
      name: 'Hassan',
      password: 'hashed_password_here', // In real apps, use bcrypt
    },
  });
  
  console.log('✅ Created user:', user);
  
  // Create a post for this user
  const post = await prisma.post.create({
    data: {
      title: 'My First Post with Prisma',
      content: 'Learning Prisma ORM is awesome!',
      published: true,
      authorId: user.id,
    },
  });
  
  console.log('✅ Created post:', post);
  
  // Fetch user with posts
  const userWithPosts = await prisma.user.findUnique({
    where: { email: '[email protected]' },
    include: { posts: true },
  });
  
  console.log('✅ User with posts:', JSON.stringify(userWithPosts, null, 2));
}

main()
  .catch((e) => {
    console.error('❌ Error:', e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Step 9: Add Scripts to package.json

Update your package.json:

{
  "name": "prisma-orm-tutorial",
  "version": "1.0.0",
  "scripts": {
    "dev": "nodemon --exec ts-node src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js",
    "prisma:migrate": "prisma migrate dev",
    "prisma:generate": "prisma generate",
    "prisma:studio": "prisma studio"
  }
}

Step 10: Run Your Application

# Run in development mode
npm run dev

You should see output like:

🔌 Connecting to database...
✅ Created user: {
  id: 1,
  email: '[email protected]',
  name: 'Hassan',
  password: 'hashed_password_here',
  role: 'user',
  createdAt: 2024-02-04T12:00:00.000Z,
  updatedAt: 2024-02-04T12:00:00.000Z
}
✅ Created post: {
  id: 1,
  title: 'My First Post with Prisma',
  content: 'Learning Prisma ORM is awesome!',
  published: true,
  createdAt: 2024-02-04T12:00:00.000Z,
  updatedAt: 2024-02-04T12:00:00.000Z,
  authorId: 1
}
✅ User with posts: {
  "id": 1,
  "email": "[email protected]",
  "name": "Hassan",
  "password": "hashed_password_here",
  "role": "user",
  "createdAt": "2024-02-04T12:00:00.000Z",
  "updatedAt": "2024-02-04T12:00:00.000Z",
  "posts": [
    {
      "id": 1,
      "title": "My First Post with Prisma",
      "content": "Learning Prisma ORM is awesome!",
      "published": true,
      "createdAt": "2024-02-04T12:00:00.000Z",
      "updatedAt": "2024-02-04T12:00:00.000Z",
      "authorId": 1
    }
  ]
}

Exploring Your Database with Prisma Studio

Prisma Studio is a visual database browser. I use it constantly during development:

npm run prisma:studio

This opens http://localhost:5555 in your browser, where you can:

• View all tables

• Browse records

• Edit data directly

• Test queries

Common Issues I've Encountered

Issue 1: "Can't reach database server"

Error: P1001: Can't reach database server at localhost:5432

Solution:

# Check if PostgreSQL is running
docker-compose ps

# Restart if needed
docker-compose restart

Issue 2: "Environment variable not found: DATABASE_URL"

Make sure .env exists in your project root and is not in .gitignore for local development (but exclude it in production repos).

Issue 3: Port 5432 already in use

# Find what's using port 5432
lsof -i :5432

# Kill the process or use different port in docker-compose.yml
ports:
  - "5433:5432"  # Map to 5433 on host

# Update DATABASE_URL
DATABASE_URL="postgresql://postgres:postgres@localhost:5433/prisma_tutorial?schema=public"

Project Structure

Here's how my project looks now:

prisma-orm-tutorial/
├── node_modules/
├── prisma/
│   ├── migrations/
│   │   └── 20240204120000_init/
│   │       └── migration.sql
│   └── schema.prisma
├── src/
│   └── index.ts
├── .env
├── .gitignore
├── docker-compose.yml
├── package.json
├── tsconfig.json
└── README.md

Best Practices I Follow

1. Always use migrations: Never modify the database directly in production

2. Version control migrations: Commit migration files to Git

3. Exclude .env from Git: Use .env.example for templates

4. Use Prisma Studio: Great for debugging and exploring data

5. Enable query logging: Helps understand what SQL is generated

6. Handle disconnection: Always call prisma.$disconnect() in finally block

What's Next?

We now have a fully functional TypeScript + Prisma + PostgreSQL setup! In Part 3, we'll dive deeper into:

• Schema design best practices

• Defining complex relationships (one-to-many, many-to-many)

• Data validation with Prisma

• Migration strategies for evolving schemas

• Indexing for performance

Conclusion

Setting up Prisma with TypeScript and PostgreSQL is straightforward once you know the steps. The combination of type safety from TypeScript and the developer experience from Prisma makes building database-backed applications enjoyable.

The setup we've created here is production-ready (with a few security additions we'll cover later). I use this exact stack for microservices handling thousands of requests per second.

---

This article is part of my personal knowledge sharing based on real projects I've built. The setup shown here is what I use in production with minor adjustments for secrets management.