Part 2: Database Integration

PostgreSQL & Prisma ORM

Complete Step-by-Step Guide for Beginners

──────────────────────────────

Prerequisite: Zero to API with [Link] (Part 1)

Backend Development Lab - Complete Edition
Table of Contents
Right-click and select "Update Field" to refresh page numbers

What You Will Learn ..................................................................................................................... 3

STEP 1: Install PostgreSQL Database ............................................................................................. 4

STEP 2: PostgreSQL GUI Tools (IDE) ............................................................................................... 7

STEP 3: Create Database and User ................................................................................................ 9

STEP 4: Understanding the Database URL.................................................................................... 11

STEP 5: Project Setup & Prisma Installation ................................................................................. 12

STEP 6: Database Schema Design ................................................................................................ 14

STEP 7: Run Migration ................................................................................................................ 16

STEP 8: Converting routes/[Link] to Database........................................................................... 17

STEP 9: Adding Posts with Relationships ..................................................................................... 22

STEP 10: Error Handling .............................................................................................................. 26

STEP 11: Testing with Postman/HTTPie ....................................................................................... 27

Troubleshooting Guide ............................................................................................................... 30

Final Project Structure ................................................................................................................ 32

 NodeJS + PostgreSQL + Prisma Tutorial

What You Will Learn

In Part 1, you built a REST API with [Link] using an in-memory array:

// routes/[Link] (from Part 1)

let users = [
{ id: 1, name: 'Alice', role: 'student' },
{ id: 2, name: 'Bob', role: 'instructor' }
];

// PROBLEM: Data is lost when server restarts!

In this tutorial, you will learn to:

• Install PostgreSQL database on your computer

• Create and manage databases using GUI tools

• Connect your Express app to a real database

• Use Prisma ORM to work with the database

• Build relationships between data (Users and Posts)

• Test your API using Postman and HTTPie

Prerequisites:You must have completed Part 1 (Zero to API with [Link]) and have a
working backend-lab project with [Link] and npm installed.

3 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 1: Install PostgreSQL Database

PostgreSQL is a database server that stores your data permanently. Unlike the in-memory array, data
in PostgreSQL survives server restarts.

Option A: Windows Installation

0. Go to [Link]

1. Click the download link for Windows x86-64

2. Run the downloaded installer

3. Click 'Next' through the setup wizard

4. When asked for a password, enter: postgres123 (write this down!)

5. Keep the default port: 5432

6. Complete the installation

Verify Windows Installation

# Open Command Prompt and type:
psql --version

# You should see something like:

# psql (PostgreSQL) 15.4

Option B: macOS Installation

The easiest way on macOS is using Homebrew:

# Install PostgreSQL
brew install postgresql@15

# Start PostgreSQL service

brew services start postgresql@15

# Verify installation
psql --version

Alternative: [Link]
If Homebrew doesn't work:

7. Download from [Link]

8. Drag [Link] to your Applications folder

9. Open [Link] and click 'Initialize'

Option C: Linux (Ubuntu/Debian) Installation

# Update package list

4 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service

sudo systemctl start postgresql

# Enable PostgreSQL to start on boot

sudo systemctl enable postgresql

# Verify installation
psql --version

5 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 2: PostgreSQL GUI Tools (IDE)

While you can use the command line, a GUI (Graphical User Interface) tool makes it easier to view
and edit your database. Here are the options:

Option 1: pgAdmin (Recommended - Free)

pgAdmin comes bundled with PostgreSQL on Windows. On Mac/Linux, install it separately:

# macOS with Homebrew

brew install --cask pgadmin4

# Or download from [Link]

To use pgAdmin:

10. Open pgAdmin

11. Double-click 'Servers' in the left panel

12. Enter the password you set during installation (postgres123)

13. Expand: Servers > PostgreSQL 15 > Databases

14. You'll see your databases listed here

Option 2: DBeaver (Free, Cross-Platform)

DBeaver is a universal database tool that works with PostgreSQL and many other databases:

15. Download from [Link]

16. Install and open DBeaver

17. Click 'New Database Connection' (plug icon)

18. Select PostgreSQL and click Next

19. Enter these settings:

Host: localhost
Port: 5432
Database: postgres
Username: postgres
Password: postgres123 (or whatever you set)

Option 3: TablePlus (Mac/Windows - Free Trial)

20. Download from [Link]

21. Click 'Create a new connection'

22. Select PostgreSQL

23. Enter the same connection details as above

6 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

