Prisma Reference: Schema, CRUD, Relations, Raw Queries, Transactions & Error Handling

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

datasource db {
  provider = "postgresql"   // postgresql | mysql | sqlite | mongodb
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt         // auto-set on every update
  posts     Post[]                      // one-to-many relation
  profile   Profile?                    // one-to-one (optional)
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      Tag[]    @relation("PostTags")  // many-to-many
  @@index([authorId])                       // add index
}

// Migrations:
npx prisma migrate dev --name add_posts     // create + apply migration (dev)
npx prisma migrate deploy                   // apply migrations (CI/prod, no prompt)
npx prisma db push                          // push schema changes without migration file (prototyping)
npx prisma studio                           // open GUI to browse data

// Client:
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient({
  log: ["query", "warn", "error"],   // log queries in dev
});
// Singleton pattern for Next.js / serverless (avoid multiple instances):
// export const prisma = global.prisma ?? new PrismaClient();
// if (process.env.NODE_ENV !== "production") global.prisma = prisma;

// Create:
const user = await prisma.user.create({
  data: { name: "Alice", email: "alice@example.com" },
  select: { id: true, name: true, email: true },  // only return these fields
});

// findMany with filtering:
const users = await prisma.user.findMany({
  where: {
    email: { contains: "@example.com" },
    createdAt: { gte: new Date("2026-01-01") },
    posts: { some: { published: true } },   // has at least one published post
  },
  orderBy: { createdAt: "desc" },
  skip: 0,
  take: 20,
  include: { posts: { where: { published: true }, orderBy: { createdAt: "desc" } } },
});

// findUnique (throws if not found — use findFirst for "maybe"):
const user = await prisma.user.findUnique({ where: { email: "alice@example.com" } });
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } });

// Update:
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: "Alice Smith", updatedAt: new Date() },
});

// Upsert (create or update):
const user = await prisma.user.upsert({
  where: { email: "alice@example.com" },
  update: { name: "Alice" },
  create: { email: "alice@example.com", name: "Alice" },
});

// Delete:
await prisma.user.delete({ where: { id: 1 } });
await prisma.user.deleteMany({ where: { createdAt: { lt: new Date("2025-01-01") } } });

// Count:
const count = await prisma.user.count({ where: { posts: { some: {} } } });

// include: eager load relations (generates JOIN or extra SELECT):
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 5,
      select: { id: true, title: true },  // only post fields needed
    },
    profile: true,
  },
});

// select vs include (can't use both on same level):
const user = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: { select: { posts: true } },  // count of relations
  },
});

// Nested create (create user + posts in one call):
await prisma.user.create({
  data: {
    name: "Bob",
    email: "bob@example.com",
    posts: {
      create: [
        { title: "First post" },
        { title: "Second post", published: true },
      ],
    },
  },
});

// Connect existing records (many-to-many):
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: { connect: [{ id: 5 }, { id: 6 }] },   // add tags
    // disconnect: [{ id: 3 }]                     // remove tag
    // set: [{ id: 5 }]                            // replace all tags
  },
});

// $queryRaw: use when Prisma's query API isn't enough:
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) AS post_count
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  WHERE u."createdAt" > ${new Date("2026-01-01")}
  GROUP BY u.id, u.name
  ORDER BY post_count DESC
`;
// Template literals are parameterized — safe from SQL injection
// Don't use $queryRawUnsafe with user input

// groupBy + aggregate:
const stats = await prisma.post.groupBy({
  by: ["authorId"],
  _count: { id: true },
  _avg: { viewCount: true },
  having: { viewCount: { _avg: { gt: 100 } } },
  orderBy: { _count: { id: "desc" } },
});

// Prisma middleware (runs on every query):
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  if (after - before > 500) {
    console.warn(`Slow query: ${params.model}.${params.action} took ${after - before}ms`);
  }
  return result;
});

// Soft-delete pattern with middleware:
prisma.$use(async (params, next) => {
  if (params.model === "Post" && params.action === "delete") {
    return next({ ...params, action: "update", args: { ...params.args, data: { deletedAt: new Date() } } });
  }
  if (params.action === "findMany") {
    params.args.where = { ...params.args?.where, deletedAt: null };
  }
  return next(params);
});

// Transaction (all succeed or all rollback):
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { name: "Alice", email: "alice@example.com" } }),
  prisma.post.create({ data: { title: "Hello", authorId: 1 } }),
]);

// Interactive transaction (with logic between queries):
await prisma.$transaction(async (tx) => {
  const from = await tx.account.findUniqueOrThrow({ where: { id: fromId } });
  if (from.balance < amount) throw new Error("Insufficient funds");
  await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
  await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
});

// Error handling:
import { PrismaClientKnownRequestError } from "@prisma/client/runtime/library";

try {
  await prisma.user.create({ data: { email: "dupe@example.com" } });
} catch (e) {
  if (e instanceof PrismaClientKnownRequestError) {
    if (e.code === "P2002") {
      throw new Error(`Unique constraint violation on: ${e.meta?.target}`);
    }
    if (e.code === "P2025") {
      throw new Error("Record not found");
    }
  }
  throw e;
}
// Common error codes: P2002 unique, P2003 FK violation, P2025 not found

// Connection pooling (serverless — pgBouncer or Prisma Accelerate):
// DATABASE_URL="postgresql://...?pgbouncer=true&connection_limit=1"
// Or: Prisma Accelerate (edge-optimized connection pooler + global cache)

// Always disconnect in scripts:
prisma.$disconnect();