Akhilesh

Posted on Feb 15

Complete Guide to PostgreSQL and Prisma ORM for Modern Web Development

#postgres #prisma #database #orm

This guide covers everything you need to know about PostgreSQL database and Prisma ORM for building modern web applications. Written in simple, professional language with practical examples you can use in real projects.

PostgreSQL is a powerful, open-source relational database. Prisma is a modern ORM (Object-Relational Mapping) tool that makes working with databases easier and safer. Together, they provide a professional solution for data management in your applications.

Understanding Databases and ORMs
PostgreSQL Fundamentals
SQL Basics for PostgreSQL
Database Design and Relationships
Prisma ORM Introduction
Setting Up Prisma
Prisma Schema
CRUD Operations with Prisma
Prisma Relationships
Advanced Prisma Queries
Migrations with Prisma
Database Seeding
Prisma in Production
Complete Project Example

1. Understanding Databases and ORMs

What is a Database?
A database is an organized collection of data stored electronically. Instead of saving data in files, we use databases because they provide:

Structure: Data is organized in tables with rows and columns
Relationships: Connect related data together
Query capability: Find and filter data quickly
Concurrent access: Multiple users can access simultaneously
Data integrity: Ensures data accuracy and consistency
Security: Control who can access what data

What is an ORM?
ORM stands for Object-Relational Mapping. It is a programming technique that converts data between incompatible systems (your code and the database).

Without ORM (Raw SQL):

const result = await db.query('SELECT * FROM users WHERE email = $1', [email]);
const user = result.rows[0];

With ORM (Prisma):

const user = await prisma.user.findUnique({
    where: { email: email }
});

Benefits of using an ORM:

Write database queries using your programming language
Type safety (catches errors before runtime)
Automatic SQL generation
Database migrations made easy
Prevents SQL injection attacks
Works with multiple databases with minimal code changes

2. PostgreSQL Fundamentals

What is PostgreSQL?
PostgreSQL (often called Postgres) is a free, open-source relational database management system. It has been in active development for over 30 years and is known for reliability and performance.
Why Choose PostgreSQL?

Open Source: Free to use, no licensing costs
ACID Compliant: Ensures data reliability (Atomicity, Consistency, Isolation, Durability)
Advanced Features: Supports JSON, full-text search, geospatial data
Scalable: Handles small to very large databases
Extensible: Add custom functions and data types
Strong Community: Excellent documentation and support
Cross-Platform: Works on Windows, macOS, Linux

PostgreSQL vs Other Databases

Feature	PostgreSQL	MySQL	MongoDB
Type	Relational	Relational	NoSQL
ACID Compliance	Yes	Yes	Limited
JSON Support	Excellent	Basic	Native
Complex Queries	Excellent	Good	Limited
Licensing	Open Source	Open Source	Open Source
Use Case	General purpose	Web applications	Document storage

Installing PostgreSQL
Installation on Different Platforms
Windows:

Download PostgreSQL installer from official website (postgresql.org)
Run the installer
Set password for postgres user
Keep default port (5432)
Finish installation

# Using Homebrew
brew install postgresql@15
brew services start postgresql@15

# Or download Postgres.app (GUI application)

Linux (Ubuntu/Debian):

sudo apt update
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Docker (Recommended for Development):

# Pull PostgreSQL image
docker pull postgres:15

# Run PostgreSQL container
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:15

# Check if running
docker ps

Accessing PostgreSQL
Using psql (Command Line):

# Connect to PostgreSQL
psql -U postgres

# Connect to specific database
psql -U myuser -d mydb

# Common psql commands
\l              # List all databases
\c mydb         # Connect to database
\dt             # List all tables
\d users        # Describe table structure
\q              # Quit psql

Using GUI Tools:

pgAdmin (Official GUI tool)

Download from pgadmin.org
Cross-platform
Feature-rich

DBeaver (Universal database tool)

Free and open source
Supports multiple databases
Good for beginners

TablePlus (Modern GUI)

Clean interface
Fast and lightweight
macOS, Windows, Linux

3. SQL Basics for PostgreSQL

Creating Databases and Tables

-- Create a new database
CREATE DATABASE my_application;

-- Connect to the database
\c my_application

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create another table with foreign key
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    published BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Types in PostgreSQL