Why Use a GUI?:GUI tools let you: - See all your tables and data visually - Run SQL queries
with syntax highlighting - Edit data directly in a spreadsheet-like view - Export and import
data easily

7 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 3: Create Database and User

Now you need to create a database specifically for your project and a user to access it.

Method 1: Using Command Line

Open your terminal/command prompt:

# Switch to the postgres user (Linux/Mac)

sudo -u postgres psql

# On Windows, open 'SQL Shell (psql)' from Start Menu

# Press Enter to accept defaults, then enter your password
Once you're in the PostgreSQL prompt (you'll see postgres=#), run these commands:

-- Create a new database for your project

CREATE DATABASE backend_lab_db;

-- Create a new user with a password

CREATE USER lab_user WITH ENCRYPTED PASSWORD 'your_password_here';

-- Give the user permission to use the database

GRANT ALL PRIVILEGES ON DATABASE backend_lab_db TO lab_user;

-- Exit PostgreSQL
\q

Method 2: Using pgAdmin (GUI)

24. Open pgAdmin and connect to your server

25. Right-click on 'Databases' > 'Create' > 'Database'

26. Enter Database name: backend_lab_db

27. Click 'Save'

28. Right-click on 'Login/Group Roles' > 'Create' > 'Login/Group Role'

29. In the General tab, enter Name: lab_user

30. In the Definition tab, enter Password: your_password_here

31. In the Privileges tab, enable 'Can login?'

32. Click 'Save'

Verify Your Database Was Created

# List all databases
psql -U postgres -l

# You should see 'backend_lab_db' in the list

8 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 4: Understanding the Database URL

Your application needs a connection string (URL) to connect to PostgreSQL. This URL contains all the
connection details.

Database URL Format

postgresql://USERNAME:PASSWORD@HOST:PORT/DATABASE?schema=public
Breaking down each part:

Part Description Example

USERNAME The database user you created lab_user

PASSWORD The password for that user your_password_here

HOST Where PostgreSQL is running localhost

PORT PostgreSQL port (default 5432) 5432

DATABASE The database name backend_lab_db

schema The schema within the database public

Your Database URL

Based on what we created in Step 3, your URL will be:

# Replace 'your_password_here' with the actual password you set

postgresql://lab_user:your_password_here@localhost:5432/backend_lab_db?schema=p
ublic

Important Security Note:Never commit your .env file with the real password to GitHub. Add
.env to your .gitignore file.

9 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 5: Project Setup & Prisma Installation

Now let's add Prisma to your existing backend-lab project from Part 1.

Install Prisma Packages

# Navigate to your project folder
cd backend-lab

# Install Prisma CLI (command line tool)

npm install prisma@6 --save-dev

# Install Prisma Client (for your code to use)

npm install @prisma/client@6

# Install dotenv for environment variables

npm install dotenv

What Was Installed?

• prisma - The CLI tool for running migrations and generating code

• @prisma/client - The library your [Link] code uses to talk to the database

• dotenv - Loads environment variables from .env file

Initialize Prisma
npx is a tool that comes with npm (installed with [Link]). It lets you run command-line tools:

# Initialize Prisma in your project

npx prisma init

# If npx doesn't work, try:

npm exec prisma init
This creates two files:

• prisma/[Link] - Where you define your database tables

• .env - Where you store your database URL

Configure Your Database URL

Open the .env file and add your database connection URL:

# File: .env

# Database connection URL

DATABASE_URL="postgresql://lab_user:your_password_here@localhost:5432/backend_l
ab_db?schema=public"

# Server port
PORT=3000

10 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

Replace the Password!:Make sure to replace 'your_password_here' with the actual

password you set in Step 3.

11 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 6: Database Schema Design

The schema defines what tables your database has and what columns they contain.

Open the Schema File

Open prisma/[Link] in your code editor. Replace its contents with:

// File: prisma/[Link]

// This tells Prisma to generate a JavaScript client

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

// This tells Prisma you're using PostgreSQL

// and to read the connection URL from environment variables
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

// Define the User table

model User {
id Int @id @default(autoincrement())
email String @unique
name String
role String @default("student")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt

@@map("users")
}

Understanding the Schema

Line Meaning

@id This is the primary key (unique identifier)

@default(autoincrement()) Automatically assign the next number

@unique No two users can have the same email

@default(now()) Set to current date/time when created

@updatedAt Automatically update when record changes

@@map("users") Name the actual database table 'users'

12 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 7: Run Migration

