PostgreSQL & Prisma ORM

📖 Concept

PostgreSQL is the most advanced open-source relational database, and Prisma is a modern, type-safe ORM for Node.js/TypeScript that replaces traditional ORMs like Sequelize.

Why PostgreSQL?

ACID transactions for data integrity
Complex joins, aggregations, and window functions
JSON/JSONB columns for flexible data
Full-text search built-in
Time-tested reliability at scale

Why Prisma over Sequelize/TypeORM?

Feature	Prisma	Sequelize
Schema definition	`.prisma` file (declarative)	JavaScript models
Type safety	Auto-generated TypeScript types	Manual typing
Migrations	Auto-generated, versioned	Manual or auto
Query API	Intuitive, chainable	String-based in complex queries
Relations	Declarative in schema	Configured in models
Learning curve	Low	Medium-high

Prisma workflow:

Define schema in schema.prisma
Run npx prisma migrate dev to create/update database tables
Run npx prisma generate to generate the TypeScript client
Use PrismaClient in your app for type-safe queries

🏠 Real-world analogy: If MongoDB is a filing cabinet (flexible, documents), PostgreSQL is a spreadsheet application (structured, relational). Prisma is the smart assistant who writes your SQL queries for you and ensures every formula (type) is correct.

💻 Code Example

codeTap to expand ⛶

1// PostgreSQL with Prisma — Complete Guide
2
3// === schema.prisma ===
4// datasource db {
5//   provider = "postgresql"
6//   url      = env("DATABASE_URL")
7// }
8//
9// generator client {
10//   provider = "prisma-client-js"
11// }
12//
13// model User {
14//   id        Int       @id @default(autoincrement())
15//   email     String    @unique
16//   name      String
17//   password  String
18//   role      Role      @default(USER)
19//   posts     Post[]
20//   profile   Profile?
21//   createdAt DateTime  @default(now())
22//   updatedAt DateTime  @updatedAt
23//
24//   @@index([email])
25//   @@map("users")
26// }
27//
28// model Post {
29//   id        Int       @id @default(autoincrement())
30//   title     String
31//   content   String?
32//   published Boolean   @default(false)
33//   author    User      @relation(fields: [authorId], references: [id])
34//   authorId  Int
35//   tags      Tag[]
36//   createdAt DateTime  @default(now())
37//
38//   @@index([authorId])
39//   @@map("posts")
40// }
41//
42// model Profile {
43//   id     Int    @id @default(autoincrement())
44//   bio    String?
45//   avatar String?
46//   user   User   @relation(fields: [userId], references: [id])
47//   userId Int    @unique
48// }
49//
50// model Tag {
51//   id    Int    @id @default(autoincrement())
52//   name  String @unique
53//   posts Post[]
54// }
55//
56// enum Role {
57//   USER
58//   ADMIN
59//   MODERATOR
60// }
61
62// === src/db.js — Prisma Client Singleton ===
63const { PrismaClient } = require("@prisma/client");
64
65const prisma = new PrismaClient({
66  log: process.env.NODE_ENV === "development" ? ["query", "warn", "error"] : ["error"],
67});
68
69// Graceful shutdown
70process.on("beforeExit", async () => {
71  await prisma.$disconnect();
72});
73
74module.exports = prisma;
75
76// === Usage: CRUD Operations ===
77const prisma2 = require("./db");
78
79async function prismaExamples() {
80  // CREATE
81  const user = await prisma2.user.create({
82    data: {
83      name: "Alice",
84      email: "alice@example.com",
85      password: "hashed_password",
86      profile: {
87        create: { bio: "Node.js developer" },
88      },
89    },
90    include: { profile: true },
91  });
92
93  // READ with relations
94  const userWithPosts = await prisma2.user.findUnique({
95    where: { id: 1 },
96    include: {
97      posts: { where: { published: true }, orderBy: { createdAt: "desc" } },
98      profile: true,
99    },
100  });
101
102  // Pagination
103  const users = await prisma2.user.findMany({
104    where: { role: "USER" },
105    skip: 0,
106    take: 10,
107    orderBy: { createdAt: "desc" },
108    select: { id: true, name: true, email: true },
109  });
110
111  // UPDATE
112  const updated = await prisma2.user.update({
113    where: { id: 1 },
114    data: { name: "Alice Updated" },
115  });
116
117  // DELETE
118  await prisma2.user.delete({ where: { id: 1 } });
119
120  // Transaction
121  const [newUser, newPost] = await prisma2.$transaction([
122    prisma2.user.create({ data: { name: "Bob", email: "bob@example.com", password: "hash" } }),
123    prisma2.post.create({ data: { title: "Hello", content: "World", authorId: 1 } }),
124  ]);
125
126  // Aggregation
127  const stats = await prisma2.user.groupBy({
128    by: ["role"],
129    _count: { _all: true },
130    _avg: { id: true },
131  });
132
133  // Raw SQL (escape hatch)
134  const result = await prisma2.$queryRaw`
135    SELECT u.name, COUNT(p.id) as post_count
136    FROM users u
137    LEFT JOIN posts p ON p."authorId" = u.id
138    GROUP BY u.name
139    ORDER BY post_count DESC
140  `;
141}

🏋️ Practice Exercise

Exercises:

Create a Prisma schema for an e-commerce app with Users, Products, Orders, and OrderItems
Implement CRUD operations for all models with proper relation handling
Write a transaction that creates an order and updates product stock atomically
Set up Prisma migrations — create, modify, and roll back schema changes
Implement cursor-based pagination for a high-volume posts endpoint
Compare query performance: Prisma generated queries vs raw SQL for complex aggregations

⚠️ Common Mistakes

Not creating a Prisma Client singleton — instantiating new PrismaClient() per request exhausts database connections
Forgetting to call prisma.$disconnect() on shutdown — open connections prevent the process from exiting cleanly
Using N+1 queries — fetching users then looping to fetch each user's posts; use include for eager loading
Not running prisma generate after schema changes — the client won't have the updated types until regenerated
Ignoring migration drift — always use prisma migrate dev in development and prisma migrate deploy in production

💼 Interview Questions

🎤 Mock Interview

Mock interview is powered by AI for PostgreSQL & Prisma ORM. Login to unlock this feature.

Was this topic helpful?

← PreviousMongoDB & Mongoose Next →Redis & Caching Strategies