-- Numeric types
INTEGER             -- Whole numbers
BIGINT              -- Large whole numbers
DECIMAL(10, 2)      -- Exact decimal (10 digits, 2 after decimal)
NUMERIC(10, 2)      -- Same as DECIMAL
REAL                -- Floating point
DOUBLE PRECISION    -- Double precision floating point
SERIAL              -- Auto-incrementing integer
BIGSERIAL           -- Auto-incrementing big integer

-- String types
VARCHAR(n)          -- Variable length string with limit
TEXT                -- Unlimited length string
CHAR(n)             -- Fixed length string

-- Date and time
DATE                -- Date only (2024-01-15)
TIME                -- Time only (14:30:00)
TIMESTAMP           -- Date and time
TIMESTAMPTZ         -- Timestamp with timezone

-- Boolean
BOOLEAN             -- true or false

-- JSON
JSON                -- JSON data
JSONB               -- Binary JSON (faster, recommended)

-- Arrays
INTEGER[]           -- Array of integers
TEXT[]              -- Array of text

CRUD Operations in SQL

-- CREATE (Insert data)
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- Insert multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Jane Smith', 'jane@example.com', 25),
    ('Bob Johnson', 'bob@example.com', 35),
    ('Alice Brown', 'alice@example.com', 28);

-- READ (Select data)
-- Get all users
SELECT * FROM users;

-- Get specific columns
SELECT name, email FROM users;

-- Get with condition
SELECT * FROM users WHERE age > 25;

-- Get with multiple conditions
SELECT * FROM users 
WHERE age > 25 AND is_active = true;

-- Get with pattern matching
SELECT * FROM users 
WHERE email LIKE '%@example.com';

-- Order results
SELECT * FROM users 
ORDER BY created_at DESC;

-- Limit results
SELECT * FROM users 
LIMIT 10 OFFSET 20;  -- Skip 20, get next 10

-- Count records
SELECT COUNT(*) FROM users;

-- Get unique values
SELECT DISTINCT age FROM users;

-- UPDATE (Modify data)
UPDATE users 
SET age = 31, is_active = false 
WHERE email = 'john@example.com';

-- Update multiple rows
UPDATE users 
SET is_active = true 
WHERE age > 25;

-- DELETE (Remove data)
DELETE FROM users 
WHERE email = 'john@example.com';

-- Delete with condition
DELETE FROM users 
WHERE is_active = false;

-- Delete all records (careful!)
DELETE FROM users;

Joins and Relationships

-- INNER JOIN (returns matching records from both tables)
SELECT users.name, posts.title 
FROM users
INNER JOIN posts ON users.id = posts.user_id;

-- LEFT JOIN (returns all from left table, matching from right)
SELECT users.name, posts.title 
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

-- Count posts per user
SELECT users.name, COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.name;

-- Get users with their posts
SELECT 
    users.name,
    users.email,
    posts.title,
    posts.published
FROM users
INNER JOIN posts ON users.id = posts.user_id
WHERE posts.published = true
ORDER BY posts.created_at DESC;

Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE is_active = true;

-- SUM
SELECT SUM(age) FROM users;

-- AVG
SELECT AVG(age) FROM users;

-- MIN and MAX
SELECT MIN(age), MAX(age) FROM users;

-- GROUP BY
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
ORDER BY count DESC;

-- HAVING (filter grouped results)
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;

Advanced Queries

-- Subqueries
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM posts WHERE published = true
);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.user_id = u.id AND p.published = true
);

-- CASE statement
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END as age_group
FROM users;

-- Common Table Expressions (CTE)
WITH active_users AS (
    SELECT * FROM users WHERE is_active = true
)
SELECT * FROM active_users WHERE age > 25;

-- Window functions
SELECT 
    name,
    age,
    RANK() OVER (ORDER BY age DESC) as age_rank
FROM users;

4. Database Design and Relationships

Database Normalization
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
First Normal Form (1NF):

Each column contains atomic (indivisible) values
Each column contains values of a single type
Each column has a unique name
Order of rows does not matter

-- Bad (not 1NF)
CREATE TABLE users (
    id INTEGER,
    name VARCHAR(100),
    phones VARCHAR(255)  -- Multiple phone numbers in one field
);

-- Good (1NF)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_phones (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20)
);

Second Normal Form (2NF):