A migration applies your schema to the actual database, creating the tables.

Create Your First Migration

# Run this command in your terminal (inside backend-lab folder)
npx prisma migrate dev --name init

# Or if npx doesn't work:

npm exec prisma migrate dev --name init
What happens when you run this command:

33. Prisma compares your schema with the database

34. It creates a SQL migration file in prisma/migrations/

35. It runs the SQL to create the 'users' table

36. It generates the Prisma Client code

Verify in pgAdmin
Open pgAdmin and check that the 'users' table was created:

37. Expand: Servers > PostgreSQL 15 > Databases > backend_lab_db

38. Expand: Schemas > public > Tables

39. You should see the 'users' table listed!

13 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 8: Converting routes/[Link] to Database

Now we transform your in-memory users from Part 1 to use the database. Here's the comparison:

BEFORE: In-Memory (Part 1)

// File: routes/[Link] (Part 1)
const express = require('express');
const router = [Link]();

// In-memory data - LOST on restart!

let users = [
{ id: 1, name: 'Alice', role: 'student' },
{ id: 2, name: 'Bob', role: 'instructor' }
];

[Link]('/', (req, res) => {

[Link]({ count: [Link], data: users });
});

[Link] = router;

AFTER: Database Version

First, create the Prisma client file:

// File: lib/[Link]
const { PrismaClient } = require('@prisma/client');

// Create a single Prisma client instance

const prisma = new PrismaClient();

// Clean up when the app closes

[Link]('beforeExit', async () => {
await prisma.$disconnect();
});

[Link] = prisma;
Now replace routes/[Link] with the database version:

// File: routes/[Link] - DATABASE VERSION

const express = require('express');
const router = [Link]();
const prisma = require('../lib/prisma'); // Import Prisma client

// GET /api/users - List all users

[Link]('/', async (req, res) => {
try {
const { role, search } = [Link];

// Build filter object

const where = {};
if (role) [Link] = role;
if (search) {
[Link] = { contains: search, mode: 'insensitive' };
}

14 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

// Fetch users from DATABASE (not memory!)

const users = await [Link]({
where,
orderBy: { createdAt: 'desc' }
});

[Link]({ count: [Link], data: users });

} catch (error) {
[Link]('Error:', error);
[Link](500).json({ error: 'Failed to fetch users' });
}
});
// File: routes/[Link] (continued)
// GET /api/users/:id - Get single user
[Link]('/:id', async (req, res) => {
try {
const userId = parseInt([Link]);

// Validate ID is a number
if (isNaN(userId)) {
return [Link](400).json({ error: 'Invalid user ID' });
}

// Find user in database

const user = await [Link]({
where: { id: userId }
});

if (!user) {
return [Link](404).json({ error: 'User not found' });
}

[Link](user);
} catch (error) {
[Link](500).json({ error: 'Failed to fetch user' });
}
});
// File: routes/[Link] (continued)
// POST /api/users - Create new user
[Link]('/', async (req, res) => {
try {
const { email, name, role } = [Link];

// Validate required fields

if (!email || !name) {
return [Link](400).json({
error: 'Email and name are required'
});
}

// Create user in database

const user = await [Link]({
data: {
email,
name,
role: role || 'student'

15 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

}
});

[Link](201).json(user);
} catch (error) {
// Handle duplicate email error
if ([Link] === 'P2002') {
return [Link](409).json({ error: 'Email already exists' });
}
[Link](500).json({ error: 'Failed to create user' });
}
});
// File: routes/[Link] (continued)
// PUT /api/users/:id - Update user
[Link]('/:id', async (req, res) => {
try {
const userId = parseInt([Link]);
const { email, name, role } = [Link];

const user = await [Link]({

where: { id: userId },
data: { email, name, role }
});

[Link](user);
} catch (error) {
if ([Link] === 'P2025') {
return [Link](404).json({ error: 'User not found' });
}
[Link](500).json({ error: 'Failed to update user' });
}
});
// File: routes/[Link] (continued)
// DELETE /api/users/:id - Delete user
[Link]('/:id', async (req, res) => {
try {
const userId = parseInt([Link]);

await [Link]({
where: { id: userId }
});

[Link](204).send();
} catch (error) {
if ([Link] === 'P2025') {
return [Link](404).json({ error: 'User not found' });
}
[Link](500).json({ error: 'Failed to delete user' });
}
});

[Link] = router;

