Prisma ORM

// ===== Install =====
npm install prisma --save-dev
npm install @prisma/client
npx prisma init

// ===== Schema (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?
  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])

  @@index([authorId, published])
}

// ===== Migrate =====
npx prisma migrate dev --name init       // creates SQL migration + applies it
npx prisma migrate deploy                // production: apply pending migrations
npx prisma db push                        // dev only: sync without migration

// ===== Generate client =====
npx prisma generate
// Generates types in node_modules/.prisma/client

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

// Create
const user = await prisma.user.create({
  data: { email: 'a@x.io', name: 'Alex', posts: { create: [{ title: 'Hi' }] } },
});

// Read
const u = await prisma.user.findUnique({
  where: { email: 'a@x.io' },
  include: { posts: true },
});

const users = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
  orderBy: { createdAt: 'desc' },
  take: 20,
});

// Update
await prisma.post.update({
  where: { id: 1 },
  data: { published: true },
});

// Delete
await prisma.post.delete({ where: { id: 1 } });

// Transactions
const [u, p] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'b@x.io' } }),
  prisma.post.create({ data: { title: 'New', authorId: 1 } }),
]);

// Interactive transactions
await prisma.$transaction(async (tx) => {
  const u = await tx.user.create({ data: { email: 'c@x.io' } });
  await tx.post.create({ data: { title: 'X', authorId: u.id } });
});

// Raw SQL (when you need it)
const result = await prisma.$queryRaw\`SELECT * FROM "User" WHERE email = ${email}\`;

// ===== Connection pooling =====
// Long-lived processes: one PrismaClient per app
// Serverless: use PgBouncer or Prisma Accelerate

// In Next.js dev:
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// ===== Prisma Studio =====
npx prisma studio
// Visual DB browser at http://localhost:5555

// ===== Patterns =====
// - Schema-first; migrations in source control
// - One PrismaClient per process
// - include/select for explicit field selection
// - Transactions for multi-write atomicity
// - Raw SQL for hot queries Prisma's planner does not nail

// ===== Pitfalls =====
// - Forgetting to run prisma generate after schema changes
// - Many PrismaClients in dev (hot reload) -> connection exhaustion
// - Select * by accident with include everywhere
// - Not using PgBouncer / Accelerate in serverless

// schema.prisma defines models; client is generated.
const users = await prisma.user.findMany({ where: { active: true } });
const u = await prisma.user.create({ data: { name: 'Ada' } });