Must be in 1NF
All non-key columns are fully dependent on the primary key

Third Normal Form (3NF):

Must be in 2NF
No transitive dependencies (non-key columns depend only on primary key)

Relationship Types
One-to-One (1:1)
Each record in table A relates to exactly one record in table B.

-- Example: User and UserProfile
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(255)
);

One-to-Many (1:N)
Each record in table A can relate to multiple records in table B.

-- Example: User has many Posts
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(255),
    content TEXT
);

Many-to-Many (N:M)
Records in table A can relate to multiple records in table B and vice versa. Requires a junction table.

-- Example: Students and Courses
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- Junction table
CREATE TABLE student_courses (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(student_id, course_id)
);

Indexes for Performance
Indexes speed up data retrieval but slow down writes. Use them wisely.

-- Create index on frequently queried column
CREATE INDEX idx_users_email ON users(email);

-- Create index on foreign key
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite index (multiple columns)
CREATE INDEX idx_posts_user_published 
ON posts(user_id, published);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (only for specific rows)
CREATE INDEX idx_active_users 
ON users(email) WHERE is_active = true;

-- View all indexes
\di

-- Drop index
DROP INDEX idx_users_email;

Constraints

-- Primary Key
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

-- Foreign Key
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

-- Unique constraint
CREATE TABLE users (
    email VARCHAR(255) UNIQUE
);

-- Check constraint
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0)
);

-- Not null
CREATE TABLE users (
    name VARCHAR(100) NOT NULL
);

-- Default value
CREATE TABLE posts (
    published BOOLEAN DEFAULT false
);

5. Prisma ORM Introduction

What is Prisma?
Prisma is a next-generation ORM that consists of three main tools:

Prisma Client: Auto-generated query builder for Node.js and TypeScript
Prisma Migrate: Declarative data modeling and migration system
Prisma Studio: GUI to view and edit data in your database

Why Prisma?

Type Safety: Auto-completion and compile-time error checking
Auto-Generated: Client is generated from your schema
Developer Experience: Clean and intuitive API
Database Agnostic: Works with PostgreSQL, MySQL, SQLite, SQL Server, MongoDB
Migrations: Built-in migration tool
Relations: Easy to work with relationships
Performance: Optimized queries

Prisma vs Traditional ORMs:

Feature	Prisma	Sequelize	TypeORM
Type Safety	Excellent	Limited	Good
Learning Curve	Easy	Medium	Medium
Auto-completion	Yes	No	Partial
Migrations	Built-in	Separate	Built-in
Query Builder	Intuitive	Complex	Medium

6. Setting Up Prisma

Installation

# Initialize a new Node.js project
npm init -y

# Install Prisma as dev dependency
npm install -D prisma

# Install Prisma Client
npm install @prisma/client

# Initialize Prisma
npx prisma init

This creates:

prisma/schema.prisma - Your database schema
.env - Environment variables file

Environment Configuration

# .env file
DATABASE_URL="postgresql://username:password@localhost:5432/database_name"

# Example for local PostgreSQL
DATABASE_URL="postgresql://postgres:mypassword@localhost:5432/myapp"

# Example for Docker
DATABASE_URL="postgresql://myuser:mypassword@localhost:5432/mydb"

# Example for cloud (Heroku, Railway, etc.)
DATABASE_URL="postgresql://user:pass@host.provider.com:5432/dbname"

7. Prisma Schema

Understanding the Schema File

// prisma/schema.prisma

// Generator: Tells Prisma to generate the client
generator client {
  provider = "prisma-client-js"
}

// Datasource: Database connection
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Models: Represent database tables
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  age       Int?
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]   // Relation to posts
}

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

  user      User     @relation(fields: [userId], references: [id])
}

Field Types

model Example {
  // String types
  name        String
  email       String   @unique
  description String?  // Optional (nullable)

  // Number types
  age         Int
  price       Float
  bigNumber   BigInt

  // Boolean
  isActive    Boolean  @default(true)

  // Date and time
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  birthDate   DateTime

  // JSON
  metadata    Json

  // Enum
  role        Role     @default(USER)

  // ID types
  id          Int      @id @default(autoincrement())
  uuid        String   @id @default(uuid())
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Field Attributes

model User {
  // @id: Primary key
  id        Int      @id @default(autoincrement())

  // @unique: Unique constraint
  email     String   @unique

  // @default: Default value
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())

  // @updatedAt: Auto-update timestamp
  updatedAt DateTime @updatedAt

  // @db: Database-specific type
  bio       String   @db.Text

  // @map: Custom database column name
  firstName String   @map("first_name")

  // @@: Model-level attributes
  @@map("users")  // Custom table name
  @@index([email])  // Index
  @@unique([email, name])  // Composite unique
}