Key Changes from Part 1:1. Added 'async/await' to all route handlers 2. Replaced array
methods with Prisma methods: - [Link]() -> [Link]() - [Link]() ->
[Link]() - [Link]() -> [Link]() 3. Added error handling for
database errors 4. Data now persists in PostgreSQL!

16 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 9: Adding Posts with Relationships

Let's add Posts that belong to Users (One-to-Many relationship).

Update the Schema

Add the Post model to prisma/[Link]:

// File: prisma/[Link]
// Add this AFTER the User model:

model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
createdAt DateTime @default(now())

// Relationship: Post belongs to one User

authorId Int
author User @relation(fields: [authorId], references: [id])

@@map("posts")
}
Also update the User model to include the reverse relationship:

// File: prisma/[Link]
// Update the User model to add posts relation:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
role String @default("student")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
posts Post[] // A user can have many posts

@@map("users")
}

Run Migration for Posts

# Create migration for the new Post table
npx prisma migrate dev --name add_posts

# Or:
npm exec prisma migrate dev --name add_posts

Create Posts Routes

Create a new file routes/[Link]:

// File: routes/[Link]
const express = require('express');
const router = [Link]();

17 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

const prisma = require('../lib/prisma');

// GET /api/posts - List all posts

[Link]('/', async (req, res) => {
try {
const { published, authorId } = [Link];

const where = {};

if (published !== undefined) {
[Link] = published === 'true';
}
if (authorId) [Link] = parseInt(authorId);

const posts = await [Link]({

where,
include: {
author: { select: { id: true, name: true } }
},
orderBy: { createdAt: 'desc' }
});

[Link]({ count: [Link], data: posts });

} catch (error) {
[Link](500).json({ error: 'Failed to fetch posts' });
}
});
// File: routes/[Link] (continued)
// POST /api/posts - Create new post
[Link]('/', async (req, res) => {
try {
const { title, content, authorId } = [Link];

if (!title || !authorId) {
return [Link](400).json({
error: 'Title and authorId are required'
});
}

const post = await [Link]({

data: {
title,
content,
authorId: parseInt(authorId)
},
include: {
author: { select: { id: true, name: true } }
}
});

[Link](201).json(post);
} catch (error) {
[Link](500).json({ error: 'Failed to create post' });
}
});
// File: routes/[Link] (continued)
// PUT /api/posts/:id/publish - Publish a post
[Link]('/:id/publish', async (req, res) => {

18 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

try {
const postId = parseInt([Link]);

const post = await [Link]({

where: { id: postId },
data: { published: true },
include: {
author: { select: { id: true, name: true } }
}
});

[Link](post);
} catch (error) {
[Link](404).json({ error: 'Post not found' });
}
});

[Link] = router;

Update [Link]
Add the posts routes to your main [Link]:

// File: [Link]
// Add near the top with other requires:
const postRoutes = require('./routes/posts');

// Add after [Link]('/api/users', userRoutes):

[Link]('/api/posts', postRoutes);

19 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 10: Error Handling

Create a centralized error handler for your application.

Create Error Handler

Create middleware/[Link]:

// File: middleware/[Link]

// Map Prisma error codes to HTTP responses

const PRISMA_ERRORS = {
P2002: { status: 409, message: 'Email already exists' },
P2025: { status: 404, message: 'Record not found' }
};

function errorHandler(err, req, res, next) {

[Link]('Error:', err);

// Check if it's a known Prisma error

if ([Link] && PRISMA_ERRORS[[Link]]) {
const { status, message } = PRISMA_ERRORS[[Link]];
return [Link](status).json({ error: message });
}

// Default server error

[Link](500).json({ error: 'Internal Server Error' });
}

[Link] = errorHandler;
Add it to [Link] (must be at the END, before [Link]):

// File: [Link]
// Add at the top with other requires:
const errorHandler = require('./middleware/errorHandler');

// ... all your routes ...

// 404 handler - for unknown routes

[Link]((req, res) => {
[Link](404).json({ error: 'Endpoint not found' });
});

// Global error handler - MUST be last!

[Link](errorHandler);

