Prisma ORM/Cheat sheet/PostgreSQL

Contents

Everything you need to manage PostgreSQL using Prisma ORM — from setup to queries, migrations, and performance tuning.
This is your all-in-one reference for building modern apps with TypeScript + PostgreSQL + Prisma. 🐘⚡

---

⚙️ Setup

Install Dependencies

npm install prisma --save-dev
npm install @prisma/client

Initialize Prisma

npx prisma init

This creates:

project/
 ├─ prisma/
 │   └─ schema.prisma
 ├─ .env

Example .env

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

---

🧱 Prisma Schema (schema.prisma)

Basic Example

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?
  posts     Post[]
  createdAt DateTime @default(now())
}

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

Common Field Attributes

---

🔄 Migrations

Create Migration

npx prisma migrate dev --name init

Deploy Migration (production)

npx prisma migrate deploy

Reset Database

npx prisma migrate reset

Check DB Status

npx prisma migrate status

---

🧰 Prisma Client

Generate Client

npx prisma generate

Import Client (Node.js / TypeScript)

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

Disconnect

await prisma.$disconnect();

---

🧠 CRUD Operations

Create

const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
  },
});

Read (Find)

const allUsers = await prisma.user.findMany();
const oneUser = await prisma.user.findUnique({
  where: { id: 1 },
});

Update

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Wonderland' },
});

Delete

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

---

🔍 Query Filtering

const users = await prisma.user.findMany({
  where: {
    email: { contains: 'example.com' },
    name: { startsWith: 'A' },
  },
});

---

🧩 Relations

Create Related Data

const post = await prisma.post.create({
  data: {
    title: 'My Post',
    author: {
      connect: { id: 1 },
    },
  },
});

Include Relations

const users = await prisma.user.findMany({
  include: { posts: true },
});

Nested Writes

const newUser = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    posts: {
      create: [{ title: 'Hello World' }, { title: 'Second Post' }],
    },
  },
});

Update Nested

await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true },
      },
    },
  },
});

---

🧮 Aggregations

const stats = await prisma.post.aggregate({
  _count: true,
  _avg: { id: true },
  _max: { createdAt: true },
});

Group By

const grouped = await prisma.post.groupBy({
  by: ['published'],
  _count: { _all: true },
});

---

🧾 Transactions

Basic

await prisma.$transaction([
  prisma.user.create({ data: { email: 'a@a.com' } }),
  prisma.post.create({ data: { title: 'New Post' } }),
]);

With Logic

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'b@b.com' } });
  await tx.post.create({ data: { title: 'Post', authorId: user.id } });
});

---

🧩 Raw SQL

Query Raw

const result = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = 'alice@example.com'`;

Execute Raw (non-select)

await prisma.$executeRaw`UPDATE "User" SET name = 'Bob' WHERE id = 1`;

⚠️ Use parameterized queries to prevent SQL injection:

const email = 'bob@example.com';
await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;

---

🧠 Filtering & Pagination

const posts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 20,
});

Example with cursor:

const nextPosts = await prisma.post.findMany({
  take: 5,
  skip: 1,
  cursor: { id: lastPost.id },
});

---

🧰 Utility Queries

---

🧮 Filtering JSON & Arrays (PostgreSQL Specific)

JSONB Field Example

model Event {
  id     Int    @id @default(autoincrement())
  meta   Json
}

Query:

await prisma.event.findMany({
  where: {
    meta: {
      path: ['type'],
      equals: 'conference',
    },
  },
});

Array Field Example

model Tag {
  id    Int     @id @default(autoincrement())
  names String[]
}

Query:

await prisma.tag.findMany({
  where: { names: { has: 'tech' } },
});

---

🧩 Enums & Defaults

enum Role {
  USER
  ADMIN
}

model User {
  id   Int   @id @default(autoincrement())
  role Role  @default(USER)
}

Usage:

await prisma.user.create({
  data: { email: 'a@a.com', role: 'ADMIN' },
});

---

🧭 Prisma Studio

Interactive GUI to explore your data.

npx prisma studio

Opens http://localhost:5555

---

🧾 Debugging & Logs

Enable logs in Prisma Client:

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

See raw SQL queries in your console while debugging.

---

⚡ Performance Tips

• Use indexes on frequently queried fields (@unique or @@index).
• Batch queries using Promise.all() or $transaction().
• Prefer findMany with pagination over huge unfiltered queries.
• Use connection pooling (e.g., with pgBouncer or Neon).
• Use Prisma Data Proxy for serverless environments.
• Cache frequent reads using Redis or in-memory store.

---

🧾 TL;DR Mind Map