8. CRUD Operations with Prisma

Setting Up Prisma Client

// prisma/client.js
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],  // Optional: log queries
});

module.exports = prisma;

Create Operations

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

async function createOperations() {
  // Create single record
  const user = await prisma.user.create({
    data: {
      email: 'john@example.com',
      name: 'John Doe',
      age: 30
    }
  });
  console.log('Created user:', user);

  // Create with relations
  const userWithPost = await prisma.user.create({
    data: {
      email: 'jane@example.com',
      name: 'Jane Smith',
      posts: {
        create: [
          { title: 'First Post', content: 'Hello World' },
          { title: 'Second Post', content: 'Learning Prisma' }
        ]
      }
    },
    include: {
      posts: true  // Include related posts in response
    }
  });
  console.log('Created user with posts:', userWithPost);

  // Create many records
  const users = await prisma.user.createMany({
    data: [
      { email: 'user1@example.com', name: 'User 1' },
      { email: 'user2@example.com', name: 'User 2' },
      { email: 'user3@example.com', name: 'User 3' }
    ],
    skipDuplicates: true  // Skip if email already exists
  });
  console.log(`Created ${users.count} users`);
}

Read Operations

async function readOperations() {
  // Find all records
  const allUsers = await prisma.user.findMany();
  console.log('All users:', allUsers);

  // Find with conditions
  const activeUsers = await prisma.user.findMany({
    where: {
      isActive: true,
      age: {
        gte: 18  // greater than or equal to
      }
    }
  });

  // Find one by unique field
  const user = await prisma.user.findUnique({
    where: {
      email: 'john@example.com'
    }
  });

  // Find first matching record
  const firstUser = await prisma.user.findFirst({
    where: {
      age: {
        gt: 25
      }
    },
    orderBy: {
      createdAt: 'desc'
    }
  });

  // Select specific fields
  const usersWithNameOnly = await prisma.user.findMany({
    select: {
      name: true,
      email: true
    }
  });

  // Include relations
  const usersWithPosts = await prisma.user.findMany({
    include: {
      posts: true
    }
  });

  // Include with conditions
  const usersWithPublishedPosts = await prisma.user.findMany({
    include: {
      posts: {
        where: {
          published: true
        }
      }
    }
  });

  // Pagination
  const page = 1;
  const pageSize = 10;
  const paginatedUsers = await prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: {
      createdAt: 'desc'
    }
  });

  // Count records
  const userCount = await prisma.user.count();
  const activeUserCount = await prisma.user.count({
    where: {
      isActive: true
    }
  });

  // Aggregate
  const stats = await prisma.user.aggregate({
    _count: true,
    _avg: {
      age: true
    },
    _max: {
      age: true
    },
    _min: {
      age: true
    }
  });
  console.log('User stats:', stats);
}

Update Operations

async function updateOperations() {
  // Update single record
  const updatedUser = await prisma.user.update({
    where: {
      email: 'john@example.com'
    },
    data: {
      age: 31,
      name: 'John Updated'
    }
  });

  // Update or create (upsert)
  const user = await prisma.user.upsert({
    where: {
      email: 'new@example.com'
    },
    update: {
      name: 'Updated Name'
    },
    create: {
      email: 'new@example.com',
      name: 'New User'
    }
  });

  // Update many records
  const result = await prisma.user.updateMany({
    where: {
      age: {
        lt: 18
      }
    },
    data: {
      isActive: false
    }
  });
  console.log(`Updated ${result.count} users`);

  // Increment/Decrement
  const incrementedUser = await prisma.user.update({
    where: {
      id: 1
    },
    data: {
      age: {
        increment: 1
      }
    }
  });
}

Delete Operations