[Link](PORT, () => {
[Link](`Server running on [Link]
});

20 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

STEP 11: Testing with Postman/HTTPie

Option A: HTTPie (Command Line)
HTTPie is a user-friendly command-line HTTP client.

Install HTTPie
# Windows (requires Python pip)
pip install httpie

# macOS
brew install httpie

# Ubuntu/Debian
sudo apt install httpie

Test Commands
# ========== USERS ==========

# Get all users

http GET localhost:3000/api/users

# Filter by role
http GET localhost:3000/api/users role==student

# Search by name
http GET localhost:3000/api/users search==alice

# Create a user
http POST localhost:3000/api/users \
email="alice@[Link]" \
name="Alice" \
role="student"

# Update a user
http PUT localhost:3000/api/users/1 \
name="Alice Updated"

# Delete a user
http DELETE localhost:3000/api/users/1
# ========== POSTS ==========

# Get all posts

http GET localhost:3000/api/posts

# Get only published posts

http GET localhost:3000/api/posts published==true

# Get posts by author

http GET localhost:3000/api/posts authorId==1

# Create a post (authorId must exist)

http POST localhost:3000/api/posts \
title="My First Post" \

21 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

content="Hello World!" \
authorId:=1

# Publish a post
http PUT localhost:3000/api/posts/1/publish

Option B: Postman (GUI)

40. Download from [Link]

41. Create a free account and sign in

42. Click 'Collections' then '+' to create new collection

43. Name it 'Backend Lab API'

Set Up Environment Variables

1. Click the gear icon (Manage Environments)
2. Click 'Add'
3. Name: 'Local Development'
4. Add variable:
Variable: baseUrl
Initial Value: [Link]
Current Value: [Link]
5. Click 'Save'

Create Requests

Request Method URL Body

Name

Get All Users GET {{baseUrl}}/api/users -

{ "email": "test@[Link]", "name":

Create User POST {{baseUrl}}/api/users
"Test" }

Get User GET {{baseUrl}}/api/users/1 -

Update User PUT {{baseUrl}}/api/users/1 { "name": "Updated" }

Delete User DELETE {{baseUrl}}/api/users/1 -

Get Posts GET {{baseUrl}}/api/posts -

Create Post POST {{baseUrl}}/api/posts { "title": "New", "authorId": 1 }

Publish Post PUT {{baseUrl}}/api/posts/1/publish -

22 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

Troubleshooting Guide
Common Errors and Solutions

Error: 'psql' is not recognized

PostgreSQL is not in your system PATH.

# Windows: Add to PATH

# 1. Find PostgreSQL bin folder (e.g., C:\Program Files\PostgreSQL\15\bin)
# 2. Add it to your system PATH environment variable
# 3. Restart your terminal

# Alternative: Use full path

"C:\Program Files\PostgreSQL\15\bin\psql" --version

Error: 'password authentication failed'

# Make sure you're using the correct password
# The default postgres user password is what you set during installation

# Reset postgres password (Linux/Mac):

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Error: 'database backend_lab_db does not exist'

# Create the database:
sudo -u postgres psql -c "CREATE DATABASE backend_lab_db;"

# Or in psql:
CREATE DATABASE backend_lab_db;

Error: 'P1001: Can't reach database server'

# PostgreSQL is not running. Start it:

# macOS:
brew services start postgresql@15

# Linux:
sudo systemctl start postgresql

# Windows: Open Services, find PostgreSQL, click Start

Error: 'connect ECONNREFUSED [Link]:5432'

# Check if PostgreSQL is running on port 5432

# Check your DATABASE_URL in .env file:

# - Is the password correct?
# - Is the database name correct?
# - Is the port correct?

23 / 24
 NodeJS + PostgreSQL + Prisma Tutorial

Final Project Structure

backend-lab/
├── node_modules/
├── prisma/
│ ├── migrations/ # Database migration files
│ │ └── 20240101000000_init/
│ │ └── [Link]
│ └── [Link] # Database schema definition
├── routes/
│ ├── [Link] # User CRUD routes (DATABASE)
│ └── [Link] # Post routes with relationships
├── lib/
│ └── [Link] # Prisma client instance
├── middleware/
│ └── [Link] # Global error handler
├── .env # Environment variables (DON'T COMMIT!)
├── .gitignore # Git ignore file
├── [Link] # Main Express application
└── [Link] # Dependencies

Summary
Congratulations! You have:

• Installed PostgreSQL database server

• Learned to use GUI tools (pgAdmin/DBeaver)

• Created a database and user

• Connected your Express app to PostgreSQL

• Used Prisma ORM for database operations

• Built relationships between tables

• Tested your API with HTTPie and Postman

Next Steps:1. Add authentication with JWT tokens 2. Implement pagination for large
datasets 3. Add input validation with Joi or Zod 4. Write unit tests with Jest 5. Deploy to
Railway, Render, or Heroku

24 / 24