async function deleteOperations() {
  // Delete single record
  const deletedUser = await prisma.user.delete({
    where: {
      email: 'john@example.com'
    }
  });

  // Delete many records
  const result = await prisma.user.deleteMany({
    where: {
      isActive: false
    }
  });
  console.log(`Deleted ${result.count} users`);

  // Delete all records
  const deleteAll = await prisma.user.deleteMany({});
  console.log(`Deleted all ${deleteAll.count} users`);
}

9. Prisma Relationships

One-to-One Relationship

// schema.prisma
model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id])
}

// Create user with profile
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    profile: {
      create: {
        bio: 'Software developer'
      }
    }
  },
  include: {
    profile: true
  }
});

// Get user with profile
const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true }
});

One-to-Many Relationship

// schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  userId Int
  user   User   @relation(fields: [userId], references: [id])
}

// Create user with posts
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    posts: {
      create: [
        { title: 'First Post' },
        { title: 'Second Post' }
      ]
    }
  },
  include: {
    posts: true
  }
});

// Add post to existing user
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    user: {
      connect: { id: 1 }
    }
  }
});

// Get user with posts
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { 
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' }
    }
  }
});

Many-to-Many Relationship

// schema.prisma
model Student {
  id      Int      @id @default(autoincrement())
  name    String
  courses CourseEnrollment[]
}

model Course {
  id       Int      @id @default(autoincrement())
  name     String
  students CourseEnrollment[]
}

model CourseEnrollment {
  id         Int      @id @default(autoincrement())
  studentId  Int
  courseId   Int
  enrolledAt DateTime @default(now())

  student    Student  @relation(fields: [studentId], references: [id])
  course     Course   @relation(fields: [courseId], references: [id])

  @@unique([studentId, courseId])
}

// Enroll student in courses
const student = await prisma.student.create({
  data: {
    name: 'John Doe',
    courses: {
      create: [
        { course: { connect: { id: 1 } } },
        { course: { connect: { id: 2 } } }
      ]
    }
  },
  include: {
    courses: {
      include: {
        course: true
      }
    }
  }
});

// Get student with courses
const studentWithCourses = await prisma.student.findUnique({
  where: { id: 1 },
  include: {
    courses: {
      include: {
        course: true
      }
    }
  }
});

10. Advanced Prisma Queries

Filtering

// Equals
const users = await prisma.user.findMany({
  where: {
    name: 'John Doe'
  }
});

// Not equals
const users = await prisma.user.findMany({
  where: {
    name: {
      not: 'John Doe'
    }
  }
});

// In array
const users = await prisma.user.findMany({
  where: {
    name: {
      in: ['John', 'Jane', 'Bob']
    }
  }
});

// Not in array
const users = await prisma.user.findMany({
  where: {
    name: {
      notIn: ['Spam', 'Test']
    }
  }
});

// Less than / Greater than
const users = await prisma.user.findMany({
  where: {
    age: {
      lt: 30,    // less than
      lte: 30,   // less than or equal
      gt: 18,    // greater than
      gte: 18    // greater than or equal
    }
  }
});

// Contains (case-sensitive)
const users = await prisma.user.findMany({
  where: {
    email: {
      contains: '@gmail.com'
    }
  }
});

// Starts with / Ends with
const users = await prisma.user.findMany({
  where: {
    name: {
      startsWith: 'John',
      endsWith: 'Doe'
    }
  }
});

// AND condition
const users = await prisma.user.findMany({
  where: {
    AND: [
      { age: { gte: 18 } },
      { isActive: true }
    ]
  }
});

// OR condition
const users = await prisma.user.findMany({
  where: {
    OR: [
      { age: { lt: 18 } },
      { age: { gt: 65 } }
    ]
  }
});

// NOT condition
const users = await prisma.user.findMany({
  where: {
    NOT: {
      email: {
        contains: '@spam.com'
      }
    }
  }
});

// Complex conditions
const users = await prisma.user.findMany({
  where: {
    AND: [
      {
        OR: [
          { age: { lt: 18 } },
          { age: { gt: 65 } }
        ]
      },
      { isActive: true }
    ]
  }
});

Sorting

// Sort by single field
const users = await prisma.user.findMany({
  orderBy: {
    createdAt: 'desc'
  }
});

// Sort by multiple fields
const users = await prisma.user.findMany({
  orderBy: [
    { isActive: 'desc' },
    { name: 'asc' }
  ]
});

// Sort by related field
const posts = await prisma.post.findMany({
  orderBy: {
    user: {
      name: 'asc'
    }
  }
});

Grouping and Aggregation

// Group by
const usersByAge = await prisma.user.groupBy({
  by: ['age'],
  _count: {
    id: true
  },
  _avg: {
    age: true
  },
  orderBy: {
    age: 'asc'
  }
});

// Having clause
const activeUsersByAge = await prisma.user.groupBy({
  by: ['age'],
  _count: {
    id: true
  },
  having: {
    age: {
      gt: 18
    }
  }
});

Transactions

// Sequential transactions
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: 'john@example.com', name: 'John' }
  }),
  prisma.post.create({
    data: { title: 'First Post', userId: 1 }
  })
]);

// Interactive transactions
const result = await prisma.$transaction(async (prisma) => {
  const user = await prisma.user.create({
    data: { email: 'john@example.com', name: 'John' }
  });

  const post = await prisma.post.create({
    data: {
      title: 'First Post',
      userId: user.id
    }
  });

  return { user, post };
});

// Rollback on error
try {
  await prisma.$transaction(async (prisma) => {
    await prisma.user.create({
      data: { email: 'test@example.com', name: 'Test' }
    });

    throw new Error('Rollback transaction');

    await prisma.post.create({
      data: { title: 'Post', userId: 1 }
    });
  });
} catch (error) {
  console.log('Transaction rolled back');
}

Raw Queries

// Raw SQL query
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE age > ${18}
`;

// Raw SQL with type safety
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users WHERE email LIKE ${'%@gmail.com'}
`;

// Execute raw SQL
await prisma.$executeRaw`
  UPDATE users SET is_active = true WHERE age > ${18}
`;

11. Migrations with Prisma

Creating Migrations

# Create a migration
npx prisma migrate dev --name init

# This will:
# 1. Create SQL migration file
# 2. Apply migration to database
# 3. Generate Prisma Client

# Create migration without applying
npx prisma migrate dev --create-only

# Apply pending migrations
npx prisma migrate deploy

# Reset database (delete all data and migrations)
npx prisma migrate reset

Migration Workflow

# 1. Modify your schema.prisma file
# For example, add a new field:
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  phone     String?  // New field
}

# 2. Create and apply migration
npx prisma migrate dev --name add_phone_to_user

# 3. Generated migration file (prisma/migrations/XXX_add_phone_to_user/migration.sql)
-- AlterTable
ALTER TABLE "User" ADD COLUMN "phone" TEXT;

Migration Best Practices

# Always name your migrations descriptively
npx prisma migrate dev --name add_user_role
npx prisma migrate dev --name create_posts_table
npx prisma migrate dev --name add_published_to_posts

# Check migration status
npx prisma migrate status

# Create baseline migration for existing database
npx prisma migrate resolve --applied "migration_name"

12. Database Seeding

Creating Seed File

// prisma/seed.js
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  console.log('Start seeding...');

  // Delete existing data
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();

  // Create users
  const user1 = await prisma.user.create({
    data: {
      email: 'john@example.com',
      name: 'John Doe',
      age: 30,
      posts: {
        create: [
          {
            title: 'Getting Started with Prisma',
            content: 'Prisma is amazing...',
            published: true
          },
          {
            title: 'Advanced Prisma Techniques',
            content: 'Learn about relations...',
            published: false
          }
        ]
      }
    }
  });

  const user2 = await prisma.user.create({
    data: {
      email: 'jane@example.com',
      name: 'Jane Smith',
      age: 25,
      posts: {
        create: [
          {
            title: 'My First Post',
            content: 'Hello World',
            published: true
          }
        ]
      }
    }
  });

  console.log('Seeding finished.');
  console.log({ user1, user2 });
}

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

Configure Seed Command

// package.json
{
  "name": "my-project",
  "scripts": {
    "seed": "node prisma/seed.js"
  },
  "prisma": {
    "seed": "node prisma/seed.js"
  }
}

# Run seed
npm run seed

# Or
npx prisma db seed

13. Prisma in Production

Environment Setup

# .env.production
DATABASE_URL="postgresql://prod_user:prod_pass@prod-host:5432/prod_db"
NODE_ENV="production"

# .env.development
DATABASE_URL="postgresql://dev_user:dev_pass@localhost:5432/dev_db"
NODE_ENV="development"

Connection Pooling

// prisma/client.js
const { PrismaClient } = require('@prisma/client');

let prisma;

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient({
    datasources: {
      db: {
        url: process.env.DATABASE_URL + '?connection_limit=10'
      }
    }
  });
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient({
      log: ['query', 'info', 'warn', 'error']
    });
  }
  prisma = global.prisma;
}

module.exports = prisma;

Error Handling

const { PrismaClientKnownRequestError } = require('@prisma/client/runtime');

async function createUser(data) {
  try {
    const user = await prisma.user.create({
      data
    });
    return user;
  } catch (error) {
    if (error instanceof PrismaClientKnownRequestError) {
      // Unique constraint violation
      if (error.code === 'P2002') {
        throw new Error('Email already exists');
      }

      // Foreign key constraint violation
      if (error.code === 'P2003') {
        throw new Error('Related record not found');
      }

      // Record not found
      if (error.code === 'P2025') {
        throw new Error('Record not found');
      }
    }

    throw error;
  }
}

Performance Tips

// 1. Use select to fetch only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true
  }
});

// 2. Use pagination
const page = 1;
const pageSize = 20;
const users = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize
});

// 3. Use indexes (in schema)
model User {
  email String @unique

  @@index([createdAt])
  @@index([age, isActive])
}

// 4. Batch queries
const users = await prisma.user.findMany({
  include: {
    posts: true
  }
});
// Better than individual queries for each user

// 5. Use transactions for related operations
const result = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.create({ data: postData })
]);

14. Complete Project Example

Project Structure

my-app/
├── prisma/
│   ├── schema.prisma
│   ├── seed.js
│   └── migrations/
├── src/
│   ├── config/
│   │   └── database.js
│   ├── controllers/
│   │   ├── userController.js
│   │   └── postController.js
│   ├── routes/
│   │   ├── userRoutes.js
│   │   └── postRoutes.js
│   ├── middleware/
│   │   └── errorHandler.js
│   └── app.js
├── .env
├── .gitignore
├── package.json
└── server.js

Schema Definition

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  age       Int?
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  profile   Profile?

  @@index([email])
  @@index([createdAt])
}

model Profile {
  id        Int      @id @default(autoincrement())
  bio       String?
  avatarUrl String?
  userId    Int      @unique

  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

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

  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  tags      Tag[]

  @@index([userId])
  @@index([published])
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

Database Configuration

// src/config/database.js
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'info', 'warn', 'error']
    : ['error']
});

// Graceful shutdown
process.on('SIGINT', async () => {
  await prisma.$disconnect();
  process.exit(0);
});

module.exports = prisma;

User Controller

// src/controllers/userController.js
const prisma = require('../config/database');

exports.getAllUsers = async (req, res, next) => {
  try {
    const { page = 1, limit = 10, search, role } = req.query;

    const where = {};

    if (search) {
      where.OR = [
        { name: { contains: search, mode: 'insensitive' } },
        { email: { contains: search, mode: 'insensitive' } }
      ];
    }

    if (role) {
      where.role = role;
    }

    const [users, total] = await Promise.all([
      prisma.user.findMany({
        where,
        skip: (page - 1) * limit,
        take: parseInt(limit),
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          isActive: true,
          createdAt: true,
          _count: {
            select: { posts: true }
          }
        },
        orderBy: {
          createdAt: 'desc'
        }
      }),
      prisma.user.count({ where })
    ]);

    res.json({
      users,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    next(error);
  }
};

exports.getUserById = async (req, res, next) => {
  try {
    const { id } = req.params;

    const user = await prisma.user.findUnique({
      where: { id: parseInt(id) },
      include: {
        profile: true,
        posts: {
          where: { published: true },
          orderBy: { createdAt: 'desc' },
          take: 5
        }
      }
    });

    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }

    res.json(user);
  } catch (error) {
    next(error);
  }
};

exports.createUser = async (req, res, next) => {
  try {
    const { email, name, age, bio } = req.body;

    const user = await prisma.user.create({
      data: {
        email,
        name,
        age,
        profile: bio ? {
          create: { bio }
        } : undefined
      },
      include: {
        profile: true
      }
    });

    res.status(201).json(user);
  } catch (error) {
    if (error.code === 'P2002') {
      return res.status(400).json({ error: 'Email already exists' });
    }
    next(error);
  }
};

exports.updateUser = async (req, res, next) => {
  try {
    const { id } = req.params;
    const { name, age, isActive, bio } = req.body;

    const user = await prisma.user.update({
      where: { id: parseInt(id) },
      data: {
        name,
        age,
        isActive,
        profile: bio ? {
          upsert: {
            create: { bio },
            update: { bio }
          }
        } : undefined
      },
      include: {
        profile: true
      }
    });

    res.json(user);
  } catch (error) {
    if (error.code === 'P2025') {
      return res.status(404).json({ error: 'User not found' });
    }
    next(error);
  }
};

exports.deleteUser = async (req, res, next) => {
  try {
    const { id } = req.params;

    await prisma.user.delete({
      where: { id: parseInt(id) }
    });

    res.json({ message: 'User deleted successfully' });
  } catch (error) {
    if (error.code === 'P2025') {
      return res.status(404).json({ error: 'User not found' });
    }
    next(error);
  }
};

Post Controller

// src/controllers/postController.js
const prisma = require('../config/database');

exports.getAllPosts = async (req, res, next) => {
  try {
    const { published, userId, tag } = req.query;

    const where = {};

    if (published !== undefined) {
      where.published = published === 'true';
    }

    if (userId) {
      where.userId = parseInt(userId);
    }

    if (tag) {
      where.tags = {
        some: {
          name: tag
        }
      };
    }

    const posts = await prisma.post.findMany({
      where,
      include: {
        user: {
          select: {
            id: true,
            name: true,
            email: true
          }
        },
        tags: true,
        _count: {
          select: { tags: true }
        }
      },
      orderBy: {
        createdAt: 'desc'
      }
    });

    res.json(posts);
  } catch (error) {
    next(error);
  }
};

exports.createPost = async (req, res, next) => {
  try {
    const { title, content, published, userId, tags } = req.body;

    const post = await prisma.post.create({
      data: {
        title,
        content,
        published,
        userId: parseInt(userId),
        tags: tags ? {
          connectOrCreate: tags.map(tag => ({
            where: { name: tag },
            create: { name: tag }
          }))
        } : undefined
      },
      include: {
        user: {
          select: {
            id: true,
            name: true
          }
        },
        tags: true
      }
    });

    res.status(201).json(post);
  } catch (error) {
    next(error);
  }
};

exports.updatePost = async (req, res, next) => {
  try {
    const { id } = req.params;
    const { title, content, published } = req.body;

    const post = await prisma.post.update({
      where: { id: parseInt(id) },
      data: {
        title,
        content,
        published
      },
      include: {
        tags: true
      }
    });

    res.json(post);
  } catch (error) {
    if (error.code === 'P2025') {
      return res.status(404).json({ error: 'Post not found' });
    }
    next(error);
  }
};

exports.incrementViewCount = async (req, res, next) => {
  try {
    const { id } = req.params;

    const post = await prisma.post.update({
      where: { id: parseInt(id) },
      data: {
        viewCount: {
          increment: 1
        }
      }
    });

    res.json(post);
  } catch (error) {
    next(error);
  }
};

Express Application

// src/app.js
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const morgan = require('morgan');

const userRoutes = require('./routes/userRoutes');
const postRoutes = require('./routes/postRoutes');
const errorHandler = require('./middleware/errorHandler');

const app = express();

// Middleware
app.use(helmet());
app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(morgan('dev'));

// Routes
app.use('/api/users', userRoutes);
app.use('/api/posts', postRoutes);

// Health check
app.get('/health', (req, res) => {
  res.json({ status: 'OK' });
});

// Error handling
app.use(errorHandler);

module.exports = app;

Conclusion
You now have a comprehensive understanding of PostgreSQL and Prisma ORM.

PostgreSQL and Prisma together provide a powerful, type-safe, and developer-friendly solution for database management in modern applications. The combination gives you professional-grade tools while maintaining simplicity and ease of use.

The skills covered in this guide are used in production applications by companies worldwide. Continue practicing by building real projects, exploring more advanced features, and always referring to the official documentation for the latest updates.

Remember that learning databases is a continuous journey. Start with the basics, build projects, encounter real problems, and solve them. Each challenge you overcome makes you a better developer.

Keep building, keep learning, and good luck with your development journey.

DEV Community